KingbaseES Collate排序规则对结果集的影响
背景
前端在客户现场遇到一个问题，模糊查询报错：error:invalid multibyte charactor for locale pg the server LC_TYPE locale is probably incompatible with the database encoding
经查询现场环境：server_encoding，client_encoding，操作系统字符集，终端工具字符集均为zh_CN.GBK
查询语句为：select ..from ...where t_name like '国家%';
分析
通过\l+查看对应数据库的 Collate为ci_x_icu , Ctype为zh_CN.GBK。
根据报错提示，我们有理由可看出ci_x_icu排序规则阻止了中文字符排序的输出。因为查到的操作系统命令locale查出本地语言环境变量 LC_COLLATE，LC_CTYPE均为zh_CN.GBK
这时候解决方法是
1，在sql语句后面附加上排序规则，例如：
select ..from ...where t_name like '国家%' collate "zh_CN.GBK";
2，新建数据库初始化排序规则，然后将原表导入，经测试不再报错：
create database test encoding 'zh_CN.GBK' lc_collate 'zh_CN.GBK' lc_ctype 'zh_CN.GBK' template template0;
具体选择哪种方式根据业务是否多变性灵活选择。
关于排序规则(Collation)
[
](javascript:void(0)😉
LC_COLLATE String sort order
LC_CTYPE 字符分类
LC_MESSAGES 消息的语言
LC_MONETARY 货币使用的格式
LC_NUMERIC 数字使用的格式
LC_TIME 时间日期使用的格式
[](javascript:void(0)😉
查询字符集支持的LC_COLLATE和LC_CTYPE信息
test=> select pg_encoding_to_char(collencoding) as encoding,collname,collcollate,collctype from pg_collation ;
返回结果如下所示，encoding为空时，表示这个collation支持所有的字符集。
[](javascript:void(0)😉
encoding | collname | collcollate | collctype
------------+-----------------------+-----------------------+-----------------------
| default | |
| C | C | C
| POSIX | POSIX | POSIX
UTF8 | aa_DJ | aa_DJ.utf8 | aa_DJ.utf8
LATIN1 | aa_DJ | aa_DJ | aa_DJ
LATIN1 | aa_DJ.iso88591 | aa_DJ.iso88591 | aa_DJ.iso88591
UTF8 | aa_DJ.utf8 | aa_DJ.utf8 | aa_DJ.utf8
UTF8 | aa_ER | aa_ER | aa_ER
UTF8 | aa_ER.utf8 | aa_ER.utf8 | aa_ER.utf8
.......
EUC_CN | zh_CN | zh_CN | zh_CN
UTF8 | zh_CN | zh_CN.utf8 | zh_CN.utf8
[](javascript:void(0)😉
设置数据库的本土化（collate）信息
1，设置字段的本土化
执行如下SQL命令，查询当前数据库的字符集，并了解清楚与您当前数据库字符集兼容的collate。
postgres=# select datname,pg_encoding_to_char(encoding) as encoding from pg_database;
操作步骤：
可以在创建表时指定当前字符集兼容的collate：
CREATE TABLE test1 (
a text COLLATE "zh_CN.GBK",
b text COLLATE "ci_x_icu",
...
);
也可以修改列collate：
注意：修改列collate时，会导致rewrite table，大表请谨慎操作。会耗时很久。
alter table a alter c type text COLLATE "en_US";
2，在SQL使用本土化
如上文提到的方法：
test=# select * from a order by c collate "C";
3，使用本土化索引进行排序
排序语句中的collate与索引的collate保持一致，才能使用这个索引进行排序。命令如下：
create index idxa on a(c collate "en_US");
explain select * from a order by c collate "en_US";
总结
可以通过如下四种方法来设置按拼音排序：四种方法以上均已提供。
1，使用本土化 SQL。该方法不修改原有数据。
2，使用本土化字段。若已有数据，使用该方法时需要调整原有数据。
3，使用本土化索引以及本土化 SQL。该方法不修改原有数据。
4，将数据库的collate设置为en_US，数据会将默认使用这个collate按拼音排序。
