SQLite STRICT 表：一个被低估的防御性编程利器

来源Hacker News Best216 points— Evan Hahn《Prefer strict tables in SQLite》上周 HN 上关于 SQLite STRICT 表的讨论拿了 216 分105 条回复。分不高但评论区密度挺说明问题的。SQLite 作为嵌入式数据库之王全球装机量估计超过一万亿份。但它的类型系统一直有个特色——说得好听叫灵活说得难听叫摆烂。STRICT 表在 SQLite 3.45.02025 年 1 月发布中引入允许你声明一个严格模式的表禁止 SQLite 经典的隐式类型转换。下面不翻译原文只聊实战——STRICT 表解决了什么、为什么你下一个项目就该用。1. SQLite 的类型系统到底有什么问题只用 SQLite 做玩具项目的话你可能从来没被它的类型系统坑过。但一上生产——不管移动端、桌面应用还是 IoT——你迟早会遇到类型亲和性Type Affinity这个魔幻行为。CREATETABLEusers(idINTEGERPRIMARYKEY,ageINTEGER);INSERTINTOusersVALUES(1,25);-- 这是字符串不是整数这段代码在 SQLite 里完全合法。25会被插入到age列虽然你声明了INTEGER类型。SQLite 只是把它标记为亲和类型为 INTEGER但实际存储的值是文本25。这还不是最离谱的。更常见的是这种场景-- 某个字段本应是数字但插入了空字符串INSERTINTOconfigVALUES(theme,);-- 期望存 NULL 或数字-- 查询时发现 不等于 0 也不等于 NULLSELECT*FROMconfigWHEREvalue0;-- 查不到SELECT*FROMconfigWHEREvalue;-- 才查到SQLite 官方文档对类型亲和性的定义长达数页总结起来就是SQLite 会尝试把输入值转换为目标列的亲和类型但如果转换失败它就直接存原始值。这种设计在 2000 年 SQLite 刚诞生时是有道理的——当时它主要被用在嵌入式场景数据格式松散Schema 经常变。但在 2026 年的今天SQLite 被用在 WhatsApp、Chrome、微信、几乎所有 Android 应用里这种宽松反而成了 bug 的温床。-- 假设一个 JSON 字段存储了用户配置CREATETABLEprefs(keyTEXTPRIMARYKEY,valueTEXT-- 实际可以是数字、布尔、字符串);INSERTINTOprefsVALUES(notifications_enabled,false);-- 几个月后某次查询SELECT*FROMprefsWHEREvaluetrue;-- 没问题。但如果有人插入了 0 而不是 false...INSERTINTOprefsVALUES(dark_mode,0);SELECT*FROMprefsWHEREvaluefalse;-- 查不到因为 0 存的是 INTEGER不是 TEXT2. STRICT 表做了什么STRICT 表的核心改动极其简单声明为 STRICT 的表禁止任何隐式类型转换。类型不匹配直接报错不商量。创建方式只有一个关键字的区别-- 传统方式CREATETABLEusers(idINTEGERPRIMARYKEY,nameTEXT,ageINTEGER);-- STRICT 方式末尾加 STRICTCREATETABLEusers(idINTEGERPRIMARYKEY,nameTEXT,ageINTEGER)STRICT;区别在行为上-- STRICT 表拒绝类型不匹配的插入INSERTINTOusersVALUES(1,Alice,25);-- Error: cannot store TEXT value in INTEGER column age-- 必须传真正的整数INSERTINTOusersVALUES(1,Alice,25);-- OKSTRICT 表支持的数据类型只有 5 种INTEGER、REAL、TEXT、BLOB、ANY。ANY是个有意思的补充——它表示我不关心类型相当于显式声明这个字段可以是任何类型。这在某些场景下很有用比如存储 JSON 数据或配置值。关键规则INTEGER列只接受真正的整数没有小数点、不是字符串REAL列只接受浮点数TEXT列只接受字符串BLOB列只接受二进制数据ANY列接受任何类型相当于传统 SQLite 的行为3. 从 HN 讨论看 STRICT 表的真实价值HN 原文下面有 105 条回复分成几个阵营。我筛选出最有价值的讨论阵营一给 ORM 和 SQL 生成器用最大的受益者是 ORM 库。SQLite 的类型宽松导致 ORM 需要做大量的额外校验。Prisma、Drizzle 等团队在 HN 上表示STRICT 表能让他们省掉至少 30% 的类型校验代码。# 使用传统 SQLite 表时ORM 需要额外校验classUser(Base):__tablename__usersidColumn(Integer,primary_keyTrue)ageColumn(Integer)# 需要额外校验validates(age)defvalidate_age(self,key,value):ifnotisinstance(value,int):raiseValueError(age must be int)returnvalue# 使用 STRICT 表后这个校验可以交给数据库阵营二防御性编程的最佳实践有开发者分享了一个真实案例他们的移动应用中有一个 SQLite 数据库某次升级后一个字段从INTEGER变成了TEXT因为 ORM 迁移脚本写错了但数据没有损坏——传统 SQLite 默默接受了混合类型。半年后他们才发现某些查询结果异常定位花了整整一周。如果用 STRICT 表迁移脚本在第一次插入错误类型时就会报错。阵营三性能优势STRICT 表还有一个隐性收益类型确定性带来的查询优化。SQLite 的查询优化器在处理 STRICT 表时可以做出更激进的类型假设从而生成更优的执行计划。根据 HN 上的讨论STRICT 表在某些查询上的性能比传统表高 5-15%。原因是 SQLite 不需要在查询执行时进行运行时类型检查。-- 传统表查询时需要运行时检查类型SELECTAVG(age)FROMusers;-- SQLite 需要检查每个 age 的实际类型-- STRICT 表可以直接假设 age 是 INTEGERSELECTAVG(age)FROMusers_strict;-- 优化器直接生成整数求和计划阵营四迁移成本反对声音主要是迁移成本。如果你的应用已经有几十万行 SQLite 数据迁移到 STRICT 表意味着导出数据清洗数据找出所有类型不匹配的行重建表为 STRICT导入数据对于大表这个过程可能很慢。而且 SQLite 不支持ALTER TABLE ... ADD STRICT必须重建表。4. 什么时候该用什么时候不该用STRICT 表不是万能的。它解决的是类型安全的问题不是业务逻辑正确性的问题。推荐使用 STRICT 表的场景新项目、新数据库没有任何理由不开启 STRICT。这是默认选项。ORM 管理的数据库ORM 生成的表天然就是类型安全的STRICT 表只是把在代码层校验提升到在数据库层校验。API 或服务端使用的 SQLite输入来自不可信来源需要数据库层面的防御。团队协作项目减少谁把字符串插到整数列了这种低级 bug。不建议使用 STRICT 表的场景已有大量数据的传统表迁移成本高需要做数据清洗。可以逐步迁移。需要动态 Schema 的场景比如键值存储、配置表。这种情况下可以用ANY类型或者继续用传统表。SQLite 3.45.0 之前版本STRICT 表需要 3.45.0。如果你的部署环境有旧版本升级后再用。一个折中方案对混合场景可以在同一个数据库文件中同时使用 STRICT 表和传统表。SQLite 完全支持混用-- 同一个数据库两种表共存CREATETABLEusers(idINTEGER,nameTEXT,ageINTEGER)STRICT;CREATETABLElogs(idINTEGER,messageTEXT,timestampTEXT);-- 传统表5. 实战如何迁移到 STRICT 表如果你决定迁移这里是一个经过验证的步骤第一步识别类型不匹配的行-- 找出所有 age 列不是真正整数的行SELECTid,age,TYPEOF(age)FROMusersWHERETYPEOF(age)!integer;TYPEOF()函数是 SQLite 的运行时类型检查函数。它会返回值的实际类型integer、real、text、blob、null。第二步清洗数据-- 将字符串类型的 age 转换为整数UPDATEusersSETageCAST(ageASINTEGER)WHERETYPEOF(age)textANDage GLOB[0-9]*;-- 对于无法转换的设置为 NULL 或默认值UPDATEusersSETageNULLWHERETYPEOF(age)textANDageNOTGLOB[0-9]*;第三步重建表为 STRICT-- 1. 创建 STRICT 表CREATETABLEusers_new(idINTEGERPRIMARYKEY,nameTEXT,ageINTEGER)STRICT;-- 2. 复制数据这里会报错如果还有类型不匹配的行INSERTINTOusers_newSELECT*FROMusers;-- 3. 替换原表DROPTABLEusers;ALTERTABLEusers_newRENAMETOusers;第四步验证-- 确认表是 STRICT 模式SELECTname,strictFROMsqlite_masterWHEREtypetable;-- strict 列返回 1 表示是 STRICT 表如果迁移过程中遇到类型不匹配SQLite 会抛出明确的错误信息告诉你哪一行、哪一列、什么类型不匹配。这比传统 SQLite 默默接受错误数据要好得多。总结STRICT 表没引入新语法、新概念、新范式——就是在经典模式上加了个严格模式开关。对于新项目STRICT 表应该是默认选择。对于已有项目可以逐步迁移一次迁移一张表。STRICT 表不会让 SQLite 变成 PostgreSQL它仍然是那个轻量级、零配置的嵌入式数据库。但它让 SQLite 变得更可靠了——尤其是在你写了 10 万行代码后还能保证数据库里每一行数据都是你期望的类型。附如果你正在用 SQLite运行下面这条 SQL 看看你的数据库里有多少类型不匹配的行SELECTCOUNT(*)FROMyour_tableWHERETYPEOF(your_column)!你的预期类型;结果可能不太好看。