SQLite的类型

SQLite的类型亲和性分析

参考：
SQLite剖析之数据类型
SQLite的类型亲和性分析

sqlite类型

NULL 值是一个 NULL 值。
INTEGER 值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。 布尔值被存储为整型0(false)和1(true)。
REAL 值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。
TEXT 值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。
BLOB 值是一个 blob 数据，完全根据它的输入存储。

一、类型亲和性介绍

SQLite不强制数据类型约束。任何数据都可以插入任何列。你可以向一个整型列中插入任意长度的字符串，向布尔型列中插入浮点数，或者向字符型列中插入日期型值。
在 Create TABLE 中所指定的数据类型不会限制在该列中插入任何数据。任何列均可接受任意长度的字符串（只有一种情况除外：标志为INTEGER PRIMARY KEY的列只能存储64位整数， 当向这种列中插数据除整数以外的数据时，将会产生错误。）
但SQLite确实使用声明的列类型来指示你所期望的格式。
所以，例如你向一个整型列中插入字符串时，SQLite会试图将该字符串转换成一个整数。如果可以转换，它将插入该整数；否则，将插入字符串。这是一个特性，而不是一个bug。
这种特性被称为类型或列亲和性(type or column affinity).

二、类型亲和性总结(优点)：

1 提高和其它DBMS的兼容性，让用户就像是在用一般的DBMS一样而使用它，提高了容错能力。
2 SQLite支持的数据类型只有五种，而其它的大型DBMS支持的数据类型有几十种，那么如果要将其它的数据转换成SQLite下的数据就根本不能实现，所以就将它的数据类型设计为亲和性的，数据类型种类少了系统实现会简单很多，整个系统也就不会太庞大，因为如
果有太多的数据类型限制的话，本身系统在实现方面也会困难些。然而，虽然它支持的类型虽然只有五种，可是实际上任何类型都支持了，这就是SQLite数据类型亲和性的巧妙之处。由此我个人认为这也就是将数据类型设计成为亲和性的初衷。

3 在插入数据的时候只要做一些检查和转换即可，实现容易

三、数据类型亲和性(缺点)：

1. 在对表中数据进行统计方面如果有不一致的数据存在则运算比较混乱，其实也就是放宽政策为的是让更多人去维护。不过它自己是有处理方法的，如果在运算时出现不同类型的数据时就忽略不计等(我认为这点也是很牵强，因为如果跳过就会得到一些不合乎人期望的结果，但我认为一般情况下，对于一列数据来说，基本上会是一致的，因为如果在很大程序上不一致的话就没什么意义的)。

2. 还有在数据比较方面也存在同样的问题，不过也有相应的补救措施，自己规定了比较准则：
   a) 一个具有空存储类型的值被认为小于任何值（包括另外一个具有空存储类型的值）。
   b) 一个整数值或实数值小于任何文本值和BLOB值。 当一个整数或实数和另一个整数或实数相比较的时候，则按照实际数值来比较。
   c) 一个文本值小于BLOB值。当两个文本值相比较的时候，则用C语言类库中的memcmp()函数来比较。然而，有时候也不是这样的，比如在下面所描述的“用户定义的整理顺序”情况下。
   d)当两个BLOB文本被比较的时候，结果决定于memcmp()函数。

（1）列亲和类型的确定(决定字段亲缘性的规则)

列的亲和类型由列的声明类型(在写SQL语句时指定)来确定，即字段的亲缘性是根据该字段在声明时被定义的类型来决定，根据以下规则顺序来判断：
1）如果列的声明类型包含字符串”INT”，则被定义为整数亲和类型。
2）如果列的声明类型包含字符串”CHAR”、”CLOB”或”TEXT”中的某一个，则列具有文本亲和类型。注意VARCHAR类型包含字符串”CHAR”，因此被定义为文本亲和类型。
3）如果列的声明类型包含字符串”BLOB”，或者没有为列声明数据类型，则列具有NONE亲和类型。
4）如果列的声明类型包含字符串”REAL”、”FLOA”或”DOUB”中的某一个，则列具有REAL亲和类型。
5）否则，列的亲和类型为NUMERIC。
需要注意的是以下情况的列表顺序，即如果某一字段类型同时符合两种亲缘性，那么排在前面的规则将先产生作用。确定列亲和类型的规则顺序非常重要，声明类型为”CHARINT”的列匹配规则1和2，但按顺序优先使用规则1，因此列被定义为整数亲和类型。

