SQLite中文网
灵活打字的优点
▼ 目录

一、简介

SQLite 为开发人员提供了以任何所需格式存储内容的自由，而不管列的声明数据类型如何。有些人觉得这个功能很麻烦。一些开发人员惊讶地发现可以将文本插入到标记为 INTEGER 的列中。

本文提倡支持 SQLite 中灵活的类型规则。

2.关于灵活打字

有关 SQLite 灵活类型系统的详细信息，请参阅单独的 SQLite 中的数据类型文档。这是一个快速总结：

  • 列定义上的数据类型名称是可选的。列定义可以只包含列名而没有其他内容。

  • 当提供数据类型名称时，它们几乎可以是任何文本。SQLite 尝试根据列定义中的数据类型名称推断列的首选数据类型，但该首选数据类型是建议性的，而不是强制性的。首选数据类型称为“列亲和力”。

  • 尝试将传入数据转换为列的首选数据类型。（所有的 SQL 数据库引擎都这样做，不仅仅是 SQLite。）如果这个转换成功，那么一切都很好。但如果不成功，SQLite 不会引发错误，而是使用其原始数据类型存储内容。

  • 上述情况可能会导致僵化打字的提倡者觉得不方便的情况：

    Column DatatypeTypes Allowed In That Column
    INTEGERINTEGER, REAL, TEXT, BLOB
    REALREAL, TEXT, BLOB
    TEXTTEXT, BLOB
    BLOBINTEGER, REAL, TEXT, BLOB

  • 请注意，INTEGER 或 REAL 值永远不会最终存储在 TEXT 列中，因为 INTEGER 或 REAL 值可以而且总是会转换为其等效的 TEXT 表示形式。同样，INTEGER 也永远不会存储在 REAL 列中，因为它总是会转换为 REAL。但是 TEXT 并不总是看起来像 INTEGER 或 REAL 值，因此不能总是进行转换。BLOB 不能转换成任何东西，也没有其他东西可以转换成 BLOB。

3.灵活打字有用的情况

一些读者在第一次接触到 SQLite 中的灵活类型时，会问自己“这怎么会有用？” 这是回答该问题的尝试：

3.1. 属性表

许多应用程序，尤其是那些使用 SQLite 作为 应用程序文件格式的应用程序，需要一个地方来存储杂项属性，例如缩略图（作为 BLOB 值）、短文本（例如用户名）以及数字、日期、和 JSON 值。创建单个表来处理此存储很方便： 

CREATE TABLE attribute(name TEXT PRIMARY KEY, value) WITHOUT ROWID;

如果没有灵活的类型，这样的表格将需要更复杂，每种可能的数据类型都有单独的列。“值”列的灵活类型使表在概念上更简单、更节省空间并且更易于访问和更新。

在Fossil 版本控制系统中，每个存储库都有一个 CONFIG 表，用于存储各种可能的数据类型的各种设置。Fossil 的用户特定配置文件（~/.fossil 文件）是一个单独的 SQLite 数据库，其中包含一个属性表，用于保存所有存储库中的用户特定状态。

一些应用程序使用 SQLite 数据库作为纯键值存储数据库模式包含一个看起来像这样的表： 

CREATE TABLE storage(name TEXT PRIMARY KEY, value ANYTHING);

3.2. json_tree 虚拟表的“值”列输出

SQLite 中内置的json_tree和json_each表值函数都有一个“值”列，可以根据相应 JSON 字段的类型保存 INTEGER、REAL 或 TEXT 类型的值。例如： 

SELECT typeof(value) FROM json_each('{"a":1,"b":2.5,"c":"hello"}');

上面的查询返回一列的三行，值分别为“integer”、“real”和“text”。

3.3. 脏数据存储

分析师有时会遇到 CSV 文件，其中某些列混合包含整数、实数和文本数据。例如，从 Excel 电子表格导出中获得的 CSV 文件通常具有此特征。将此类“脏数据”导入 SQL 数据库时，可以方便地导入灵活的列。

当然，脏数据不仅限于来自 Excel 的 CSV 文件。在许多数据源中，单个字段可能包含多种类型。例如，数据列有时可能是自 1970 年以来的秒数，或者在其他情况下可能是文本日期字符串。清理这些不一致的表示是可取的，但同时在清理进行时能够将所有不同的表示存储在中间数据库的同一列中是很方便的。

3.4. 动态编程语言

