详解SQLite中的数据类型-木庄网络博客

本文整理自网络，侵删。

大多数 SQL 数据库引擎（据我们所知，除 SQLite 之外的所有 SQL 数据库引擎）都使用严格的静态类型。使用静态类型，值的类型便由它的容器 -- 存储值的特定的列 -- 来决定。

SQLite 使用更通用的动态类型系统。在 SQLit 中，值的数据类型与值本身相关，而不是与它的容器。SQLite 的动态类型系统与其它数据库引擎的常用静态类型系统是向后兼容的，在这个意义上，工作在静态类型数据库上的 SQL 语句应该以同样的方式工作在 SQLite 中。然而，SQLite 中的动态类型允许它做传统的严格类型的数据库所不能做的事。

1.0 存储类型与数据类型

存储在 SQLite 数据库中的每个值（或是由数据库引擎所操作的值）都有一个以下的存储类型：

NULL. 值是空值。
INTEGER. 值是有符号整数，根据值的大小以1，2，3，4，6 或8字节存储。
REAL. 值是浮点数，以8字节 IEEE 浮点数存储。
TEXT. 值是文本字符串，使用数据库编码（UTF-8, UTF-16BE 或 UTF-16LE）进行存储。
BLOB. 值是一个数据块，按它的输入原样存储。

注意，存储类型比数据类型更笼统。以 INTEGER 存储类型为例，它包括6种不同的长度不等的整数类型，这在磁盘上是不同的。但是只要 INTEGER 值从磁盘读取到内存进行处理，它们就被转换为更为一般的数据类型（8字节有符号整型）。因此在一般情况下，“存储类型” 与 “数据类型” 没什么差别，这两个术语可以互换使用。

SQLite 版本3数据库中的任何列，除了整型主键列，都可用于存储任何存储类型的值。

SQL 语句中的任何值，无论它们是嵌入到 SQL 语句中的字面量还是绑定到预编译 SQL 语句中的参数，都有一个隐含的存储类型。在下述情况下，数据库引擎会在执行查询时在数值存储类型（INTEGER 和 REAL）和 TEXT 之间进行转换。

1.1 布尔类型

SQLite 并没有单独的布尔存储类型，而是将布尔值存储为整数 0 (false) 和 1 (true)。
1.2 日期和时间类型

SQLite 没有另外的存储类型来存储日期和时间。SQLite 的内置的日期和时间函数能够将日期和时间存为 TEXT、REAL 或 INTEGER 值：

TEXT ISO8601 字符串 ("YYYY-MM-DD HH:MM:SS.SSS")。
REAL 儒略日数 (Julian Day Numbers)，按照前公历，自格林威治时间公元前4714年11月24日中午以来的天数。
INTEGER Unix 时间，自 1970-01-01 00:00:00 UTC 以来的秒数。

应用可以选择这些格式中的任一种存储日期和时间，并使用内置的日期和时间函数在这些格式间自由转换。

2.0 类型亲和性

为了最大限度地提高 SQLite 和其它数据库引擎之间的兼容性，SQLite 支持列的“类型亲和性”的概念。列的类型亲和性是指数据存储于该列的推荐类型。这里重要的思想是类型是推荐的，而不是必须的。任何列仍可以存储任何类型的数据。这只是让一些列有选择性地优先使用某种存储类型。一个列的首选存储类型被称为它的“亲和性”。

每个 SQLite 3 数据库中的列都归于以下的类型亲和性中的一种：

TEXT
NUMERIC
INTEGER
REAL
NONE

一个具有 TEXT 亲和性的列使用存储类型 NULL、 TEXT 或 BLOB 存储所有数据。如果数值数据被插入到一个具有 TEXT 亲和性的列，则数据在存储前被转换为文本形式。

数值亲和性的列可能包含了使用所有五个存储类的值。当插入文本数据到数值列时，该文本的存储类型被转换成整型或实数（按优先级排序）如果这种转换是无损或可逆的的话。对于文本与实数类型之间的转换，如果前15个重要十进制数字被保留的话，SQLite认为这种转换是无损并可逆的。如果文本不能无损地转换成整型或实数，那这个值将以文本类型存储。不要试图转换NULL或BLOB值。

一个字符串可能看上去像带有小数点和/或指数符的浮点文字，但只要这个值可以用一个整型表示，数值亲和性就会把它转换成一个整型。因此，字符串‘3.0e+5'以整型300000，而不是浮点值30000.0的形式存储在一个数值亲和性的列里。

一个使用整型亲和性的列与具有数值亲和性的列表现一致。只是在CAST表达式里，它们之间的区别体现得明显。

除了强制将整型值转换成浮点表示外，一个具有实数亲和性的列与具有数值亲和性的列表现一致（作为一个内部的优化，为了少占用空间，无小数部分且存储在实数亲和性列上的小浮点值以整型形式写到磁盘，读出时自动转换回浮点值。在SQL级别，这种优化是完全不可见的，并且只能通过检查数据库文件的原始比特检测到）。

