Collation

Collation 为执行引擎中的文本排序与比较提供规则。Collation 对本地化很有用，因为不同语言或不同国家/地区的文本排序规则并不相同，而且这些排序规则通常彼此不兼容。例如，在英语中，字母 y 位于 x 和 z 之间；而在立陶宛语中，字母 y 位于 i 和 j 之间。因此系统支持不同的 collation。用户在执行排序和比较操作时，必须选择要使用的 collation。

默认使用 BINARY collation。这意味着字符串只按其二进制内容进行排序和比较。对于标准 ASCII 字符（即 A-Z 字母与 0-9 数字）这通常是合理的，但对特殊 unicode 字符通常不太合适。不过，这也是目前为止速度最快的排序与比较方式。因此，除非确有需要，建议保持使用 BINARY collation。

BINARY collation 也可以使用别名 C 和 POSIX。

警告 Goose 的 collation 支持存在一些已知限制，并且有若干计划中的改进。

使用 Collation

在 Goose 的独立安装中，内置了三种 collation：NOCASE、NOACCENT 和 NFC。NOCASE collation 会在比较时忽略大小写；NOACCENT collation 会在比较时忽略重音符号；NFC collation 会执行基于 NFC 规范化的比较。更多信息参见 Unicode normalization。

SELECT 'hello' = 'hElLO';

false

SELECT 'hello' COLLATE NOCASE = 'hElLO';

true

SELECT 'hello' = 'hëllo';

false

SELECT 'hello' COLLATE NOACCENT = 'hëllo';

true

多个 collation 可以通过点运算符链式组合。需要注意的是，并非所有 collation 都能互相组合。通常 NOCASE collation 可以与其他 collator 组合，但大多数其他 collation 之间不能组合。

SELECT 'hello' COLLATE NOCASE = 'hElLÖ';

false

SELECT 'hello' COLLATE NOACCENT = 'hElLÖ';

false

SELECT 'hello' COLLATE NOCASE.NOACCENT = 'hElLÖ';

true

默认 Collation

到目前为止看到的 collation 都是按表达式级别指定的。你也可以指定默认 collator，作用范围可以是整个数据库，也可以是基表列。可通过 PRAGMA default_collation 指定全局默认 collator。当未显式指定其他 collator 时，就会使用它。

SET default_collation = NOCASE;
SELECT 'hello' = 'HeLlo';

true

创建表时也可以为列单独指定 collation。当该列参与比较时，会使用该列的 collation 来执行比较。

CREATE TABLE names (name VARCHAR COLLATE NOACCENT);
INSERT INTO names VALUES ('hännes');

SELECT name
FROM names
WHERE name = 'hannes';

hännes

但这里需要注意，不同的 collation 不能直接组合。当你要比较设置了不同 collation 的列时，这会带来问题。

SELECT name
FROM names
WHERE name = 'hannes' COLLATE NOCASE;

ERROR: Cannot combine types with different collation!

CREATE TABLE other_names (name VARCHAR COLLATE NOCASE);
INSERT INTO other_names VALUES ('HÄNNES');

SELECT names.name AS name, other_names.name AS other_name
FROM names, other_names
WHERE names.name = other_names.name;

ERROR: Cannot combine types with different collation!

此时需要手动覆盖 collation：

SELECT names.name AS name, other_names.name AS other_name
FROM names, other_names
WHERE names.name COLLATE NOACCENT.NOCASE = other_names.name COLLATE NOACCENT.NOCASE;

name	other_name
hännes	HÄNNES

ICU Collation

到目前为止看到的 collation 都不依赖地区，也不遵循特定地区规则。如果你希望遵循某个地区或语言的规则，需要使用 ICU collations。为此，你需要先加载 ICU 扩展。

加载该扩展后，会向数据库添加一组语言和地区专用的 collations。你可以通过 PRAGMA collations 命令或查询 pragma_collations 函数来查看它们。

PRAGMA collations;
SELECT list(collname) FROM pragma_collations();

[af, am, ar, ar_sa, as, az, be, bg, bn, bo, br, bs, ca, ceb, chr, cs, cy, da, de, de_at, dsb, dz, ee, el, en, en_us, eo, es, et, fa, fa_af, ff, fi, fil, fo, fr, fr_ca, fy, ga, gl, gu, ha, haw, he, he_il, hi, hr, hsb, hu, hy, icu_noaccent, id, id_id, ig, is, it, ja, ka, kk, kl, km, kn, ko, kok, ku, ky, lb, lkt, ln, lo, lt, lv, mk, ml, mn, mr, ms, mt, my, nb, nb_no, ne, nfc, nl, nn, noaccent, nocase, om, or, pa, pa_in, pl, ps, pt, ro, ru, sa, se, si, sk, sl, smn, sq, sr, sr_ba, sr_me, sr_rs, sv, sw, ta, te, th, tk, to, tr, ug, uk, ur, uz, vi, wae, wo, xh, yi, yo, yue, yue_cn, zh, zh_cn, zh_hk, zh_mo, zh_sg, zh_tw, zu]

这些 collations 的使用方式与前文其他 collations 相同。它们也可以与 NOCASE collation 组合。例如，如需使用德语的 collation 规则，可以使用以下代码片段：

CREATE TABLE strings (s VARCHAR COLLATE DE);
INSERT INTO strings VALUES ('Gabel'), ('Göbel'), ('Goethe'), ('Goldmann'), ('Göthe'), ('Götz');
SELECT * FROM strings ORDER BY s;

"Gabel", "Göbel", "Goethe", "Goldmann", "Göthe", "Götz"