如何按拼音排序 - 資料庫本土化特性(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的官方文檔，有對應的字元集支援清單

<a href="https://www.postgresql.org/docs/9.6/static/multibyte.html">https://www.postgresql.org/docs/9.6/static/multibyte.html</a>

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

euc_jis_2004

extended unix code-jp, jis x 0213

euc_kr

extended unix code-kr

korean

euc_tw

extended unix code-tw

traditional chinese, taiwanese

gb18030

national standard

chinese

1-4

gbk

extended national standard

win936, windows936

iso_8859_5

iso 8859-5, ecma 113

latin/cyrillic

1

iso_8859_6

iso 8859-6, ecma 114

latin/arabic

iso_8859_7

iso 8859-7, ecma 118

latin/greek

iso_8859_8

iso 8859-8, ecma 121

latin/hebrew

johab

korean (hangul)

koi8r

koi8-r

cyrillic (russian)

koi8

koi8u

koi8-u

cyrillic (ukrainian)

latin1

iso 8859-1, ecma 94

western european

iso88591

latin2

iso 8859-2, ecma 94

central european

iso88592

latin3

iso 8859-3, ecma 94

south european

iso88593

latin4

iso 8859-4, ecma 94

north european

iso88594

latin5

iso 8859-9, ecma 128

turkish

iso88599

latin6

iso 8859-10, ecma 144

nordic

iso885910

latin7

iso 8859-13

baltic

iso885913

latin8

iso 8859-14

celtic

iso885914

latin9

iso 8859-15

latin1 with euro and accents

iso885915

latin10

iso 8859-16, asro sr 14111

romanian

iso885916

mule_internal

mule internal code

multilingual emacs

sjis

shift jis

mskanji, shiftjis, win932, windows932

shift_jis_2004

shift jis, jis x 0213

sql_ascii

unspecified (see text)

any

uhc

unified hangul code

win949, windows949

utf8

unicode, 8-bit

all

unicode

win866

windows cp866

cyrillic

alt

win874

windows cp874

thai

win1250

windows cp1250

win1251

windows cp1251

win

win1252

windows cp1252

win1253

windows cp1253

greek

win1254

windows cp1254

win1255

windows cp1255

hebrew

win1256

windows cp1256

arabic

win1257

windows cp1257

win1258

windows cp1258

vietnamese

abc, tcvn, tcvn5712, vscii

使用如下sql可以查詢系統表pg_collation得到字元集支援的lc_collate和lc_ctype。

其中encoding為空時，表示這個collation支援所有的字元集。

<a href="https://github.com/digoal/blog/blob/master/201704/20170424_01.md">《如何設定資料庫的lc_collate, lc_ctype, encoding, template》</a>

在操作前，請了解清楚與您目前資料庫字元集(encoding)相容的collate，使用如下sql可以得到目前資料庫的encoding

1. 在建立表時，指定相容目前字元集的collate

2. 修改列collate(會導緻rewrite table)，大表請謹慎操作

1. 使用本土化, 改變order by輸出排序

2. 使用本土化, 改變操作符的結果

注意排序語句中的collate與索引的collate保持一緻，才能使用這個索引進行排序。

1. 方法1，使用本土化sql（不修改原有資料）

2. 方法2，使用本土化字段（如果已有資料，則需要調整原有資料）

3. 方法3，使用本土化索引以及本土化sql（不修改原有資料）

4. 設定資料庫的collate為zh_cn，将預設使用這個collate，按拼音排序

有些多音字，例如重慶(chongqing), 編碼時"重"可能是按zhong編碼，影響輸出。

greenplum不支援單列設定collate，按拼音排序有些許不同。

在greenplum中，可以使用字元集轉換，按對應二進制排序，得到拼音排序的效果。

<a href="https://github.com/digoal/blog/blob/master/201612/20161205_01.md">《postgresql 按拼音排序 - convert to gbk/euc_cn coding》</a>

<a href="https://www.postgresql.org/docs/9.6/static/charset.html">https://www.postgresql.org/docs/9.6/static/charset.html</a>