如何按拼音排序 - 数据库本土化特性(collate, ctype, ...)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介:

标签

PostgreSQL , 按拼音排序 , collate


背景

数据库为了支持国际化,通常会涉及到collate, ctype的概念。

初始化数据库集群时,可以设置如下参数,用于设置数据库的字符串排序、字符归类方法、数值\日期\时间\货币的格式等。

LC_COLLATE String sort order
LC_CTYPE Character classification (What is a letter? Its upper-case equivalent?)
LC_MESSAGES Language of messages
LC_MONETARY Formatting of currency amounts
LC_NUMERIC Formatting of numbers
LC_TIME Formatting of dates and times

用户可以利用这些特性,按本土化需求,输出对应的顺序或者格式。

按中文的拼音为顺序排序就是一个常见的需求。

PostgreSQL支持哪些字符集(encoding)

用户可以参考PostgreSQL的官方文档,有对应的字符集支持列表

https://www.postgresql.org/docs/9.6/static/multibyte.html

Server=Yes表示该字符集支持用于create database。否则只支持作为客户端字符集。

Name Description Language Server? Bytes/Char Aliases
BIG5 Big Five Traditional Chinese No 1-2 WIN950, Windows950
EUC_CN Extended UNIX Code-CN Simplified Chinese Yes 1-3 -
EUC_JP Extended UNIX Code-JP Japanese Yes 1-3 -
EUC_JIS_2004 Extended UNIX Code-JP, JIS X 0213 Japanese Yes 1-3 -
EUC_KR Extended UNIX Code-KR Korean Yes 1-3 -
EUC_TW Extended UNIX Code-TW Traditional Chinese, Taiwanese Yes 1-3 -
GB18030 National Standard Chinese No 1-4 -
GBK Extended National Standard Simplified Chinese No 1-2 WIN936, Windows936
ISO_8859_5 ISO 8859-5, ECMA 113 Latin/Cyrillic Yes 1 -
ISO_8859_6 ISO 8859-6, ECMA 114 Latin/Arabic Yes 1 -
ISO_8859_7 ISO 8859-7, ECMA 118 Latin/Greek Yes 1 -
ISO_8859_8 ISO 8859-8, ECMA 121 Latin/Hebrew Yes 1 -
JOHAB JOHAB Korean (Hangul) No 1-3 -
KOI8R KOI8-R Cyrillic (Russian) Yes 1 KOI8
KOI8U KOI8-U Cyrillic (Ukrainian) Yes 1 -
LATIN1 ISO 8859-1, ECMA 94 Western European Yes 1 ISO88591
LATIN2 ISO 8859-2, ECMA 94 Central European Yes 1 ISO88592
LATIN3 ISO 8859-3, ECMA 94 South European Yes 1 ISO88593
LATIN4 ISO 8859-4, ECMA 94 North European Yes 1 ISO88594
LATIN5 ISO 8859-9, ECMA 128 Turkish Yes 1 ISO88599
LATIN6 ISO 8859-10, ECMA 144 Nordic Yes 1 ISO885910
LATIN7 ISO 8859-13 Baltic Yes 1 ISO885913
LATIN8 ISO 8859-14 Celtic Yes 1 ISO885914
LATIN9 ISO 8859-15 LATIN1 with Euro and accents Yes 1 ISO885915
LATIN10 ISO 8859-16, ASRO SR 14111 Romanian Yes 1 ISO885916
MULE_INTERNAL Mule internal code Multilingual Emacs Yes 1-4 -
SJIS Shift JIS Japanese No 1-2 Mskanji, ShiftJIS, WIN932, Windows932
SHIFT_JIS_2004 Shift JIS, JIS X 0213 Japanese No 1-2 -
SQL_ASCII unspecified (see text) any Yes 1 -
UHC Unified Hangul Code Korean No 1-2 WIN949, Windows949
UTF8 Unicode, 8-bit all Yes 1-4 Unicode
WIN866 Windows CP866 Cyrillic Yes 1 ALT
WIN874 Windows CP874 Thai Yes 1 -
WIN1250 Windows CP1250 Central European Yes 1 -
WIN1251 Windows CP1251 Cyrillic Yes 1 WIN
WIN1252 Windows CP1252 Western European Yes 1 -
WIN1253 Windows CP1253 Greek Yes 1 -
WIN1254 Windows CP1254 Turkish Yes 1 -
WIN1255 Windows CP1255 Hebrew Yes 1 -
WIN1256 Windows CP1256 Arabic Yes 1 -
WIN1257 Windows CP1257 Baltic Yes 1 -
WIN1258 Windows CP1258 Vietnamese Yes 1 ABC, TCVN, TCVN5712, VSCII

