likes
comments
collection
share

[可能是]比较中区分大小写的唯二数据库 | 数据库教程9：PostgreSQL中对序规则、区分大小写和字符集的支持，及修改查看Collate

作者站长头像
站长
· 阅读数 13

这是我参与8月更文挑战的第11天，活动详情查看：8月更文挑战

字符集支持

字符集

对字符集的支持，可以允许你使用多种字符集(也称为编码)存储文本，包括单字节字符集（如ISO 8859）和多字节字符集（如 EUC——Extended Unix Code、UTF-8和Mule internal code）

字符集在 initdb 命令初始化PostgreSQL数据库集群是设置；也可以在创建数据库创建时指定。

关于PostgreSQL中的 LC_CTYPE 和 LC_COLLATE

  • LC_CTYPE —— character classification locale settings：本地设置的字符分类，即本地设置的字符类别，与字符集含义类似。

  • LC_COLLATE —— string sort order locale settings：本地设置的字符串排序，即本地设置的排序规则。

对于 C 或 POSIX locale，任何字符集都是允许的。其他则不一定，比如Windows下，UTF-8编码可以在任何locale下使用。

locale 直接含义为本地，在此处也可以翻译为“语言环境”，或“本地语言环境”。

设置字符集

设置默认编码

为PostgreSQL集群定义默认的字符集编码：

initdb -E EUC_JP

上面设置的默认字符集为：EUC_JP (Extended Unix Code for Japanese)。-E 是参数 --encoding 的缩写。

EUC_JP, EUC_CN, EUC_KR, EUC_TW.

为数据库指定编码和排序规则

数据库指定LC_COLLATE和LC_CTYPE后无法修改。

为一个数据库指定另一个编码（改编码需要与 初始化集群时的locale兼容）

createdb -E EUC_KR -T template0 --lc-collate=ko_KR.euckr --lc-ctype=ko_KR.euckr korean

上面创建一个名为 korean 的数据库，该数据库使用字符集 EUC_KR 和语言环境(locale) ko_KR。

也可以使用 SQL 命令实现：

CREATE DATABASE korean WITH ENCODING 'EUC_KR' LC_COLLATE='ko_KR.euckr' LC_CTYPE='ko_KR.euckr' TEMPLATE=template0;

上述命令指定复制 template0 数据库。复制任何其他数据库时，无法更改来自源数据库的数据的编码和区域设置，因为这可能会导致数据损坏。

查看数据库的编码

使用系统目录 pg_database 可以查看数据看编码；也可以使用 psql -l 或 \l 命令查看

shop=# \l
                                                        数据库列表
   名称    |  拥有者  | 字元编码(Encoding) |            校对规则(排序规则Collation)           |             Ctype              |       存取权限
-----------+----------+----------+--------------------------------+--------------------------------+-----------------------
 demo      | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
 postgres  | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
 shop      | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
 template0 | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres          +
           |          |          |                                |                                | postgres=CTc/postgres
 template1 | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres          +
           |          |          |                                |                                | postgres=CTc/postgres
(5 行记录)

可排序数据类型

概念上，可排序数据类型的每个表达式都有一个排序规则。

一般常见的内置的可排序数据类型（collatable data type）有 text, varchar 和 char，用户定义的基类型也可以标记为可整理...

如果表达式是列引用，则表达式的排序规则是列上已定义的排序规则。

如果表达式是常量，则排序规则是常量数据类型的默认排序规则。

更复杂表达式的排序规则源自其输入的排序规则。

如果要进行排序操作或其他需要排序规则的操作，则必须要有确定的表达式的排序规则，表达式排序规则不确定，排序将失败。

比如Order By、或<比较操作符，或函数，会使用输入表达式（输入列）的排序规则。

获取当前支持的collation

在所有平台中，default、 C 和 POSIX 这三种排序规则都是可用的。

其他的排序规则取决于操作系统的支持。

SQL标准的排序规则ucs_basic（用于UTF8编码的collation）也是可用的。

要查看当前可用的排序可以可以使用系统目录pg_collation。（在初始化数据库系统时，initdb会使用在操作系统中找到的所有语言环境的排序规则填充系统目录 pg_collat​​ion）

SELECT * FROM pg_collation;

或在 psql 中，执行 \dOS+ 命令查看。

shop=# SELECT * FROM pg_collation WHERE COLLNAME LIKE '%CN%';
  oid  |     collname     | collnamespace | collowner | collprovider | collisdeterministic | collencoding | collcollate | collctype  | collversion
-------+------------------+---------------+-----------+--------------+---------------------+--------------+-------------+------------+-------------
 12391 | bo-CN-x-icu      |            11 |        10 | i            | t                   |           -1 | bo_CN       | bo_CN      | 137.51.25
 12665 | ii-CN-x-icu      |            11 |        10 | i            | t                   |           -1 | ii_CN       | ii_CN      | 137.51.25
 12930 | ug-Arab-CN-x-icu |            11 |        10 | i            | t                   |           -1 | ug_Arab_CN  | ug_Arab_CN | 137.51.25
 12963 | zh-Hans-CN-x-icu |            11 |        10 | i            | t                   |           -1 | zh_Hans_CN  | zh_Hans_CN | 137.51.25
(4 行记录)


