985      阿裏雲  技術社區[雲棲]

如何按拚音排序 - 數據庫本土化特性(collate, ctype, ...)

標簽

PostgreSQL , 按拚音排序 , collate

背景

數據庫為了支持國際化，通常會涉及到collate, ctype的概念。

初始化數據庫集群時，可以設置如下參數，用於設置數據庫的字符串排序、字符歸類方法、數值\日期\時間\貨幣的格式等。

用戶可以利用這些特性，按本土化需求，輸出對應的順序或者格式。

按中文的拚音為順序排序就是一個常見的需求。

PostgreSQL支持哪些字符集(encoding)

用戶可以參考PostgreSQL的官方文檔，有對應的字符集支持列表

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

Server=Yes表示該字符集支持用於create database。否則隻支持作為客戶端字符集。

如何獲取字符集支持的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

最後更新：2017-04-24 21:32:59

  上一篇： PostgreSQL upsert功能(insert on conflict do)的用法

  下一篇： 如何在PostgreSQL中調試plpgsql存儲過程(pldebugger, pldbgapi)