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 整数主键

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 被视为不同的字符串。