SQLite COLLATE NOCASE 和 BINARY 对排序的影响

同一张表，同一批名字，换一个 COLLATE，ORDER BY 的结果就变了。表里存着 apple、Banana、APPLE、banana、Cherry、cherry 这几个词，按默认排序跑一遍，再按 NOCASE 跑一遍，两份结果的第一行就不一样。数据没有改，SQL 除了 COLLATE 什么都没动，排序却像换了规则。这篇笔记用本地 :memory: 数据库把这个现象复现出来，定位到 BINARY 和 NOCASE 两条比较规则的差别，再给出选哪条、以及已经建错的表怎么修，全部命令都可以直接复制验证。

现象：同一批大小写混合的字符串，两种排序各排各的

建一张最小的表，列声明里不写 COLLATE，插进一组大小写混合的名字：

CREATE TABLE t_name(name TEXT);
INSERT INTO t_name VALUES ('apple'), ('Banana'), ('APPLE'), ('banana'), ('Cherry'), ('cherry'), ('10'), ('2');

跑默认排序，也就是 BINARY：

SELECT name FROM t_name ORDER BY name ASC;

结果是：

10
2
APPLE
Banana
Cherry
apple
banana
cherry

这个输出要按字符码位来读。数字字符 ‘1’、‘2’ 的码位在字母前面，所以 ‘10’、‘2’ 排在最前面。‘10’ 排在 ‘2’ 前面，不是数值比较，是字典序比较第一个字符 ‘1’ 小于 ‘2’。大写字母的码位区间在小写字母前面，所以 APPLE、Banana、Cherry 这三个首字母大写的词排在 apple、banana、cherry 前面。同一个词的大小写版本被拆开了：APPLE 在很前面，apple 在很后面，中间隔着 Banana、Cherry。按这个结果去翻一页用户名列表，找 apple 要翻两处，一处在 A 区，一处在 a 区。

换成 NOCASE 再跑：

SELECT name FROM t_name ORDER BY name COLLATE NOCASE ASC;

结果是：

10
2
APPLE
apple
Banana
banana
Cherry
cherry

数字段没有变，‘10’ 仍在 ‘2’ 前面，NOCASE 只管字母大小写，不管数字字符的字典序问题。字母段变了：APPLE 和 apple 挨在一起，Banana 和 banana 挨在一起，Cherry 和 cherry 挨在一起。大小写版本不再被拆成两段，而是按忽略大小写后的字母顺序归组。组内 APPLE 在 apple 前面，这个组内顺序是 BINARY 规则的残留：NOCASE 比较时先按忽略大小写后的值分组，分不出高下时，SQLite 会用 BINARY 再比一次做决胜，大写字母码位小，所以大写版本在组内排前面。组内顺序不是随机的，是 BINARY 兜底的结果。

再看一个容易踩坑的细节：NOCASE 只对 ASCII 字母 A-Z、a-z 有效。表里如果有带重音的字母，或者中文，NOCASE 不会把它们当成同一个字母的不同大小写：

CREATE TABLE t_uni(v TEXT);
INSERT INTO t_uni VALUES ('Äpfel'), ('äpfel'), ('Apple'), ('apple');
SELECT v FROM t_uni ORDER BY v COLLATE NOCASE ASC;

Ä 和 ä 在 NOCASE 下仍按各自的码位排，不会归到同一组。以为 NOCASE 能处理全部 Unicode 大小写，是对这个规则最常见的误读。SQLite 文档里写得很明确，内置的 NOCASE 只折叠 ASCII，超出这个范围要自定义 collation。

还有一个现象值得单独记：COLLATE 不只影响 ORDER BY，也影响比较和去重。跑下面两条语句：

SELECT COUNT(*) FROM t_name WHERE name = 'apple';
SELECT COUNT(*) FROM t_name WHERE name = 'apple' COLLATE NOCASE;

第一条只命中 apple 这一行，第二条命中 apple 和 APPLE 两行。WHERE 里的等值比较默认也是 BINARY，COLLATE 一改，匹配范围就跟着变。排序乱了的表，查询条件里也可能藏着同样的大小写问题，修排序时要连 WHERE、GROUP BY、DISTINCT 一起看。

判据：先确认排序异常是不是 collation 造成的

碰到排序结果里大小写版本被拆开，先不要改业务代码，也不要怀疑客户端显示。跑三条语句做区分：

SELECT sql FROM sqlite_master WHERE name = 't_name';
PRAGMA table_info(t_name);
SELECT name FROM t_name ORDER BY name ASC;
SELECT name FROM t_name ORDER BY name COLLATE NOCASE ASC;

