SQLite中更推荐使用严格表

背景

SQLite 以其灵活的类型系统著称，默认允许将任何类型的数据插入任何列——例如，你可以在 INTEGER 列中放入文本。这种设计虽然带来了便利，但也容易导致数据错误。作者在 Hacker News 上分享了一篇博客，主张优先使用 SQLite 的 严格表（STRICT tables） 功能，以强制启用严格类型检查，避免类型混乱引发的 bug。严格表自 SQLite 3.37.0（2021 年 11 月发布）起可用，但许多人并未充分重视这一特性。

核心内容

什么是严格表？

严格表通过在 CREATE TABLE 语句末尾添加 STRICT 关键字来定义。例如：

CREATE TABLE people (name TEXT) STRICT;

相比普通表（非严格表），严格表对列的类型实施更严格的控制，行为更接近传统 SQL 数据库。

严格表的优势

1. 防止插入/更新时的类型不匹配
   非严格表允许将文本放入 INTEGER 列，而严格表会拒绝这种操作：

   -- 非严格表：允许
   CREATE TABLE people_nonstrict (age INTEGER);
   INSERT INTO people_nonstrict (age) VALUES ('garbage'); -- 成功
   
   -- 严格表：拒绝
   CREATE TABLE people_strict (age INTEGER) STRICT;
   INSERT INTO people_strict (age) VALUES ('garbage'); -- 错误：无法将 TEXT 值存入 INTEGER 列
   

   如果值可以无损转换（如字符串 '123' 可以完美转为整数），严格表仍会接受。因此以下两行等价：

   INSERT INTO people_strict (age) VALUES ('123');
   INSERT INTO people_strict (age) VALUES (123);
   

   UPDATE 操作同样会进行类型校验。

2. 防止建表时使用无效列类型
   默认情况下，SQLite 允许创建带有任意类型名称的列，即使该类型不是 SQLite 支持的类型：

   CREATE TABLE tbl (name GARBAGE);
   CREATE TABLE tbl (name DATETIME);
   CREATE TABLE tbl (name JSON);
   CREATE TABLE tbl (name UUID);
   CREATE TABLE tbl (name BLOBB);
   

   这些语句都不会报错，容易导致混乱。严格表会拒绝这些无效类型，只允许 INT、INTEGER、REAL、TEXT、BLOB 和 ANY。此外，严格表要求每列必须声明类型，因此 CREATE TABLE tbl (name) 也会报错。

3. 仍然保留灵活性：ANY 类型
   如果某列确实需要存储任意类型，可以使用 ANY 数据类型：

   CREATE TABLE tbl (value ANY) STRICT;
   INSERT INTO tbl (value) VALUES (123);
   INSERT INTO tbl (value) VALUES ('text');
   INSERT INTO tbl (value) VALUES (12.34);
   INSERT INTO tbl (value) VALUES (X'8647');
   

   这样既享受严格表的其他约束，又保留了灵活性。

严格表的劣势

1. 无法将现有表转为严格表
   SQLite 没有 ALTER TABLE ... STRICT 命令。要迁移现有表，需手动创建新严格表并复制数据：

   CREATE TABLE new_people (name TEXT) STRICT;
   INSERT INTO new_people SELECT * FROM people; -- 如果原表有类型错误，此处会失败
   DROP TABLE people;
   ALTER TABLE new_people RENAME TO people;
   

   如果原表包含无效数据（如整数列中有文本），迁移时会出错，需要先清理或转换数据。

2. SQLite 官方立场不同
   SQLite 官方文档有专门页面《灵活类型的优势》（The Advantages Of Flexible Typing），认为灵活的类型行为是好的。作者虽然不同意，但承认灵活类型在某些场景下有用，例如纯键值存储、存储不同类型杂项属性，或直接导入脏 CSV 数据时。

3. 版本限制
   严格表仅支持 SQLite 3.37.0 及以上版本。旧版本无法读取包含严格表的数据库文件。

4. 潜在性能开销
   严格表在插入/更新时需要额外检查类型，理论上可能更慢。但作者的粗略测试（向 100 列表格插入数百万行）未发现明显差异，文件大小也相同。作者认为实际上不会造成性能问题，甚至可能因避免类型亲和性（affinity）错配而更快。

关键要点

• 严格表定义：在 CREATE TABLE 末尾添加 STRICT 关键字，强制列类型检查。
• 类型检查范围：仅允许 INT、INTEGER、REAL、TEXT、BLOB、ANY；拒绝无效类型名（如 DATETIME、JSON、UUID）。
• 无损转换宽容：值能无损转换（如 '123'→123）时仍被接受。
• 灵活性出口：使用 ANY 类型可在严格表中保留任意类型存储。
• 迁移困难：无法 ALTER 现有表为严格表，需新建表并复制数据，可能因脏数据失败。
• 版本要求：>= SQLite 3.37.0（2021年11月），旧版本无法读取。
• 性能影响：作者测试未发现明显性能下降，但理论上存在极小开销。
• 作者观点：严格表利大于弊，能提前暴露类型错误，提升数据完整性，推荐在新表中使用。

意义与影响

严格表是 SQLite 向传统关系数据库靠拢的重要一步，它在保留 SQLite 轻量、嵌入式优势的同时，提供了更严格的类型安全。对于开发者，这意味着：

• 减少隐蔽 bug：类型错误会在插入/更新时立即报错，而非等到查询时才发现异常数据。
• 提升代码可维护性：表结构意图更清晰，避免因类型名拼写错误（如 BLOBB）或误解（如 JSON 不是 SQLite 原生类型）导致的混乱。
• 促进最佳实践：强制声明列类型，有助于团队遵循规范化设计，特别适合 API 或数据存储层使用。
• 兼容性考量：如果项目需要兼容旧版 SQLite，或需要导入/导出不规则数据，严格表可能不适合。但作者建议至少对新表启用严格表，逐步推进。

尽管 SQLite 官方文档仍推崇灵活类型，但社区中严格表的受欢迎程度正在上升。这一特性对数据治理、质量保障有积极意义，尤其适合那些对数据类型敏感的应用场景（如金融数据、配置管理、日志存储）。未来，随着更多版本支持，严格表很可能成为 SQLite 的默认推荐行为。