SQLite3数据类型与亲和类型
SQLite3 数据类型与亲和类型帆组词
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 没有单独的存储布尔值得存储类型。⽽是把fal存储为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，这⼆者在最终存储的结果上没有任何差别）。
实际上，只需要知道这⼀点就⾏：我们在创建表时，使⽤的时第三章节的类型，⽽⾮第⼆章节的类型。
