SQLite 版本 2 中的数据类型

小巧。快速。可靠。
三选其二。

SQLite 版本 2 中的数据类型

1.0 无类型性

SQLite 是“无类型”的。这意味着您可以在任何表的任何列中存储任何类型的数据，而不管该列声明的数据类型是什么。（请参阅下面第 2.0 节中对此规则的一个例外。）此行为是一项功能，而不是错误。数据库应该存储和检索数据，并且数据库不应该关心数据的格式是什么。大多数其他 SQL 引擎中发现并在 SQL 语言规范中编纂的强类型系统是一个错误的功能——它是实现显示到接口中的一个例子。SQLite 试图通过允许您将任何类型的数据存储到任何类型的列中以及通过允许灵活地指定数据类型来克服此错误的功能。

对于 SQLite 而言，数据类型是零个或多个名称的任意序列，后面可以选择跟一个或两个带括号的带符号整数列表。特别要注意，数据类型可以是零个或多个名称。这意味着空字符串在 SQLite 看来是有效的数据类型。因此，您可以声明表，其中每列的数据类型未指定，如下所示

CREATE TABLE ex1(a,b,c);

即使 SQLite 允许省略数据类型，但在您的 CREATE TABLE 语句中包含它仍然是一个好主意，因为数据类型通常可以作为其他程序员关于您打算在列中放入什么的良好提示。如果您将代码移植到另一个数据库引擎，那么另一个引擎可能会需要某种数据类型。SQLite 接受所有常用数据类型。例如

CREATE TABLE ex2(
  a VARCHAR(10),
  b NVARCHAR(15),
  c TEXT,
  d INTEGER,
  e FLOAT,
  f BOOLEAN,
  g CLOB,
  h BLOB,
  i TIMESTAMP,
  j NUMERIC(10,5)
  k VARYING CHARACTER (24),
  l NATIONAL VARYING CHARACTER(16)
);

等等。基本上，任何名称序列后面可以选择跟一个或两个带括号的带符号整数都可以。

2.0 INTEGER PRIMARY KEY

SQLite 无类型的一个例外是类型为 INTEGER PRIMARY KEY 的列。（并且您必须使用“INTEGER”而不是“INT”。类型为 INT PRIMARY KEY 的列与任何其他列一样是无类型的。）INTEGER PRIMARY KEY 列必须包含一个 32 位带符号整数。任何尝试插入非整数数据的操作都将导致错误。

INTEGER PRIMARY KEY 列可用于实现 AUTOINCREMENT 的等效项。如果您尝试将 NULL 插入 INTEGER PRIMARY KEY 列，则该列实际上将填充一个比表中已有的最大键大 1 的整数。或者，如果最大键是 2147483647，则该列将填充一个随机整数。无论哪种方式，INTEGER PRIMARY KEY 列都将分配一个唯一的整数。您可以使用sqlite_last_insert_rowid() API 函数或在后续的 SELECT 语句中使用last_insert_rowid() SQL 函数检索此整数。

3.0 比较和排序顺序

SQLite 在决定允许将哪些数据存储在列中时是无类型的。但是，在排序和比较数据时，会用到某种类型的概念。出于这些目的，列或表达式可以是两种类型之一：数字和文本。排序或比较可能会根据正在排序或比较的数据类型给出不同的结果。

如果数据类型为文本，则比较由标准 C 数据比较函数memcmp()或strcmp()确定。比较逐个查看两个输入的字节，并返回第一个非零差异。字符串以'\000'结尾，因此较短的字符串在较长的字符串之前排序，正如您所期望的那样。

对于数字数据，情况比较复杂。如果两个输入看起来都像格式良好的数字，则使用atof()将其转换为浮点值并进行数值比较。如果一个输入不是格式良好的数字，但另一个是，则该数字被认为小于非数字。如果两个输入都不是格式良好的数字，则使用strcmp()进行比较。

不要混淆这样一个事实，即列可能具有“数字”数据类型。这并不意味着该列只能包含数字。它仅仅意味着如果该列确实包含一个数字，则该数字将按数字顺序排序。

对于文本和数字值，NULL 都排在任何其他值之前。使用像“<”或“>=”这样的运算符将任何值与 NULL 进行比较始终为假。

4.0 SQLite 如何确定数据类型

对于 SQLite 版本 2.6.3 及更早版本，所有值都使用数字数据类型。文本数据类型出现在 2.7.0 及更高版本中。在后续内容中，假设您使用的是 2.7.0 或更高版本的 SQLite。

对于表达式，结果的数据类型通常由最外层的运算符确定。例如，算术运算符（“+”，“*”，“%”）始终返回数字结果。字符串连接运算符（“||”）返回文本结果。等等。如果您对表达式的类型有任何疑问，可以使用特殊的typeof() SQL 函数来确定数据类型是什么。例如

sqlite> SELECT typeof('abc'+123);
numeric
sqlite> SELECT typeof('abc'||123);
text

对于表列，数据类型由 CREATE TABLE 语句的类型声明确定。当且仅当类型声明包含以下一个或多个字符串时，数据类型为文本

BLOB
CHAR
CLOB
TEXT

当然，在类型声明中搜索这些字符串时不区分大小写。如果上述任何字符串出现在类型声明的任何位置，则该列的数据类型为文本。请注意，类型“VARCHAR”包含“CHAR”作为子字符串，因此它被认为是文本。

如果类型声明中没有任何上述字符串出现，则数据类型为数字。特别要注意，具有空类型声明的列的数据类型为数字。

5.0 示例

考虑以下两个命令序列

CREATE TABLE t1(a INTEGER UNIQUE);        CREATE TABLE t2(b TEXT UNIQUE);
INSERT INTO t1 VALUES('0');               INSERT INTO t2 VALUES(0);
INSERT INTO t1 VALUES('0.0');             INSERT INTO t2 VALUES(0.0);

在左侧的序列中，第二个插入将失败。在这种情况下，字符串“0”和“0.0”被视为数字，因为它们被插入到数字列中，但 0==0.0，这违反了唯一性约束。但是，右侧序列中的第二个插入有效。在这种情况下，常量 0 和 0.0 被视为字符串，这意味着它们是不同的。

SQLite 始终将数字转换为双精度（64 位）浮点数以进行比较。这意味着，如果数字列中只有一位不重要的数字不同的长数字序列将比较相等，但如果它们在文本列中则将比较不相等。我们有

INSERT INTO t1                            INSERT INTO t2
   VALUES('12345678901234567890');           VALUES(12345678901234567890);
INSERT INTO t1                            INSERT INTO t2
   VALUES('12345678901234567891');           VALUES(12345678901234567891);

如前所述，左侧的第二个插入将失败，因为比较将首先将两个字符串转换为浮点数，并且字符串中唯一的区别在于第 20 位，它超过了 64 位浮点数的分辨率。相反，右侧的第二个插入将有效，因为在这种情况下，插入的数字是字符串，并且使用 memcmp() 进行比较。

数字和文本类型对 DISTINCT 关键字也有影响

CREATE TABLE t3(a INTEGER);               CREATE TABLE t4(b TEXT);
INSERT INTO t3 VALUES('0');               INSERT INTO t4 VALUES(0);
INSERT INTO t3 VALUES('0.0');             INSERT INTO t4 VALUES(0.0);
SELECT DISTINCT * FROM t3;                SELECT DISTINCT * FROM t4;

左侧的 SELECT 语句返回一行，因为“0”和“0.0”被视为数字，因此是相同的。但是，右侧的 SELECT 语句返回两行，因为 0 和 0.0 被视为字符串，它们是不同的。

此页面上次修改于 2022-01-08 05:02:57 UTC