如何获取字符集支持的LC_COLLATE, LC_CTYPE信息

使用如下SQL可以查询系统表pg_collation得到字符集支持的lc_collate和lc_ctype。

其中encoding为空时,表示这个collation支持所有的字符集。

test=> 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       | 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    
 EUC_CN     | zh_CN.gb2312          | zh_CN.gb2312          | zh_CN.gb2312    
 UTF8       | zh_CN.utf8            | zh_CN.utf8            | zh_CN.utf8    
 UTF8       | zh_HK                 | zh_HK.utf8            | zh_HK.utf8    
 UTF8       | zh_HK.utf8            | zh_HK.utf8            | zh_HK.utf8    
 EUC_CN     | zh_SG                 | zh_SG                 | zh_SG    
 UTF8       | zh_SG                 | zh_SG.utf8            | zh_SG.utf8    
 EUC_CN     | zh_SG.gb2312          | zh_SG.gb2312          | zh_SG.gb2312    
 UTF8       | zh_SG.utf8            | 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    
 EUC_TW     | zh_TW.euctw           | zh_TW.euctw           | zh_TW.euctw    
 UTF8       | zh_TW.utf8            | zh_TW.utf8            | zh_TW.utf8    
 UTF8       | zu_ZA                 | zu_ZA.utf8            | zu_ZA.utf8    
 LATIN1     | zu_ZA                 | zu_ZA                 | zu_ZA    
 LATIN1     | zu_ZA.iso88591        | zu_ZA.iso88591        | zu_ZA.iso88591    
 UTF8       | zu_ZA.utf8            | zu_ZA.utf8            | zu_ZA.utf8    
(869 rows)    

如何设置数据库的本土化(collate)信息

《如何设置数据库的LC_COLLATE, LC_CTYPE, ENCODING, TEMPLATE》

如何设置字段的本土化(collate)

在操作前,请了解清楚与您当前数据库字符集(encoding)兼容的collate,使用如下SQL可以得到当前数据库的encoding

postgres=# select datname,pg_encoding_to_char(encoding) as encoding from pg_database;  
      datname       | encoding    
--------------------+-----------  
 template1          | UTF8  
 template0          | UTF8  
 db                 | SQL_ASCII  
 db1                | EUC_CN  
 contrib_regression | UTF8  
 test01             | UTF8  
 test02             | UTF8  
 postgres           | UTF8  
(8 rows)  

1. 在创建表时,指定兼容当前字符集的collate

CREATE TABLE test1 (  
    a text COLLATE "de_DE",  
    b text COLLATE "es_ES",  
    ...  
);  

2. 修改列collate(会导致rewrite table),大表请谨慎操作

alter table a alter c1 type text COLLATE "zh_CN";  

如何在SQL用使用本土化(collate)

1. 使用本土化, 改变order by输出排序

test=# select * from a order by c1 collate "C";  
   c1     
--------  
 刘少奇  
 刘德华  
(2 rows)  
  
test=# select * from a order by c1 collate "zh_CN";  
   c1     
--------  
 刘德华  
 刘少奇  
(2 rows)  

2. 使用本土化, 改变操作符的结果

test=# select * from a where c1 > '刘少奇' collate "C";  
   c1     
--------  
 刘德华  
(1 row)  
  
test=# select * from a where c1 > '刘少奇' collate "zh_CN";  
 c1   
----  
(0 rows)  

如何使用本土化索引, 按拼音排序

注意排序语句中的collate与索引的collate保持一致,才能使用这个索引进行排序。

postgres=# create index idxa on a(c1 collate "zh_CN");  
CREATE INDEX  
  
postgres=# explain select * from a order by c1 collate "zh_CN";  
                               QUERY PLAN                                 
------------------------------------------------------------------------  
 Index Only Scan using idxa on a  (cost=0.15..31.55 rows=1360 width=64)  
(1 row)  

如何按拼音排序

1. 方法1,使用本土化SQL(不修改原有数据)

test=# select * from a order by c1 collate "zh_CN";  
   c1     
--------  
 刘德华  
 刘少奇  
(2 rows)  

2. 方法2,使用本土化字段(如果已有数据,则需要调整原有数据)

alter table a alter c1 type text COLLATE "zh_CN";  

3. 方法3,使用本土化索引以及本土化SQL(不修改原有数据)

