sqlite3 的数据类型

最近重构 https://github.com/issue9/orm，又重新梳理了一遍数据库的相关知识，发现 sqlite3 在数据处理方面与其它数据库还是有很大的不同的，比如它的长度信息是没有用的，varchar(1) 在实际处理过程中，括号中以括号中的内容将被忽略，可以插入任意长度的内容；对数据类型的处理上也有很大地不同。

存储类型

sqlite3 不像其它数据库一样，数据类型由列限定死了，无法插入不同的数据类型，在 sqlite3 中的数据类型是动态的，在插入时根据插入的值决定其实际的存储类型。比如：

 1create table test (
 2    col1 int,
 3    col2 varchar(1),
 4    col3 decimal(8,4)
 5);
 6
 7insert into test (col1, col2, col3) values("abc", "abc", "abc"),(1, 2, 3),(NULL, NULL, NULL), (1.1, 2.2, 3.3);
 8
 9select typeof(col1), typeof(col2), typeof(col3) from test;
10
11-- 输出以下内容
12-- "typeof(col1)"  "typeof(col2)"  "typeof(col3)"
13-- text            text            text
14-- integer         text            integer
15-- null            null            null
16-- real            text            real

这里就可以看出，每一行数据的类型，根据实际存储的值进行了调整。目前支持以下几种类型：

亲和类型

存储类型表示的是实际存储时的类型，而亲和类型则是 sqlite3 中实际支持的类型，所有写入 sqlite3 的数据，都按规则转换成亲和类型。比如上面示例中的 varchar(1) 其亲和类型为 TEXT，最终会被以字符串的形式存储，目前亲和类型和判断规则如下：

1. INTEGER：类型名中包含 INT；
2. TEXT：类型名中包含 TEXT、CHAR 或 CLOB，比如 varchar(1) 包含了 CHAR，会被判断为 TEXT；
3. BLOB：类型名中包含 BLOB 或是未指定类型名；
4. REAL：类型名中包含 REAL、FLOA 或 DOUB；
5. NUMERIC 其它情况都属于 NUMERIC；

以上规则的判断不区分大小写。如果存在同时匹配多个规则，则按顺序判断，比如 VARCHARINT，同时与 TEXT 和 INTEGER 匹配，优先匹配规则 1，即亲和类型为 INTEGER；长度信息会被忽略，varchar(1) 和 decimal(8,2) 可以是任意长度的 TEXT 和 NUMERIC。

那么我们应该用 varchar 还是直接使用 text 呢？个人认为，如果你的代码仅限于 sqlite3，可以直接使用 text 这样的亲和类型，如果要同时兼容多个数据库的，则只能采用 varchar 等标准的 SQL 类型名称。

对照表

需求注意的是：FLOATING POINT 同时匹配 REAL 和 INTEGER，按优先级规则匹配给了 INTEGER，而 STRING 看起来像是字符数据，便是与 1-4 号规则都不匹配，而按照 5 号规则匹配了 NUMERIC 类型。

比较

排序

排序比较操作是基于两个数据的存储类型，按以下规则：

1. 存储类型为 NULL 类型的值，小于任意其它值，包括另一个 NULL 存储类型；
2. 存储类型为 INTEGER 和 REAL 的数据小于 TEXT 和 BLOB 存储类型的数据，如果同为 INTEGER 和 REAL，则采用数值规则进行比较；
3. 存储类型为 TEXT 的小于存储类型为 BLOB，两个 TEXT 比较，采用文本字符串规则比较；
4. 两个存储类型为 BLOB 进行比较，则采用 c 语言的 memcmp() 函数进行比较；

比较前的类型转换

Sqlite3 在比较之前，会在存储类型为 INTEGER、REAL 和 TEXT 之间进行转换，是否转换取决于操作数的亲和类型。