如何按拼音排序-資料庫本土化特性(collate,ctype,…)
標籤
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
相關文章
- Sql Server資料庫漢字按字母、筆劃、拼音首字母、排序SQLServer資料庫排序
- mysql的中文資料按拼音排序的2個方法MySql排序
- Oracle資料庫中文拼音,部首,筆畫排序問題，NLS_SORT設定Oracle資料庫排序
- C 標準庫 – ctype.h
- 如何讓漢字以拼音字母順序排序排序
- 資料庫排序查詢資料庫排序
- mysql資料庫姓名排序MySql資料庫排序
- js 漢字按照拼音排序效果JS排序
- java對中文（拼音）進行排序Java排序
- JavaScript按照漢字拼音順序排序JavaScript排序
- 2.8.1.3 Oracle特性資料庫服務Oracle資料庫
- 圖資料庫 Nebula Graph TTL 特性資料庫
- 資料庫ACDI四大特性資料庫
- MySQL InnoDB資料庫如何保證事務特性示例詳解MySql資料庫
- 分散式資料庫排序及優化分散式資料庫排序優化
- mysql按照漢字拼音進行order by排序MySql排序
- 城市列表-根據拼音首字母排序排序
- layui資料表格初始化時按某列進行排序UI排序
- Java面試題：如何對HashMap按鍵值排序Java面試題HashMap排序
- oracle資料庫事物四大特性Oracle資料庫
- Excel表格如何按漢字的筆畫排序？Excel表格按漢字的筆畫排序的方法Excel排序
- 什麼是騰訊雲資料庫 CynosDB？雲資料庫 TencentDB for CynosDB 的特性資料庫
- 資料庫之DQL排序&分組&函式資料庫排序函式
- 分散式資料庫排序及最佳化分散式資料庫排序
- 更改系統資料庫的排序規則資料庫排序
- PHP 字串陣列按照拼音排序的問題PHP字串陣列排序
- 如何為資料行的新增操作按鈕
- 【postgresl】PG資料庫sql特性簡單解析資料庫SQL
- Data Guard新特性：快照備用資料庫資料庫
- 資料庫週刊18│4月資料庫排行；PG是最好的資料庫；TiDB 4.0新特性資料庫TiDB
- 全國省市區資料庫，帶拼音,簡稱,行政編碼，郵政編碼等資料庫
- 如何修改資料庫例項及資料庫名資料庫
- [譯]按功能（特性）分包
- 磁碟排序對Oracle資料庫效能的影響排序Oracle資料庫
- SSIS 資料庫排序規則衝突問題資料庫排序
- elasticsearch實戰—中文拼音A-Z排序（完美解決）Elasticsearch排序
- elasticsearch實戰---中文拼音A-Z排序（完美解決）Elasticsearch排序
- MYSQL中的COLLATE是什麼？MySql