SQLite STRICT 表:被低估的 3.37.0 一行改动,以及它没解决的事

一、起因

HN 48873940 上了首页,讨论一个 SQLite 我之前没仔细看过的特性:STRICT 表。原文作者 Evan Hahn(他自己的 evanhahn.com 是典型个人技术博客,跟 Patrick McCann / xeiaso.net 同类)写了一篇《Prefer strict tables in SQLite》表达他立场。我看完后有几个判断值得在博客园这边展开,因为这个问题在 ORM / 嵌入式存储 / 长期演化 schema 这三个博客园读者高频遇到的场景里都是隐形坑。

先说结论:STRICT 表是 3.37.0(2021-11)引入的附加能力,加 STRICT 一行就能把 SQLite 的 "flexible typing 默认行为" 翻成 strict mode,代价是:老 SQLite 读不了、不能 ALTER TABLE 加、严格意义上多一点点 CPU。但博客园读者画像(3-15 年后端/全栈/老程序员)长期被 SQLite 的 "type affinity 不是 type" 折磨 —— 这条改动是值得翻出来讨论的。

二、SQLite 默认的 flexible typing 到底有多松

先看原文里的两个反例:

-- Non-strict tables let you put anything anywhere.
CREATE TABLE people_nonstrict (age INTEGER);
INSERT INTO people_nonstrict (age) VALUES ('garbage');
-- => works fine, SQLite 自己 affinity 转不出来就当 TEXT 存

-- 这些 SQLite 都不认识,但默认 CREATE 全接受
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);

第一段意味着 ORM 错位、ETL 拿错列类型、JSON 序列化后写入数字列 —— 这些坑在生产里每个博客园老程序员都能讲出三五个故事。第二段更狠:数据库连 schema 写错都没人拦,跨团队 / 跨服务共享 SQLite 文件的场景下,下游 consumer 看到 name JSON 这个声明会误以为有 JSON validation,实际 SQLite 自己根本不知道 JSON 是什么。

三、STRICT 表做了什么

加 STRICT 一行,行为反转:

-CREATE TABLE people (name TEXT);
+CREATE TABLE people (name TEXT) STRICT;

具体规则(SQLite 官方文档 + 原文逐条实测):

  1. 拒绝类型错位的写入:INSERT/UPDATE 时如果值的类型跟声明不符,直接报错。'123' 和 123 仍然等价(可无损转换),但 'garbage' 写进 INTEGER 列直接拒绝
  2. 拒绝 bogus 类型声明:GARBAGE / DATETIME / JSON / UUID / BLOBB 全部报错,只有 INT / INTEGER / REAL / TEXT / BLOB / ANY / NUMERIC 这几个 SQLite 真认的类型合法
  3. 强制每列必须有类型:CREATE TABLE tbl (name) 这种不带类型的写法报错
  4. ANY 类型保留 escape hatch:如果一列确实需要存任意类型,显式声明 ANY,STRICT 模式也接受

我自己在 SQLite 3.46+ 上跑过原文里那几个例子,行为一致。注意第三点的边界:SQLite 自己的 type affinity 仍然存在,比如 INTEGER 列存 '123' 字符串仍会被 affinity 转成整数,只是不再容忍 'abc' 这种完全错位的情况。

四、不能 ALTER 回 STRICT —— 这是工程现实

原文和 HN 评论里都没充分展开的一点:STRICT 是表属性,不是列属性。ALTER TABLE people ADD STRICT 这种写法 SQLite 不支持。要把已有非 STRICT 表转 STRICT,只有一条路:

-- 1. Create new strict table with same schema
CREATE TABLE new_people (name TEXT) STRICT;

-- 2. Copy data (risky if types are wrong!)
INSERT INTO new_people SELECT * FROM people;

-- 3. Replace old table
DROP TABLE people;
ALTER TABLE new_people RENAME TO people;

坑点(坑点 / 不足):

  • 第 2 步的 INSERT INTO ... SELECT * 在源表有 affinity 错位数据时会直接失败,因为 STRICT 模式拒绝任何类型不匹配的行 —— 等于 "想升级 STRICT 必须先清洗数据,清洗过程又依赖新的 STRICT 校验"。生产里这种死锁很常见
  • HN 评论里 @Cyberdog 提到一个 workaround:用 view + trigger 模拟 STRICT 行为,不真的改表。这种做法对长期演化的 schema 更友好,但要写 50+ 行 trigger,我没实测过 trigger 性能开销
  • 如果你已经有 ORM 工具链(migration 框架比如 alembic / knex / dbmate),这些工具默认不会生成 STRICT DDL,需要手写 migration 或 fork 工具

