每天5分钟PG聊通透第8期,为什么order by不按中文拼音排序?

文中参考文档可点击阅读原文打开, 推荐《最好的PostgreSQL学习镜像》。

---

每天5分钟PG聊通透第8期,为什么order by不按中文拼音排序?

背景

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

链接、驱动、SQL

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

本期彩蛋 - 开源Clup: PostgreSQL&PolarDB高可用与日常管理软件

clup由《PostgreSQL从小工到专家》作者唐成乘数科技出品, 包含开源版本和企业版本, 是非常成熟的PostgreSQL&PolarDB集群管理软件. 

官网: https://www.csudata.com/clup

开源项目地址: https://gitee.com/csudata

使用CLup可以轻松管理几十套至上百套PostgreSQL、PolarDB高可用的数据库集群，发生故障自动切换，不影响生产系统的运行。故障切换后有详细的故障日志，方便定位故障原因，还可以手工一键切换。CLup还提供了数据库的一些基本监控和TOP SQL的监控，CLup后续版本还会增加更多的功能。

• 管理基于PostgreSQL流复制的集群

• 管理基于共享存储的PolarDB集群
  

• 产品优势

最后推荐2本大佬的新书

文章中的参考文档请点击阅读原文获得. 

---

欢迎关注我的github (https://github.com/digoal/blog) , 及视频号: