大小写敏感的排序规则在ORDER BY排序子句中的行为

可以看到，在PostgreSQL、MySQL/MariaDB、SQL Server等数据库中，在排序子句ORDER BY中使用区分大小写的排序规则(Collation)，并不会使结果按照大小写排序。

三种数据库的行为都是一样的。

要想使排序结果按照大小写字母的顺序，或严格按照字符（Unicode）编码的码值/码点排序，需要指定其他的COLLATE。

目前所知，PostgreSQL中，指定 order by <col_name> collate "ucs_basic"/"C"/"POSIX";

SQL Server中指定，带_BIN/_BIN2缩写标识的collate；MySQL/MariaDB中指定_bin缩写的collate。即使用二进制(码值)比较排序。

如下为各数据库中ORDER BY排序严格按照字符编码值进行的示例：

• PostgreSQL

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

• MySQL/MariaDB：

MariaDB [test]> select * from OrderTest order by letter collate latin1_bin;
+--------+
| letter |
+--------+
| A      |
| B      |
| a      |
| b      |
+--------+
4 rows in set (0.002 sec)

• SQL Server

select * from OrderTest order by letter COLLATE Chinese_PRC_BIN2; -- 或者 COLLATE Chinese_PRC_BIN;

比较时不区分大小写

在数据库中搜索，很多时候都是要求不区分大小写。

搜索文本列不区分大小写的最高效的方法是，使用数据库使用不区分大小写的排序规则（case insensitive collation），前提是使用了正确的索引。

PostgreSQL中字符比较不区分大小写

PostgreSQL是和SQLite一样，【应该是】仅有的两个字符的排序规则默认区分大小写的数据库。

且，目前还不知，PostgreSQL有不区分大小写的默认支持的排序规则。

下面是几种PG实现不区分大小写的方式：

1. Nondeterministic Collations

PG支持CREATE COLLATE，创建自己的排序规则。详细可参考官方文档。

不确定排序规则，即：deterministic = false，在创建排序规则时指定。

CREATE COLLATION ndcoll (provider = icu, locale = 'und', deterministic = false);

2. ILIKE 关键字

可以使用关键字 ILIKE 代替 LIKE， 使匹配按照活动区域设置不区分大小写。这不是 SQL 标准，而是 PostgreSQL 的扩展。

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

3. 使用lower或upper函数

查询中借助lower或upper函数不区分大小写。

Order By子句中也可以使用lower或upper。

使用lower或upper之类的函数将导致无法有效利用索引。

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

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

LIKE查询

shop=# select * from OrderTest where lower(letter) like '%a%';
 letter
--------
 A
 a
(2 行记录)

lower或upper函数在MySQL/MariaDB、PostgreSQL、SQL Server中均支持，同时MySQL/MariaDB还支持lcase和ucase函数。

3. 使用 CITEXT 【不推荐，基本已弃用】

CITEXT 扩展是一个很好的替代 text 的方案。不过不很推荐，官方推荐使用nondeterministic collations，可以更好的处理不区分大小写、不区分重音，更好的处理Unicode字符等。

citext模块提供了不区分大小写的字符串类型 citext。本质上，它在比较值时在内部调用lower，除此之外，它的行为几乎与text完全一样。

【通用处理】通过附加列作为原数据列的副本，实现不区分大小写

实现表中数据不区分大小写的一种通用方案是，通过添加一个副本列，其内存储原数据列的小写内容（或大写内容）。

这是一种存储冗余信息的方法（redundant information）：原始数据存储在一列，标准化数据（可能是全部小写不变的）存储一列。若需要可以分别对两者进行索引，通常只会获取或显示原始数据。

关于从原始列生成标准化数据的冗余列的方法

上面提到的【通用处理】使用额外列存储原始数据的小写内容，有多种方式实现。

比如触发器，但是需要插入、更新触发器中，实现对副本列数据的计算生成。

最最合适的方式是使用生成列（Generated Column），也称为衍生列或计算列（Computed Column），下一篇将会对其进行介绍。

注：字符编码中的ICU——International Components for Unicode： Unicode的国际组件

使用正则表达式实现区分或不区分大小写

查询时不区分大小写，还有一个方法是，查询的条件中使用正则表达式。

不通过的数据库对正则的支持有所差异，后续会进行相关介绍。

但这绝对是一个很好的方法！