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 亲和列的测定
一个列的亲和类型是由列的声明类型决定的，根据下面展示的有顺序的规则：
- 如果类型声明包含字符串“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 在值被插入到一个表时，如何使用列的亲和类型来进行类型转换。
t TEXT -- 规则 2 的文本亲和类型
nu NUMERIC -- 规则 5 的数字亲和类型
i INTEGER -- 规则 1 的整数亲和类型
r REAL -- 规则 4 的浮点数亲和类型
no BLOB -- 规则 3 的无亲和类型
);
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
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
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
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
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 比较示例
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 关联的内部比较操作数上 - 值被直接进行比较。
扫一扫
2993
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