SQLite 最初是一个 TCL 扩展，后来被广泛使用。TCL 是一种动态语言，因为程序员不需要知道数据类型。在幕后，TCL 仔细跟踪每个值的数据类型，但对于 TCL 程序的开发人员和用户来说，一切看起来都像一个字符串。灵活类型非常适合与 TCL 等动态编程语言一起使用，因为使用动态编程语言，您无法总是提前预测变量将包含的数据类型。因此，当您需要将该变量的值存储到数据库中时，拥有一个支持灵活类型的数据库可以使存储变得更加容易。

3.5. 数据类型名称交叉兼容性

每个 SQL 数据库引擎似乎都有自己独特的一组受支持的数据类型名称：

  • BIGINT
  • UNSIGNED SMALL INT
  • TEXT
  • VARCHAR
  • VARYING CHARACTER
  • NATIONAL VARYING CHARACTER
  • NVARCHAR
  • JSON
  • REAL
  • FLOAT
  • DOUBLE PRECISION
  • ... and so forth ...

事实上，SQLite 将接受这些名称中的任何一个作为有效的类型名称，并允许您将任何类型的内容存储到列中，这增加了为在其他 SQL 数据库引擎上运行而编写的脚本也可以在 SQLite 中运行的机会。

3.6. 重新利用遗留数据库中未使用或废弃的列

由于 SQLite 数据库文件是磁盘上的单个文件，因此某些应用程序使用 SQLite 作为应用程序文件格式。这意味着应用程序的单个实例在其生命周期中可能会与成百上千个单独的数据库通信，每个数据库都在一个单独的文件中。当此类应用程序经过多年发展后，底层数据库中的某些列的含义将发生微妙的变化。或者，可能需要重新调整现有色谱柱的用途以服务于两个或多个目的。如果该列具有灵活的数据类型，这将更容易做到。

4.灵活打字的缺点（反驳）

从 Hacker News 和 Reddit 以及开发人员讨论此类问题的类似论坛上的无数帖子中收集和汇编了以下灵活类型的缺点。如果你能想到灵活类型不是一个好主意的其他原因，请联系 SQLite 开发人员或在SQLite 论坛上发帖，以便将你的想法添加到列表中。

4.1. 我们以前从未这样做过

许多对灵活打字持怀疑态度的人只是表示震惊和怀疑，而没有提供任何理由来说明为什么他们认为灵活打字是个坏主意。在没有支持论据的情况下，人们必须假设他们不喜欢灵活打字的原因是它与他们习惯的不同。

想必，许多对 SQLite 的灵活类型感到震惊的开发人员都有这种感觉，因为他们以前从未遇到过类似的事情。所有之前接触过的数据库，尤其是 SQL 数据库都涉及严格类型，而 SQL 的读者心智模型包括严格类型作为一个基本特征。灵活的打字打乱了他们的世界观。

是的，灵活类型是一种思考 SQL 数据库中数据的新方法。但新的不一定坏。有时，我认为尤其是在灵活打字的情况下，创新会带来改进。

4.2. 刚性类型实施有助于防止应用程序错误

防止应用程序错误的最佳方法是严格的类型强制执行，这已成为许多程序员的信条。但我没有找到支持这一点的证据。

可以肯定的是，严格的类型强制执行确实有助于防止 C 和 C++ 等低级语言中的某些类型的错误，这些错误提供了接近机器硬件的模型。但这似乎不是高级抽象语言的情况，在高级抽象语言中，所有数据都在某种“值”超类中传递，该超类是各种低级数据类型的子类。当一切都是值对象时，特定的数据类型就不再重要了。

本技术说明由 SQLite 的原作者撰写。我已经编写 TCL 程序 27 年了。TCL 没有任何类型强制执行。TCL 中的“Value”类（称为 Tcl_Obj）可以保存许多不同的数据类型，但它将内容作为字符串呈现给程序和应用程序用户。这些年来，我在那些 TCL 程序中遇到了很多错误。但我不记得有任何一个例子表明 bug 可能已被严格的类型系统捕获。在 35 年的时间里，我还编写了很多 C 代码，其中最重要的是 SQLite 本身。我发现 C 中的类型系统对于发现和预防问题非常有帮助。对于 Fossil 版本控制系统, 它是用 C 编写的，我什至实现了补充静态分析程序，在编译之前扫描 Fossil 源代码，寻找编译器遗漏的问题。这适用于编译程序。

