糟糕的默认设置 #1默认忽略外键约束SQLite 是出色数据库引擎用于嵌入式项目和部分服务器软件如 lobste.rs。它以库形式存在软件自包含无需自定义序列化器和解析器。但默认设置全错外键约束是确保数据库一致性、避免悬空引用的主要工具SQL 外键约束示例如下CREATE TABLE users ( id INTEGER PRIMARY KEY, display_name TEXT ); CREATE TABLE posts ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, content TEXT NOT NULL, FOREIGN KEY(user_id) REFERENCES users(id) );其他 RDBMS 中帖子的 user_id 列须引用有效用户 ID而 SQLite 是唯一默认不强制执行此约束的 RDBMS且倾向于重用 ROWID导致 ID 重用使悬空引用易变成引用到“错误的列”。以下是简单数据库模式中的假设操作序列-- 鲍勃创建了一个用户账户 INSERT INTO users (display_name) VALUES (Bob); SELECT * FROM users; -- id | display_name -- 1 | Bob -- 鲍勃发布了一篇介绍帖子 INSERT INTO posts (user_id, content) VALUES (1, Hello, I am Bob); SELECT u.display_name, p.content FROM users as u, posts as p WHERE u.id p.user_id; -- display_name | content -- Bob | Hello, I am Bob -- 鲍勃删除了他的账户 -- 但我们忘记删除他的帖子。 -- SQLite 不会报错因为它忽略了我们的外键。 DELETE FROM users WHERE id 1; -- 爱丽丝创建了一个账户。 -- 由于 ROWID 算法爱丽丝获得了和鲍勃相同的 ID。 INSERT INTO users (display_name) VALUES (Alice); SELECT * FROM users; -- id | display_name -- 1 | Alice -- 爱丽丝现在继承了鲍勃的旧帖子 SELECT u.display_name, p.content FROM users as u, posts as p WHERE u.id p.user_id; -- display_name | content -- Alice | Hello, I am Bob解决办法是使用 pragma 启用 foreign_keysPRAGMA foreign_keys ON;若一开始就这么做有问题的 DELETE 操作就会报错DELETE FROM users WHERE id 1; -- 运行时错误FOREIGN KEY 约束失败 (19)糟糕的默认设置 #2列可以存储错误的数据类型SQLite 类型系统简单值可为 NULL、INTEGER、REAL双精度浮点数、TEXT 或 BLOB二进制数据列可定义存储这些类型的值。但定义为 INTEGER 类型的列“使用 INTEGER 亲和性”规则如下1. 若插入 TEXT 值且是有效整数的字符串表示形式会转换为整数存储2. 若插入 TEXT 值且是有效实数的字符串表示形式会转换为实数存储3. 否则值按原样存储。其他亲和性规则具有 BLOB 亲和性的列按原样存储值具有 TEXT 亲和性的列按原样存储 BLOB、TEXT 和 NULL 值将数值转换为 TEXT具有 REAL 亲和性的列与 INTEGER 亲和性列类似整数值会转换为 REAL。实际情况示例CREATE TABLE music ( id INTEGER PRIMARY KEY, name TEXT, duration_sec INTEGER ); INSERT INTO music (name, duration_sec) VALUES (Lost In Hollywood, 321); INSERT INTO music (name, duration_sec) VALUES (Comfortably Numb, 382); INSERT INTO music (name, duration_sec) VALUES (The Way of All Flesh, Way too long, I mean come on); SELECT * FROM music; -- id | name | duration_sec -- 1 | Lost In Hollywood | 321 -- 2 | Comfortably Numb | 382 -- 3 | The Way of All Flesh | Way too long, I mean come on这表明数据库数据验证粗心大意是坏主意。不过SQLite 有 [严格表](https://sqlite.org/stricttables.html) 概念插入错误类型值会产生类型错误CREATE TABLE music ( id INTEGER PRIMARY KEY, name TEXT, duration_sec INTEGER ) strict; INSERT INTO music (name, duration_sec) VALUES (The Way of All Flesh, Way too long, I mean come on); -- 运行时错误无法将 TEXT 值存储在 INTEGER 列 music.duration_sec 中 (19)遗憾的是无全局 pragma 让所有表成严格表需手动为每个表添加 strict 标签。关于严格表的争议SQLite 开发者 [曾撰文](https://sqlite.org/flextypegood.html) 表达对“灵活类型”的偏好但未举例说明将 BLOB 插入 INTEGER 列的用处仅说明有时拥有可存储任何类型值的列是有用的。严格表有 ANY 数据类型解决方案可创建接受任何值的列但必须明确指定。[用户 zie 在 lobste.rs 上提出](https://lobste.rs/s/ko1ji1/lobste_rs_is_now_running_on_sqlite#c_xiqiny)SQLite 中的严格表不仅“强制执行”类型还改变类型说明符的解析规则。非严格的 SQLite 表确定列类型的规则如下来自 [SQLite 文档](https://sqlite.org/datatype3.html#determination_of_column_affinity)1. 如果声明的类型包含字符串 INT则该列具有 INTEGER 亲和性。2. 如果列的声明类型包含字符串 CHAR、CLOB 或 TEXT则该列具有 TEXT 亲和性。请注意VARCHAR 类型包含字符串 CHAR因此被赋予 TEXT 亲和性。3. 如果列的声明类型包含字符串 BLOB或者未指定类型则该列具有 BLOB 亲和性。4. 如果列的声明类型包含字符串 REAL、FLOA 或 DOUB则该列具有 REAL 亲和性。5. 否则亲和性为 NUMERIC。结合 SQLite 的宽松类型此规则的一个结果是可给列指定如 DATETIME、KEY_VALUE_SET 或 COLOR 等类型名称数据库连接器/包装器会自动知道如何序列化和反序列化具有自定义类型的列这些自定义类型名称可作为有用的文档。若简单将默认设置从非严格表改为严格表会失去这个特性。不过自定义类型别名会更好。若能这样写CREATE TYPE KEY_VALUE_SET TEXT;然后在严格表中使用 KEY_VALUE_SET 作为类型名称大家可能都会满意还可大量使用此功能记录列中预期的数据模式。此外若能将 [CHECK 约束](https://www.sqlite.org/lang_createtable.html#check_constraints) 与自定义类型关联起来就更好了。**更新**[lobste.rs 上的 masklinn 指出](https://lobste.rs/s/2nry82/sqlite_should_have_rust_style_editions#c_iwitbn)SQL 99 标准已规定了类型别名即 [CREATE DOMAIN](https://sql-99.readthedocs.io/en/latest/chapters/19.html#create-domain-statement)它也支持约束。所以实际上SQLite 只需添加对标准 CREATE DOMAIN 语句的支持即可。糟糕的默认设置 #3并发写入时出现 SQLITE_BUSY 错误SQLite 允许多个并发读取操作但同一时间只允许一个写入操作。默认情况下若两个进程同时尝试获取写锁其中一个会立即收到 SQLITE_BUSY 错误。这并非期望的行为期望 SQLite 能等待锁被释放直到达到某个超时时间。这种默认行为导致实际项目中出现一些 bug系统有时会直接崩溃不得不手动编写重试循环来解决。解决办法是使用 pragma 设置 busy_timeoutPRAGMA busy_timeout 5000;这会让 SQLite 在出现 SQLITE_BUSY 错误之前最多尝试获取锁 5 秒钟。直到最近才了解到这个设置这似乎是显而易见的默认设置很惊讶它居然不是默认的。**更新**这里说明一下支持并发写入的必要性。正常运行时通常最好将软件结构设计为所有写入操作都由一个进程最好是一个线程完成并发写入永远不会快。但也有特殊情况比如可能需要使用 sqlite3 工具在命令行上交互式地手动清理数据库或者有一些不常用的管理脚本还没来得及为其编写前端界面。这些都是合理且常见的用例。SQLite 的默认设置很糟糕因为在这些情况下可能会因抛出意外的 SQLITE_BUSY 错误而导致软件崩溃。糟糕的默认设置 #4性能问题关于 SQLite 的性能调优有很多可讲若配置正确它可成为非常快速的 RDBMS能胜任通常由 PostgreSQL 或 MySQL 等大型服务器承担的任务。但默认情况下它的性能并不好。更聪明的人已写了很多相关内容若对这个话题感兴趣推荐 Sylvain Kerkour 的 [为服务器优化 SQLite](https://kerkour.com/sqlite-for-servers)。最严重的一个默认问题是SQLite 的 [预写日志](https://sqlite.org/wal.html)WAL默认是禁用的。可使用以下命令启用它PRAGMA journal_mode WAL;在大多数情况下WAL 可显著提高写入速度。此外通过更改另一个设置可在不冒数据损坏风险的情况下大幅减少磁盘同步的次数PRAGMA synchronous NORMAL;具体 synchronous 的作用可查看 [SQLite 文档](https://sqlite.org/pragma.html#pragma_synchronous)。解决方案版本机制这些默认设置一直保持不变的常见原因是向后兼容性。现在更改默认设置可能会破坏许多旧软件让人们不敢在未来升级 SQLite。不过解决方案很简单添加一个“超级 pragma”来更改所有糟糕的默认设置。建议以下语句PRAGMA edition 2026;至少应该是以下一组 pragma 的别名PRAGMA foreign_keys ON; PRAGMA busy_timeout 5000; PRAGMA journal_mode WAL; PRAGMA synchronous NORMAL;同时让 strict 模式成为表的默认模式。这是一个很好的折衷方案既避免了破坏向后兼容性又能让数据库引擎向前发展。“版本”的想法借鉴了 [Rust 版本机制](https://doc.rust-lang.org/cargo/reference/manifest.html#the-edition-field)。基于年份的版本机制比 JavaScript 的 “use strict”; 更有优势因为随着时间的推移合理的默认设置可能会发生变化。例如到 2034 年像 [Hctree 的 WAL2](https://sqlite.org/hctree/doc/wal2/doc/wal2.md) 这样的功能可能会被合并到主分支中那么 PRAGMA edition 2034 可能会将 PRAGMA journal_mode 设置为 WAL2。总之认为 SQLite 应该有一个版本系统来更新默认设置。有没有遗漏使得这个想法不可行的地方呢或者还有哪些 pragma 应该添加到设想的“2026 版本”中呢