（2）亲和类型名称实例

按照上面5条规则，下面例子显示传统SQL实现中的各种通用数据类型（CREATE TABLE语句或CAST表达式中的数据类型）怎样被转化成SQLite中的亲和类型。这里只是所有传统数据类型中的一部分，它们能够被SQLite接受。注意跟在类型名后面的括号中的数字参数在SQLite中被忽略。SQLite不在字符串、BLOB对象或数值上强加任何长度限制（除了大的全局SQLITE_MAX_LENGTH限制）。
* INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8: 定义为INTEGER亲和类型（按规则1）。
*
* CHARACTER(20), VARCHAR(255), VARYING CHARACTER(255), NCHAR(255), NATIVE CHARACTER(70), NVARCHAR(100), TEXT, CLOB: 定义为TEXT亲和类型（按规则2）。
*
* BLOB, 不声明类型: 定义为NONE亲和类型（按规则3）。
*
* REAL, DOUBLE, DOUBLE PRECISION, FLOAT: 定义为REAL亲和类型（按规则4）。
*
* NUMERIC, DECIMAL(10,5), BOOLEAN, DATE, DATETIME: 定义为NUMERIC亲和类型（按规则5）。
*
注意声明类型”FLOATING POINT”将得到INTEGER亲和类型，而不是REAL亲和类型，因为”POINT”中有子串”INT”。声明类型”STRING”为NUMERIC亲和类型，而不是TEXT。

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

smallint: 16 位元的整数。

interger: 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 :包含了 年、月、日、时、分、秒、千分之一秒。

datetime 包含日期时间格式，必须写成’2010-08-05’不能写为’2010-8-5’，否则在读取时会产生错误！

SQLite支持常见的数据类型, 如:

CREATE TABLE ex2( 
    a VARCHAR(10), 
    b NVARCHAR(15), 
    c TEXT, 
    d INTEGER, 
    e FLOAT, 
    f BOOLEAN, 
    g CLOB, 
    h BLOB, 
    i TIMESTAMP, 
    j NUMERIC(10,5), 
    k VARYING CHARACTER (24), 
    l NATIONAL VARYING CHARACTER(16) 
);

char、varchar、text和nchar、nvarchar、ntext的区别

CHAR。
CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间,不足的自动用空格填充。

VARCHAR。
存储变长数据，但存储效率没有CHAR高。如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1。为什么“+1”呢？这一个字节用于保存实际使用了多大的长度。从空间上考虑，用varchar合适；从效率上考虑，用char合适，关键是根据实际情况找到权衡点。

TEXT。
text存储可变长度的非Unicode数据，最大长度为2的31次方-1(即2,147,483,647)个字符。

NCHAR、NVARCHAR、NTEXT
这三种从名字上看比前面三种多了个“N”。它表示存储的是Unicode数据类型的字符。我们知道字符中，英文字符只需要一个字节存储就足够了，但汉字众多，需要两个字节存储，英文与汉字同时存在时容易造成混乱，Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示。nchar、nvarchar的长度是在1到4000之间。和char、varchar比较起来，nchar、nvarchar则最多存储4000个字符，不论是英文还是汉字；而char、varchar最多能存储8000个英文，4000个汉字。可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字，较为方便，但在存储英文时数量上有些损失。

所以一般来说，如果含有中文字符，用nchar/nvarchar，如果纯英文和数字，用char/varchar。