SQLite 应该采用 (Rust 风格的) 版本

# SQLite 应该引入（类似 Rust 的）版本（edition）机制 来源：https://mort.coffee/home/sqlite-editions/ 日期：2025\-07\-15 仓库：https://gitlab.com/mort96/blog/blob/published/content/00000-home/00017-sqlite-editions.md SQLite 是一个了不起的数据库引擎。我在许多嵌入式项目中使用它作为数据库，并且我认为称它为本地数据存储的行业标准并不夸张。一些服务器端软件甚至也在使用它；例如，lobste.rs 现在运行在 SQLite 上（https://lobste.rs/s/ko1ji1/lobste_rs_is_now_running_on_sqlite）。 与传统的 RDBMS（关系型数据库管理系统）不同，SQLite 不是一个单独的进程；它是一个以库形式提供的 RDBMS，这意味着你的软件保持自包含。与传统的文件格式不同，你不需要编写自定义的序列化器和解析器。从某些方面来说，它融合了两者的优点。 但有一个巨大的问题：它的默认设置全都是错的。 ## 糟糕的默认值 #1：外键约束默认被忽略 你没看错。外键约束可以说是我们确保数据库保持一致、没有悬空引用的主要工具。 简单来说，下面是一个 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。你不能在未提供有效用户 ID 的情况下创建新帖子，也不能在未删除其帖子时删除用户，否则会报外键约束违反错误。 据我所知，唯一一个默认不强制执行此规则的 RDBMS 就是 SQLite。 雪上加霜的是，SQLite 倾向于重用 `ROWID`。你看，在这个例子中，那些 `INTEGER PRIMARY KEY` 行成为了表的 `ROWID` 的别名，`ROWID` 是 SQLite 中分配给表中每一行的唯一整型 ID。分配 `ROWID` 的算法有点复杂（更多细节见 SQLite 文档（https://sqlite.org/autoinc.html）），但在某些情况下会导致 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; ``` 如果一开始就这样做，那么有 bug 的 `DELETE` 就会产生错误： ``` DELETE FROM users WHERE id = 1; -- 运行时错误：外键约束失败 (19) ``` ## 糟糕的默认值 #2：列可以存储错误的数据类型 SQLite 有一个简单的类型系统：一个值可以是 `NULL`、`INTEGER`、`REAL`（即双精度浮点数）、`TEXT` 或 `BLOB`（即二进制数据）。因此，列可以被定义为存储这些类型中的任何一种值。 然而，被定义为 `INTEGER` 的列并不限制只能存储整数；相反，SQLite 认为它“使用 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 是一个明确声明为动态类型的文档数据库，那还说得过去，但它不是。SQLite 通过它的语法规则问我：“你想在这个列中存放什么类型？” 我曾经不得不清理一个项目，其中有些代码不小心将字符串 `'1'` 和 `'0'` 写入了本应存储布尔值（`1` 和 `0`）的列。那可真是一段糟糕的调试经历。 幸运的是，SQLite 有严格表（https://sqlite.org/stricttables.html）的概念，当错误类型被插入列时，SQLite 会产生类型错误： ``` 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'); -- 运行时错误：无法在 INTEGER 列 music.duration_sec 中存储 TEXT 值 (19) ``` 不幸的是，没有全局 pragma 可以使所有表都成为严格表。所以你必须在每个表上手动添加 `strict` 标签。 --- 针对严格表，有一些反对意见我想在这里提一下。 SQLite 的作者写过（https://sqlite.org/flextypegood.html）关于他们偏好“灵活类型”的文章。就个人而言，我觉得这篇文章很奇怪。它没有提供任何示例说明为什么将 `BLOB` 插入 `INTEGER` 列会是有用的。它只是说明了有时拥有一个可以存储任意类型值的列是有用的。严格表为此提供了解决方案：那就是 `ANY` 数据类型。你仍然可以创建接受任何值的列，只是需要明确声明。 一个更好的论点来自 lobste.rs 上的用户 'zie'（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））： > 对于未声明为 STRICT 的表，列的亲和性根据声明的列类型确定，规则按以下顺序应用： > 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` 作为类型名，我想每个人都会满意。我很可能会开始广泛使用这样的特性来记录列中数据的预期模式。在真实的数据库模式中，你不可避免地会遇到需要由应用程序代码解析的 `TEXT` 列。 作为上述附带讨论的一个旁注，如果能将 CHECK 约束（https://www.sqlite.org/lang_createtable.html#check_constraints）与自定义类型关联起来，那会很棒。 ## 糟糕的默认值 #3：并发写入时出现 SQLITE_BUSY 错误 SQLite 允许多个并发读取，但一次只有一个写入者。默认情况下，如果有两个进程试图同时获取写锁，其中一个会立即收到 `SQLITE_BUSY` 错误。 这不是我期望的行为。我期望 SQLite 等待锁被释放，直到某个超时时间。毕竟它是在进行磁盘 I/O，所以我编写代码时已经假设写入可能很慢。 默认行为曾导致我写出真实的 bug，系统有时会直接崩溃。我手动编写了重试循环来修复它。 解决办法是通过 pragma 设置 `busy_timeout`： ``` PRAGMA busy_timeout = 5000; ``` 这使得 SQLite 在返回 `SQLITE_BUSY` 错误之前，尝试获取锁最多 5 秒。 我是最近才了解到这个设置的。如此明显的默认值，竟然不是默认的，这让我很惊讶。 ## 糟糕的默认值 #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）。 ## 解决方案：版本（edition）机制？ 这些默认值之所以保持现状，常被引用的原因是向后兼容性。现在*更改*默认值很可能会破坏大量旧软件，并使人们将来在升级 SQLite 时担心再次破坏一切，就像我害怕升级 Python 一样，因为每次“升级”都会破坏一堆我使用的软件。这是一个值得称赞且罕见的目标——尽量不破坏你的依赖者。 然而，我认为解决方案很简单：添加一个“超级 pragma”，它可以更改所有糟糕的默认值。我提议以下内容： ``` PRAGMA edition = 2026; ``` 应该至少是以下 pragma 集合的别名： ``` PRAGMA foreign_keys = ON; PRAGMA busy_timeout = 5000; PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; ``` 并且还应该让 `strict` 模式成为表的默认模式。 这应该是一个不错的折中方案，既避免了破坏向后兼容性，又让数据库引擎能够向前发展，不被自己的历史所拖累。 `edition` 的想法直接借鉴自 Rust 的版本机制（https://doc.rust-lang.org/cargo/reference/manifest.html#the-edition-field）。与 JavaScript 的 `"use strict";`（https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Strict_mode）相比，基于年份的版本机制的优势在于，随着时间的推移，合理的默认值可能会改变。例如，像 Hctree 的 WAL2（https://sqlite.org/hctree/doc/wal2/doc/wal2.md）这样的东西可能会在某年（比如说 2034 年）进入主分支，那么 `PRAGMA edition = 2034` 有一天可能会设置 `PRAGMA journal_mode = WAL2`。 总之，就这些。我认为 SQLite 应该有一个版本系统，其中包含更新后的默认值集合。有没有我遗漏的地方，让这个想法变得不太可行？或者有没有其他 pragma 应该加入我虚构的“2026 版本”中？