每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第8期 - 为什么`order by`并没有按中文拼音排序?

作者

digoal

日期

2021-12-20

标签

PostgreSQL , 热门问题

• 问题说明(现象、环境)
• 分析原因
• 结论和解决办法

8、为什么order by并没有按中文拼音排序? (LC collate)

https://www.bilibili.com/video/BV1KR4y1W7BU/
字符串排序受什么影响?
- 字符集 - LC_COLLATE (string sort order)

几处可以指定collate:
- 初始化PostgreSQL 实例时指定template的collate
- 创建数据库时指定数据库的默认collate
- 指定表字段的collate
- 排序时指定collate
- 创建索引时指定collate
- 用到这样的索引必须在order by排序时使用与索引一样的collate, 否则索引不会被使用

中文拼音排序推荐用法:
- order by convert_to(字符串字段,'EUC_CN'); 简体
- order by convert_to(info,'GB18030'); 简体+繁体+少数民族字体+各种中国的符号

postgres=# select * from (values ('刘德华'),('刘少奇'),('张学友'),('郭富城'),('郭德纲'),('黎明'),('李刚'),('中山'),('重庆'),('冲哥')) as t(info)    
order by info collate "C";   
  info     
--------   
 中山   
 冲哥   
 刘少奇   
 刘德华   
 张学友   
 李刚   
 郭富城   
 郭德纲   
 重庆   
 黎明   
(10 rows)   
postgres=# select * from (values ('刘德华'),('刘少奇'),('张学友'),('郭富城'),('郭德纲'),('黎明'),('李刚'),('中山'),('重庆'),('冲哥')) as t(info)    
order by info collate "zh_CN";   
  info     
--------   
 中山   
 冲哥   
 李刚   
 重庆   
 黎明   
 刘少奇   
 刘德华   
 张学友   
 郭富城   
 郭德纲   
(10 rows)   
postgres=# select * from (values ('刘德华'),('刘少奇'),('张学友'),('郭富城'),('郭德纲'),('黎明'),('李刚'),('中山'),('重庆'),('冲哥')) as t(info)    
order by convert_to(info,'GB18030');   
  info     
--------   
 冲哥   
 郭德纲   
 郭富城   
 黎明   
 李刚   
 刘德华   
 刘少奇   
 张学友   
 中山   
 重庆   
(10 rows)   
postgres=# select * from (values ('刘德华'),('刘少奇'),('张学友'),('郭富城'),('郭德纲'),('黎明'),('李刚'),('中山'),('重庆'),('冲哥')) as t(info)    
order by convert_to(info,'EUC_CN');   
  info     
--------   
 冲哥   
 郭德纲   
 郭富城   
 黎明   
 李刚   
 刘德华   
 刘少奇   
 张学友   
 中山   
 重庆   
(10 rows)   
postgres=# \df convert_to   
                            List of functions   
   Schema   |    Name    | Result data type | Argument data types | Type    
------------+------------+------------------+---------------------+------   
 pg_catalog | convert_to | bytea            | text, name          | func   
(1 row)   
复制

索引必须是immutable的.
postgres=# create index idx_d_1 on d (convert_to(c1,'GBK'));
ERROR:  functions in index expression must be marked IMMUTABLE
postgres=# create or replace function immut_convert_to(text,text) returns bytea as $$
postgres$#   select convert_to($1,$2);
postgres$# $$ language sql strict immutable;
CREATE FUNCTION
postgres=# create index idx_d_1 on d (immut_convert_to(c1,'GBK'));                                                                                                                                        CREATE INDEX
postgres=# set enable_sort=off;
SET
postgres=# explain select * from d order by immut_convert_to(c1, 'GBK'::text);
                            QUERY PLAN                            
------------------------------------------------------------------
 Index Scan using idx_d_1 on d  (cost=0.12..2.59 rows=1 width=64)
(1 row)
复制

参考:
- PostgreSQL 支持的服务端与客户端字符集:
- https://www.postgresql.org/docs/current/multibyte.html
- 《PostgreSQL MySQL 兼容性之 - order by 拼音 or binary or 指定 collate》
- 《如何按拼音排序 - 数据库本土化特性(collate, ctype, ...)》
- 《PostgreSQL 按拼音排序 - convert to GBK/EUC_CN coding》
- 中文相关字符集,注意有的只能作为客户端编码, 有的既能作为客户端编码也能作为服务端编码.
- GB2312（简体），
- GBK（简体+繁体），
- GB18030（简体+繁体+少数民族字体+各种中国的符号）
- EUC_CN Extended UNIX Code-CN Simplified Chinese
- BIG5 Big Five Traditional Chinese
- EUC_TW Extended UNIX Code-TW Traditional Chinese, Taiwanese

期望 PostgreSQL 增加什么功能?

PolarDB for PostgreSQL云原生分布式开源数据库

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.