SQLite 混合类型排序：数字、文本、NULL、BLOB 的先后顺序

一张表里的一列混进了几种类型的值，ORDER BY 一跑，顺序看着完全不合直觉：NULL 排在最前面，数字按大小排，文本按字典序排，二进制数据排在最后面，还有一个文本形式的 ‘42’ 没有跟数字 42 排在一起，而是跑到了文本那一组里。数据没有插错，SQL 也没有报错，排序结果却跟“按大小排”这个直觉对不上。这篇笔记用本地 :memory: 数据库把这个现象复现一遍，定位到 SQLite 的类型排序规则，再给出两种让混合列按业务语义排的写法，全部命令都可以直接复制验证。

现象：一列里四种类型，排出来的顺序分成了四段

SQLite 的列不像别的数据库那样把类型卡得很死。一个没有声明类型的列，或者声明类型后仍被塞进不同存储类别的值，同一列里可以同时出现 NULL、整数、实数、文本和 BLOB。建一张最小的表验证：

CREATE TABLE t_mix(v);
INSERT INTO t_mix VALUES (NULL);
INSERT INTO t_mix VALUES (42);
INSERT INTO t_mix VALUES (3.14);
INSERT INTO t_mix VALUES ('apple');
INSERT INTO t_mix VALUES ('42');
INSERT INTO t_mix VALUES (x'00ff');
INSERT INTO t_mix VALUES (7);
INSERT INTO t_mix VALUES ('banana');

注意这张表的列声明是空的：CREATE TABLE t_mix(v) 里 v 后面没有写类型。这样做是有意的，列没有亲和性，插进去什么存储类别，存下来就是什么存储类别，排序时才能看到原始的类型顺序。如果把列写成 INTEGER，文本 ‘42’ 插进去时会被转成整数 42，混合现象就被亲和性掩盖了，复现会失败。后面判据一节还会回到这个坑。

跑默认升序：

SELECT v, typeof(v) AS t FROM t_mix ORDER BY v ASC;

结果是：

NULL | null
3.14 | real
7 | integer
42 | integer
apple | text
banana | text
42 | text
00ff | blob

这个输出要一行一行读。第一行是 NULL，单独一段。第二段是数字：3.14、7、42 按数值大小排，整数和实数混在一起比数值，不按整数在前、实数在后分。第三段是文本：apple、banana、‘42’ 按字典序排，‘42’ 这个文本没有跟数字 42 排在一起，它属于文本段，排在 banana 后面，因为按 BINARY 规则比较时数字字符的码位在小写字母后面。第四段是 BLOB：x’00ff’ 排在所有文本后面。

换成降序，整段顺序倒过来：

SELECT v, typeof(v) AS t FROM t_mix ORDER BY v DESC;

结果是 BLOB 在最前面，接着文本段倒序、数字段倒序，NULL 落到最后面。降序不是“每段内部倒序、段顺序不变”，而是整个比较结果取反，四段的先后也跟着倒过来。这一点跟单类型列降序时数值倒排的感觉不一样，混合列降序要按“整条规则倒置”来理解。

还有一个容易被误读的细节：数字段内部 3.14 排在 7 前面，7 排在 42 前面，这是数值比较。文本段内部 ‘42’ 排在 banana 后面，这是字典序比较，不是数值比较。同一条 ORDER BY 里，比较规则随值类型切换，数字按数值比，文本按字符比，BLOB 按字节比，跨类型时不比大小，只比类型段位。把这条规则记下来，后面的现象都能解释。

判据：先确认列里是不是真的混了类型

碰到排序结果异常，先不要改 SQL，也不要怀疑客户端显示。跑两条语句做区分：

SELECT v, typeof(v) AS t, COUNT(*) AS c FROM t_mix GROUP BY t ORDER BY t;
SELECT v, typeof(v) AS t FROM t_mix ORDER BY v ASC;

第一条按 typeof 分组计数，告诉你列里到底有几种存储类别、每种几个值。第二条是实际排序结果。把两条输出对照起来看：如果 typeof 里同时出现 null、integer、real、text、blob，排序输出又分成对应段落，问题就锁定在混合类型排序上，跟索引、字符集、客户端分页都没有关系。

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

SELECT sql FROM sqlite_master WHERE name = 't_mix';
PRAGMA table_info(t_mix);

看列声明里有没有类型。列声明是 INTEGER、TEXT、REAL、NUMERIC 时，SQLite 会按亲和性规则转换插进去的值，文本 ‘42’ 可能被转成整数，整数 42 存进 TEXT 列可能被转成文本。判据的完整链条是：列声明决定亲和性，亲和性决定实际存储类别，实际存储类别决定排序段位。只看 INSERT 语句里写的是什么类型不够，要看 typeof 查出来的实际类型。

