在SQLite中推荐使用严格表

# 在 SQLite 中优先使用 STRICT 表 来源: https://evanhahn.com/prefer-strict-tables-in-sqlite/ *简而言之: 我更倾向于在 SQLite 中使用 strict 表 (https://sqlite.org/stricttables.html) ，因为它们能避免一些数据类型问题，比如将文本放入数字列。* SQLite 有一个我认为被低估的功能：**strict 表** (https://sqlite.org/stricttables.html) 。strict 表有助于强制类型严格，避免将文本放入整数列等错误。我喜欢它们，并写了这篇文章来推广使用！ 要创建一个 strict 表，只需在表定义末尾加上 `STRICT`。像这样： ```sql -- 之前: CREATE TABLE people (name TEXT); -- 之后: CREATE TABLE people (name TEXT) STRICT; ``` 就这样！但它有什么作用呢？ ## strict 表的优点 总的来说，strict 表有助于强制类型严格，就像其他 SQL 引擎一样。 ### 防止插入/更新时的类型不匹配 最显著的是，strict 表能防止你将错误类型的值插入列中。例如，SQLite 通常允许你将文本放入 `INTEGER` 列，但在 strict 表中不允许。 ```sql -- 非 strict 表允许你随意放任何东西。 CREATE TABLE people_nonstrict (age INTEGER); INSERT INTO people_nonstrict (age) VALUES ('garbage'); -- => 正常工作 -- strict 表不允许这样，我更喜欢这样。 CREATE TABLE people_strict (age INTEGER) STRICT; INSERT INTO people_strict (age) VALUES ('garbage'); -- => 错误: 无法将 TEXT 值存入 INTEGER 列 ``` 就我个人而言，我觉得试图将文本放入整数列（或反之）是一个错误。我不希望 SQLite 让我犯这个错误！(https://evanhahn.com/the-two-kinds-of-error/) 同样的验证也适用于 `UPDATE` 操作。 值得注意的是，如果某个值可以无损转换，它仍然会被接受。例如，字符串 `'123'` 可以完美地转换为整数，因此允许插入。以下两行语句是等价的，即使对于 strict 表也是如此： ```sql INSERT INTO people_strict (age) VALUES ('123'); INSERT INTO people_strict (age) VALUES (123); ``` ### 防止创建表时使用无效的列类型 默认情况下，你可以创建具有无效类型的列。例如，以下所有操作都能成功，即使它们不是有效的 SQLite 数据类型： ```sql -- SQLite 不支持这些类型，但都能被接受。 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); ``` 我认为这些并非开发者本意。其中一些是拼写错误，一些是对 SQLite 支持哪些数据类型 (https://sqlite.org/datatype3.html) 的误解，还有些是严重的错误。 在这些语句末尾加上 `STRICT` 会使它们报错。在我看来，这才是正确的行为！ ```sql -- 所有这些都会报错，我更喜欢这样。 CREATE TABLE tbl (name GARBAGE) STRICT; CREATE TABLE tbl (name DATETIME) STRICT; CREATE TABLE tbl (name JSON) STRICT; CREATE TABLE tbl (name UUID) STRICT; CREATE TABLE tbl (name BLOBB) STRICT; ``` 只允许 `INT`、`INTEGER`、`REAL`、`TEXT`、`BLOB` 和 `ANY`。 strict 表还要求列必须有类型，因此你不能写 `CREATE TABLE tbl (name)`。 ### 通过 `ANY` 仍保留灵活性 如果你仍然需要一个灵活的列，可以使用 `ANY` 数据类型。顾名思义，它允许任何值——即使在 strict 表中也是如此。 ```sql CREATE TABLE tbl (value ANY) STRICT; -- 以下所有操作都有效，因为列类型是 ANY： INSERT INTO tbl (value) VALUES (123); INSERT INTO tbl (value) VALUES ('text'); INSERT INTO tbl (value) VALUES (12.34); INSERT INTO tbl (value) VALUES (X'8647'); ``` 我还没有找到它的用途，但也许你会用到！ ## strict 表的缺点 我更喜欢 strict 表，但必须提几点缺点。并非所有方面都更好！ ### 无法将现有表变为 strict 我认为最好从一开始就使用 strict，但并非总是可行。 不幸的是，我认为没有办法通过 `ALTER` 将表改为 strict。你必须将数据从非 strict 表复制到 strict 表中。大致如下： ```sql -- 1. 创建一个具有相同模式的新 strict 表 CREATE TABLE new_people (name TEXT) STRICT; -- 2. 复制数据（如果类型错误则有风险！） INSERT INTO new_people SELECT * FROM people; -- 3. 替换旧表 DROP TABLE people; ALTER TABLE new_people RENAME TO people; ``` 请注意，如果非 strict 表中存在无效数据，可能会很棘手！例如，如果旧数据意外地在整数列中包含文本，那么在迁移时会出错。你可能需要清理数据或进行类型转换 (https://sqlite.org/lang_expr.html#cast_expressions)。 你可以为代码库制定规则：所有*新*表都是 strict 的。这可能有用——至少你*有些*表是有效的！但这可能也意味着你表之间的验证不一致，这可能比所有表都使用弱验证更令人意外。由你来决定这是否适合你。 ### SQLite 开发者并不同意我的观点 SQLite 有一个专门的页面叫做“灵活类型的优点” (https://sqlite.org/flextypegood.html)，他们在那里论证 SQLite 的灵活行为实际上是有益的。 我不太想卷入静态与动态的争议中，但在大多数情况下我持不同意见。我个人遇到过*很多* bug，其中意外的数据类型导致微妙的麻烦。我更希望这些错误大声地暴露出来。但需要注意的是，SQLite 的开发者似乎并不像我一样偏爱 strict 表！ 他们指出了灵活表的一些好的用途，例如“纯键值存储”或“存储不同类型杂项属性的地方”。他们还提到，在某些情况下你*可能*想保留无效数据，比如直接导入杂乱无章的 CSV 文件，不想丢失任何数据。我仍然更喜欢 strict 表，但承认有些合理的情况适合非 strict 表。 （另外，在 SQLite 源码中至少有一条注释将非 strict 表称为“legacy” (https://sqlite.org/src/file?ci=trunk&name=src%2Finsert.c&ln=143)，但我认为这不如官方文档可靠。） ### 仅适用于 SQLite 3.37.0+ SQLite 在 3.37.0 版本 (https://sqlite.org/releaselog/3_37_0.html) 中引入了 strict 表，该版本于 2021 年 11 月发布。如果你使用的是更早版本的 SQLite，则无法使用 strict 表。 值得注意的是，旧版本的 SQLite 无法读取包含 strict 表的数据库。例如，如果你在最新版本的 SQLite 中创建了一个 strict 表，然后尝试在 SQLite 3.36.0（添加 strict 表之前）中读取该数据库，你将收到错误——即使该 strict 表已经存在于数据库中。 ### 性能*可能*？ strict 表在理论上更慢，因为它们需要做一些额外的工作。例如，它们会在插入或更新时检查数据类型 (https://sqlite.org/src/file?ci=trunk&name=src%2Finsert.c&ln=182-203)。 但实际上，我认为这不是问题。我写了一个简陋的脚本，向一个包含 100 列的表中插入数百万行数据，在我尝试的多台机器上没有明显差异。磁盘上的文件大小也相同。我没有彻底测试过，所以可能遗漏了什么，但我认为 strict 表不会带来性能问题。 事实上，你可能会预期*更好*的性能，因为你不会意外地不匹配 SQLite 的列亲和性。但同样，我没有测试过。 ## 结论：我喜欢 strict 表！ 就个人而言，我认为 strict 表的优点多于缺点。 我通常更喜欢类型被严格强制。它消除了一类错误，并有助于确保良好的数据完整性。它们不是万能药，但通常易于添加，且作用巨大。 如果你认为 SQLite 还有某个被低估的功能，请告诉我 (https://evanhahn.com/contact/)。