详解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 (fal) 和 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表达式。
3.4 ⽐较⽰例
CREATE TABLE t1(
a TEXT, -- text affinity
b NUMERIC, -- numeri
c affinity
c BLOB, -- no affinity
d -- no affinity
);
-- Values will be stored as TEXT, INTEGER, TEXT, and INTEGER respectively
INSERT INTO t1 VALUES('500', '500', '500', 500);
SELECT typeof(a), typeof(b), typeof(c), typeof(d) FROM t1;
text|integer|text|integer
-- Becau column "a" has text affinity, numeric values on the
-- right-hand side of the comparisons are converted to text before
-- the comparison occurs.
SELECT a < 40, a < 60, a < 600 FROM t1;
0|1|1
-
- Text affinity is applied to the right-hand operands but since
-- they are already TEXT this is a no-op; no conversions occur.
SELECT a < '40', a < '60', a < '600' FROM t1;
0|1|1
-- Column "b" has numeric affinity and so numeric affinity is appliedopp袋是什么
-- to the operands on the right. Since the operands are already numeric,
-- the application of affinity is a no-op; no conversions occur. All
-- values are compared numerically.
SELECT b < 40, b < 60, b < 600 FROM t1;
0|0|1
-- Numeric affinity is applied to operands on the right, converting them
-
- from text to integers. Then a numeric comparison occurs.
SELECT b < '40', b < '60', b < '600' FROM t1;
0|0|1
-- No affinity conversions occur. Right-hand side values all have
-- storage class INTEGER which are always less than the TEXT values
-- on the left.
SELECT c < 40, c < 60, c < 600 FROM t1;
0|0|0
-- No affinity conversions occur. Values are compared as TEXT.
SELECT c < '40', c < '60', c < '600' FROM t1;
0|1|1
-
- No affinity conversions occur. Right-hand side values all have
-- storage class INTEGER which compare numerically with the INTEGER
-- values on the left.
SELECT d < 40, d < 60, d < 600 FROM t1;
0|0|1
-- No affinity conversions occur. INTEGER values on the left are
-- always less than TEXT values on the right.
SELECT d < '40', d < '60', d < '600' FROM t1;
1|1|1
若⽰例中的⽐较被替换——例如"a<40"被写作"40>a"——所有的结果依然相同相同。
4.0 操作符
所有的数学运算符(+, -, *, /, %, <<, >>, &, and |)在展开前会将两个操作数放⼊ NUMERIC 储存类。即使这个过程是有损和不可逆转的。⼀个 NULL 操作数在数学运算符上产⽣⼀个 NULL 结果。在数算运算符上的操作数不被视为数字，NULL 并不会被转为0或0.0。
5.0 排序, 分组和组合查询
当查询结果使⽤ ORDER BY ⼦句排序时, 存储类型的NULL空值是排在第⼀位的, 其次是INTEGER和散布在数字顺序的REAL 数据, 其次是按照核对序列顺序的TEXT值, 最后为memcmp() order 的BLOB值. 排序之前不会出现任何存储类型转换.
当使⽤GROUP BY ⼦句分组时不同类型的值被认为是不同的数据, 除了INTEGER 和 REAL 值如果他们数值相等则被认为是相同的的数据. 没有任何亲和性适⽤于GROUP BY ⼦句结果的任意值.
组合查询使⽤ UNION, INTERSECT 和 EXCEPT 在数据之间执⾏隐式的⽐较. 没有任何亲和性适⽤于与UNION, INTERSECT，或者 EXCEPT关联的隐式⽐较的运算数 - 数据的⽐较就像这样.
6.0 整理序列
当 SQLite ⽐较两个字符串时，它使⽤⼀个整理序列或整理函数（⼀物两表）来决定当两个字符串相同时，哪个字符串值更⾼。SQLite 拥有三个内建整理函数：BINARY, NOCASE, 和 RTRIM。
BINARY - 使⽤ memcmp() ⽐较字符串，⽆视⽂本编码。
NOCASE - 与⼆进制⽐较相同，除了 ASCII 的26个⼤写字母在⽐较前将会转为其⼩写形势。注意，只有 ASCII 字符会⼤⼩写转化。由于表⼤⼩的需求，SQLite 并不会尝试 UTF ⼤⼩写转化。
RTRIM - 与⼆进制⽐较相同，除了尾部空格符将被忽略。
应⽤可以通过 sqlite3_create_collation() 接⼝注册额外的整理函数。
6.1 设定SQL中的排列顺序
每个表中的每⼀个列都具有⼀个相关的排序函数。如果没有显式地定义排序函数，那么，就会缺省使⽤BINARY作为排序函数。列定义中的COLLATE⼦句可为列定义⼀个可选的排序函数。
对于⼆元⽐较运算符(=, <, >, <=, >=, !=, IS, and IS NOT)来说，判定到底使⽤哪个排序函数的规则按顺序如下所列：
如果两个操作数中有任意⼀个操作数具有使⽤后缀COLLATE运算符显式定义的排序函数，那么就会⽤该函数进⾏⽐较，如果两个操作数都有的情况下，优先使⽤左操作数的排序函数。
如果两个操作数中任意⼀个操作数是⼀个列，那么就会使⽤该列的排序函数进⾏⽐较，但在两个操作数都是列的情况下，优先使⽤左操作数对应的列的排序函数。为了达到这句话的⽬的，列名前带有1个或多个⼀元运算符"+"的，仍然按原列名处理。
其它情况下，采⽤BINARY排序函数进⾏⽐较。
⽐较运算中的操作数，如果在它的任何⼦表达式中使⽤了后缀 COLLATE运算符，就可以认为是具有显式的排序函数（上⽂中的规则1）。再者，如果在⽐较表达式中的任何地⽅使⽤了 COLLATE运算符，那么该运算符所定义的排序函数就会⽤于字符串的⽐较，⽽⽆论在表达式中出现了表中的哪⼀列。如果在⽐较中的任何地⽅出现了两个或多个 COLLATE运算符⼦表达式，⽆论在表达式中嵌⼊得多深，也⽆论表达式是怎么使⽤括号的，都会使⽤出现在最左侧的显式排序函数。
表达式"x BETWEEN y and z"从逻辑上讲，同"x >= y AND x <= z"这两个⽐较运算完全等价，在使⽤排序函数时它们俩要象两个本来就是独⽴的⽐较运算⼀样进⾏处理。在判定排列顺序时，表达式"x IN (SELECT y ...)"处理⽅式完全同表达式"x = y"⼀样，形如"x IN (y, z, ...)"的表达式，排列顺序完全同X的排列顺序⼀样。
作为 SELECT语句的⼀个部分，ORDER BY⼦句中排序条件也可以通过使⽤COLLATE运算符设定排列顺序，如果设定了排序时就要按照设定的排序函数进⾏排序。否则，如果ORDER BY⼦句使⽤的排
序表达式是⼀个列，那么该列的排列顺序就⽤于判定排列顺序。如果该排序表达式不是列并且也⽆COLLATE⼦句，就会使⽤BINARY排列顺序。
6.2 整理序列⽰例
下⾯的⽰例将识别整理序列，决定 SQL 语句的⽂本⽐较结果。注意，在⽂本⽐较时，如果是数字，⼆进制或Null值，整理序列可能并没有被使⽤。
CREATE TABLE t1(
x INTEGER PRIMARY KEY,
a, /* collating quence BINARY */
b COLLATE BINARY, /* collating quence BINARY */
c COLLATE RTRIM, /* collating quence RTRIM */卤大肠
d COLLATE NOCASE /* collating quenc
e 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');
/* a=b 的⽂本⽐较表现为使⽤ BINARY （⼆进制）整理序列。 */
SELECT x FROM t1 WHERE a = b ORDER BY x;
--结果 1 2 3
/* a=b 的⽂本⽐较表现为使⽤ RTRIM 整理序列。 */
SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;
-
-结果 1 2 3 4
/* d=a 的⽂本⽐较表现为使⽤ NOCASE 整理序列。 */
SELECT x FROM t1 WHERE d = a ORDER BY x;
--结果 1 2 3 4
/* a=d 的⽂本⽐较表现为使⽤ BINARY （⼆进制）整理序列。 */
SELECT x FROM t1 WHERE a = d ORDER BY x;
--结果 1 4
/* 'abc'=c 的⽂本⽐较表现为使⽤ RTRIM （⼆进制）整理序列。 */
SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
--结果 1 2 3
/* c='abc' 的⽂本⽐较表现为使⽤ RTRIM 整理序列。 */
SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;
--结果 1 2 3
