SQLite 3 中的数据类型

1.0 存储类和数据类型

存储在 SQLite 数据库中的每个值（或者由数据库引擎操纵的值）都是以下的存储类之一：

• NULL。该值是一个 NULL 值。
• INTEGER。该值是一个有符号的整数，根据值的大小，存储 1、2、3、4、5、6 或 8 字节内容。
• REAL。该值是一个浮点值，存储一个 8 字节的 IEEE 浮点数。
• TEXT。该值是一个文本字符串，使用数据库编码（UTF-8、 UTF-16BE 或 UTF-16LE）进行存储。
• BLOB。该值是一个数据块，按照它的输入直接存储。

注意，一个存储类要略普遍于一个数据类型。INTEGER 存储类，例如，包含不同长度的 6 个不同的整数类型。这在磁盘上是不同的。但是一旦 INTEGER 值从磁盘读取到内存进行处理，它们都将被转换为最普通的数据类型（8 字节有符号的整数）。因此大部分“存储类”是无异于“数据类型”的，并且有 2 个条款能被互换地使用。

在一个 SQLite 版本 3 数据库中的任何列，除了 INTEGER PRIMARY KEY 列，都可以被用来存储一个任意存储类的值。

SQL 语句中的所有值，无论它们是嵌入在 SQL 语句文本里的字面量，还是绑定到预编译 SQL 语句的参数，都有一个内在存储类。在以下描述的情况中，数据库引擎会在执行查询过程中，在数字存储类（INTEGER 和 REAL）和 TEXT 之间进行转换。

1.1 Boolean 数据类型

SQLite 没有一个分开的 Boolean 存储类。替代的，Boolean 值被存储为整数 0（false）和 1（true）。

1.2 Date 和 Time 数据类型

SQLite 没有一个预留的存储日期和/或时间的存储类。替代的，SQLite 内置的 Date 和 Time 函数有能力把日期和时间存储为 TEXT、REAL 或 INTEGER 值：

• TEXT 为 ISO8601 字符串（YYYY-MM-DD HH:MM:SS.SSS）。
• REAL 为“朱利安”天数，天数从“格林威治”公元前4714年11月24日中午到预期的公历。
• INTEGER 为 Unix Time，自 1970-01-01 00:00:00 UTC 以来的秒数。

应用程序可以选择任何格式来存储日期和时间，并且使用内置的日期和时间函数在格式之间自由地转换。

2.0 亲和类型

为了最大化 SQLite 和其它数据库引擎之间的兼容性，SQLite 支持列上的“亲和类型”概念。一个列的亲和类型是存储在该列的数据的推荐类型。在这里最重要的是，类型是被推荐的，而不是必须的。所有列仍可以存储任意类型的数据。这只是一些列，给出选择，将会使用一个存储类覆盖原先的。一个列首选的存储类称为它的“亲和类型”。

SQLite 3 数据库中的每个列被分配了以下亲和类型之一：

• TEXT
• NUMERIC
• INTEGER
• REAL
• NONE

一个带有 TEXT 亲和类型的列，可以使用存储类 NULL、TEXT、BLOB 来存储所有的数据。如果数值数据被插入到一个带有 TEXT 亲和类型的列中，它将从原来被存储的格式转换为现在的文本格式。

一个带有 NUMERIC 亲和类型的列可以包含所有的 5 种存储类。当文本数据被插入到一个 NUMERIC 列中，如果转换是无损和可逆的，文本存储类将被转换为 INTEGER 或 REAL（优先顺序）。对于 TEXT 和 REAL 存储类之间的转换，如果数的前 15 位是有意义的十进制数字，SQLite 考虑到转换要无损和可逆，都将会被保留。如果 TEXT 到 INTEGER 或 REAL 的无损转换是不可能的，那么值将使用 TEXT 存储类进行存储。不要尝试将其转换为 NULL 或 BLOB 值。

