SQLite STRICT 表：严格执行列类型

为什么会有 STRICT 表

SQLite 对类型的默认处理方式出了名地宽松。把一列声明为 INTEGER，再插入字符串 "hello"，SQLite 会无所谓地把这个字符串存下来。这种灵活性是 90 年代有意为之的设计选择，但它会让从 Postgres 或 MySQL 转过来的人感到意外，而且会掩盖 bug。

SQLite 3.37 加入的 STRICT 表解决了这个问题。你可以按表启用，从那以后，列类型就名副其实了。

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    age   INTEGER
) STRICT;

INSERT INTO users (name, age) VALUES ('Ada', 30);
SELECT * FROM users;

STRICT 关键字写在右括号后面。其他部分看起来和普通的 CREATE TABLE 一样。一旦你试图把类型不对的值放进某一列，区别就显现出来了。

STRICT 到底强制执行了什么

在普通表中，类型亲和性会尝试把值转换成声明的类型，转换不了就退而原样存储。在 STRICT 表中，类型不符就是错误。

CREATE TABLE accounts (
    id      INTEGER PRIMARY KEY,
    balance INTEGER
) STRICT;

INSERT INTO accounts (balance) VALUES (100);     -- ok
INSERT INTO accounts (balance) VALUES ('100');   -- ok, '100' converts cleanly
INSERT INTO accounts (balance) VALUES ('oops');  -- error

对非 STRICT 表做同样的操作，第三条插入会成功：SQLite 会欣然把字符串 'oops' 存进你声明为 INTEGER 的列里。几个月后，某个聚合查询返回了莫名其妙的结果，你得花一下午去排查。STRICT 让失败发生在插入的那一刻，你可以当场修复。

你会看到这样的错误：

Runtime error: cannot store TEXT value in INTEGER column accounts.balance

清晰、即时、无法忽视。

允许的五种类型

STRICT 表只接受五种类型名称：

• INTEGER：整数。
• REAL：浮点数。
• TEXT：字符串。
• BLOB：原始字节。
• ANY：任意类型，不做转换。

就这些。SQLite 平时接受的那些随意的别名，比如 VARCHAR(255)、DOUBLE、BOOLEAN、DATETIME、INT，在 STRICT 表中都会报错：

CREATE TABLE bad (
    id   INTEGER PRIMARY KEY,
    name VARCHAR(255)
) STRICT;

错误信息：

Parse error: unknown datatype for bad.name: "VARCHAR(255)"

解决办法是使用这五个标准名称之一。VARCHAR(255) 改成 TEXT，DATETIME 改成 TEXT（反正 SQLite 也是把日期存成 ISO 字符串），BOOLEAN 改成 INTEGER（用 0 和 1）。

ANY 这个例外通道

ANY 是唯一能让 STRICT 列存放不同类型值的类型，在键值表中做一个通用的 value 列时很有用：

CREATE TABLE settings (
    key   TEXT PRIMARY KEY,
    value ANY
) STRICT;

INSERT INTO settings VALUES ('max_users', 100);
INSERT INTO settings VALUES ('site_name', 'Coddy');
INSERT INTO settings VALUES ('ratio', 1.5);

SELECT key, value, typeof(value) FROM settings;

ANY 在 STRICT 表中很特殊：它存储值时不做类型强制转换，而同一个词在别处是会暗示这种转换的。字符串 '100' 保持为字符串；整数 100 保持为整数。上面查询中的 typeof() 调用证明了这一点。

在非 STRICT 表中，具有 ANY 亲和性的列会把看起来像数字的字符串强制转换成数字。STRICT 则会原封不动地保留原始类型。

STRICT 与 PRIMARY KEY

有一个细微的区别：在普通表中，INTEGER PRIMARY KEY 很特殊，它会成为 rowid 的别名，只接受整数。其他主键声明则比较宽松。

在 STRICT 表中，无论是不是主键，列类型都会被强制执行：

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL
) STRICT;

INSERT INTO products VALUES ('A-001', 'Widget', 9.99);
INSERT INTO products VALUES (42, 'Gizmo', 14.99);  -- 42 converts to '42'? No - error.

SELECT * FROM products;

第二条插入失败了。在非 STRICT 表中，42 会被悄悄存进 TEXT 类型的主键列。在这里，你会收到提示。

混用 STRICT 表和非 STRICT 表

STRICT 是按表设置的，而不是按数据库设置的。你可以在同一个文件中有一张严格的 users 表和一张宽松的 events 表。它们之间的外键照常工作。

CREATE TABLE users (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
) STRICT;

CREATE TABLE events (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    payload   -- no type declared; affinity rules apply
);

INSERT INTO users (name) VALUES ('Boris');
INSERT INTO events (user_id, payload) VALUES (1, 'login');
INSERT INTO events (user_id, payload) VALUES (1, 42);

SELECT id, payload, typeof(payload) FROM events;

events 表没有 STRICT，payload 也没有声明类型，所以你往里放什么它都接受。偶尔有用，但作为默认做法很危险。只有当你确实需要一个什么都能装的列时，才使用无类型存储。

什么时候用 STRICT

对于新的表结构，答案是“几乎总是”。代价很小：每张表多一个关键字，再记住五个标准类型名称。好处是那些平时会潜伏在数据中的 bug，会在引发它们的那次插入时就暴露出来。

以下情况可以不用 STRICT：

• 你在维护一个旧的 SQLite 数据库，现有的表结构依赖宽松的类型。
• 你的目标环境是 3.37（2021 年 10 月）之前的 SQLite，那里还没有这个关键字。
• 你确实想让某一列存放混合类型；即便如此，也应优先使用 STRICT 加一个 ANY 列，而不是非 STRICT 表，因为这样其他所有列仍然会被强制检查。

把普通表转换成 STRICT 表时的简短清单：

• 把 VARCHAR、CHAR、NVARCHAR 换成 TEXT。
• 把 DOUBLE、FLOAT、NUMERIC 换成 REAL。
• 把 BOOLEAN、BIT、TINYINT 换成 INTEGER。
• 把 DATETIME、TIMESTAMP、DATE 换成 TEXT（如果存的是 Unix 时间戳，就换成 INTEGER）。
• 在右括号后面加上 STRICT。

下一步：主键

STRICT 表收紧了列存储数据的方式。下一个值得收紧的是用哪一列来标识每一行。SQLite 的主键有几个特殊行为（尤其是 INTEGER PRIMARY KEY 和 rowid 相关的），在设计真正的表结构之前值得了解。