一次数据库字符串排序规则(COLLATE)的踩坑记录

在一次实际项目中,需要对某个 vchar 字段做排序,字段内容由大小写字母和数字组成。在开始设计的时候很想当然的以为数据库对字符串的排序是根据 ASCII 码顺序来排的,毕竟在 Java 代码中 String 就是按这种方式排的。

可实际结果却出乎意料。。。

示例

为了说明问题,这里建张简单的表(PostgreSQL),并插入几条数据:

1
2
3
4
5
6
7
8
9
-- 简单的用户表
CREATE TABLE t_user (
user_id character varying(32) NOT NULL,
user_name character varying(20),
CONSTRAINT t_user_pkey PRIMARY KEY (user_id)
);
INSERT INTO public.t_user(user_id, user_name) VALUES ('a', '张三');
INSERT INTO public.t_user(user_id, user_name) VALUES ('b', '李四');
INSERT INTO public.t_user(user_id, user_name) VALUES ('A', '王五');

然后执行 SQL(select * from t_user order by user_id asc),对 user_id 字段排序输出:

1577347603969

结果却是数据库既没有根据 ASCII 码排序(ASCII 码顺序 A 应该是在 a 前面的),也没有按字母顺序(a 后面应该紧跟着 b)。

问题原因

其实是我们自己把数据库想象的太简单了,现在主流的数据库都支持很多种字符集的,每个字符集都有很多对应支持的排序规则。只是在日常开发中我们没太关注而已。

以 PostgreSQL 数据库为例,可以使用如下语句查询数据库支持的排序规则:

1
2
3
4
5
6
select
pg_encoding_to_char(collencoding) as encoding,
collname,
collcollate,
collctype
from pg_collation;

查询结果:

encoding collname collcollate collctype
default
C C C
POSIX POSIX POSIX
UTF8 ucs_basic C C
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@saaho aa_ER@saaho aa_ER@saaho
UTF8 aa_ER.utf8 aa_ER.utf8 aa_ER.utf8
UTF8 aa_ER.utf8@saaho aa_ER.utf8@saaho aa_ER.utf8@saaho
UTF8 aa_ET aa_ET aa_ET
UTF8 aa_ET.utf8 aa_ET.utf8 aa_ET.utf8
LATIN1 af_ZA af_ZA af_ZA
LATIN1 af_ZA.iso88591 af_ZA.iso88591 af_ZA.iso88591
UTF8 af_ZA.utf8 af_ZA.utf8 af_ZA.utf8
UTF8 am_ET am_ET am_ET
UTF8 am_ET.utf8 am_ET.utf8 am_ET.utf8
LATIN9 an_ES an_ES an_ES
LATIN9 an_ES.iso885915 an_ES.iso885915 an_ES.iso885915
UTF8 zh_CN zh_CN.utf8 zh_CN.utf8
UTF8 zh_HK zh_HK.utf8 zh_HK.utf8
UTF8 zh_SG zh_SG.utf8 zh_SG.utf8
EUC_TW zh_TW zh_TW.euctw zh_TW.euctw
UTF8 zh_TW zh_TW.utf8 zh_TW.utf8
UTF8 zu_ZA zu_ZA.utf8 zu_ZA.utf8

encoding 为空表示是所有字符集都支持的,在表格中也能看到常见的zh_CN

所以并不是简单认为的以 ASCII 码排序。

解决办法

建表时指定排序规则

还是以上述简单的用户表,建表语句可以改成:

1
2
3
4
5
CREATE TABLE t_user (
user_id character varying(32) COLLATE "C" NOT NULL,
user_name character varying(20) COLLATE "zh_CN",
CONSTRAINT t_user_pkey PRIMARY KEY (user_id)
);

user_id 因为都是数字和字母组成,所以可以使用”C”;user_name 存的会是中文,则可以使用”zh_CN”。

如果表是已经创建好了的,可以通过 ALTER 语句修改字段的排序规则,如:

1
2
alter table t_user alter user_id type character varying(32) COLLATE "C";
alter table t_user alter user_name type character varying(20) COLLATE "zh_CN";

查询时指定排序规则

如果表结构无法修改,也可以在 SQL 查询时指定排序规则。不过不建议这么做,因为这样用到该字段排序的地方都要改。

查询语句格式:

1
2
select * from t_user order by user_id COLLATE "C" asc;
select * from t_user order by user_name COLLATE "zh_CN" asc;