通常只有數字/英文字母的資料，排序上很單純，就依照英文字母歌的順序一樣～
但是遇到其他語言的文字。。。就需要好好設定 Collation 了。
目前作業系統都會內建多國語言排序模組，例如，在 Linux 就有標準的 libc 函式庫可以用。多數需要用到排序的主流軟體（文書編輯器、資料庫）都會呼叫這個模組以支援多國語言資料排序功能。但這在不同 OS 之間會有不同差異，換了 OS 就有可能就會有些不同。
而 PGSQL 在 v10 之後，額外提供了 ICU 這個標準模組的整合，用來強化排序的跨平台一致性。
這邊會在 EDB PGSQL 11 企業版上以生活中常用的中文筆劃排序的設定練習，簡單紀錄一次～這些步驟在原生的 PGSQL 11 也適用～
首先，這邊所準備的資料庫 Instance，在 initdb 時就被初始化成 LC_ALL=C 的語系狀態（也就是沒有指定的意思），以及編碼為 SQL_ASCII（以 Single Byte 儲存資料，而且不進行編碼檢查的格式。通常用作 Garbage-in, garbage-out 的存放模式）
-bash-4.2$ psql -d edb
psql.bin (11.4.11)
Type "help" for help.
edb=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU | Access privileges
-----------+--------------+-----------+---------+-------+-----+-------------------------------
edb | enterprisedb | SQL_ASCII | C | C | |
postgres | enterprisedb | SQL_ASCII | C | C | |
template0 | enterprisedb | SQL_ASCII | C | C | | =c/enterprisedb +
| | | | | | enterprisedb=CTc/enterprisedb
template1 | enterprisedb | SQL_ASCII | C | C | | =c/enterprisedb +
| | | | | | enterprisedb=CTc/enterprisedb
(4 rows)
edb=#
目前裡面的 Database （edb 與 postgres 兩個預設的）都無法再變更編碼，因此要建立一個新的 Database，指定編碼為 UTF8。
在設置編碼與排序 Collation 之前，先來查看一個系統表，pg_collation
edb=# select * from pg_collation where collname like 'zh%';
collname | collnamespace | collowner | collprovider | collencoding | collcollate | collctype | collicu | collversion
------------------+---------------+-----------+--------------+--------------+--------------+--------------+---------+-------------
zh_CN | 11 | 10 | c | 2 | zh_CN | zh_CN | |
zh_CN.gb2312 | 11 | 10 | c | 2 | zh_CN.gb2312 | zh_CN.gb2312 | |
zh_CN.utf8 | 11 | 10 | c | 6 | zh_CN.utf8 | zh_CN.utf8 | |
zh_HK.utf8 | 11 | 10 | c | 6 | zh_HK.utf8 | zh_HK.utf8 | |
zh_SG | 11 | 10 | c | 2 | zh_SG | zh_SG | |
zh_SG.gb2312 | 11 | 10 | c | 2 | zh_SG.gb2312 | zh_SG.gb2312 | |
zh_SG.utf8 | 11 | 10 | c | 6 | zh_SG.utf8 | zh_SG.utf8 | |
zh_TW.euctw | 11 | 10 | c | 4 | zh_TW.euctw | zh_TW.euctw | |
zh_TW.utf8 | 11 | 10 | c | 6 | zh_TW.utf8 | zh_TW.utf8 | |
zh_CN | 11 | 10 | c | 6 | zh_CN.utf8 | zh_CN.utf8 | |
zh_HK | 11 | 10 | c | 6 | zh_HK.utf8 | zh_HK.utf8 | |
zh_SG | 11 | 10 | c | 6 | zh_SG.utf8 | zh_SG.utf8 | |
zh_TW | 11 | 10 | c | 4 | zh_TW.euctw | zh_TW.euctw | |
zh_TW | 11 | 10 | c | 6 | zh_TW.utf8 | zh_TW.utf8 | |
zh-x-icu | 11 | 10 | i | -1 | zh | zh | | 137.51.25
zh-Hans-x-icu | 11 | 10 | i | -1 | zh_Hans | zh_Hans | | 137.51.25
zh-Hans-CN-x-icu | 11 | 10 | i | -1 | zh_Hans_CN | zh_Hans_CN | | 137.51.25
zh-Hans-HK-x-icu | 11 | 10 | i | -1 | zh_Hans_HK | zh_Hans_HK | | 137.51.25
zh-Hans-MO-x-icu | 11 | 10 | i | -1 | zh_Hans_MO | zh_Hans_MO | | 137.51.25
zh-Hans-SG-x-icu | 11 | 10 | i | -1 | zh_Hans_SG | zh_Hans_SG | | 137.51.25
zh-Hant-x-icu | 11 | 10 | i | -1 | zh_Hant | zh_Hant | | 137.51.25
zh-Hant-HK-x-icu | 11 | 10 | i | -1 | zh_Hant_HK | zh_Hant_HK | | 137.51.25
zh-Hant-MO-x-icu | 11 | 10 | i | -1 | zh_Hant_MO | zh_Hant_MO | | 137.51.25
zh-Hant-TW-x-icu | 11 | 10 | i | -1 | zh_Hant_TW | zh_Hant_TW | | 137.51.25
(24 rows)
edb=#
edb=# select * from pg_collation where collname like '%TW%';
collname | collnamespace | collowner | collprovider | collencoding | collcollate | collctype | collicu | collversion
-------------------+---------------+-----------+--------------+--------------+-------------------+-------------------+---------+-------------
nan_TW.utf8@latin | 11 | 10 | c | 6 | nan_TW.utf8@latin | nan_TW.utf8@latin | |
nan_TW@latin | 11 | 10 | c | 6 | nan_TW@latin | nan_TW@latin | |
zh_TW.euctw | 11 | 10 | c | 4 | zh_TW.euctw | zh_TW.euctw | |
zh_TW.utf8 | 11 | 10 | c | 6 | zh_TW.utf8 | zh_TW.utf8 | |
zh_TW | 11 | 10 | c | 4 | zh_TW.euctw | zh_TW.euctw | |
zh_TW | 11 | 10 | c | 6 | zh_TW.utf8 | zh_TW.utf8 | |
zh-Hant-TW-x-icu | 11 | 10 | i | -1 | zh_Hant_TW | zh_Hant_TW | | 137.51.25
(7 rows)
edb=#
這個系統表顯示的，是目前資料庫裡面支援的 Collation。這邊分別列舉與中文（代號 zh）與 TW 的部份。我們這邊會選用的是帶有 zh + TW 的選項～另外。。上面可以看到閩南語（nan_TW），但這邊沒有要使用～
接著就要來準備 Database 了。
雖然這個 Postgres Instance 初始化指定為 SQL_ASCII，但裡面還是可以再建立新的 UTF8 編碼的 Database。因此兩種編碼的 Database 會並存在同一個 Instance 裡面。
注意：
- 可以直接對 Database 指定 Collation，指定 LC_COLLATE 僅能夠選 locale 指令可行的項目
- 要在建立 Database 時，從上面系統表的 collname 挑 ICU 的來用的話，在企業版資料庫裡面，可以指定 ICU_SHORT_FORM 參數，指定成上面系統表的 'ZH-HANT-TW-X-ICU'。不過原生的 Postgres 沒有支援～
- 建好一個 Database 之後，就無法再使用 ALTER DATABASE 變更語系排序設定了
- Postgres 不支援 Big5 當作內建編碼
edb=# create database collatest
with template=template0
encoding='utf8'
lc_collate= 'zh_TW';
ERROR: encoding "UTF8" does not match locale "zh_TW"
DETAIL: The chosen LC_COLLATE setting requires encoding "BIG5".
edb=# create database collatest
with template=template0
encoding='big5'
lc_collate= 'zh_TW';
ERROR: big5 is not a valid encoding name
LINE 3: encoding='big5'
^
edb=# create database collatest
with template=template0
encoding='utf8'
lc_collate= 'zh_TW.utf8';
CREATE DATABASE
edb=#
edb=# create database icucollatest
with template=template0
encoding='utf8'
lc_collate= 'zh-Hant-TW-x-icu';
ERROR: invalid locale name: "zh-Hant-TW-x-icu"
edb=#
edb=# create database icucollatest
with template=template0
encoding='utf8'
lc_collate='zh_TW.utf8'
ICU_SHORT_FORM='ZH-HANT-TW-X-ICU';
WARNING: ICU_SHORT_FORM "ZH-HANT-TW-X-ICU" recognized as locale "root", collated as less-specific locale "root"
NOTICE: standard form of ICU_SHORT_FORM "ZH-HANT-TW-X-ICU" is "LROOT"
CREATE DATABASE
edb=#
edb=#
edb=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU | Access privileges
--------------+--------------+-----------+------------+-------+------------------+-------------------------------
collatest | enterprisedb | UTF8 | zh_TW.utf8 | C | |
edb | enterprisedb | SQL_ASCII | C | C | |
icucollatest | enterprisedb | UTF8 | zh_TW.utf8 | C | ZH-HANT-TW-X-ICU |
postgres | enterprisedb | SQL_ASCII | C | C | |
template0 | enterprisedb | SQL_ASCII | C | C | | =c/enterprisedb +
| | | | | | enterprisedb=CTc/enterprisedb
template1 | enterprisedb | SQL_ASCII | C | C | | =c/enterprisedb +
| | | | | | enterprisedb=CTc/enterprisedb
(6 rows)
edb=#
Note: 雖然上面建立了兩個 Database，但下面我們只用一個來測，便於快速對照。
接著要連線進去建測試資料，表格中有一個裝中文字的欄位，用來存放部首表。
由於有兩種可以採用的正體中文語系排序模式：OS 預設的 'zh_TW.utf8' 以及 ICU 提供的 'zh-Hant-TW-x-icu' ，我們分別建一個表格來測試。
表格上的 Collation 指定，需要對個別欄位指定才行。
collatest=# create table zhtw_radicals(chars varchar collate "zh_TW");
CREATE TABLE
collatest=#
collatest=# create table zhtw_radicals_icu(chars varchar collate "zh-Hant-TW-x-icu");
CREATE TABLE
collatest=#
collatest=# \d+ zhtw_radicals
Table "public.zhtw_radicals"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
chars | character varying | zh_TW | | | extended | |
collatest=# \d+ zhtw_radicals_icu
Table "public.zhtw_radicals_icu"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-------------------+------------------+----------+---------+----------+--------------+-------------
chars | character varying | zh-Hant-TW-x-icu | | | extended | |
collatest=#
然後塞資料（從國語小字典_部首字音讀表整理的）
collatest=# insert into zhtw_radicals values
('一'),('丨'),('丶'),
('丿'),('乙'),('亅'),
('二'),('亠'),('人'),
('儿'),('入'),('八'),
('冂'),('冖'),('冫'),
('几'),('凵'),('刀'),
('力'),('勹'),('匕'),
('匚'),('匸'),('十'),
('卜'),('卩'),('厂'),
('厶'),('又'),
('口'),('囗'),('土'),
('士'),('夂'),('夊'),
('夕'),('大'),('女'),
('子'),('宀'),('寸'),
('小'),('尢'),('尸'),
('屮'),('山'),('巛'),
('工'),('己'),('巾'),
('干'),('幺'),('广'),
('廴'),('廾'),('弋'),
('弓'),('彐'),('彡'),
('彳'),
('心'),('戈'),('戶'),
('手'),('支'),('攴'),
('文'),('斗'),('斤'),
('方'),('无'),('日'),
('曰'),('月'),('木'),
('欠'),('止'),('歹'),
('殳'),('毋'),('比'),
('毛'),('氏'),('气'),
('水'),('火'),('爪'),
('父'),('爻'),('爿'),
('片'),('牙'),('牛'),
('犬'),
('玄'),('玉'),('瓜'),
('瓦'),('甘'),('生'),
('用'),('田'),('疋'),
('疒'),('癶'),('白'),
('皮'),('皿'),('目'),
('矛'),('矢'),('石'),
('示'),('禸'),('禾'),
('穴'),('立'),
('竹'),('米'),('糸'),
('缶'),('网'),('羊'),
('羽'),('老'),('而'),
('耒'),('耳'),('聿'),
('肉'),('臣'),('自'),
('至'),('臼'),('舌'),
('舛'),('舟'),('艮'),
('色'),('艸'),('虍'),
('虫'),('血'),('行'),
('衣'),('襾'),
('見'),('角'),('言'),
('谷'),('豆'),('豕'),
('豸'),('貝'),('赤'),
('走'),('足'),('身'),
('車'),('辛'),('辰'),
('辵'),('邑'),('酉'),
('釆'),('里'),
('金'),('長'),('門'),
('阜'),('隶'),('隹'),
('雨'),('青'),('非'),
('面'),('革'),('韋'),
('韭'),('音'),('頁'),
('風'),('飛'),('食'),
('首'),('香'),
('馬'),('骨'),('高'),
('髟'),('鬥'),('鬯'),
('鬲'),('鬼'),
('魚'),('鳥'),('鹵'),
('鹿'),('麥'),('麻'),
('黃'),('黍'),('黑'),
('黹'),
('黽'),('鼎'),('鼓'),
('鼠'),
('鼻'),('齊'),
('齒'),
('龍'),('龜'),
('龠');
INSERT 0 214
collatest=#
collatest=# insert into zhtw_radicals_icu select * from zhtw_radicals;
INSERT 0 214
collatest=#
接著就排序看看：
collatest=# select string_agg(chars, ',' order by chars) from zhtw_radicals;
string_agg
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
一,丨,丶,丿,乙,亅,二,亠,人,儿,入,八,冂,冖,冫,几,凵,刀,力,勹,匕,匚,匸,十,卜,卩,厂,厶,又,口,,土,士,夂,夊,夕,大,女,子,宀,寸,小,尢,尸,屮,山,巛,工,己,巾,干,幺,广,廴,廾,弋,弓,彐,彡,彳,心,戈,戶,手,支,攴,文,斗,斤,方,无,日,曰,月,木,欠,止,歹,殳,毋,比,毛,氏,气,水,火,爪,父,爻,爿,片,牙,牛,犬,玄,玉,瓜,瓦,甘,生,用,田,疋,疒,癶,白,皮,皿,目,矛,矢,石,示,禸,禾,穴,立,竹,米,糸,缶,网,羊,羽,老,而,耒,耳,聿,肉,臣,自,至,臼,舌,舛,舟,艮,色,艸,虍,虫,血,行,衣,襾,見,角,言,谷,豆,豕,,貝,赤,走,足,身,車,辛,辰,辵,邑,酉,釆,里,金,長,門,阜,隶,隹,雨,青,非,面,革,韋,韭,音,頁,風,飛,食,首,香,馬,骨,高,髟,鬥,鬯,鬲,鬼,魚,鳥,鹵,鹿,麥,麻,黃,黍,黑,黹,黽,鼎,鼓,鼠,鼻,齊,齒,龍,龜,龠
(1 row)
collatest=#
collatest=# select string_agg(chars, ',' order by chars) from zhtw_radicals_icu;
string_agg
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
一,丨,丶,丿,乙,亅,二,亠,人,儿,入,八,冂,冖,冫,几,凵,刀,力,勹,匕,匚,匸,十,卜,卩,厂,厶,又,口,,土,士,夂,夊,夕,大,女,子,宀,寸,小,尢,尸,屮,山,巛,工,己,巾,干,幺,广,廴,廾,弋,弓,彐,彡,彳,心,戈,戶,手,支,攴,文,斗,斤,方,无,日,曰,月,木,欠,止,歹,殳,毋,比,毛,氏,气,水,火,爪,父,爻,爿,片,牙,牛,犬,玄,玉,瓜,瓦,甘,生,用,田,疋,疒,癶,白,皮,皿,目,矛,矢,石,示,禸,禾,穴,立,竹,米,糸,缶,网,羊,羽,老,而,耒,耳,聿,肉,臣,自,至,臼,舌,舛,舟,艮,色,艸,虍,虫,血,行,衣,襾,見,角,言,谷,豆,豕,,貝,赤,走,足,身,車,辛,辰,辵,邑,酉,釆,里,金,長,門,阜,隶,隹,雨,青,非,面,革,韋,韭,音,頁,風,飛,食,首,香,馬,骨,高,髟,鬥,鬯,鬲,鬼,魚,鳥,鹵,鹿,麥,麻,黃,黍,黑,黹,黽,鼎,鼓,鼠,鼻,齊,齒,龍,龜,龠
(1 row)
collatest=#
上面看起來好像沒什麼問題。。不過因為資料預先已經排序完了，因此再測一下。這次換成另外亂塞一點資料再看看。其中包含了目前電腦可以有納入的最多筆劃的字～(龍龍龍龍，𪚥)
collatest=# create table testsort(chars varchar collate "zh_TW");
CREATE TABLE
collatest=# insert into testsort values
('二'),('𪚥'),('子'),('升')
,('傅'),('楊'),('一'),('安')
,('何'),('我'),('灑'),('杜')
,('官'),('龘');
INSERT 0 14
collatest=#
collatest=# create table testsort_icu(chars varchar collate "zh-Hant-TW-x-icu");
CREATE TABLE
collatest=# insert into testsort_icu select * from testsort;
INSERT 0 14
collatest=#
排序之後，仔細看一下。。。發現排序的標準好像不一樣。。
collatest=# select chars,chars::bytea as hex_code from testsort order by chars; chars | hex_code -------+------------ 𪚥 | \xf0aa9aa5 一 | \xe4b880 二 | \xe4ba8c 何 | \xe4bd95 傅 | \xe58285 升 | \xe58d87 子 | \xe5ad90 安 | \xe5ae89 官 | \xe5ae98 我 | \xe68891 杜 | \xe69d9c 楊 | \xe6a58a 灑 | \xe78191 龘 | \xe9be98 (14 rows) collatest=# collatest=# select chars,chars::bytea as hex_code from testsort_icu order by chars; chars | hex_code -------+------------ 一 | \xe4b880 二 | \xe4ba8c 子 | \xe5ad90 升 | \xe58d87 安 | \xe5ae89 何 | \xe4bd95 我 | \xe68891 杜 | \xe69d9c 官 | \xe5ae98 傅 | \xe58285 楊 | \xe6a58a 灑 | \xe78191 龘 | \xe9be98 𪚥 | \xf0aa9aa5 (14 rows) collatest=#
這邊可以發現，預設的 libc 對中文排序是依照 UTF8 的 hex code 排序，而 ICU 則是依照筆劃（Stroke）排序的。
最後有幾個注意事項
有時我們會希望依照部首（Radical）排序，但目前還沒有在 Postgres 裡面找到方法。。。這可能得在程式碼上多多著墨，各顯神通了
若有自訂的擴增編碼，對擴增的 BIG5 難字可能沒有預期的排序結果
參考資料：
PG字符：使用collation设置排序规则 - 雄起 - OSCHINA
其他資料庫對中文排序的支援方式
沒有留言:
張貼留言