背景
前端在客户现场遇到一个问题,模糊查询报错: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)
[
LC_COLLATE String sort order
LC_CTYPE 字符分类
LC_MESSAGES 消息的语言
LC_MONETARY 货币使用的格式
LC_NUMERIC 数字使用的格式
LC_TIME 时间日期使用的格式
[
查询字符集支持的LC_COLLATE和LC_CTYPE信息
test=> select pg_encoding_to_char(collencoding) as encoding,collname,collcollate,collctype from pg_collation ;
返回结果如下所示,encoding为空时,表示这个collation支持所有的字符集。
[
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
[
设置数据库的本土化(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按拼音排序。