偏好 SQLite 中的 STRICT 表

偏好 SQLite 中的 STRICT 表

使用 STRICT 表强制数据完整性

SQLite 的 STRICT 表通过强制严格的类型约束，防止常见的数据完整性问题，确保只能在列中存储声明的数据类型。通过在 CREATE TABLE 语句的末尾添加 STRICT，开发者可以阻止 SQLite 默认在非严格表中允许将文本插入整数列或出现其他类型不匹配的情况。

-- 非严格表允许任何类型存入任何列
CREATE TABLE people_nonstrict (age INTEGER);
INSERT INTO people_nonstrict (age) VALUES ('garbage'); -- 正常工作

-- 严格表强制声明的类型
CREATE TABLE people_strict (age INTEGER) STRICT;
INSERT INTO people_strict (age) VALUES ('garbage'); -- 错误：无法在 INTEGER 列中存储 TEXT 值

仍然允许无损转换；例如，字符串 '123' 可以存入 INTEGER 列，因为它可以完美地转换为整数。

防止无效的列定义

严格表消除了创建具有不存在或拼写错误的数据类型列的可能性。在标准的 SQLite 表中，DATETIME、JSON 或 UUID 等类型会被接受，尽管它们并非 SQLite 原生类型。而在 STRICT 表中，只允许以下类型：

  • INT / INTEGER
  • REAL
  • TEXT
  • BLOB
  • ANY

在创建表时尝试使用任何其他类型名称都会导致错误，从而防止拼写错误和对 SQLite 支持类型的误解。

ANY 类型的灵活性

对于需要保持灵活性的场景——例如键值存储或审计表，需要跟踪不同字段类型的变化——可以在严格表中使用 ANY 类型。这使得该列可以接受任何数据类型，而表的其余部分仍保持严格的类型约束。

CREATE TABLE tbl (value ANY) STRICT;
INSERT INTO tbl (value) VALUES (123);
INSERT INTO tbl (value) VALUES ('text');

STRICT 表的权衡与限制

虽然 STRICT 表提升了可预测性，但它们也带来了一些技术限制：

版本要求与兼容性

STRICT 表是在 SQLite 3.37.0（2021 年 11 月）中引入的。包含严格表的数据库无法被低于 3.37.0 的 SQLite 版本读取，这在使用旧版 SQLite 的环境中可能会造成兼容性障碍。

迁移挑战

现有表不能通过 ALTER TABLE 命令直接转换为严格表。将非严格表迁移为严格表需要创建一个新的严格表并复制数据：

  1. 使用相同的模式创建一个新的严格表。
  2. 使用 INSERT INTO ... SELECT * FROM ... 复制数据。
  3. 删除旧表并将新表重命名。

如果现有数据包含类型不匹配，迁移将会失败，开发者需要先清理或强制转换数据。

性能考虑

严格表会带来少量开销，因为 SQLite 必须在 INSERT 和 UPDATE 操作期间验证数据类型。然而，实证测试表明在大多数实际应用中，这种开销可以忽略不计，有人甚至认为避免列亲和性不匹配可能会提升性能。

社区对灵活与严格类型的观点

SQLite 开发者与许多用户在灵活类型的实用性上存在显著分歧。官方 SQLite 文档认为灵活类型是一大优势，认为它可以防止在导入混乱数据（如 CSV）时的数据丢失，并且更容易发现某些类型的错误。

社区成员表达了不同的看法：

“我个人遇到过许多错误，意外的数据类型会导致细微但棘手的问题。我宁愿这些错误大声爆炸。”

“如果一个应用把字符串存入数值列，会导致所有其他人都出问题……SQLite 的主要使用场景是嵌入式数据库……应用代码知道每列应该是什么类型。”

相反，也有用户认为严格模式限制了使用更有意义的列类型名称的能力，这些名称在应用层映射时可能更有用（例如在 Rust 的 sqlx crate 中使用）。

对于因版本限制或特定约束需求而无法使用 STRICT 表的情况，可以使用 CHECK 约束作为手动替代方案来强制数据正确性。

Sources