一个具有NONE亲和性的列不能从一种存储类型转换成另一种，也不要试图强制对它进行转换。

2.1 列亲和性测定

列的亲和性是由它的声明类型决定的，按照以下顺序所示的规则：

1. 如果声明类型包含字符串“INT”，那它被指定为整型亲和性；

2. 如果列的声明类型包含任何“CHAR”、“CLOB”或“TEXT”字符串，那么该列具有文本亲和性。注意：VARCHAR类型包含“CHAR”并且被指定为文本亲和性；

3. 如果列的声明类型包含“BLOB”或者没有指定类型，那这列具有NONE亲和性；

4. 如果列的声明类型包含任何“REAL”、“FLOA”或“DOUB”字符串，则该列具有实数亲和性；

5. 否则，它将具有数值亲和性。

注意：判定列亲和性规则的顺序是很重要的。一个具有“CHARINT”声明类型的列将匹配规则1和2，但是规则1优先所有该列具有整型亲和性。

2.2 亲和性名字实例

下表显示了有多少从更传统的SQL实现的常用数据类型名，通过上一节介绍的五个规则被转换成各种亲和性类型。这张表只显示了SQLite可接受的一小部分数据类型名。注意：跟在类型名后，括号内数值参数（如：VARCHAR（255））将被SQLite忽略 - SQLite不对字符串、BLOBs或数值的长度强加任何限制（除了大型全局SQLITE_MAX_LENGTH限制）。

注意：因为在“POINT”末尾的“INT”，一个“ FLOATING POINT”声明类型会被赋予整型亲和性，而不是实数亲和性。而且“STRING”声明类型具有数值亲和性，而不是文本亲和性。

2.3 列亲和性行为实例

以下SQL演示当有值插入到一张表时，SQLite如何使用列亲和性实现类型转换的：

CREATE TABLE t1(
  t TEXT,   -- text affinity by rule 2
  nu NUMERIC, -- numeric affinity by rule 5
  i INTEGER, -- integer affinity by rule 1
  r REAL,   -- real affinity by rule 4
  no BLOB   -- no affinity by rule 3
);

-- Values stored as 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
 
-- Values stored as 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
 
-- Values stored as 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 are always stored as BLOBs regardless of column affinity. 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 are also unaffected by affinity
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 比较表达式

同标准SQL一样，SQLite 3支持如下的比较操作符："=", "==", "<", "<=", ">", ">=", "!=", "<>", "IN", "NOT IN", "BETWEEN", "IS", 以及 "IS NOT"。
3.1 排序规则

比较的结果与操作数的存储类型有关，同时依据以下的规则：

NULL值小于其他任何值（包括另外一个NULL）
INTEGER或REAL小于TEXT，BLOB值；若两个INTEGER(或者REAL)比较，则按照实际的数值进行。
TEXT小于BLOB，若两个TEXT比较，结果则由适当的整理顺序决定
若两个BLOD比较，与memcmp()的结果一致

3.2 操作数进行比较时的相似性

在进行值的比较之前，SQLite会尝试在存储类INTEGER、REAL和/或TEXT之间进行值的转换。在比较之前尝不尝试进行转换完全取决于操作数的相似性。操作数相似性的判定规则如下：

只是对一个列中的值进行引用的表达式同被引用的列具有完全相同的相似性。注意，如果X、Y.Z代表的是列的名称，那么+X和+Y.Z可以认为是为了判定其相似性的表达式。
"CAST(expr AS type)"所表示的表达式同类型定义为"type"的列具有完全相同的相似性。
其它情况下的表达式具有NONE相似性。

3.3 比较前的类型转换

“应用相似性”（"apply affinity"）的意思是，当且仅当所涉及的转换是无损且可逆的情况下，将一个操作数转换为某特定的存储类型。在进行比较之前对比较运算符的操作数应用相似性的规则如下按顺序所示：

如果其中的一个操作数具有INTEGER、REAL或者NUMERIC相似性而另外一个操作数具有TEXT或者NONE相似性，那么就要对这另外一个操作数应用NUMERIC 相似性。
如果其中的一个操作数具有TEXT相似性而另外一个具有NONE相似性，那么就要对这另外一个操作数应用TEXT相似性。
其它情况下不会应用任何相似性，两个操作数按照各自的原样进行比较。

将表达式"a BETWEEN b AND c"看作两个单独的二元比较运算"a >= b AND a <= c"，即使这么一来，可能会造成其中的a在两次比较中会被应用不同的相似性，也要这么处理。Datatype conversions in comparisons of the form 在"x IN (SELECT y ...)"这种形式的比较中，数据类型的转换完全同"x=y"一样进行处理。表达式"a IN (x, y, z, ...)" 同"a = +x OR a = +y OR a = +z OR ..."等价。换句话说，IN运算符右侧的值(本例中就是"x", "y", and "z")被看作是无相似性的，即使它们凑巧是某列的值或者是CAST表达式。

阅读剩余部分