postgres=# create index idxa on a(c1 collate "zh_CN");  
CREATE INDEX  
  
postgres=# explain select * from a order by c1 collate "zh_CN";  
                               QUERY PLAN                                 
------------------------------------------------------------------------  
 Index Only Scan using idxa on a  (cost=0.15..31.55 rows=1360 width=64)  
(1 row)  

4. 设置数据库的collate为zh_CN,将默认使用这个collate,按拼音排序

test02=# create database test03 encoding 'UTF8' lc_collate 'zh_CN.utf8' lc_ctype 'zh_CN.utf8' template template0;  
CREATE DATABASE  
  
test02=# \c test03  
You are now connected to database "test03" as user "postgres".  
  
test03=# select * from (values ('刘德华'),('刘少奇')) as a(c1) order by c1 ;  
   c1     
--------  
 刘德华  
 刘少奇  
(2 rows)  

注意多音字

有些多音字,例如重庆(chongqing), 编码时"重"可能是按zhong编码,影响输出。

test03=# select * from (values ('中山'),('重庆')) as a(c1) order by c1 collate "zh_CN";  
  c1    
------  
 中山  
 重庆  
(2 rows)  

Greenplum按拼音排序

greenplum不支持单列设置collate,按拼音排序有些许不同。

在greenplum中,可以使用字符集转换,按对应二进制排序,得到拼音排序的效果。

postgres=# select * from (values ('刘德华'), ('刘少奇')) t(id) order by byteain(textout(convert(id,'UTF8','EUC_CN')));  
   id     
--------  
 刘德华  
 刘少奇  
(2 rows)  

参考

《PostgreSQL 按拼音排序 - convert to GBK/EUC_CN coding》

《如何设置数据库的LC_COLLATE, LC_CTYPE, ENCODING, TEMPLATE》

https://www.postgresql.org/docs/9.6/static/charset.html

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
7月前
|
SQL 存储 关系型数据库
数据库事务——事务的特性(ACID)
数据库事务——事务的特性(ACID)
120 0
|
15天前
|
SQL 关系型数据库 MySQL
排序与分页——“MySQL数据库”
排序与分页——“MySQL数据库”
|
15天前
|
关系型数据库 MySQL 测试技术
数据库专家带你体验PolarDB MySQL版 Serverless的极致弹性特性!
本次基于阿里云瑶池数据库解决方案体验馆,带你体验PolarDB MySQL Serverless形态下的性能压测环境,基于可选择的标准压测工具进行压测,构造弹性场景进行压测,实时动态展示弹性能力、价格和性价比结果,压测环境可开放定制修改、可重复验证。参与活动即有机会获得鼠标、小米打印机、卫衣等精美礼品。
数据库专家带你体验PolarDB MySQL版 Serverless的极致弹性特性!
|
15天前
|
关系型数据库 MySQL 测试技术
数据库专家带你体验PolarDB MySQL版 Serverless的极致弹性特性
本次基于阿里云瑶池数据库解决方案体验馆,带你体验PolarDB MySQL Serverless形态下的性能压测环境,基于可选择的标准压测工具进行压测,构造弹性场景进行压测,实时动态展示弹性能力、价格和性价比结果,压测环境可开放定制修改、可重复验证。参与活动即有机会获得鼠标、小米打印机、卫衣等精美礼品。
|
15天前
|
存储 关系型数据库 MySQL
【2024】新建mysql数据库,如何选择字符集和排序规则
【2024】新建mysql数据库,如何选择字符集和排序规则
209 1
|
15天前
|
SQL 数据库管理
sqlite语句order by两个字段同时排序处理
sqlite语句order by两个字段同时排序处理
24 0
|
15天前
|
SQL 人工智能 运维
数据库基础入门 — SQL排序与分页
数据库基础入门 — SQL排序与分页
30 0
|
15天前
|
关系型数据库 数据库 RDS
为了确保数据的完整性和准确性,建议您在进行数据迁移前,充分理解源数据库和目标数据库的特性以及迁移过程中可能出现的问题
为了确保数据的完整性和准确性,建议您在进行数据迁移前,充分理解源数据库和目标数据库的特性以及迁移过程中可能出现的问题
47 1
|
15天前
|
关系型数据库 MySQL 数据库
Mysql 创建数据库字符集与排序规则
Mysql 创建数据库字符集与排序规则
173 2
|
6月前
|
关系型数据库 Java MySQL
数据库事务特性、传播行为和隔离级别总结
数据库事务特性、传播行为和隔离级别总结
58 1