09. 五种存储类与类型亲和性

如果你之前用过 MySQL 或 PostgreSQL，一上来就会碰到 SQLite 第一个让人困惑的地方：别的数据库里你小心翼翼地给每列选 INT、VARCHAR、DATETIME，到了 SQLite 似乎怎么写都”能跑”。有人因此说”SQLite 是弱类型""SQLite 不挑类型”，这其实是误解。SQLite 不是弱类型，而是动态类型（dynamic typing）——它把”类型”绑在值上，而不是绑在列上。理解这一点，是后续所有建表、插数据、比大小操作不踩坑的基础。

为什么 SQLite 用动态类型

绝大多数 SQL 数据库（除了 SQLite）采用静态类型（static typing）：一个值是什么类型，取决于它”装”在哪个列里。比如某列声明为 INT，你塞进去字符串 '123'，数据库会先把字符串转成整数 123 再存。SQLite 反其道而行：一个值是什么类型，由值自己决定。你往某列插入整数 123，它就以整数存；你插入字符串 '123'，它就以文本存——哪怕这两列声明得”类型不同”。

这种灵活不是 bug，而是 SQLite 刻意保留的特性。它让 SQLite 写文件、嵌进手机 App 或浏览器时又小又稳。从 3.37.0 版本起，如果你实在想要传统”刚性类型校验”，可以用 STRICT 表（建表时加 STRICT 关键字），但入门阶段我们默认聊的是普通表。

五种存储类逐一拆解

下面这五种，是 SQLite 世界里数据的全部”真身”：

1. NULL：就是空值，表示”没有值”。任何列默认都可以装 NULL，除非你用 NOT NULL 约束拦住（下一章讲）。
2. INTEGER：有符号整数，按数值大小自动用 0、1、2、3、4、6 或 8 字节存储。布尔值也归到这里——TRUE/FALSE 本质就是整数 1 和 0。
3. REAL：浮点数，固定用 8 字节 IEEE 754 双精度存。带小数点的数、科学计数法都属于它。
4. TEXT：文本字符串，按数据库编码（UTF-8 / UTF-16）原样存。
5. BLOB：二进制大对象，你传进来什么字节就原样存什么字节，SQLite 绝不篡改。图片、压缩包、自定义序列化数据都走它。

注意一个关键区别：除了 INTEGER PRIMARY KEY 这一特殊列，普通列可以装任意存储类的值。也就是说，一张表的一列里，第一行存整数、第二行存文本、第三行存 BLOB，SQLite 完全允许。这正是动态类型和静态类型最拧巴的地方。

那”列类型”到底有什么用：类型亲和性

既然列不强制类型，为什么建表时还要写 INTEGER、TEXT 这些声明？答案是类型亲和性（Type Affinity）。亲和性可以理解为：这一列”更偏爱”哪种存储类。它是个建议，不是命令。当值能顺利转换时，SQLite 会顺着列的”偏好”把值转成对应存储类；转不了就原样存。

亲和性怎么定？看你在 CREATE TABLE 里写的”声明类型”字符串，按下面 5 条规则依次判断（顺序很重要）：

1. 声明类型里含 INT（如 INT、INTEGER、BIGINT）→ INTEGER 亲和性。
2. 含 CHAR、CLOB、TEXT（如 TEXT、VARCHAR(255)、CHAR(20)）→ TEXT 亲和性。注意 VARCHAR 因为含 CHAR，所以是 TEXT。
3. 含 BLOB，或完全不写类型 → BLOB 亲和性（也就是”无偏好”，来啥存啥）。
4. 含 REAL、FLOA、DOUB（如 REAL、FLOAT、DOUBLE）→ REAL 亲和性。
5. 以上都不命中 → NUMERIC 亲和性（比如 DATE、DECIMAL、BOOLEAN 都落在这里）。

亲和性真正做了什么：几个直观例子

光看规则太抽象，看个对照表就明白了。建一张实验表：

CREATE TABLE t1(
  t  TEXT,                              -- 规则2 → TEXT 亲和性
  nu NUMERIC,                           -- 规则5 → NUMERIC 亲和性
  i  INTEGER,                           -- 规则1 → INTEGER 亲和性
  r  REAL,                              -- 规则4 → REAL 亲和性
  no BLOB                              -- 规则3 → BLOB 亲和性（无偏好）
);

当我们插入同一串 '500.0' 文本时，各列实际存成了什么？

sqlite> INSERT INTO t1 VALUES('500.0','500.0','500.0','500.0','500.0');
sqlite> SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|text

看：TEXT 列老老实实存文本；NUMERIC 和 INTEGER 列把能转的整数转成了整数；REAL 列转成了浮点；BLOB 列因为”无偏好”，文本就是文本。再换成真正的整数 500 插入：

sqlite> DELETE FROM t1;
sqlite> INSERT INTO t1 VALUES(500,500,500,500,500);
sqlite> SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|integer

只有 TEXT 列把 500 变成了 '500' 文本，其余列按各自亲和性处理。这就是”声明类型是建议、实际存储由值决定”的活体演示。

什么时候该关心存储类

你可能会问：既然 SQLite 这么”随便”，我还有必要管存储类吗？非常有必要，三个场景直接受影响：

• 比大小与排序：INTEGER/REAL 之间按数值比，TEXT 按字符编码比，NULL 永远最小，BLOB 永远最大。如果你把数字当文本存（'10' 和 '9'），按字符串排会得到 '10' < '9' 这种反直觉结果。
• 占空间与性能：整数比等长文本省空间；BLOB 原样存但不参与文本比较优化。
• 函数行为：length() 对文本数字符、对 BLOB 数字节；日期函数只认 TEXT/REAL/INTEGER 三种形态的日期。

类比小结

把 SQLite 的类型系统想象成”贴了标签的收纳盒”：盒子外面写着”建议放整数”（亲和性），但真放进去一个写着字的纸条，SQLite 也会收下（动态类型），只是会尽量按标签把纸条上的字念成数字。而别的数据库像”带锁的格子”，标签写整数就只收整数，塞纸条直接拒收。理解了这个比喻，后面建表写 INTEGER、TEXT 时，你就知道它们到底是”硬性要求”还是”温柔建议”了。