SQLite 默认设置问题多，借鉴 Rust 版本机制更新能否解决？

糟糕的默认设置 #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_keys`：

PRAGMA 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_timeout`：

PRAGMA 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 版本”中呢？