第三个判据针对文本段内部：文本 ‘10’ 和文本 ‘2’ 的顺序。按字典序，‘10’ 排在 ‘2’ 前面，因为第一个字符 ‘1’ 小于 ‘2’。按数值，10 大于 2，应该排在后面。跑一下就能区分当前列走的是哪条比较：

CREATE TABLE t_txt(v);
INSERT INTO t_txt VALUES ('10'), ('2'), ('100');
SELECT v FROM t_txt ORDER BY v ASC;

输出是 10、100、2，这就是字典序的证据。如果业务想要的是数值顺序 2、10、100，那列里就不该存文本，或者排序时要先转类型，修法一节会给写法。

还有一个判据是 BLOB 段：BLOB 按字节逐个比较，不是按十六进制字符串比较。x’00ff’ 和 x’0100’ 比较时，第一个字节 0x00 小于 0x01，所以 x’00ff’ 在前，哪怕把它写成文本 ‘00ff’ 和 ‘0100’ 比较也是同样的字节顺序，巧合而已。换一组值验证：

CREATE TABLE t_blob(v);
INSERT INTO t_blob VALUES (x'ff'), (x'0100'), (x'00ff');
SELECT hex(v) FROM t_blob ORDER BY v ASC;

输出是 00FF、0100、FF，按首字节 0x00、0x01、0xFF 排。BLOB 段内部也是逐字节比，跟文本段的字典序思路接近，但比较单位是字节，不是字符。

根因：SQLite 的跨类型排序规则是固定的四段

SQLite 官方文档给出的排序规则是一条固定顺序：NULL 最小，接着是数字（整数和实数按数值比），接着是文本，接着是 BLOB 最大。用不等式写出来就是 NULL < 数字 < 文本 < BLOB。这个顺序跟值本身的大小没有关系，跨类型比较时只看段位，段位低的永远在前，段位高的永远在后。

数字段内部的规则是数值比较，整数和实数不分段。3.14、7、42 按 3.14 < 7 < 42 排，存储类别是 integer 还是 real 不影响数字段内部的顺序。文本段内部默认按 BINARY 规则比较，也就是按字符编码的码位逐个比，区分大小写，‘Z’ 排在 ‘a’ 前面，因为大写字母的码位小于小写字母。BLOB 段内部按 memcmp 逐字节比，短的 BLOB 是长的 BLOB 的前缀时，短的在前。

这个规则解释了现象一节的全部输出：NULL 段在最前，数字段按数值排，文本段按字典序排，文本 ‘42’ 不跟数字 42 排在一起，BLOB 段在最后。降序时整个比较取反，段位顺序跟着倒置，BLOB 跑到最前，NULL 落到最后。规则本身没有例外，例外都来自亲和性转换：你以为插的是文本，实际存下来是整数，段位就变了。

亲和性转换的规则也值得单独记一条。列声明包含 INT 时是 INTEGER 亲和性，文本形式的整数会被转成整数；列声明是 TEXT 时是 TEXT 亲和性，数字会被转成文本；列声明是空或者 BLOB 时没有亲和性，值保持原样。混合类型排序问题里，很多“排序乱了”的现场其实是亲和性在插数据时悄悄转了类型，排序规则只是忠实地按转完之后的类型执行。查 typeof 才能看到真相，查 INSERT 语句看不到。

跟别的数据库对照一下，能帮你避开迁移时的坑。PostgreSQL 里不同类型不能直接比较，混合列排序要先统一类型，否则会报错。MySQL 里数字和文本比较时会把文本往数字转，‘42’ 和 42 可能被当成相等。SQLite 选的是第三条路：不报错、不转换比较，只按固定段位排。这个选择让 SQLite 对混合数据很宽容，也让排序结果在不了解规则的人看来很奇怪。迁移 SQL 时不能只搬 ORDER BY，要连列类型和亲和性一起看。

修法：两种写法，按业务语义选

业务上对混合列的排序需求一般分两种，写法不一样，选错会把结果改错。

第一种需求：业务只关心数值大小，文本形式的数字也要按数值排，NULL 和 BLOB 要排到最后或者单独处理。写法是在排序键里先做类型归一，再排序：

SELECT v, typeof(v) AS t FROM t_mix
ORDER BY
  CASE WHEN typeof(v) IN ('integer', 'real') THEN 0 ELSE 1 END,
  CAST(v AS REAL) ASC;

