相关的SQLite数据类型如INT、INTEGER、SMALLINT和TINYINT有什么区别？

Question

在创建SQLite3表时，我遇到了许多数据类型，它们的含义非常相似，这让我感到困惑，请问以下这些数据类型之间有什么区别？

INT, INTEGER, SMALLINT, TINYINT

DEC, DECIMAL

LONGCHAR, LONGVARCHAR

DATETIME, SMALLDATETIME

是否有某个文档列出了各种数据类型的最小/最大容量？例如，我猜 smallint 的最大值比 tinyint 大，但比整数小，但我不知道这些容量是多少。

- Alan Harris-Reid

Answer 1

SQLite在技术上没有数据类型，而是使用显式类型转换系统的存储类，如果您习惯于传统的RDBMS，可能会感到困惑。内部所有数据都存储为文本。数据类型根据亲和性（分配给列的数据类型）被强制转换/转换为各种存储位置。

我建议您做的最好的事情是：

1. 暂时忘记您以前了解的独立数据库数据类型

2. 阅读来自SQLite网站的上述链接。

3. 获取旧模式中基于类型的类型，并查看它们在SQLite中的映射

4. 将所有数据迁移到SQLite数据库。

注意：数据类型限制可能很麻烦，特别是如果您在SQL中添加时间持续时间、日期或类似的东西。 SQLite对此类事物提供的内置函数非常少。但是，SQLite通过sqlite3_create_function库函数为您提供了一种轻松的方法来创建自己的内置函数以添加时间持续时间和类似的事物。您将使用该功能来替换传统存储过程。

- J. Polfer

---

感谢回复和提供链接。这是否意味着我应该坚持使用基本的文本、数字、整数、实数和无类型？对我来说似乎非常有限，特别是我之前使用过MSSQL、Foxpro和Oracle。谢谢。 - Alan Harris-Reid

我同意你的观点，起初它似乎有限。然而，我认为你会发现，通过SQLite的亲和性系统，你可以很轻松地将数据放入数据库中。SQLite并不是独立存在的 - 它被设计成一个小型数据库后端，只能通过代码中的数据库访问API来访问。在SQLite中解决问题通常是确定它们希望你如何解决的问题。 - J. Polfer

我会坚持使用基本类型。 - J. Polfer

你可能会发现将数字亲和列声明为DATE或BOOLEAN类型很有用，但我不会费心去区分不同大小的整数。对于INTEGER PRIMARY KEY的情况尤其如此，这是唯一一个确切的类型名称很重要的情况。 - dan04

“一切都存储为文本”似乎是错误的，根据https://www.sqlite.org/fileformat.html，数字被存储为紧凑的varints / float64，只有blob和text被存储为字符串。 - phiresky

Answer 2

区别在于语法糖。就类型亲和性而言，仅有少数类型名称的子字符串是重要的。

• INT、INTEGER、SMALLINT、TINYINT → INTEGER 亲和性，因为它们都包含 "INT"。
• LONGCHAR、LONGVARCHAR → TEXT 亲和性，因为它们包含 "CHAR"。
• DEC、DECIMAL、DATETIME、SMALLDATETIME → NUMERIC，因为它们不包含任何重要的子字符串。

确定亲和性的规则列在SQLite 网站上。

如果您坚持严格类型，可以使用 CHECK 约束来实现。

CREATE TABLE T (
   N   INTEGER CHECK(TYPEOF(N) = 'integer'),
   Str TEXT CHECK(TYPEOF(Str) = 'text'),
   Dt  DATETIME CHECK(JULIANDAY(Dt) IS NOT NULL)
);

但我从不去烦它。

至于每种类型的容量：

• INTEGER 总是带有符号的64位。请注意，SQLite会在幕后优化小整数的存储，因此TINYINT也没有用。
• REAL 总是64位（double）。
• TEXT 和 BLOB 的最大大小由预处理器宏确定，默认为1,000,000,000字节。

- dan04

---

不错的技巧。请注意，此方法需要要插入/更新的值具有与TYPEOF中相同的存储类。因此，尝试插入一个本来会被SQLite转换为NUMERIC / INTEGER存储类的TEXT（即，根据https://www.sqlite.org/datatype3.html#affinity，这种转换是无损的）将失败。换句话说，这种方法比通过插入值然后以某种神奇的方式验证SQLite用于存储该值的存储类的临时方法更加*严格*。对于更宽松的方法，请参见下面的答案。 - eold

Answer 3

大部分是为了兼容性而存在的。实际上，您只有整数、浮点数、文本和 blob（二进制大对象）类型可用。日期可以存储为数字（Unix时间为整数，Microsoft时间为浮点数）或文本。

- Jay

---

我在基于Web的项目中使用它，最终所有内容都以文本形式放入HTML页面中。我省略了很多数据转换，只是大部分列使用了文本。效果很好。 - Jay

Answer 4

NULL。该值为一个空值。

INTEGER。该值为一个有符号整数，根据值的大小，存储在1、2、3、4、6或8个字节中。

REAL。该值为浮点数，以8字节IEEE浮点数的形式存储。

TEXT。该值为文本字符串，使用数据库编码（UTF-8、UTF-16BE或UTF-16LE）进行存储。

BLOB。该值为数据块，完全按输入时的格式进行存储。

- user2393484

Answer 5

作为对dan04的回答的补充，如果您想要盲目插入一个由TEXT表示但确保文本可以转换为数字的非零NUMERIC：

your_numeric_col NUMERIC CHECK(abs(your_numeric_col) <> 0)

典型用例是在程序查询中，将所有数据视为文本（为了统一性和简单性，因为SQLite已经这样做了）。好处在于它允许像这样的结构：

INSERT INTO table (..., your_numeric_column, ...) VALUES (..., some_string, ...)

如果您使用占位符，这将非常方便，因为您不必特别处理这些非零数字字段。一个使用Python的sqlite3模块的例子如下：

conn_or_cursor.execute(
    "INSERT INTO table VALUES (" + ",".join("?" * num_values) + ")",   
    str_value_tuple)  # no need to convert some from str to int/float

在上面的例子中，当传递给SQlite时，str_value_tuple 中的所有值都将被转义并作为字符串引用。然而，由于我们没有通过 TYPEOF 显式地检查类型，而是只检查了可转换性，因此它仍将按预期工作（即，SQLite将将其存储为数字或以其他方式失败）。

- eold