这条 ALTER 限制决定了:STRICT 适合从第一天就启用,中途切换成本远高于一开始就开。

五、性能不是问题,但 ecosystem 兼容性是真问题

原文作者跑过一个简单基准:100 列的表,插入百万行,STRICT vs 非 STRICT 在他机器上没明显差异,磁盘文件大小也一致。这跟 SQLite 官方文档一致 —— STRICT 在 insert/update 路径上多做的检查是 O(1) per row,相对于 disk I/O 可以忽略。

真问题(不足):

  • 老 SQLite 读不了:STRICT 表文件被 3.36.0 及更早版本打开会报 database disk image is malformed。这意味着生产环境必须明确升级路径,移动端 / 嵌入式场景尤甚
  • ORM / migration 工具链普遍不感知:SQLAlchemy / Alembic / Prisma / TypeORM / Drizzle ORM 默认生成的 schema 都不带 STRICT 后缀。判断标准:如果团队 ORM 工具链不感知 STRICT,启用 STRICT 实际上等同于 "所有 DDL 手写 + 所有 ORM 改源码",改造成本不可忽视
  • 跟 SQLite 官方文档立场冲突:SQLite 官方有专门的 "The Advantages Of Flexible Typing" 页面(https://sqlite.org/flextypegood.html) 主张柔性类型更适合嵌入式场景(纯 key-value 存盘 / 跨应用共享 / schema 演化期)。Evan Hahn 在 HN 评论里被质疑没引用官方反方观点

六、目前还没完全搞清楚的几个点(局限与待验证项)

  • STRICT 表的 query planner 路径(待验证):理论上 STRICT 不会影响 query plan(没有 schema-level statistics 变化),但 100 列宽表实测没明显差异的结论是否能外推到 1000+ 列宽表 / 大量索引场景,我没实测
  • migration 工具链适配(不足):SQLAlchemy / Alembic / Drizzle ORM 默认是否能在 v2.x 自动加 STRICT,需要逐个工具查文档。我目前只确认了手写 migration 这一条路能跑通
  • JSON 类型的实际可用方案(不足):STRICT 模式拒绝 JSON 类型声明,但实际业务大量需要 JSON 字段。目前唯一合法做法是 TEXT 列存 JSON + 应用层解析,等于把 SQLite 当 JSON 存储用,放弃了 SQLite 自己的类型系统
  • STRICT 在 view / trigger 里的传播(待验证):基于 STRICT 表建的 view / trigger 是否自动继承 STRICT 行为,SQLite 文档没明说,我没系统性测试
  • 跟 PostgreSQL 风格 strict 的差距(坑点):PostgreSQL CREATE TABLE 默认就是 strict,SQLite STRICT 是 opt-in,从 PG 迁过来的人容易误以为 SQLite 默认也是 strict,踩坑后才补
  • 大量历史数据的清洗成本(还在调研):5 年历史的 SQLite 库若积累几百万行 affinity 错位数据,从非 STRICT 升级到 STRICT 要写专门的清洗脚本,清洗脚本本身是否要 STRICT-aware 这点没共识

七、适用场景建议

场景 是否启用 STRICT 备注
新项目 + ORM 自定义 schema 生成 强烈建议 改造成本最低,长期收益最大
嵌入式 / 移动端 / 单文件 SQLite 视 ORM 选型 React Native SQLite / expo-sqlite 默认不带 STRICT,要手动加
跨应用共享 SQLite 文件 建议 共享场景下类型错位 bug 影响面最大
已有 5+ 年历史的数据库 不建议直接切 ALTER 不支持 + 清洗成本高,先用 view + trigger 模拟
纯 key-value / 杂项属性存储 不建议 SQLite 官方推荐 flexible,跟 STRICT 哲学冲突
长期 schema 演化期(频繁 ADD COLUMN) 视情况 演化期 flexible 更友好,稳定期再切

我的判断:如果今天开始一个新项目,我会把 STRICT 加到 ORM 的 DDL 生成模板里作为默认值。SQLite 官方把 STRICT 当 opt-in 是历史包袱,新项目没必要继承。

参考

posted @ 2026-07-12 07:08  Ninghg  阅读(14)  评论(0)    收藏  举报