SQLite 类型亲和性：列类型的真实行为

亲和性是一种倾向，而不是规则

SQLite 是动态类型的。每个值自带自己的存储类（NULL、INTEGER、REAL、TEXT、BLOB），列的声明类型并不会严格限制你能往里放什么。声明类型真正的作用是给这一列一个亲和性：SQLite 会尝试把传入的值转换成这种首选的存储类。

看看当亲和性不足以阻止类型不匹配时会发生什么：

CREATE TABLE items (
    id INTEGER,
    name TEXT
);

INSERT INTO items VALUES (1, 'apple');
INSERT INTO items VALUES ('two', 'banana');

SELECT id, typeof(id), name FROM items;

第二行把字符串 'two' 存进了 INTEGER 列。SQLite 尝试把 'two' 转成数字，转不了（它不是数值），于是还是把它当作 TEXT 存了下来。typeof() 能显示每个值实际的存储类，而它并不总是和列声明暗示的一致。

从 Postgres 或 MySQL 转过来的人常常会对此感到意外。但这是有意这样设计的。

五种亲和性

非 STRICT 表中的每一列都恰好拥有下面其中一种：

• TEXT：倾向于字符串。
• NUMERIC：倾向于数字，但无法转换时也接受文本。
• INTEGER：和 NUMERIC 类似，但会把没有小数部分的值存成整数。
• REAL：倾向于浮点数。
• BLOB：没有倾向，你给什么就存什么。

BLOB 亲和性也被称为“无亲和性”，完全不声明类型时得到的就是它。

CREATE TABLE samples (
    a,                 -- BLOB affinity (no type declared)
    b TEXT,            -- TEXT
    c INTEGER,         -- INTEGER
    d REAL,            -- REAL
    e NUMERIC          -- NUMERIC
);

INSERT INTO samples VALUES ('42', '42', '42', '42', '42');
SELECT typeof(a), typeof(b), typeof(c), typeof(d), typeof(e) FROM samples;

同样的输入，也就是字符串 '42'，得到了五种不同的存储类型。每一列都按照自己的亲和性进行了转换（或者没有转换）。

SQLite 如何根据声明选出亲和性

容易让人困惑的地方在这里：SQLite 并没有一份固定的“合法”类型列表。列名后面几乎可以写任何东西，SQLite 会按下面的顺序在文本中查找子串来确定亲和性：

1. 包含 INT → INTEGER
2. 包含 CHAR、CLOB 或 TEXT → TEXT
3. 包含 BLOB，或者没写类型 → BLOB
4. 包含 REAL、FLOA 或 DOUB → REAL
5. 其他任何情况 → NUMERIC

这就是全部算法。它解释了很多怪现象：

CREATE TABLE quirks (
    a VARCHAR(255),       -- TEXT  (contains CHAR)
    b BIGINT,             -- INTEGER (contains INT)
    c DOUBLE PRECISION,   -- REAL (contains DOUB)
    d DECIMAL(10,2),      -- NUMERIC (matches none of the above)
    e BOOLEAN,            -- NUMERIC (matches none of the above)
    f DATETIME,           -- NUMERIC (matches none of the above)
    g FLOATING_POINTS,    -- INTEGER (contains INT - surprise!)
    h GIBBERISH           -- NUMERIC (matches none of the above)
);

SELECT name, type FROM pragma_table_info('quirks');

FLOATING_POINTS 会变成 INTEGER，因为 POINTS 里出现了子串 INT。规则从上往下匹配，第一条命中的规则生效。这也是为什么照搬其他数据库的类型，得到的结果可能和你预期的不一样。

亲和性的实际作用：插入时的转换

亲和性最关键的作用，是决定 SQLite 要转换你的值还是按原样存储。规则如下：

• TEXT 亲和性：数字和 BLOB 会被转换成文本。
• NUMERIC、INTEGER、REAL 亲和性：看起来像数字的文本会被转换，不像数字的文本保持为文本。
• BLOB 亲和性：不做任何转换。

CREATE TABLE conv (
    n NUMERIC,
    t TEXT,
    b BLOB
);

INSERT INTO conv VALUES ('123', 456, 789);
INSERT INTO conv VALUES ('12.5', 7.5, 'hello');
INSERT INTO conv VALUES ('hello', 'world', 'world');

SELECT n, typeof(n), t, typeof(t), b, typeof(b) FROM conv;

逐行来看：

• NUMERIC 列中的 '123' 变成了整数 123。文本到数字的转换成功，而且没有损失。
• '12.5' 变成了实数 12.5。
• NUMERIC 列中的 'hello' 仍然是文本，因为没有数字可以转换。
• TEXT 列会把数字转换成字符串形式。
• BLOB 列完全按给定的值存储，类型也原封不动。

INTEGER 和 REAL 的小差别

INTEGER 亲和性的行为和 NUMERIC 几乎完全一样，只有一点不同：像 3.0 这样没有真正小数部分的值，会被存成整数 3 以节省空间。

CREATE TABLE nums (
    i INTEGER,
    n NUMERIC
);

INSERT INTO nums VALUES (3.0, 3.0);
INSERT INTO nums VALUES (3.5, 3.5);

SELECT i, typeof(i), n, typeof(n) FROM nums;

3.0 在两列中都以 INTEGER 存储，因为 NUMERIC 也会做这个优化。3.5 保留了小数部分，仍然是 REAL。要点是：不要指望用 typeof() 判断一列声明的是 INTEGER 还是 REAL。它告诉你的是实际存储的类型，而这在每一行之间都可能不同。

亲和性什么时候会坑你

这种灵活性很方便，直到它不方便的时候。真实代码中会出现两种问题：

1. 脏数据混进来。 如果你的应用有 bug，把 'N/A' 写进了 INTEGER 列，SQLite 会照样存下来。之后对这一列做算术运算的查询会返回奇怪的结果或 NULL。没有错误，没有警告，只有悄无声息的数据损坏。

2. 比较结果变得奇怪。 排序和相等判断会区别对待不同存储类的值：

CREATE TABLE mixed (val INTEGER);
INSERT INTO mixed VALUES (10), (2), ('100'), ('20'), (3);

SELECT val, typeof(val) FROM mixed ORDER BY val;

整数按数值排序，文本按字典序排序，而且排在所有数字之后。所以你会先得到 2, 3, 10（按数值排序的整数），再得到 '20', '100'（按字母顺序排序的字符串）。这不是大多数人想要的结果。

如果插入由你掌控并且做了仔细的校验，普通表就够用了。如果不是，或者你只是想让数据库替你强制类型，还有更好的选择。

下一步：STRICT 表

SQLite 3.37 加入了 STRICT 表，它会关闭亲和性，拒绝与声明类型不符的值。想要动态类型时可以用默认行为，不想要时就能得到 Postgres 那样的强制检查。这就是下一页的内容。