排序规则表达式 (COLLATE)

什么是排序规则表达式？

排序规则(Collation)是数据库中用于确定字符串如何排序和比较的规则集。在PostgreSQL中，你可以使用COLLATE子句来指定特定表达式应该使用哪种排序规则。

基本语法

expr COLLATE collation

其中：

• expr 是要应用排序规则的表达式
• collation 是排序规则的名称，可以带有模式限定符

排序规则的工作原理

当你在数据库中比较字符串时，结果可能会因使用的排序规则而不同。例如：

• 有些排序规则区分大小写，有些则不区分
• 有些排序规则会特殊处理重音字符
• 不同语言的排序规则会按照该语言的习惯排序字符

排序规则的优先级

1. 如果表达式中明确指定了COLLATE子句，则使用指定的排序规则
2. 如果没有明确指定，系统会从表达式中涉及的列派生排序规则
3. 如果表达式中没有涉及任何列，则使用数据库的默认排序规则

重要特点

• COLLATE子句的绑定优先级高于大多数运算符
• 需要时可以使用括号来明确优先级
• 在一个操作中，如果对多个参数使用不匹配的排序规则，会产生错误

常见用途

1. 自定义排序顺序

-- 使用"C"排序规则（按字节值排序）对结果进行排序
SELECT a, b, c FROM tbl WHERE ... ORDER BY a COLLATE "C";

2. 影响字符串比较操作

-- 使用"C"排序规则比较字符串
SELECT * FROM tbl WHERE a > 'foo' COLLATE "C";

这在处理多语言数据或需要特定语言规则进行比较时非常有用。

正确使用COLLATE的方法

✅ 正确：将COLLATE子句附加到运算符的输入参数

-- 这两种写法效果相同
SELECT * FROM tbl WHERE a > 'foo' COLLATE "C";
SELECT * FROM tbl WHERE a COLLATE "C" > 'foo';

❌ 错误：将COLLATE子句附加到运算符结果

-- 这是错误的！
SELECT * FROM tbl WHERE (a > 'foo') COLLATE "C";

这是错误的，因为它试图将排序规则应用于比较运算符>的结果，而该结果是布尔类型（boolean），不是可排序的数据类型。

常见排序规则

排序规则名称	描述	用途
"C"	按字节值排序	最快，适合机器排序
"POSIX"	传统的UNIX行为	类似于"C"
"en_US"	美式英语	英语数据
"zh_CN"	简体中文	中文数据
"und-u-ks-level1"	Unicode 大小写不敏感	需要忽略大小写

初学者提示

• 当你需要按特定语言规则排序或比较字符串时，使用COLLATE
• 在国际化应用程序中特别有用
• 记住将COLLATE应用于输入值，而不是运算符结果
• 排序规则可能影响性能，因为它们可能需要额外的处理

实际案例

多语言用户名排序

-- 创建一个有不同语言用户名的表
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username TEXT
);

-- 插入多语言用户名
INSERT INTO users (username) VALUES 
  ('Zürich'), ('apple'), ('Ángel'), ('Beijing');

-- 使用不同排序规则比较结果
SELECT username FROM users ORDER BY username;                 -- 使用默认排序规则
SELECT username FROM users ORDER BY username COLLATE "C";     -- 使用C排序规则
SELECT username FROM users ORDER BY username COLLATE "en_US"; -- 使用美式英语排序规则

通过这个例子，你可以看到不同的排序规则会产生不同的排序结果。