第一条看建表语句里列后面有没有写 COLLATE NOCASE，第二条看列定义，第三、四条是同一批数据的两种排序对照。把两份排序输出并排看：如果 BINARY 版大小写分段、NOCASE 版大小写归组，问题就锁定在 collation 上，跟索引、字符集、分页都没有关系。如果两种排序输出完全一样，说明列里本来就没有大小写混合，或者列定义里已经写死了某一种 collation，ORDER BY 后面加的 COLLATE 被列定义覆盖了，要回头查建表语句。

判据里要加一条索引检查，因为它是最大的干扰项：

SELECT name, sql FROM sqlite_master WHERE type = 'index' AND tbl_name = 't_name';

索引在创建时就绑定了列的 collation。列定义是 COLLATE NOCASE，索引就按 NOCASE 建；列定义没有写，索引就按 BINARY 建。查询时即使 ORDER BY 后面写了 COLLATE NOCASE，优化器也可能因为索引顺序不匹配而放弃走索引，改成全表扫描加排序，结果正确但变慢。判据的完整链条是：列定义决定默认 collation，索引继承列定义，查询时的 COLLATE 决定本次比较规则，三者不一致时，结果可能对、性能可能差。

第三个判据针对唯一约束：

CREATE TABLE t_u1(v TEXT UNIQUE);
INSERT INTO t_u1 VALUES ('apple');
INSERT INTO t_u1 VALUES ('APPLE');

上面两条 INSERT 在 BINARY 默认下都能成功，表里同时存在 apple 和 APPLE。把列改成 v TEXT UNIQUE COLLATE NOCASE 再试，第二条会报唯一约束冲突。判据做到这一步，就能区分“排序看着乱”和“去重逻辑本身就按大小写敏感在跑”这两种不同的问题，前者改查询，后者要改表定义。

根因：BINARY 按码位比，NOCASE 只折叠 ASCII 大小写

BINARY 是 SQLite 的默认 collation，比较规则是 memcmp 逐字节比字符编码的码位。ASCII 表里数字字符在 0x30-0x39，大写字母在 0x41-0x5A，小写字母在 0x61-0x7A，这个码位区间直接决定了默认排序：数字在前，大写在中，小写在后。BINARY 不认识“同一个字母的大小写版本”，它只认识码位，A 和 a 是两个不同的字节，排出来的位置自然隔得很远。

NOCASE 的规则是在 BINARY 之前加一步 ASCII 大小写折叠：比较时把 A-Z 先映射到 a-z，再按码位比。折叠之后 APPLE 和 apple 变成同一个比较键，归到同一组，组内再用 BINARY 做决胜，大写版本排前面。折叠范围只覆盖 ASCII，Ä、é、中文字符不在折叠表里，各自按原码位排。NOCASE 不是“忽略一切大小写”，是“忽略 ASCII 大小写”，这个边界要记准。

列定义、查询、索引三处都可以写 COLLATE，优先级也是一条链：查询里 ORDER BY 后面显式写的 COLLATE 优先级最高，会覆盖列定义；列定义里写的 COLLATE 是默认值，查询没写时就用它；索引创建时按当时列的 collation 定序，之后改列定义不会自动重建索引。排序异常的现场，很多是三处写得不一致：列定义没写，查询里写了 NOCASE，索引却是 BINARY，查询结果对了，执行计划却走了全表排序。

跟别的数据库对照一下，能帮你避开迁移时的坑。PostgreSQL 的默认排序依赖数据库的 locale，同一个查询在 C locale 和 en_US locale 下大小写顺序可能不一样。MySQL 的 utf8mb4_0900_ai_ci 这类 ci 排序规则默认就不区分大小写，还不区分重音。SQLite 的 NOCASE 只折叠 ASCII，比 MySQL 的 ci 窄得多。迁移 SQL 时不能只搬 COLLATE NOCASE 这个写法，要连它覆盖的字符范围一起看，否则原来在 MySQL 里归到一组的重音字母，到了 SQLite 里又被拆开了。

修法：查询层改写法，表定义层改默认值，选错位置会白修

修法分两层，选哪层看问题的影响面。

影响面只在某一个查询，比如一个列表页要按忽略大小写排序，别的查询仍要区分大小写。写法是在这个查询的 ORDER BY 后面显式写 COLLATE：

SELECT name FROM t_name ORDER BY name COLLATE NOCASE ASC;

这个写法只改本次排序，不动表定义，不动索引，不影响 WHERE 的默认比较。代价是这个查询可能走不了现有的 BINARY 索引，要接受一次排序开销，或者给这个查询单独建一个 NOCASE 索引：

