Prefer STRICT tables in SQLitePrefer STRICT tables in SQLite

简而言之：我更喜欢 SQLite 中的严格表（strict tables），因为它们能避免一些数据类型问题，比如将文本放入数字列中。

SQLite 有一个我认为被低估的特性：严格表。严格表有助于强制执行严格的类型约束，防止将文本放入整数列等错误。我很喜欢它们，并写下这篇文章来推广它们的使用！

要创建严格表，只需在其定义末尾添加 STRICT。像这样：

-CREATE TABLE people (name TEXT);
+CREATE TABLE people (name TEXT) STRICT;

就是这样！但它有什么作用呢？

严格表的优势

总体而言，严格表有助于像其他 SQL 引擎一样强制执行严格的类型。

防止插入/更新时的类型不匹配

最重要的是，严格表能防止你向列中插入错误的类型。例如，SQLite 通常允许你将文本放入 INTEGER 列，但严格表不允许。

-- Non-strict tables let you put anything anywhere.
CREATE TABLE people_nonstrict (age INTEGER);
INSERT INTO people_nonstrict (age) VALUES ('garbage');
-- => works fine

-- Strict tables don't allow that, which I prefer.
CREATE TABLE people_strict (age INTEGER) STRICT;
INSERT INTO people_strict (age) VALUES ('garbage');
-- => error: cannot store TEXT value in INTEGER column

就我个人而言，我认为试图将文本放入整数列（或反之）是一个错误。我不希望 SQLite 允许我犯这种错误！

UPDATE 操作也会进行同样的验证。

值得注意的是，如果一个值可以无损转换，它仍然会被接受。例如，字符串 '123' 可以完美转换为整数，因此是允许的。这两行是等效的，即使在严格表中也是如此：

INSERT INTO people_strict (age) VALUES ('123');
INSERT INTO people_strict (age) VALUES (123);

防止在创建表时使用无效的列类型

默认情况下，你可以创建具有无效类型的列。例如，以下这些都能运行，尽管它们并不是有效的 SQLite 数据类型：

-- SQLite doesn't support these types, but this is all accepted.
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 支持哪些数据类型的误解，还有一些是极其严重的错误。

在这些语句中添加 STRICT 会使它们报错。在我看来，这才是正确的行为！

-- All of these give errors, which I prefer.
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。

严格表还要求指定列类型，因此你不能使用 CREATE TABLE tbl (name)。

通过 ANY 仍然保持灵活性

如果你仍然需要某一列保持灵活性，可以使用 ANY 数据类型。顾名思义，它允许任何内容——即使在严格表中也是如此。

CREATE TABLE tbl (value ANY) STRICT;

-- All of these are valid because the column is 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');

我还没有发现它的用途，但也许你会！

严格表的缺点

我更喜欢严格表，但我必须分享一些缺点。并非所有方面都更好！

无法将现有表变为严格表

我认为从一开始就使用严格约束是最好的，但这并不总是可行的。

遗憾的是，我认为没有办法通过 ALTER 将表变为严格表。我认为你必须将数据从非严格表复制到严格表中。大概像这样：

-- 1. Create a new strict table with the same schema
CREATE TABLE new_people (name TEXT) STRICT;

-- 2. Copy data (risky if types are wrong!)
INSERT INTO new_people SELECT * FROM people;

-- 3. Replace the old table
DROP TABLE people;
ALTER TABLE new_people RENAME TO people;

请注意，如果非严格表包含无效数据，这可能会很棘手！例如，如果旧数据在整数列中意外包含了文本，你在进行迁移时就会遇到错误。你可能需要清理数据或对其进行类型转换。

你可以为你的代码库制定一条规则，规定所有新表都必须是严格表。这可能很有用——至少你的一部分表是有效的！但这可能也意味着你的表之间的验证不一致，这可能比所有表都使用弱验证更令人惊讶。是否适合你，由你自己决定。

SQLite 的开发者不同意我的观点

SQLite 有一个专门的页面叫做“The Advantages Of Flexible Typing”，他们在那里论证 SQLite 的灵活行为实际上是件好事。

我不太想卷入静态与动态的争议，但在大多数情况下我并不认同动态类型。我个人遇到过许多因意外的数据类型而引起的隐蔽 bug。我宁愿这些错误大声报错。但值得注意的是，SQLite 的开发者似乎并不像我那样偏好严格表！

他们指出了灵活表的几个好用例，例如“纯键值存储”或存储不同类型的“杂项属性的地方”。他们还提到，在某些情况下你可能希望保留无效数据，比如当你直接导入一个混乱的 CSV 并且不想丢失任何数据时。我仍然更喜欢严格表，但也承认非严格表在某些情况下是合理的。

（在 SQLite 源码中至少有一条评论将非严格表称为“遗留物”，但我对它的信任度不如官方文档。）

仅在 SQLite 3.37.0+ 中可用

SQLite 在 2021 年 11 月发布的 3.37.0 版本中引入了严格表。如果你使用的是旧版本的 SQLite，则无法使用严格表。

值得注意的是，旧版本的 SQLite 无法读取包含严格表的数据库。例如，如果你在最新版本的 SQLite 中创建了一个严格表，然后尝试在 SQLite 3.36.0（添加严格表之前的版本）中读取该数据库，你会得到一个错误——即使该严格表已经存在于数据库中。

也许是性能问题？

从理论上讲，严格表的速度较慢，因为它们必须做一些额外的工作。例如，它们在执行插入或更新时会检查数据类型。

但在实践中，我认为这不是个问题。我写了一个简单的脚本，向一个有 100 列的表中插入了数百万行数据，在我尝试的多台机器上并没有明显的差异。磁盘上的文件大小也相同。我没有对此进行彻底的测试，所以也许我遗漏了什么，但我认为严格表不会带来性能问题。

事实上，人们可能会期望获得更好的性能，因为你不会意外地与 SQLite 的列亲和性不匹配。但同样，我还没有测试过这一点。

结论：我喜欢严格表！

就我个人而言，我认为严格表的利大于弊。

我通常更喜欢严格强制执行类型。它消除了一类错误，并有助于维护良好的数据完整性。它们不是万能药，但通常很容易添加，并且能发挥很大作用。

如果你认为有哪个 SQLite 功能被低估了，请告诉我。