《PG迁移必读(四)：实现Sybase默认排序.aspx》出街后，收到不少同事的积极反馈，获益良多。本文将根据同事们反馈的问题，进一步深入探讨COLLATE的用法及其在实际应用中的注意事项。

问题重现及分析

最近，有同事在执行带有Where 子句的SQL时发现，在PostgreSQL（PG）和Sybase中得到的结果不一致，其实根本原因还是由于PG和Sybase分别使用了不同的Charset及Collation引起的。下面复现一下问题。

分别在PG和Sybase中建表：

CREATE TABLE t1 (   

    name varchar(10) NULL 

); 

插入以下数据：

INSERT INTO t1 values('aaa');

INSERT INTO t1 values(null); 

INSERT INTO t1 values('abc');

INSERT INTO t1 values('ABC'); 

INSERT INTO t1 values('TTT');

INSERT INTO t1 values('bbc');

然后执行以下查询，比较Sybase和PG的排序结果，会发现两边的结果是不一样的。

SELECT * FROM t1 WHERE name <'bbb';

Sybase	PG

可以看到，Sybase比PG会多出一条记录'TTT'，这是因为Sybase使用CP850字符集，采用类似ASCII的排序方案，大写字母是排在小写字母前面的，因此Sybase会判定'TTT'小于'bbb'。但PG使用Unicode字符集，默认采用unicode排序，该排序是先按字母顺序，再按大小写排序（参见《PG迁移必读(四)：实现Sybase默认排序.aspx》一文）。

解决方案

为了使PG的输出与Sybase一致，我们需要为比较的字段指定COLLATION。修改SQL如下：
SELECT * FROM t1 WHERE name <'bbb' COLLATE "POSIX"

在PG中执行修改后的SQL，结果与Sybase一致。

注意事项

虽然通过添加COLLATE "POSIX"可以解决排序问题，但我们要关注因引入COLLATE引起的问题，特别是涉及到索引时，需要特别注意。因为索引是基于默认排序规则创建的，指定了COLLATE “POSIX”后，已有的索引可能会失效，PG不再引用。

例如，在PG中创建表tcollate，并插入10万条数据：

CREATE TABLE tcollate(

    id serial,

    n1 varchar(100),

    n2 varchar(100)

);

并对字段n1做建立索引：
CREATE INDEX indext1 ON tcollate(n1);

现根据字段n1进行查找，比较使用和不使用COLLATE语句的执行计划：

不使用COLLATE语句	使用COLLATE语句
SELECT * FROM tcollate WHERE n1 BETWEEN 'aaa' AND 'abc'; 查看执行计划，会使用索引index1进行查找，整体耗时0.38ms	SELECT * FROM tcollate WHERE n1 COLLATE "POSIX" BETWEEN 'aaa' AND 'abc'; 查看执行计划，发现不会使用index1索引，整体耗时5.76ms

因此，添加COLLATE语句后，原有的索引将不会被使用。这是因为原索引是基于缺省的排序进行排序索引的，指定COLLATE后，排序方案已不一样，当然不能继续使用原索引了。如果还是希望能通过索引来提升指定COLLATE的SQL的执行效率，则要建立基于指定COLLATE的索引：

CREATE INDEX indext2 ON tcollate(n1 COLLATE "POSIX");

再次查看SELECT * FROM tcollate WHERE n1 COLLATE "POSIX" BETWEEN 'aaa' AND ‘abc’ 的执行计划，发现它已用上index2的索引了，效率也提升到0.29ms。

总结

COLLATE不仅适用于ORDER BY子句，也适用于WHERE子句中的字符字段比较。在使用COLLATE时，需要考虑索引失效的问题，并根据需要重建索引。

感谢您的阅读，期待您的反馈！