开头还是介绍一下群，如果感兴趣polardb ,mongodb ,mysql ,postgresql ,redis 等有问题，有需求都可以加群群内有各大数据库行业大咖，CTO，可以解决你的问题。加群请联系 liuaustin3 ，在新加的朋友会分到2群。
最近做了一期关于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 无关的部分吗？
推荐阅读
发表评论