CREATE INDEX idx_name_nocase ON t_name(name COLLATE NOCASE);

索引表达式里的 COLLATE 要跟查询里写的一致，优化器才能匹配上。查询写 NOCASE、索引是 BINARY，两个对不上，索引就白建了。

影响面是整张表，比如用户名列从业务上就该不区分大小写，排序、比较、去重都要按 NOCASE。写法是改列定义：

CREATE TABLE t_user2(name TEXT COLLATE NOCASE UNIQUE);
INSERT INTO t_user2 VALUES ('apple');
INSERT INTO t_user2 VALUES ('APPLE');

第二条 INSERT 会报唯一约束冲突，这就是想要的效果。已经建好的表不能直接改列的 COLLATE，SQLite 的 ALTER TABLE 改不了列定义，要走重建流程：建一张新表，写好 COLLATE NOCASE，把旧数据导过去，删旧表，改名，再重建索引。数据量小的表可以直接在 sqlite3 命令行里跑，数据量大的表要先备份再操作，重建期间写操作要停掉，避免导数过程中丢新写入的行。

一个要提醒的写法是 ORDER BY LOWER(name)。它能达到忽略大小写的排序效果，但代价是每行都要算一次 LOWER，索引也用不上，数据量一大就变慢。LOWER 同样只处理 ASCII，在 SQLite 里跟 NOCASE 的折叠范围接近，但它把比较键改成了函数结果，优化器更难匹配索引。能用 COLLATE NOCASE 解决的排序问题，不要用 LOWER 绕过去。同样，ORDER BY UPPER(name) 也是同类偏方，组内顺序和 NOCASE 的 BINARY 兜底还不一样，混用会让排序结果更难预测。

还有一个修法误区是只改 ORDER BY、不改 WHERE。列表页排序改成 NOCASE 了，搜索框里输入 apple 仍按 BINARY 匹配，APPLE 那行搜不出来，用户会觉得排序修好了、搜索还是坏的。修法要成套：排序、等值比较、GROUP BY 分组、UNIQUE 去重，业务上不区分大小写的地方，COLLATE 要统一写，否则修一处、漏三处。

验证：三组对照，确认规则和修法都生效

用现象一节的同一张表跑三组查询，对照输出：

-- A：默认 BINARY，大小写分段
SELECT name FROM t_name ORDER BY name ASC;
-- B：NOCASE，大小写归组
SELECT name FROM t_name ORDER BY name COLLATE NOCASE ASC;
-- C：NOCASE 等值比较，大小写都命中
SELECT name FROM t_name WHERE name = 'apple' COLLATE NOCASE ORDER BY name COLLATE NOCASE ASC;

预期结果：A 组是 10、2、APPLE、Banana、Cherry、apple、banana、cherry，大小写分段；B 组是 10、2、APPLE、apple、Banana、banana、Cherry、cherry，大小写归组，组内大写在前；C 组命中 APPLE 和 apple 两行，按 B 组顺序排。如果 B 组里 APPLE 和 apple 仍被隔开，说明查询里 COLLATE 没写对位置，或者列定义里写死的 collation 优先级更高，回头检查建表语句和 ORDER BY 的写法。

本地复现的完整命令，用 sqlite3 命令行一次跑完：

sqlite3 :memory: "CREATE TABLE t_name(name TEXT);
INSERT INTO t_name VALUES ('apple'), ('Banana'), ('APPLE'), ('banana'), ('Cherry'), ('cherry'), ('10'), ('2');
SELECT 'BINARY', name FROM t_name ORDER BY name ASC;
SELECT 'NOCASE', name FROM t_name ORDER BY name COLLATE NOCASE ASC;
SELECT 'NOCASE匹配', name FROM t_name WHERE name = 'apple' COLLATE NOCASE;
SELECT '建表语句', sql FROM sqlite_master WHERE name = 't_name';"

输出里能同时看到三组结果：BINARY 组大小写分段，NOCASE 组大小写归组，NOCASE 匹配组命中大小写两个版本。三组对上，说明规则理解对了，修法也生效了，问题闭环。

碰到 SQLite 排序里大小写版本被拆开、搜索时大小写版本搜不全、唯一约束里大小写版本能重复入库，先跑 BINARY 和 NOCASE 两份排序对照确认是不是 collation 问题，再查建表语句确认列定义和索引的 collation 是否一致，最后按影响面选查询层加 COLLATE 还是重建表改默认值。比较规则本身只有两条，复杂的是列定义、查询、索引三处写得不一致，排序结果才看着乱。