SQLite3 数据类型与亲和类型

本文主要是从SQLite3 数据类型与其特有的亲和类型的定义出发，解释了二者之间的联系与区别。

1. SQLite 中的数据类型

大多数SQL数据库引擎（除了SQLite以外的所有SQL数据库）使用的是静态的，刚性的类型。由此，使用静态的、刚性的数据库，气存储值的类型就由容器决定，只能存储特定类型的列数据。

SQLite应用一种更加通用的动态的类型系统，在SQLite中，数据类型和值本身有关，而不是与容器类型相关。Sqlite的动态类型系统和其他数据库的更为一般的静态类型系统相兼容（一般），但是，sqlite中的动态类型允许它能做到一些传统刚性类型数据库所不可能做到的操作。

2. 存储类与数据类型

每一个存储在SQLite数据库中的值（或者说由数据库引擎操作的）都有以下一种存储类：
 NULL，值是NULL
 INTEGER，值是有符号整形，根据值的大小以1,2,3,4,6或8字节存放
 REAL，值是浮点型值，以8字节IEEE浮点数存放
 TEXT，值是文本字符串，使用数据库编码（UTF-8，UTF-16BE或者UTF-16LE）存放
 BLOB，二进制数据类，只是一个数据块，完全按照输入存放（即没有转换）

一个存储类比一个数据类型更加的通用，一般化。比如，INTEGER存储类包含6种不同长度的integer数据类型。这使得在磁盘存储上有所差异。但是只要一个INTEGER类的值磁盘所读取，它们就会被转换成更加通用的数据类型（8进制有符号整型integer）。因此，在大多数情况下，“存储类”和“数据类型”无法区分，并且两个属于可以互相转换。

在SQLite version 3中任何一列（除了INTEGER PRIMARY KEY 整型主键值列），可以存储任何存储类型的值。[注：意思就是说，除了主键列的类型不能修改以外，其他列的属性都是可改的]

在SQL语句中的所有值，无论是嵌入在SQL语句文本中的文字还是绑定到预编译SQL语句的参数（保留字），都具有隐式存储类。 在下面描述的情况下，数据库引擎可以在查询执行期间，可以在数值存储类（INTEGER和REAL）和TEXT之间相互转换。

2.1 Boolean Datatype 布尔类型

SQLite 没有单独的存储布尔值得存储类型。而是把false存储为0，true存储为1。也就是用INTEGER来存储，表达布尔类型。

2.2 Date and Time Datatype日期和时间类型

SQLite没有为存储日期或时间而预留的存储类。

SQLite的内置Date And Time Functions(日期和时间函数)能够将日期和时间存储为TEXT，REAL或INTEGER值：
 TEXT类：如ISO8601的字符串(“YYYY-MM-DD HH:MM:SS.SSS”).
 REAL类：儒略日。是从格林威治标准时间的中午开始，包含一个整天的时间，起点的时间（0日）回溯至儒略历的公元前4713年1月1日中午12点（在格里历是公元前4714年11月24日），这个日期是三种多年周期的共同起点，且是历史上最接近现代的一个起点。例如，2000年1月1日的UT12:00是儒略日2,451,545。
 INTEGER类：如UNIX系统时间，时间戳。从1970-01-01 00:00:00 UTC开始到某一个时刻的总计秒数。

应用的时候可以使用内置的函数date and time functions自由的转换这几者类型。

3. 亲和类型

使用刚性类型的SQL数据库引擎通常会自动地将存储值转换为适当的类型。考虑下面的情况：

CREATE TABLE t1(a INT, b VARCHAR(10));
INSERT INTO t1(a,b) VALUES('123',456);

刚性类型的数据库会先将字符串“123”转换成整型123，将整型456转换成字符串“456”，然后再去做值插入。

为了最大化SQLite和其他SQL数据库引擎之间的兼容性，以便上面的例子可以在SQLite中像其他SQL数据库中一样运行，SQLite支持了一种列的“亲和类型”的概念。

列的亲和类型是指该列数据的推荐类型。一个值得注意点的是，这个类型只是推荐的，而不是必须的。任何列可以存储任何类型的数据。只是说根据选择，某些列更倾向于使用一个存储类型而不是另一个存储类型。 列的首选存储类型称为“亲和性”。

SQLite 3 数据库中的每一列都会被分配为以下“亲和类型”的一种：
 TEXT
 NUMERIC
 INTEGER
 REAL
 BLOB
(注记：“BLOB”亲和类型以前通常被叫做：“NONE”，但是这个术语容易和“NO affinity”没有亲和类型相混淆，所以这里被重新命名了。)

具有TEXT亲和类型的列可以用NULL，TEXT或者BLOB类型存储数据。如果数值数据被插入到具有TEXT近似的列，在被存储前被转换为文本形式。

