【突发】推荐在SQLite中使用严格表简而言之推荐在SQLite中使用严格表因为它们能避免一些数据类型问题比如在数字列中插入文本。SQLite有一个被低估的特性——严格表它有助于实施严格的类型约束防止出现将文本插入整数列这类错误。【创建方法】如何创建严格表要创建一个严格表只需在表定义的末尾加上 STRICT 关键字。示例如下-- 原语句 CREATE TABLE people (name TEXT); -- 修改后 CREATE TABLE people (name TEXT) STRICT;【严格表的优势】【防止插入或更新时出现类型不匹配】总体而言严格表有助于实施严格的数据类型约束就像其他SQL数据库引擎那样。最重要的是严格表能防止向列中插入错误的数据类型。例如在普通的SQLite表中可以将文本插入 INTEGER 列但在严格表中则不行。-- 非严格表允许在任何列中插入任何类型的数据 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); -- 报错无法将TEXT类型的值存储到INTEGER列中将文本插入整数列或者反之都是错误的做法。同样的类型验证也适用于 UPDATE 操作。值得注意的是如果一个值可以无损转换那么它仍然会被接受。例如字符串 123 可以完美地转换为整数因此是被允许的。即使在严格表中以下两行代码也是等效的INSERT INTO people_strict (age) VALUES (123); INSERT INTO people_strict (age) VALUES (123);【防止在创建表时使用无效的列类型】默认情况下可以创建带有无效类型的列。例如以下所有语句都能执行尽管这些类型并不是有效的SQLite数据类型-- 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支持的数据类型的误解还有一些则是严重的错误。在这些语句的末尾加上 STRICT 关键字会导致它们报错。-- 所有这些语句都会报错这正是所期望的 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; -- 由于该列的数据类型为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 (X8647);【严格表的缺点】【无法将现有表转换为严格表】从一开始就使用严格表是最好的做法但这并不总是可行的。目前没有办法通过 ALTER 语句将现有表转换为严格表必须将非严格表中的数据复制到新的严格表中。示例如下-- 1. 创建一个具有相同结构的新严格表 CREATE TABLE new_people (name TEXT) STRICT; -- 2. 复制数据如果数据类型有误这一步可能会有风险 INSERT INTO new_people SELECT * FROM people; -- 3. 删除旧表 DROP TABLE people; -- 4. 将新表重命名为旧表的名称 ALTER TABLE new_people RENAME TO people;如果非严格表中包含无效数据这个过程可能会很棘手。例如如果旧数据中不小心在整数列中包含了文本那么在迁移数据时就会报错。可能需要清理数据或进行类型转换。可以为代码库制定一个规则规定所有新表都使用严格表但这也可能意味着表在验证规则上不一致是否采用这种方式需要根据实际情况来决定。【SQLite开发者的观点与不同】SQLite有一个名为 灵活类型的优势 的页面在该页面中开发者认为SQLite的灵活类型行为实际上是有益的。但在大多数情况下并不认同他们的观点个人遇到过很多因意外的数据类型导致的微妙问题更希望这些错误能尽早暴露出来。他们指出了灵活表的一些合理使用场景比如 纯键值存储 或 存储不同类型的杂项属性还提到在某些情况下可能希望保留无效数据比如直接导入一个杂乱的CSV文件且不想丢失任何数据。尽管如此仍然推荐使用严格表但也承认非严格表在某些情况下是合理的。【仅适用于SQLite 3.37.0及以上版本】SQLite在3.37.0版本于2021年11月发布中引入了严格表。如果使用的是较旧版本的SQLite则无法使用严格表。旧版本的SQLite无法读取包含严格表的数据库。例如如果在最新版本的SQLite中创建了一个严格表然后尝试在SQLite 3.36.0严格表引入之前的版本中读取该数据库即使严格表已经存在于数据库中也会报错。【性能问题可能】从理论上讲严格表的性能可能会稍慢一些因为它们需要进行额外的检查工作。例如在插入或更新数据时它们会检查数据类型。但在实际使用中编写了一个简单的脚本向一个包含100列的表中插入了数百万行数据在多台机器上测试都没有发现明显的性能差异磁盘上的文件大小也相同。没有进行全面的测试所以可能有一些情况被忽略了但认为严格表不会带来性能问题。实际上由于避免了意外的列类型不匹配问题严格表的性能可能会更好但同样还没有对此进行测试。【结论】我喜欢严格表个人认为严格表的优点大于缺点。通常更喜欢严格的数据类型约束它可以避免一类常见的错误并有助于保证数据的完整性。严格表并非万能的但它们通常很容易实现并且能带来很大的好处。
SQLite严格表：避免数据类型问题，优点大于缺点！
【突发】推荐在SQLite中使用严格表简而言之推荐在SQLite中使用严格表因为它们能避免一些数据类型问题比如在数字列中插入文本。SQLite有一个被低估的特性——严格表它有助于实施严格的类型约束防止出现将文本插入整数列这类错误。【创建方法】如何创建严格表要创建一个严格表只需在表定义的末尾加上 STRICT 关键字。示例如下-- 原语句 CREATE TABLE people (name TEXT); -- 修改后 CREATE TABLE people (name TEXT) STRICT;【严格表的优势】【防止插入或更新时出现类型不匹配】总体而言严格表有助于实施严格的数据类型约束就像其他SQL数据库引擎那样。最重要的是严格表能防止向列中插入错误的数据类型。例如在普通的SQLite表中可以将文本插入 INTEGER 列但在严格表中则不行。-- 非严格表允许在任何列中插入任何类型的数据 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); -- 报错无法将TEXT类型的值存储到INTEGER列中将文本插入整数列或者反之都是错误的做法。同样的类型验证也适用于 UPDATE 操作。值得注意的是如果一个值可以无损转换那么它仍然会被接受。例如字符串 123 可以完美地转换为整数因此是被允许的。即使在严格表中以下两行代码也是等效的INSERT INTO people_strict (age) VALUES (123); INSERT INTO people_strict (age) VALUES (123);【防止在创建表时使用无效的列类型】默认情况下可以创建带有无效类型的列。例如以下所有语句都能执行尽管这些类型并不是有效的SQLite数据类型-- 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支持的数据类型的误解还有一些则是严重的错误。在这些语句的末尾加上 STRICT 关键字会导致它们报错。-- 所有这些语句都会报错这正是所期望的 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; -- 由于该列的数据类型为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 (X8647);【严格表的缺点】【无法将现有表转换为严格表】从一开始就使用严格表是最好的做法但这并不总是可行的。目前没有办法通过 ALTER 语句将现有表转换为严格表必须将非严格表中的数据复制到新的严格表中。示例如下-- 1. 创建一个具有相同结构的新严格表 CREATE TABLE new_people (name TEXT) STRICT; -- 2. 复制数据如果数据类型有误这一步可能会有风险 INSERT INTO new_people SELECT * FROM people; -- 3. 删除旧表 DROP TABLE people; -- 4. 将新表重命名为旧表的名称 ALTER TABLE new_people RENAME TO people;如果非严格表中包含无效数据这个过程可能会很棘手。例如如果旧数据中不小心在整数列中包含了文本那么在迁移数据时就会报错。可能需要清理数据或进行类型转换。可以为代码库制定一个规则规定所有新表都使用严格表但这也可能意味着表在验证规则上不一致是否采用这种方式需要根据实际情况来决定。【SQLite开发者的观点与不同】SQLite有一个名为 灵活类型的优势 的页面在该页面中开发者认为SQLite的灵活类型行为实际上是有益的。但在大多数情况下并不认同他们的观点个人遇到过很多因意外的数据类型导致的微妙问题更希望这些错误能尽早暴露出来。他们指出了灵活表的一些合理使用场景比如 纯键值存储 或 存储不同类型的杂项属性还提到在某些情况下可能希望保留无效数据比如直接导入一个杂乱的CSV文件且不想丢失任何数据。尽管如此仍然推荐使用严格表但也承认非严格表在某些情况下是合理的。【仅适用于SQLite 3.37.0及以上版本】SQLite在3.37.0版本于2021年11月发布中引入了严格表。如果使用的是较旧版本的SQLite则无法使用严格表。旧版本的SQLite无法读取包含严格表的数据库。例如如果在最新版本的SQLite中创建了一个严格表然后尝试在SQLite 3.36.0严格表引入之前的版本中读取该数据库即使严格表已经存在于数据库中也会报错。【性能问题可能】从理论上讲严格表的性能可能会稍慢一些因为它们需要进行额外的检查工作。例如在插入或更新数据时它们会检查数据类型。但在实际使用中编写了一个简单的脚本向一个包含100列的表中插入了数百万行数据在多台机器上测试都没有发现明显的性能差异磁盘上的文件大小也相同。没有进行全面的测试所以可能有一些情况被忽略了但认为严格表不会带来性能问题。实际上由于避免了意外的列类型不匹配问题严格表的性能可能会更好但同样还没有对此进行测试。【结论】我喜欢严格表个人认为严格表的优点大于缺点。通常更喜欢严格的数据类型约束它可以避免一类常见的错误并有助于保证数据的完整性。严格表并非万能的但它们通常很容易实现并且能带来很大的好处。