shop=# SELECT * FROM pg_collation;
  oid  |        collname        | collnamespace | collowner | collprovider | collisdeterministic | collencoding | collcollate |  collctype  | collversion
-------+------------------------+---------------+-----------+--------------+---------------------+--------------+-------------+-------------+-------------
   100 | default                |            11 |        10 | d            | t                   |           -1 |             |             |
   950 | C                      |            11 |        10 | c            | t                   |           -1 | C           | C           |
   951 | POSIX                  |            11 |        10 | c            | t                   |           -1 | POSIX       | POSIX       |
 12326 | ucs_basic              |            11 |        10 | c            | t                   |            6 | C           | C           |
 ...
 ...
 ...

CREATE COLLATION语句可以创建自定义的排序规则。

PostgreSQL的collation

创建一个存放ABab的数据表。后续操作基于此表演示。

CREATE TABLE OrderTest(
  letter char(1) NOT NULL
);
INSERT INTO OrderTest values('B'),('b'),('A'),('a');

PostgreSQL的排序规则似乎有些不同，其默认的排序规则，在比较操作中是区分大小写的，但是在排序操作中，是不区分大小写的，至少默认不是按照字母编码的值排序。

具体查看下面的结果：

比较操作区分大小写。

shop=# select * from OrderTest where letter ='A';
 letter
--------
 A
(1 行记录)

shop=# select * from OrderTest where letter ='a';
 letter
--------
 a
(1 行记录)

letter ='A'和letter ='a'是两个不同的结果。包括使用LIKE子句的比较，也是区分大小写的。

排序操作默认不严格按照字符编码值

shop=# select * from OrderTest order by letter;
 letter
--------
 a
 A
 b
 B
(4 行记录)

如上所示，默认的排序结果中，a 和 A 被视为相同并放在了一起；b 和 B 被视为相同并放在了一起。

指定排序规则'C'或'POSIX'，排序时严格区分码值。

或者SQL标准的排序规则ucs_basic（用于UTF8编码的collation），与'C'的行为一样。或者其他严格区分码值的排序规则

shop=# select * from OrderTest order by letter collate "ucs_basic";
 letter
--------
 A
 B
 a
 b
(4 行记录)

shop=# select * from OrderTest order by letter collate "C";
 letter
--------
 A
 B
 a
 b
(4 行记录)

shop=# select * from OrderTest order by letter collate "C" desc;
 letter
--------
 b
 a
 B
 A
(4 行记录)

列排序规则

排序规则（collation）特性允许指定每列甚至每个操作的数据的排序顺序和字符分类行为。

同时也可以解除数据库的 LC_COLLATE 和 LC_CTYPE 设置在创建后无法修改的限制。

指定列的排序规则

如下，在创建表时指定列的排序规则：

CREATE TABLE test1 (
    a text COLLATE "zh-Hans-CN-x-icu",
    b text COLLATE "zh-Hant-TW-x-icu"
);
insert into test1 values('A','a'),('b','B');

此时，如果执行下面的比较查询查询将会发生错误：

shop=# SELECT a < b FROM test1;
错误:  无法确定字符串比较中使用哪种排序规则
提示:  使用COLLATE子句来显示设置排序规则.

这是因为a和b有隐式的排序规则的冲突。

在查询中指定列或常量的排序规则 <column_name/constant_value> COLLATE "<collate_name>"。

如下：

shop=# SELECT a < b COLLATE "zh-Hans-CN-x-icu" FROM test1;
 ?column?
----------
 f
 t
(2 行记录)

查询中的显式使用排序规则

在某些情况下，不同的查询需要使用不同的排序规则来查询同一列。

例如，一个查询可能需要对列执行区分大小写的比较，而另一个查询可能需要对同一列执行不区分大小写的比较。这可以通过在查询内显式指定排序规则来完成：

在查询内使用COLLATE子句指定排序规则

在正常使用的SQL语句中的列名后面，指定COLLATE关键字及排序规则名，其他部分和以前的SQL一样，保持不变。

注意，COLLATE后的排序规则必选用双引号包含，如COLLATE "ucs_basic"、COLLATE "C"，否则会报错。MySQL/MariaDB、SQLServer中不需要双引号包含。

<column_name/constant_value> COLLATE "<collate_name>"

比如查询OrderTest表的按列排序。

-- 默认区分大小写
shop=# select * from OrderTest where letter='a';
 letter
--------
 a
(1 行记录)

似乎，==PostgreSQL的UTF-8编码下，未找到不区分大小写的排序规则【也可能是自己不会】==。

-- 指定查询使用的排序规则
shop=# select * from OrderTest where letter COLLATE "C" ='a';
 letter
--------
 a
(1 行记录)

shop=# select * from OrderTest where letter COLLATE "ucs_basic" ='a';
 letter
--------
 a
(1 行记录)

PostgreSQL中创建不同排序规则的索引

PostgreSQL中可以创建指定的排序规则的索引，即创建与列不同的排序规则的索引，一个列上创建多个不同索引。

详细可查看相关官方文档。

CREATE TABLE .. ( ..., field COLLATE <collation>, ... )
ALTER TABLE
CREATE INDEX .. ON ( field COLLATE <collation> )
CREATE INDEX .. ON ( (expr COLLATE <collation>) )
expr :: expr COLLATE <collation>
Possibly: CREATE COLLATE ..

参考