具有NUMERIC亲和类型的列可以使用5种存储类型来存储值。当一个文本数据插入到NUMERIC列中时，文本数据的存储类将会被转换成INTEGER或者REAL（根据优先级），如果这个转换是无损的、可反转的话（对于TEXT和REAL存储类间的转换，如果数据的前15位的被保留的话SQLite就认为这个转换是无损的、可反转的）。如果TEXT到INTEGER或REAL的转换不可避免的会造成损失，那么数据将使用TEXT存储类存储。不要试图去转换NULL或BLOB值。

字符串可能看起来像带有小数点和/或指数表示法的浮点字面值，但只要该值可以表示为整数，NUMERIC亲和关系就会将其转换为整数。 因此，字符串’3.0e + 5’存储在具有NUMERIC亲和度作为整数300000的列中，而不是作为浮点值300000.0。

具有INTEGER亲和类型的列和具有NUMERIC亲和类型的列表现相同。它们之间的差别仅处于CAST expression转换描述上。

具有REAL亲和性的列的行为类似于具有NUMERIC亲和力的列，除了一个特点：它将整数值强制为浮点表示形式。（作为内部优化，没有小数组件并存储在具有REAL亲和的列中的浮点值将作为整数写入磁盘，以便占用更少的空间，并在读取值时自动转换回浮点。 优化在SQL级别完全不可见，只能通过检查数据库文件的原始位来检测。）

具有BLOB亲和类型的列不会优先选择一个存储类，也不会强制将数据从一个存储类转换到另外一个类。

3.1 列的亲和性的决定因素

列的亲和类型由列的声明类型决定，根据以下的规则：

1. 如果声明的类型中包含字符串“INT”，那么就会被赋予INTEGER亲和类型
2. 如果声明的类型中包含“CHAR”、“CLOB”，或者“TEXT”，那么这个列就会被赋予TEXT亲和类型。比如类型VARCHAR包含了“CHAR”字符串，那么它就被赋予了TEXT亲和类型。
3. 如果声明类型中包含了字符串“BLOB”，或者没有为其声明类型，这个列被赋予亲和类型BLOB。
4. 如果声明的类型中包含了字符串“REAL”、“FLOA”、或者“DOUB”，那么该列就会被赋予REAL亲和类型
5. 其他的情况，列被赋予NUMERIC亲和类型
   注意：确定列关联性的规则的顺序很重要。声明类型为“CHARINT”的列将匹配规则1和2，但第一个规则优先，因此列关联性将为INTEGER。

3.2 亲和类型名称示例

下表显示了通过上一节的五个规则将来自传统的SQL实现的常用数据类型名称转换为亲和类型的示例。
此表仅显示一小部分数据类型名称，这些类型都是可以在SQLite接受转换的。
请注意，SQLite会忽略括号中的数字参数（例如：“VARCHAR（255）”， SQLite不会对字符串，BLOB或字符串、数值长度施加任何长度限制（但是存在全局限制：SQLITE_MAX_LENGTH）

注意：
由于“POINT”末尾的“INT”，声明的“FLOATING POINT”类型将赋予INTEGER亲和力，而不是REAL亲和力。 声明的“STRING”类型具有NUMERIC的亲和力，而不是TEXT。

3.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

4. 一些注解

Q: 第二节存储类与数据类型的数据类型中提到了数据类型，第三节亲和类型中提到了数据亲和类型，这二者到底哪一个才是存储声明的类型？这二者之间的连续与区别是什么？

A: 第二节 存储类与数据类型的数据类型，应该是从单个数据的表现形式上去描述的，说明这个值就是空值（NULL）,整型（INTEGER）,浮点值（REAL），字符串（TEXT），二进制（BLOB）。

第三节 亲和类型 中提到的亲和类型应该是列的属性。它包含两重意思，一是说可以按照一些规则进行“亲和转换”（字符串中包含某个关键字就行，不一定非得是准确的类型，它会做一些“识别转换”，就如前面提到的“CHARINT”类型）。第二个就是说，这个亲和类型是最终存储在数据库列的推荐类型，一个列类型里，可以存储多个数据，这一列中的数据可以有多种表现形式，比如TEXT亲和类型的列的数据，可以是NULL，可以是TEXT,可以是BLOB。

当然，根据上面描述的规则，这个亲和类型只是一个推荐值，而不是必须值，你可以令一列的类型为VARCHAR(255)（虽然最终在SQLite中会被存为TEXT，这二者在最终存储的结果上没有任何差别）。

实际上，只需要知道这一点就行：我们在创建表时，使用的时第三章节的类型，而非第二章节的类型。