一个字符串可能看起来像一个带有小数点和/或指数符号的浮点数字面量，但只要该值能够被表示为一个整数，NUMERIC 亲和类型会将转换它为一个整数。因此，在一个 NUMERIC 亲和类型的列中，字符串 '3.0e+5' 被存储为整数 300000，而不是浮点值 300000.0。

一个使用 INTEGER 亲和类型的列与一个使用 NUMERIC 亲和类型的列在行为上是相同的。INTEGER 和 NUMERIC 亲和类型不同点只表现在一个 CAST 表达式上。

一个带有 REAL 亲和类型的列与一个带有 NUMERIC 亲和类型的列在行为上是相同的，除了它强制把整数值作为浮点数来表示。（作为一个内部的优化，没有小数部分的小浮点数值存储到带有 REAL 亲和类型的列中时，将被作为一个整数写入到磁盘，为的是占用更少的空间和在它被读出时能被自动地转换回浮点数。该优化在 SQL 级别是完全不可见的，并且只有通过研究数据库文件的原始数据才能被检测。）

一个带有 NONE 亲和类型的列不建议将一个存储类覆盖到另一个存储类，并且不要尝试强制将数据从一个存储类变为另一个存储类。

2.1 亲和列的测定

一个列的亲和类型是由列的声明类型决定的，根据下面展示的有顺序的规则：

1. 如果类型声明包含字符串“INT”，它将被分配为 INTEGER 亲和类型。
2. 如果列的类型声明包含字符串“CHAR”、“CLOB”或“TEXT”的任意之一，列将拥有 TEXT 亲和类型。
3. 如果一个列的类型声明包含字符串“BLOB”，或者如果没有类型被指定，列将拥有 NONE 亲和类型。
4. 如果一个类的类型声明包含字符串“REAL”、“FLOA”或“DOUB”的任意之一，列将拥有 REAL 亲和类型。
5. 否则，亲和类型为 NUMERIC。

注意，决定列的亲和类型的规则的顺序是十分重要的。一个列的类型声明若是“CHARINT”，则将匹配规则 1 和 2，但是第一个规则更加优先，因此列的亲和类型为 INTEGER。

2.2 亲和名称的例子

下表展示了，如何通过前一章节的 5 个规则，将传统的 SQL 实现的通用数据类型名称转换为亲和的类型。该表只展示了 SQLite 允许的数据类型名称的一个小子集。注意，类型名称后面括号中的数字参数（如：“VARCHAR(255)”）将被 SQLite 忽略 - SQLite 不会在全局 SQLITE_MAX_LENGTH 以外，再对字符串、BLOBs 或数字值的长度上强加任何的长度限制。

来自 CREATE TABLE 语句 CAST 表达式中
类型名称的例子 亲和类型结果 被用来决定亲和类型的规则

INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER	1
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB	TEXT	2
BLOB no datatype specified	NONE	3
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL	4
NUMERIC DECIMAL(10 5) BOOLEAN DATE DATETIME	NUMERIC	5

注意，一个“FLOATING POINT”类型声明会给出 INTEGER 亲和类型，而不是 REAL 亲和类型，由于“POINT”的结尾是“INT”。并且类型声明“STRING”将是一个 NUMBERIC 亲和类型，而不是 TEXT。

2.3 列亲和行为的例子

下面的 SQL 演示了 SQLite 在值被插入到一个表时，如何使用列的亲和类型来进行类型转换。

CREATE TABLE t1(
    t  TEXT      -- 规则 2 的文本亲和类型
    nu NUMERIC   -- 规则 5 的数字亲和类型
    i  INTEGER    -- 规则 1 的整数亲和类型
    r  REAL      -- 规则 4 的浮点数亲和类型
    no BLOB      -- 规则 3 的无亲和类型
);

  

-- 值被存储为 TEXT、INTEGER、INTEGER、REAL、TEXT。
INSERT INTO t1 VALUES('500.0' '500.0' '500.0' '500.0' '500.0');
SELECT typeof(t) typeof(nu) typeof(i) typeof(r) typeof(no) FROM t1;
text|integer|integer|real|text

  

