如何按拼音排序 - 数据库本土化特性(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>