PostgreSQL Collation 怎麼調整，為什麼很多專案都選擇 Ctype

最近做了一期關於MYSQL  collation 的文字，所以基於比較的因素，PostgreSQL 的字符集，collation  等到底是怎麼回事，有什麼有趣的地方，我們也來一探究竟。在說起這個問題前，我們看下圖，圖中有三個部分

1  Encoding

2  collate

3  Ctype

按照我們的理解，Encoding 是編碼規則，collate是基於這個編碼規則中對於字元的排序，而 CTYPE 是什麼，Ctype是針對字元的大小寫比對起作用的配置。
所以在MYSQL 中一個位置的配置，在PG 中是透過 collate 和 ctype 聯合設定的，這相對可能給POSTGRESQL 更多的額靈活性。
select collname,collencoding,collcollate,collctype from pg_collation where collname  like 'C%';

下面我們提出一些關於collate 和 ctype的問題，繼續深入這個問題

1  C collcollate collctype  是 C 是一個針對POSTGRESQL 在字元進行排序和大小寫中值得推薦的選項嗎？

基於POSTGRESQL 中的編碼和作業系統之間的關係，在部分情形下編碼與相關的排序和大小寫比對的規則是不相容的，因為不同的編碼下的比對的規則與各個字符集之間包含的字元是有關的。 

而C 這個排序的規則，則是通用的一種方式，他非常的簡單，使用最簡單的規則，僅僅針對 a-z  A-Z 字母進行值的排序，所以在擔心由於作業系統或者一些不認知的字符集在排序規則或比較大小寫方面的不同（實際上是使用者對於選擇的collate,ctype的不認知），使用C collate ,C Ctype   是一個好的選擇，因為足夠的簡單，不容易產生另使用者疑惑的一些結果。

所以很多專案中尤其是外包專案中，可以發現很多的collate 和 ctype 是 C，而不是中文字符集也不是英文字符集，最簡單的未必是最好的，但最簡單的出現的問題也可能是最少的。同時選擇C 也是去除本地化作業系統的設定給POSTGRESQL 帶來影響的一個選擇。

2  不同的collate 是否可以比較大小　？

select *,name1 COLLATE "C" <  name2 COLLATE "C" as compare from collates;

可以看到如果是使用同樣的collate 是可以比較大大小的，上圖大寫的字母小於小寫的字母。

那麼我們兩個不同的COLLATE來進行比較會怎麼樣，看下圖也很清楚，兩個不同的 collate 是無法比較的。

3   建立資料庫的時候，選擇的collate 和 ctypte 是否可以改變 ？與初始化資料庫中的字符集

CREATE DATABASE database_c WITH ENCODING 'UTF8' LC_COLLATE='C' LC_CTYPE='C' TEMPLATE=template0;

以上我們建立的資料庫database_c 使用的字符集是 UTF8 ，同時使用的 COLLATE 和 CTYPE 為 C ，而如果你在建立資料庫時不指定collate, ctype則你的資料庫預設的collate, ctype 就為你初始化資料庫時給出的引數。

所以說到這裡我們還的說一下我們初始化資料庫中關於字符集的問題

這裡的說道初始化POSTGRESQL 資料庫的問題

initdb -D /pgdata/data --wal-segsize=64 -E UTF8 --locale=C  --lc-collate=C --lc-ctype=C

我們可以看到，在初始化的資料庫裡面的 locale, lc-collate , lc-ctype 等引數都選擇了C 後，在不給任何引數建立資料庫時，資料庫的引數就會和你初始化資料庫中的選擇是一致的。

下面的截圖，在建立資料庫的時候，不給任何引數的情況下，建立的資料庫的預設 encoding, collate,ctype 等均與初始化資料庫中給定的引數一致。

那麼這些有預設的資料庫，是否可以在執行後，修改encoding, collate ,ctype.

update pg_database set datcollate='en_US.utf8',datctype='en_US.utf8' where datname='database_c';

這裡我們可以看到原來的C 已經被修改成  en_us.utf8 等

update pg_database set datcollate='C',datctype='C' where datname='database_c';

那麼如果我們在資料庫初始化時選錯了collate,ctype 還有其他的方案嗎？有，我們可以在模板資料庫中做一個工作，將模板資料庫修改為你要的collate, ctype .

4  資料庫和表之間的collate, ctype 可以不同嗎 ？表的列可以有自己的collate,ctype 嗎？

create table collates (id serial primary key, name1 varchar(20) collate "en_US.utf8",name2 varchar(20));

insert into collates (name1,name2) values ('C','c');

insert into collates (name1,name2) values ('c','C');

insert into collates (name1,name2) values ('CC','cc');

insert into collates (name1,name2) values ('cc','CC');

insert into collates (name1,name2) values ('Cc','cC');

這裡我們的欄位中有獨立的一個欄位的collate 是 en_US.utf8 其他的字元欄位是C

在POSTGRESQL 中表是不能自己設定自己的collate,但是針對與表中的列是可以設定collate的。

5  Postgresql  的索引是否可以個性化建立，這對collate

POSTGRESQL 是可以針對列進行不同的collate 的索引的建立

create index idx_name1_c on collates (name1 collate "C");
create index idx_name1_zh on collates (name1 collate "zh_CN"); 

這裡針對一個欄位可以建立多個使用不同collate的索引方式，這也是眾多資料庫中獨有的一種方式。案例參見上圖和下面的步驟。

database_c=# explain select * from collates order by name1 collate "zh_CN";
                           QUERY PLAN                           
----------------------------------------------------------------
 Sort  (cost=1.11..1.12 rows=5 width=178)
   Sort Key: name1 COLLATE "zh_CN"
   ->  Seq Scan on collates  (cost=0.00..1.05 rows=5 width=178)
(3 rows)

database_c=# explain select * from collates order by name1 collate "C";
                           QUERY PLAN                           
----------------------------------------------------------------
 Sort  (cost=1.11..1.12 rows=5 width=178)
   Sort Key: name1 COLLATE "C"
   ->  Seq Scan on collates  (cost=0.00..1.05 rows=5 width=178)
(3 rows)

總結，在POSTGRESQL 中的collate ,ctype 大多依賴與系統，在最上面已經說明如果想簡單並且和系統無關，可以選擇C 作為collate, ctype 作為選擇，而如果是中文的環境，需要了解是否有中文比對的情況，如果有則需要使用對應的中文的collate, ctype 等。

所以此時你還認為collate, ctype 還是和DBA 無關的部分嗎？