原作者：
来源Datatypes In SQLite Version 3
译者边之龙
大多数 SQL 数据库引擎（据我们所知的、SQLite 以外的所有 SQL 数据库引擎）使用静态的、严格的类型。对于静态类型，数据类型的值由它的容器（存储值的特定列）来决定。
SQLite 使用的是一个更加普通的动态类型系统。在 SQLite 中，数据类型的值是与值的本身有关联，而不是它的容器。SQLite 的动态类型系统对于更多其它数据库引擎常见的静态类型系统是向后兼容的，在这个意义上，工作在静态类型数据库上的 SQL 语句同样也能工作在 SQLite 中。然而，SQLite 中的动态类型允许它做一些在传统严格类型数据库中不能做的事情。
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 级别是完全不可见的，并且只有通过研究数据库文件的原始数据才能被检测。）
一个带有 NULL 亲和类型的列不建议将一个存储类覆盖到另一个存储类，并且不要尝试强制将数据从一个存储类变为另一个存储类。
2.1 亲和列的测定
一个列的亲和类型是由列的声明类型决定的，根据下面展示的有顺序的规则：
- 如果类型声明包含字符串"INT"，它将被分配为 INTEGER 亲和类型。
- 如果列的类型声明包含字符串"CHAR"、"CLOB"或"TEXT"的任意之一，列将拥有 TEXT 亲和类型。
- 如果一个列的类型声明包含字符串"BLOB"，或者如果没有类型被指定，列将拥有 NONE 亲和类型。
- 如果一个类的类型声明包含字符串"REAL"、"FLOA"或"DOUB"的任意之一，列将拥有 REAL 亲和类型。
- 否则，亲和类型为 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 关联的内部比较操作数上 - 值被直接进行比较。
6.0 校对序列
当 SQLite 比较两个字符串时，如果两个字符串相等，它使用一个校对序列或校对函数来决定哪个字符串更大一些。SQLite 有 3 个内置的校对函数：BINARY、NOCASE 和 RTRIM。
- BINARY - 使用 memcmp() 对字符串数据进行比较，无论文本编码。
- NOCASE - 与 BINARY 相同，除了在比较执行之前会将 ASCII 码的 26 个大写字母被折换为与其等值的小写字母。注意，只有 ASCII 码的字符进行大小写折换。由于所需表的大小，SQLite 不会尝试对完整 UTF 的大小写进行折换。
- RTRIM - 与 BINARY 相同，除了尾随的空格将被忽略。
使用 sqlite3_create_collation() 接口，一个应用程序可以注册附加的校对函数。
6.1 从 SQL 分配校对序列
每个表的每个列都有一个相关联的校对函数。如果没有校对函数被明确定义，校对规则默认为 BINARY。列定义的 COLLATE 子句被用来替代地为一个列定义校对函数。
为一个二元比较操作符（=、<、>、<=、>=、!=、IS 和 IS NOT）决定使用何种校对函数，按照下面展示的规则顺序进行：
- 如果两个操作数都使用 COLLATE 后缀操作符分配了一个明确的校对函数，那么明确的校对函数被用在比较中，左边操作数的校对函数有较高的优先级。
- 如果两个操作数都是一个列，左边列的校对函数有较高的优先级。前一句的目的是，带有一个或多个一元"+"操作符的列名仍被认为是一个列名。
- 否则，比较将会使用 BINARY 校对函数。
表达式"x BETWEEN y AND z"在逻辑上等价于两个比较"x >= y AND x <= z"，并且与校对函数一起工作，就像这是有两个分开的比较一样。表达式"x IN (SELECT y ...)"与"x = y"的处理方式一样，目的是为了确定校对序列。用在"x IN (y, z, ...)"格式表达式上的校对序列，就是 x 的校对序列。
ORDER BY 子句是一个 SELECT 语句的一部分，其规则是，可使用 COLLATE 操作符为 SELECT 语句分配一个校对序列，在这种情况下，特定的校对函数被用于排序。否则，如果通过 ORDER BY 子句进行排序的表达式是一个列，列的校对序列将被用来确定排序顺序。如果表达式不是一个列并且没有 COLLATE 子句，则 BINARY 校对序列将被使用。
6.2 校对序列示例
下面的例子用来辨认校对序列，这可被用来确定由各 SQL 语句执行的文本比较的结果。注意，在 NUMERIC、BLOB 或 NULL 值的情况，一个文本的比较并不是必须的，并且不会使用校对序列。
CREATE TABLE t1(
x INTEGER PRIMARY KEY,
a, /* 校对序列 BINARY */
b COLLATE BINARY, /* 校对序列 BINARY */
c COLLATE RTRIM, /* 校对序列 RTRIM */
d COLLATE NOCASE /* 校对序列 NOCASE */
);
/* x a b c d */
INSERT INTO t1 VALUES(1,'abc','abc', 'abc ','abc');
INSERT INTO t1 VALUES(2,'abc','abc', 'abc', 'ABC');
INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');
INSERT INTO t1 VALUES(4,'abc','abc ','ABC', 'abc');
/* 使用 BINARY 校对序列执行文本比较 a = b。 */
SELECT x FROM t1 WHERE a = b ORDER BY x;
-- 结果 1 2 3
/* 使用 RTRIM 校对序列执行文本比较 a = b。 */
SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;
-- 结果 1 2 3 4
/* 使用 NOCASE 校对序列执行文本比较 d = a。 */
SELECT x FROM t1 WHERE d = a ORDER BY x;
-- 结果 1 2 3 4
/* 使用 BINARY 校对序列执行文本比较 a = d。 */
SELECT x FROM t1 WHERE a = d ORDER BY x;
-- 结果 1 4
/* 使用 RTRIM 校对序列执行文本比较 'abc' = c。 */
SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
-- 结果 1 2 3
/* 使用 RTRIM 校对序列执行文本比较 c = 'abc'。 */
SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;
-- 结果 1 2 3
/* 使用 NOCASE 校对序列进行分组（值"abc"、"ABC"和"Abc"被放在相同的分组中）。 */
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
-- 结果 4
/* 使用 BINARY 校对序列执行分组。"abc"、"ABC"和"Abc"来自不同的分组。 */
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
-- 结果 1 1 2
/* 使用 RTRIM 校对序列或列 c 执行排序。 */
SELECT x FROM t1 ORDER BY c, x;
-- 结果 4 1 2 3
/* 使用 BINARY 校对序列或 (c || '') 执行排序。 */
SELECT x FROM t1 ORDER BY (c||''), x;
-- 结果 4 2 3 1
/* 使用 NOCASE 校对序列或列 c 执行排序。 */
SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;
-- 结果 2 4 3 1
添加新评论
相关文章：
SQLite 对比其它数据库引擎的 NULL 处理
SQLite 没有实现的 SQL 特征