-- 值被存储为 TEXT、INTEGER、INTEGER、REAL、REAL。
DELETE FROM t1;
INSERT INTO t1 VALUES(500.0 500.0 500.0 500.0 500.0);
SELECT typeof(t) typeof(nu) typeof(i) typeof(r) typeof(no) FROM t1;
text|integer|integer|real|real

  

-- 值被存储为 TEXT、INTEGER、INTEGER、REAL、INTEGER。
DELETE FROM t1;
INSERT INTO t1 VALUES(500 500 500 500 500);
SELECT typeof(t) typeof(nu) typeof(i) typeof(r) typeof(no) FROM t1;
text|integer|integer|real|integer

  

-- 无论列的亲和类型，BLOBs 总是被存储为 BLOBs。
DELETE FROM t1;
INSERT INTO t1 VALUES(x'0500' x'0500' x'0500' x'0500' x'0500');
SELECT typeof(t) typeof(nu) typeof(i) typeof(r) typeof(no) FROM t1;
blob|blob|blob|blob|blob

  

-- NULLs 也不会被亲和类型所影响。
DELETE FROM t1;
INSERT INTO t1 VALUES(NULL NULL NULL NULL NULL);
SELECT typeof(t) typeof(nu) typeof(i) typeof(r) typeof(no) FROM t1;
null|null|null|null|null

 

 

3.0 比较表达式

SQLite 版本 3 有通常的 SQL 比较操作符集，包括“=”、“<”、“<=”、“>=”、“!=”、“IN”、“BETWEEN”和“IS”等。

3.1 排序顺序

一个比较的结果是依靠操作数的存储类来决定的，根据下面的规则：

• 存储类 NULL 的值被认为是小于其它任何值的（包括其它存储类 NULL 的值）。
• 一个 INTEGER 或 REAL 值小于任何 TEXT 或 BLOB 值。当一个 INTEGER 或 REAL 与其它 INTEGER 或 REAL 比较时，一个数值的比较将被执行。
• 一个 TEXT 值是小于一个 BLOB 值的。当两个 TEXT 值进行比较时，一个适当的集合序列将被用来决定其结果。
• 当两个 BLOB 值进行比较时，使用 memcmp() 来决定结果。

3.2 比较操作数的亲和类型

SQLite 在执行一个比较之前，会尝试在存储类 INTEGER、REAL 和/或 TEXT 之间执行值的转换。在比较发生之前，无论尝试任何转换，都是依靠操作数的亲和类型而进行的。操作数的亲和类型是由以下规则决定的：

• 一个表达式是一个对列值的简单引用，则拥有与列相同的亲和类型。注意，如果 X 和 Y.Z 是列名，那么 +X 和 +Y.Z 将被认为是表达式，目的是为了确定亲和类型。
• 一个“CAST(expr TO type)”格式的表达式，拥有与一个列的“type”类型声明相同的亲和类型。
• 否则，表达式为 NONE 亲和类型。

3.3 比较之前的类型转换

要“应用亲和类型”意思是转换一个操作数为一个特定的存储类，当且仅当转换是无损和可逆的。要在比较之前将亲和类型应用到一个比较操作符的操作数中，根据以下展示的规则顺序：

• 如果一个操作数为 INTEGER、REAL 或 NUMERIC 亲和类型，并且另一个操作数为 TEXT 或 NONE 亲和类型，NUMERIC 亲和类型将被应用到另一个操作数上。
• 如果一个操作数为 TEXT 亲和类型，并且另一个操作数为 NONE 亲和类型，TEXT 亲和类型将被应用到另一个操作数上。
• 否则，无亲和类型将被应用到两个比较的操作数上。

