SQLite数据类型详解

一、存储种类和数据类型

SQLite将数据值的存储划分为以下几种存储类型（即在磁盘上存储的格式）：

    NULL: 表示该值为NULL值。

    INTEGER: 无符号整型值。

    REAL: 浮点值。

    TEXT: 文本字符串，存储使用的编码方式为UTF-8、UTF-16BE、UTF-16LE。

    BLOB: 存储Blob数据，该类型数据和输入数据完全相同。

    传统SQL 数据库使用严格的静态类型（即字段的数据类型是在创建表时确定的），值的类型就是它所在字段的类型，而Sqlite采用的是动态数据类型，单独的一个字段可以包含不同存储类的值，除了整型主键列Integer Primary Key外，Sqlite数据库中的任何列，都可用于存储任何类型的值，通过值的表示法来判断其类型，下面就是其推理方法：

SQL语句中用单引号或双引号括起来的文字被指派为TEXT。

如果文字是未用引号括起来的数据，并且没有小数点和指数，被指派为INTEGER。

如果文字是未用引号括起来的数据，并且带有小数点或指数，被指派为REAL。

用NULL说明的值被指派为NULL存储类。

如果一个值的格式为X’ABCD’，其中ABCD为16进制数字，则该值被指派为BLOB。X前缀大小写皆可。

由于在SQLite中，即使在表声明中明确了字段类型，仍然可以在该字段中存储其它类型的数据，因此在创建表时不指定字段类型也是完全有效的. 如: 

sqlite>Create Table ex3(a, b, c);

另外：存储分类和数据类型也有一定的差别，如INTEGER存储类别可以包含6种不同长度的Integer数据类型，然而这些INTEGER数据一旦被读入到内存后，SQLite会将其全部视为占用8个字节无符号整型。

虽然SQLite允许忽略数据类型, 但仍建议在Create Table语句中指定各字段的数据类型，并尽可能保证数据的存储和其所在字段的类型声明一致，这样做的好处：a 便于数据库的移植，b方便交流。

1.1 布尔类型

SQLite 并没有单独的布尔存储类型，而是将布尔值存储为整数 0 (false) 和 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 以来的秒数。

应用可以选择这些格式中的任一种存储日期和时间，并使用内置的日期和时间函数在这些格式间自由转换。

二、 类型亲和性

为了最大限度地提高 SQLite 和其它数据库引擎之间的兼容性，SQLite 支持列的“类型亲和性”的概念。列的类型亲和性是指数据存储于该列的推荐类型。这里重要的思想是类型是推荐的，而不是必须的。任何列仍可以存储任何类型的数据。这只是让一些列有选择性地优先使用某种存储类型。一个列的首选存储类型被称为它的“亲和性”。

在SQLite中，字段没有类型或域。当给一个字段声明了类型，该字段实际上仅仅具有了该类型的亲和性。声明类型和类型亲和性是两回事。类型亲和性预定SQLite用什么存储类在字段中存储值。在存储一个给定的值时到底SQLite会在该字段中用什么存储类决定于值的存储类和字段亲和性的结合。

任何列可以存储任何类型的数据，但列的首选存储类是它的affinity。在SQLite3数据库中，每个表的列分配为以下类型的affinity之一：

TEXT

NUMERIC

INTEGER

REAL

NONE

一个具有 TEXT 亲和性的列使用存储类型 NULL、 TEXT 或 BLOB 存储所有数据。如果数值数据被插入到一个具有 TEXT 亲和性的列，则数据在存储前被转换为文本形式

数值亲和性的列可能包含了使用所有五个存储类的值。当插入文本数据到数值列时，该文本的存储类型被转换成整型或实数（按优先级排序）如果这种转换是无损或可逆的的话。对于文本与实数类型之间的转换，如果前15个重要十进制数字被保留的话，SQLite认为这种转换是无损并可逆的。如果文本不能无损地转换成整型或实数，那这个值将以文本类型存储。不要试图转换NULL或BLOB值。

一个字符串可能看上去像带有小数点和/或指数符的浮点文字，但只要这个值可以用一个整型表示，数值亲和性就会把它转换成一个整型。因此，字符串‘3.0e+5’以整型300000，而不是浮点值30000.0的形式存储在一个数值亲和性的列里。

sqlite3也接受如下的数据类型： 

smallint 16 位元的整数。 

integer 32 位元的整数。 

decimal(p,s) p 精确值和 s 大小的十进位整数，精确值p是指全部有几个数(digits)大小值，s是指小数点後有几位数。如果没有特别指定，则系统会设为 p=5; s=0 。 

float  32位元的实数。 

double  64位元的实数。 

char(n)  n 长度的字串，n不能超过 254。 

varchar(n) 长度不固定且其最大长度为 n 的字串，n不能超过 4000。 

graphic(n) 和 char(n) 一样，不过其单位是两个字元 double-bytes， n不能超过127。这个形态是为了支援两个字元长度的字体，例如中文字。 

vargraphic(n) 可变长度且其最大长度为 n 的双字元字串，n不能超过 2000 

date  包含了 年份、月份、日期。 

time  包含了 小时、分钟、秒。 

timestamp 包含了 年、月、日、时、分、秒、千分之一秒。 

1. 布尔数据类型：

    SQLite并没有提供专门的布尔存储类型，取而代之的是存储整型1表示true，0表示false。

 2. 日期和时间数据类型：

    和布尔类型一样，SQLite也同样没有提供专门的日期时间存储类型，而是以TEXT、REAL和INTEGER类型分别不同的格式表示该类型，如：

    TEXT: "YYYY-MM-DD HH:MM:SS.SSS"

    REAL: 以Julian日期格式存储

    INTEGER: 以Unix时间形式保存数据值，即从1970-01-01 00:00:00到当前时间所流经的秒数。

1. 决定字段亲缘性的规则：

字段的亲缘性是根据该字段在声明时被定义的类型来决定的，具体的规则可以参照以下列表。需要注意的是以下列表的顺序，即如果某一字段类型同时符合两种亲缘性，那么排在前面的规则将先产生作用。

1). 如果类型字符串中包含"INT"，那么该字段的亲缘类型是INTEGER。

2). 如果类型字符串中包含"CHAR"、"CLOB"或"TEXT"，那么该字段的亲缘类型是TEXT，如VARCHAR。

3). 如果类型字符串中包含"BLOB"，那么该字段的亲缘类型是NONE。

4). 如果类型字符串中包含"REAL"、"FLOA"或"DOUB"，那么该字段的亲缘类型是REAL。

5). 其余情况下，字段的亲缘类型为NUMERIC。