这个写法里数字段的排序键是 0，非数字段是 1，数字先排，非数字后排。数字段内部按 CAST 成实数后的值排，文本 ‘42’ 转成 42 后会跟数字 42 排在一起。注意这个写法会改变非数字段的顺序：文本 ‘apple’ 转成实数是 0，会挤到数字段前面去，所以这个写法只适用于“列里应该全是数字，混进来的文本都是数字形式”这种场景，文本本身有业务含义时不能用。

第二种需求：业务要保留类型分组，但组内按业务顺序排，比如先排数字、再排文本、最后排 NULL 和 BLOB，或者反过来。写法是把 typeof 做成显式排序键：

SELECT v, typeof(v) AS t FROM t_mix
ORDER BY
  CASE typeof(v)
    WHEN 'integer' THEN 0
    WHEN 'real' THEN 0
    WHEN 'text' THEN 1
    WHEN 'blob' THEN 2
    WHEN 'null' THEN 3
  END,
  v ASC;

这个写法把段位写进了 CASE，数字段内部按数值排，文本段内部按字典序排，BLOB 段内部按字节排，NULL 段固定在最后。段位顺序完全由 CASE 控制，想把 NULL 放最前就把它的键改成最小，想把文本放数字前面就把文本键改成 0。规则从隐式的固定四段变成了显式的业务顺序，读 SQL 的人一眼就能看出来排序意图。

还有一种常见修法是入库时就把类型统一：数值列只存数字，文本列只存文本，混进来的 ‘42’ 在入库前转成 42 或者拒绝写入。这个修法不在 SQL 里，是在数据写入层。排序 SQL 写得再巧妙，也补不回入库时丢掉的类型信息。长期跑的表，修入库规则比修 ORDER BY 更划算，ORDER BY 里的 CASE 只适合给历史脏数据做查询时兜底。

一个要提醒的写法是 ORDER BY CAST(v AS TEXT)。它把所有值都转成文本再比，数字 7、42、100 会按字典序排成 100、42、7，数值顺序全乱。这个写法只在“业务本来就要按文本显示顺序排”时才对，想按数值排时不能用 CAST 成文本。同样，ORDER BY v + 0 这种把文本往数字转的写法，会把非数字文本转成 0，挤到最前面，也是只适合数字场景的偏方。

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

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

-- A：默认行为，四段顺序
SELECT v, typeof(v) AS t FROM t_mix ORDER BY v ASC;
-- B：显式段位，NULL 固定在最后
SELECT v, typeof(v) AS t FROM t_mix
ORDER BY CASE typeof(v) WHEN 'null' THEN 1 ELSE 0 END, v ASC;
-- C：数字归一，文本数字按数值排
SELECT v, typeof(v) AS t FROM t_mix
ORDER BY CASE WHEN typeof(v) IN ('integer', 'real') THEN 0 ELSE 1 END, CAST(v AS REAL) ASC;

预期结果：A 组是 NULL、3.14、7、42、apple、banana、‘42’、BLOB 的四段顺序；B 组前几行不再是 NULL，NULL 行被 CASE 键推到最后，非 NULL 行之间仍按默认四段排；C 组里数字 3.14、7、42 仍按数值排，文本 ‘42’ 转成 42 后会出现在数字段附近，不再跟在 banana 后面。如果 B 组里 NULL 还在最前面，说明 CASE 的 WHEN 条件没写对，或者 NULL 行的 typeof 不是 ‘null’，回头检查表里是不是存了空字符串 ‘’，空字符串的 typeof 是 text，不是 null。

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

sqlite3 :memory: "CREATE TABLE t_mix(v);
INSERT INTO t_mix VALUES (NULL), (42), (3.14), ('apple'), ('42'), (x'00ff'), (7), ('banana');
SELECT '默认', v, typeof(v) FROM t_mix ORDER BY v ASC;
SELECT 'NULL最后', v, typeof(v) FROM t_mix ORDER BY CASE typeof(v) WHEN 'null' THEN 1 ELSE 0 END, v ASC;
SELECT '类型计数', typeof(v), COUNT(*) FROM t_mix GROUP BY 1;"

输出里能同时看到三组结果：默认组是四段顺序，NULL 最后组 NULL 被推到末尾，类型计数告诉你列里每种类型各有几个。三组对上，说明规则理解对了，修法也生效了，问题闭环。

碰到 SQLite 排序结果分段、文本数字没跟数字排在一起、NULL 位置异常，先跑 typeof 分组计数确认列里混了几种类型，再查列声明确认亲和性有没有偷偷转类型，最后按业务语义选显式 CASE 段位还是入库统一类型。排序规则本身只有一条四段顺序，复杂的是入库时类型没管住，查询时才在 ORDER BY 里看到后果。