sqlite字段类型
存储类(Storage Class)
如前⽂所述，SQLite在处理数据类型时与其它的数据库不同。区别在于它所⽀持的类型以及这些类型是如何存储、⽐较、强化(enforc)和指派(assign)。下⾯各节介绍SQLite处理数据类型的独特⽅法和它与域完整性的关系。
对于数据类型，SQLite的域完整性被称为域亲和性(affinity)更合适。在SQLite中，它被称为类型亲和性(type affinity)。为了理解类型亲和性，你必须先要理解存储类和弱类型(manifesttyping)。
SQLite有5个原始的数据类型，被称为存储类。存储类这个词表明了⼀个值在磁盘上存储的格式，其实就是类型或数据类型的同义词。这5个存储类在表4-6中描述。
表 4-6 SQLite存储类
名称说明
INTEGER整数值是全数字(包括正和负)。整数可以是1, 2, 3, 4, 6或 8字节。整数的最⼤范围(8 bytes)是{-9223372036854775808, 0, +9223372036854775807}。SQLite根据数字的值⾃动控制整数所占的字节数。
空注：参可变长整数的概念。
REAL实数是10进制的数值。SQLite使⽤8字节的符点数来存储实数。
写夕阳的作文
TEXT⽂本(TEXT)是字符数据。SQLite⽀持⼏种字符编码，包括UTF-8和UTF-16。字符串的⼤⼩没有限制。
BLOB⼆进制⼤对象(BLOB)是任意类型的数据。BLOB的⼤⼩没有限制。
追求正义NULL NULL表⽰没有值。SQLite具有对NULL的完全⽀持。
SQLite通过值的表⽰法来判断其类型，下⾯就是SQLite的推理⽅法：
l SQL语句中⽤单引号或双引号括起来的⽂字被指派为TEXT。
l 如果⽂字是未⽤引号括起来的数据，并且没有⼩数点和指数，被指派为INTEGER。
l 如果⽂字是未⽤引号括起来的数据，并且带有⼩数点或指数，被指派为REAL。
l ⽤NULL说明的值被指派为NULL存储类。
l 如果⼀个值的格式为X'ABCD'，其中ABCD为16进制数字，则该值被指派为BLOB。X前缀⼤⼩写皆可。
SQL函数typeof()根据值的表⽰法返回其存储类。使⽤这个函数，下⾯SQL语句返回的结果为：
sqlite> lect typeof(3.14),typeof('3.14'), typeof(314), typeof(x'3142'), typeof(NULL);
typeof(3.14) typeof('3.14') typeof(314) typeof(x'3142') typeof(NULL)
real text integer blob null
SQLite单独的⼀个字段可能包含不同存储类的值。请看下⾯的⽰例：洁面摩丝
sqlite> DROP TABLE domain;
sqlite> CREATE TABLE domain(x);
sqlite> INSERT INTO domain VALUES(3.142);
sqlite> INSERT INTO domain VALUES('3.142');
sqlite> INSERT INTO domain VALUES(3142);
sqlite> INSERT INTO domain VALUES(x'3142');
sqlite> INSERT INTO domain VALUES(NULL);
sqlite> SELECT ROWID, x, typeof(x) FROMdomain;
返回结果为：
rowid x typeof(x)
1 3.14
2 real
2 3.142 text
3 3142 integer
4 1B blob
5 NULL null
这带来⼀些问题。这种字段中的值如何存储和⽐较？如何对⼀个包含了INTEGER、REAL、TEXT、BLOB和NULL值的字段排序？⼀个整数和⼀个BLOB如何⽐较？哪个更⼤？它们能相等吗？
答案是：具有不同存储类的值可以存储在同⼀个字段中。可以被排序，因为这些值可以相互⽐较。有完善定义的规则来做这件事。不同存储类的值可以通过它们各⾃类的“类值”进⾏排序，定义如下：
1. NULL存储类具有最低的类值。⼀个具有NULL存储类的值⽐所有其它值都⼩(包括其它具有NULL存储类的值)。在NULL值之间，没有特别的可排序值。
2. INTEGER或REAL存储类值⾼于NULL，它们的类值相等。INTEGER值和REAL值通过其数值进⾏⽐较。
3. TEXT存储类的值⽐INTEGER和REAL⾼。数值永远⽐字符串的值低。当两个TEXT值进⾏⽐较时，其值⼤⼩由“排序法”决定。
4. BLOB存储类具有最⾼的类值。具有BLOB类的值⼤于其它所有类的值。BLOB值之间在⽐较时使⽤C函数memcmp()。
所以，当SQLite对⼀个字段进⾏排序时，⾸先按存储类排序，然后再进⾏类内的排序 (NULL类内部各值不必排序) 。下⾯的SQL说明了存储类值的不同：sqlite> SELECT 3 < 3.142, 3.142 <'3.142', '3.142'
< x'3000', x'3000' < x'3001';
返回：
3 < 3.142 3.142 < '3.142' '3.142'< x'3000' x'3000' < x'3001'
1 1 1 1
弱类型(manifest typing)
SQLite使⽤弱类型。
看下⾯的表：
CREATE TABLE foo( x integer,
y text, z real );
向该表插⼊⼀条记录：
INSERT INTO foo VALUES ('1', '1', '1');
当SQLite创建这条记录时，x、y和z这3个字段中存储的是什么类型呢？答案是INTEGER, TEXT和REAL。
再看下⾯例⼦：
CREATE TABLE foo(x, y, z);
现在执⾏同样的插⼊语句：
INSERT INTO foo VALUES ('1', '1', '1');
现在，x、y和z中存储的是什么类型呢？答案是TEXT、TEXT和TEXT。
那么，是否SQLite的字段类型默认为TEXT呢？再看，还是第2个表，执⾏如下插⼊语句：
INSERT INTO foo VALUES (1, 1.0, x'10');
现在，x、y和z中存储的是什么类型呢？答案是INTEGER、REAL和BLOB。
如果你愿意，可以为SQLite的字段定义类型，这看起来跟其它数据库⼀样。但这不是必须的，你可以尽管违反类型定义。这是因为在任何情况下，SQLite都可以接受⼀个值并推断它的类型。
总之，SQLite的弱类型可表⽰为：1)字段可以有类型，2)类型可以通过值来推断。类型亲和性介绍这两个规定如何相互关联。所谓类型亲和性就是在强类型(strict typing)和动态类型(dynamic typing)之间的平衡艺术。
类型亲和性(Type Affinity)
在SQLite中，字段没有类型或域。当给⼀个字段声明了类型，该字段实际上仅仅具有了该类型的新和性。声明类型和类型亲和性是两回事。类型亲和性预定SQLite⽤什么存储类在字段中存储值。在存储⼀个给定的值时到底SQLite会在该字段中⽤什么存储类决定于值的存储类和字段亲和性的结合。我们先来介绍⼀下字段如何获得它的亲和性。
字段类型和亲和性
⾸先，每个字段都具有⼀种亲和性。共有四种亲和性：NUMERIC、INTEGER、TEXT和NONE。⼀个字段的亲和性由它预声明的类型决定。所以，当你为字段声明了类型，从根本上说是为字段指定了亲和性。SQLite按下⾯的规则为字段指派亲和性：有关太空的知识
l 默认的，⼀个字段默认的亲和性是NUMERIC。如果⼀个字段不是INTEGER、TEXT或NONE的，那它⾃动地被指派为NUMERIC亲和性。
l 如果为字段声明的类型中包含了'INT'(⽆论⼤⼩写)，该字段被指派为INTEGER亲和性。
l 如果为字段声明的类型中包含了'CHAR'、'CLOB'或'TEXT'(⽆论⼤⼩写)，该字段被指派为TEXT亲和性。如'VARCHAR'包含了'CHAR'，所以被指派为TEXT亲和性。
l 如果为字段声明的类型中包含了'BLOB'(⽆论⼤⼩写)，或者没有为该字段声明类型，该字段被指派为NONE亲和性。
注意：如果没有为字段声明类型，该字段的亲和性为NONE，在这种情况下，所有的值都将以它们本⾝的(或从它们的表⽰法中推断的)存储类存储。如果你暂时
还不确定要往⼀个字段⾥放什么内容，或准备将来修改，⽤NONE亲和性是⼀个好的选择。但SQLite默认的亲和性是NUMERIC。例如，如果为⼀定字段声明了类型JUJYFRUIT，该字段的亲和性不是NONE，因为SQLite不认识这种类型，会给它指派默认的NUMERIC亲和性。所以，与其⽤⼀个不认识的类型最终得到NUMERIC亲和性，还不如不为它指定类型，从⽽使它得到NONE亲和性。
亲和性和存储
亲和性对值如何存储到字段有影响，规则如下：
l ⼀个NUMERIC字段可能包括所有5种存储类。⼀个NUMERIC字段具有数字存储类的偏好(INTEGER和REAL)。当⼀个TEXT值被插⼊到⼀个NUMERIC字段，将会试图将其转化为INTEGER存储类；如果转化失败，将会试图将其转化为REAL存储类；如果还是失败，将会⽤TEXT存储类来存储。
l ⼀个INTEGER字段的处理很像NUMERIC字段。⼀个INTEGER字段会将REAL值按REAL存储类存储。也就是说，如果这个REAL值没有⼩数部分，就会被转化为INTEGER存储类。INTEGER字段将会试着将TEXT值按REAL存储；如果转化失败，将会试图将其转化为INTEGER存储类；如果还是失败，将会⽤TEXT存储类来存储。
l ⼀个TEXT字段将会把所有的INTEGER或REAL值转化为TEXT。
l ⼀个NONE字段不试图做任何类型转化。所有值按它们本⾝的存储类存储。这就是我作文500字
l 没有字段试图向NULL或BLOB值转化——如⽆论⽤什么亲和性。NULL和BLOB值永远都按本来的⽅式存储在所有字段。
这些规则初看起来⽐较复杂，但总的设计⽬标很简单，就是：如果你需要，SQLite会尽量模仿其它的关系型数据库。也就是说，如果你将SQLite看成是⼀个传统数据库，类型亲和性将会按你的期望来存储值。如果你声明了⼀个INTEGER字段，并向⾥⾯放⼀个整数，就会按整数来存储。如果你声明了⼀
个具有 TEXT, CHAR或VARCHAR类型的字段并向⾥放⼀个整数，整数将会转化为TEXT。可是，如果你不遵守这些规定，SQLite也会找到办法来存储你的值。
亲和性的运⾏
让我们看⼀些例⼦来了解亲和性是如何⼯作的：
sqlite> CREATE TABLE domain(i int, nnumeric, t text, b blob);
sqlite> INSERT INTO domain VALUES(3.142,3.142,3.142,3.142);
sqlite> INSERT INTO domain VALUES('3.142','3.142','3.142','3.142');
sqlite> INSERT INTO domain VALUES(3142,3142,3142,3142);
sqlite> INSERT INTO domain VALUES(x'3142',x'3142',x'3142',x'3142');
sqlite> INSERT INTO domain VALUES(null,null,null,null);
sqlite> SELECT ROWID,typeof(i),typeof(n),typeof(t),typeof(b)FROM domain;
返回：
rowid typeof(i) typeof(n) typeof(t) typeof(b)社会实践报告书
1 real real text real
2 real real text text
3 integer integer text integer
4 blob blob blob blob政策创新
5 null null null null
下⾯的SQL说明存储类的排序情况：
sqlite> SELECT ROWID, b, typeof(b) FROMdomain ORDER BY b;
返回：
rowid b typeof(b)
5 NULL null
1 3.14
2 real
3 3142 integer
2 3.142 text
4 1B blob
sqlite> SELECT ROWID, b, typeof(b),b<1000 FROM domain ORDER BY b;
返回：
rowid b typeof(b) b<1000
NULL null NULL
1 3.14
2 real 1第七座墓志铭
3 3142 integer 1
2 3.142 text 0
4 1B blob 0
存储类和类型转换
关于存储类，需要关注的另⼀件事是：存储类有时会影响到值如何进⾏⽐较。特别是SQLite有时在进⾏⽐较之前，会将值在数字存储类(INTEGER和REAL)和TEXT之间进⾏转换。为进⾏⼆进制的⽐较，遵循如下规则：
l 当⼀个字段值与⼀个表达式的结果进⾏⽐较，字段的亲和性会在⽐较之前应⽤于表达式的结果。
l 当两个字段值进⾏⽐较，如果⼀个字段拥有INTEGER或NUMERIC亲和性⽽另⼀个没有，NUMERIC亲和性会应⽤于⾮NUMERIC字段的TEXT值。
l 当两个表达式进⾏⽐较，SQLite不做任何转换。如果两个表达式有相似的存储类，则直接按它们的值进⾏⽐较；否则按类值进⾏⽐较。
请看下⾯例⼦：
sqlite> lectROWID,b,typeof(i),i>'2.9' from domain ORDER BY b;
rowid b typeof(i i>'2.9'
5 NULL null NULL
1 3.14
2 real 1
3 3142 integer 1
2 3.142 real 1
4 1B blob 1
也算是“强类型(STRICT TYPING)”
如果你需要⽐类型亲和性更强的域完整性，可以使⽤CHECK约束。你可以使⽤⼀个单独的内置函数和⼀个CHECK约束来实现⼀个“假的”强类型。