SQL 语言模型是比 C/C++ 更高级的抽象。在 SQLite 中，每个数据项都作为“sqlite3_value”对象存储在内存中。该对象有用于字符串、整数、浮点数、blob 和其他表示的子类。一切都在 SQLite 实现的 SQL 语言内部传递为“sqlite3_value”对象，因此底层数据类型并不重要。我从来没有发现严格的类型强制对像 TCL 和 SQLite 这样的语言有帮助，它们有一个单一的“值”超类用于表示任何数据元素。Fossil 在其实现中广泛使用了 SQLite。Fossil 在其 14 年的历史中出现过许多错误，但我不记得有一个错误可以通过 SQLite 中的严格类型强制执行来避免。

根据几十年的经验，我拒绝严格的类型执行有助于防止应用程序错误的论点。我会接受并相信一个稍微修改过的论点：刚性类型强制有助于防止应用程序在缺少单个顶级“值”超类的语言中出现错误。但是 SQLite 确实有一个“sqlite3_value”超类，所以这句谚语不适用。

4.3. 刚性类型强制防止数据污染

有些人认为，如果您对模式有严格的约束，尤其是严格执行列数据类型，这将有助于防止将不正确的数据添加到数据库中。这不是真的。确实，类型强制可能有助于防止 严重不正确的数据进入系统。但是类型强制无助于防止记录细微的不正确数据。

因此，例如，严格的类型强制可以成功地阻止将客户名称（文本）插入到整数 Customer.creditScore 列中。另一方面，如果发生该错误，则很容易发现问题并找到所有受影响的行。但是类型强制无助于防止客户姓氏和名字颠倒的错误，因为两者都是文本字段。

（旁白：几十年前，我在一个团队工作，那里有一位名叫“Merritt Tracy”的女士。“Merritt”是她的名字，“Tracy”是她的姓氏。她报告说她花费了过多的时间和精力试图更正以“Tracy”作为她的名字和“Merritt”作为她的姓氏的数据库。）

通过抑制易于检测的错误并仅传递难以检测的错误，严格的类型强制实际上可以使查找和修复错误变得更加困难。数据错误往往会聚集在一起。如果您有 20 个不同的数据源，则大多数数据错误通常仅来自其中的 2 个或 3 个。严重错误（例如整数列中的文本）的存在是一个方便的预警信号，表明存在问题。可以快速跟踪问题的根源，并对严重错误的来源进行额外的审查，因此也有望修复细微的错误。当严重的错误被抑制时，您将失去一个重要的信号来帮助您检测和修复细微的错误。

数据错误是不可避免的。无论进行了多少类型检查，它们都会发生。严格的类型执行只能捕获这些情况的一小部分——最明显的情况。它无助于查找和修复更微妙的案例。而且，通过抑制数据源有问题的信号，有时会使细微的错误更难定位。

4.4. 其他 SQL 数据库引擎不是这样工作的

因为 SQLite 的限制较少并且允许你做更多的事情，所以在其他数据库引擎上工作的 SQL 脚本通常也可以在 SQLite 上工作，但是最初为 SQLite 编写的脚本可能无法在限制性更强的数据库引擎上工作。当开发人员使用 SQLite 进行原型设计和测试，然后将他们的应用程序迁移到限制性更强的 SQL 引擎进行部署时，这可能会导致问题。如果应用程序（无意中）利用了 SQLite 中可用的灵活类型，那么它在迁移时就会失败。

人们用这个问题来争论 SQLite 应该对数据类型有更多的限制。但是您也可以轻松地扭转这个论点，说其他数据库引擎在数据类型方面应该更加灵活。毕竟，在迁移之前，该应用程序在 SQLite 下正常工作。如果严格类型强制执行真的那么有用，为什么它会破坏以前正常工作的应用程序？

5.如果你坚持严格的类型执行......

从 SQLite 版本 3.37.0 (2021-11-27) 开始，SQLite 支持使用STRICT 表的这种开发风格。

如果您发现 STRICT 表阻止或本可以阻止应用程序错误的真实案例，请向 SQLite 论坛发帖，以便我们将您的故事添加到本文档中。

6.拥抱自由

如果在 SQL 数据库中灵活键入对您来说是一个新概念，我鼓励您尝试一下。它可能不会给您带来任何问题，它可能会使您的程序更简单，更易于编写和维护。我认为，即使您一开始持怀疑态度，如果您只是尝试灵活类型，您最终会意识到这是一种更好的方法，并且会开始鼓励其他数据库供应商至少支持 ANY 数据类型（如果不完整的话） SQLite 风格的类型灵活性。

大多数时候，灵活的类型无关紧要，因为列存储一个定义明确的类型。但偶尔你会遇到这样的情况：灵活的类型系统可以让你的问题的解决方案更清晰、更容易。