表达式“a BETWEEN b AND c”被视为两个分开的二元比较“a >= b AND a <= c”，即使这意味着不同的亲和类型被应用到每个比较的“a”上。格式“x IN (SELECT y ...)”比较中的数据类型转换，被处理为“x = y”是否为真。表达式“a IN (x y z ...)”相当于“a = +x a = +y a = +z ...”。换句话讲，IN 操作符右边的值（该例中为“x”、“y”和“z”的值）将被视为无亲和类型，即使它们碰巧是列值或 CAST 表达式。

3.4 比较示例

CREATE TABLE t1(
    a TEXT       -- 文本亲和类型
    b NUMERIC    -- 数字亲和类型
    c BLOB       -- 无亲和类型
    d            -- 无亲和类型
);

-- 值将分别被存储为 TEXT、INTEGER、TEXT 和 INTEGER。
INSERT INTO t1 VALUES('500' '500' '500' 500);
SELECT typeof(a) typeof(b) typeof(c) typeof(d) FROM t1;
text|integer|text|integer

-- 因为列“a”是文本亲和类型，右手边的数字值在发生比较之前，就被转换为文本了。
SELECT a < 40    a < 60    a < 600 FROM t1;
0|1|1

-- 文本亲和类型被应用到右手边的操作数，但因为它们已经是 TEXT，这将是一个空操作；不会发生转换。
SELECT a < '40' a < '60' a < '600' FROM t1;
0|1|1

-- 列“b”是数字亲和类型，因此数字亲和类型将被应用给右边的操作数。因为操作数已经是数字，应用程序的亲和类型将是空操作；不会发生转换。所有值进行数值比较。
SELECT b < 40    b < 60    b < 600 FROM t1;
0|0|1

-- 数字亲和类型将被应用到右边的操作数，将它们从文本转换为整数。然后产生一个数字比较。
SELECT b < '40' b < '60' b < '600' FROM t1;
0|0|1

-- 发生无亲和类型转换。右手边的值都是存储类 INTEGER，它们总是小于左边的 TEXT 值。
SELECT c < 40    c < 60    c < 600 FROM t1;
0|0|0

-- 发生无亲和类型转换。值作为 TEXT 进行比较。
SELECT c < '40' c < '60' c < '600' FROM t1;
0|1|1

-- 发生无亲和类型转换。右手边的值都是存储类 INTEGER，它们与左边的 INTEGER 值进行数值比较。
SELECT d < 40    d < 60    d < 600 FROM t1;
0|0|1

-- 发生无亲和类型转换。左边的 INTEGER 值总是小于右边的 TEXT 值。
SELECT d < '40' d < '60' d < '600' FROM t1;
1|1|1

  

如果比较被换算，该例中所有的结果都是相同的 - 如果表达式为“a < 40”格式，将被重写为“40 > a”。

4.0 操作符

所有数学运算操作符（+、-、*、/、%、<<、>>、& 和 |）两边的操作数在执行之前都被转换为 NUMERIC 存储类。即使它是有损的和不可逆的，转换都会通过。数学运算操作符中的一个 NULL 操作数，产生一个 NULL 结果。数学运算操作符中的一个操作数看起来不像任何数字或不是 NULL，它将被转换为 0 或 0.0。

5.0 排序、分组和复合的 SELECTs

当查询结果被 ORDER BY 子句进行排序，存储类 NULL 的值将被置顶，其后是穿插的以数字为序 INTEGER 和 REAL 值，再后是以序列进行排序的 TEXT 值，最后是 memcmp() 顺序的 BLOB 值。在排序之前不会发生任何存储类的转换。

当使用 GROUP BY 子句对值进行分组时，不同存储类的值被认为是不同的，除了被认为是相等的 INTEGER 和 REAL 值，如果它们在数值上是相等的话。没有亲和类型会被应用到 GROUP BY 子句的结果中。

复合 SELECT 操作符 UNION、INTERSECT 和 EXCEPT 执行内部的值比较。没有亲和类型被应用到与 UNION、INTERSECT 或 EXCEPT 关联的内部比较操作数上 - 值被直接进行比较。