Sqlite 数据类型 总结

sqlite 数据类型 总结

一般数据采用的固定的静态数据类型，而SQLite采用的是动态数据类型，会根据存入值自动判断。SQLite具有以下五种数据类型：

1.NULL：空值。
2.INTEGER：带符号的整型，具体取决有存入数字的范围大小。
3.REAL：浮点数字，存储为8-byte IEEE浮点数。
4.TEXT：字符串文本。
5.BLOB：二进制对象。

但实际上，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常用数据类型,

这句话本身就有问题，因为：SQLite是无类型的. 这意味着你可以保存任何类型的数据到你所想要保存的任何表的任何列中, 无论这列声明的数据类型是什么(只有自动递增Integer Primary Key才有用). 对于SQLite来说对字段不指定类型是完全有效的. 如:

Create Table ex3(a, b, c);

即使SQLite允许忽略数据类型, 但是仍然建议在你的Create Table语句中指定数据类型. 因为数据类型对于你和其他的程序员交流, 或者你准备换掉你的数据库引擎是非常有用的. SQLite支持常见的数据类型, 如: 

SQL代码

1. CREATE TABLE ex2(    
2. a VARCHAR(10),    
3. b NVARCHAR(15),   
4. c TEXT,    
5. d INTEGER,   
6. e FLOAT,   
7. f BOOLEAN,    
8. g CLOB,    
9. h BLOB,    
10. i TIMESTAMP,   
11. j NUMERIC(10,5),    
12. k VARYING CHARACTER (24),    
13. l NATIONAL VARYING CHARACTER(16)   
14. );

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

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

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

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

4、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。

大部分SQL数据库引擎使用静态数据类型，数据的类型取决于它的存储单元（即所在的列）的类型。
SQLite3采用了动态的数据类型，存储值的数据类型和数值本身相关，而不是由它的字段的类型决定。SQLite3的动态数据类型能够向后兼容其它数据库普遍使用的静态类型，这就意味着，在那些使用静态数据类型的数据库上使用的数据表，在SQLite3上也能被使用。

SQLite3具有以下5种存储类别（在这里，存储类别和数据类型有所区别）：

NULL          空值
INTEGER       带符号整数，根据存入的数值的大小占据1,2,3,4,6或者8个字节
REAL          浮点数，采用8byte（即双精度）的IEEE格式表示
TEXT          字符串文本，采用数据库的编码（UTF-8，UTF-16BE 或者UTF- 
              16LE）
BLOB          无类型，可用于保存二进制文件

存储类别可能比数据类型更加普遍一点，比如 INTEGER存储类别，它包含了6种长度不同的整形数，它们在磁盘上的存储方式有一点差别，但当它们被读入内存进行处理的时候，将会被转换为更加普遍的数据类型（8字节有符号数）。所以，大多数时候，“存储类别”和“数据类型”有一些不易觉察的差别，而且他们也能够被交换使用。

SQLite3数据库（SQLite version 3 database）中除了被声明为主键的INTEGER类型的列外，任何列都能够存储属于任一存储类型的数值。

SQL语句中的任何数值，无论它们是被嵌入到SQL语句中的字面值，还是被绑定到预编译过的SQL语句中的参数
有一个隐含的存储类别，在下面所描述的情形中，在执行SQL语句时，数据库引擎可能会在 NUMERIC存储类别（INTEGER 和 REAL） 和 TEXT存储类别间转换这些数值。

1.1 布尔值
    SQLite3没有独立的布尔存储类型，作为替代，布尔值被存储为INTEGER类别 1（true） 0（false）。

1.2 日期，时间
   SQLite没有单独设立存储类型来存储 日期，时间， 或者 日期时间，作为替代，内置的 “时间，日期 函数”（Date And Time Functions）能够以 TEXT，REAL 或者 INTEGER 类别来存储时间日期。

   TEXT        遵循ISO8601 字符串（"YYYY-MM-DD HH:MMSS:SSS")
   REAL        按照公历，从现在到格林威治时间的 November 24 B.C
   INTEGER     按照Unix系统时间，从1970-01-01 00:00:00 UTC 到现在的秒数
应用程序能够在这中间选择任意格式来表示时间日期，并且使用内置的时间日期函数在这几种格式之间自由的转换。

2.0 类型亲和性
    为了在SQLite和其它数据库引擎之间实现兼容性最大化，SQLite 在列上支持“类型亲和性”（原文为Type Affinity）这一概念。列类型亲和性的原意为“为存储在该列的数值推荐的类型”，注意，是推荐而不是必须的，任何列任然能够存储任意类型的数据，仅仅只是当你给它选择的机会时（应该是说当没有明确指定类型时)时，相比其他的存储类型，它更乐意使用的存储类型，这一类型即为列的 亲和性。
    SQLite3数据库中的每一列都被指定了下面所列出的类型亲和性中的一个：
               TEXT
               NUMERIC
               INTEGER
               REAL
               NONE

    具有 TEXT 亲和性的列使用 NULL，TEXT 或者 BLOB存储类别来存储数据，如果数字值（Numeric data）被插入到具有 TEXT 亲和性的列中， 在存储前，它会被转换成文本
    具有 NUMERIC 亲和性的列，有可能会使用上述的五种存储类别，当插入文本数据时，当可以进行无损且可逆的转换时（如果文本中的前15个有效10进制数字能够被保护，转换就被认为是无损且可逆的），文本的存储类别会被转换到 INTEGER 或者 REAL。 为了在 TEXT 和 REAL 存储类别间进行转换，如果无法进行无损且可逆的转换的话，数字将会采用 TEXT 存储类别。不会试图对 NULL存储类别 和 BLOB存储类别 的数值进行转换。
    一个字符串可能看起来像浮点数的十进制指数表示法，但是，只要它能够用整数来表示，NUMERIC 亲和性将会把它转换为整数，因此，字符串“3.0e+5”在具有 NUMRIC 亲和性的列中被存储为整数300000而不是浮点数300000.0 。
    具有 INTEGER 亲和性的列的表现和具有 NUMERIC 亲和性的列一致，唯一明显的差别是在进行表达式计算的时候
    具有 REAL 亲和力的列类似具有 NUMRIC 亲和力的列，但是它强制将整数使用浮点数表示（作为优化手段，存储在具有 REAL 亲和性的列中，没有小数部分且值较小的浮点数，将被当做整数写入到磁盘以节省空间，当从磁盘独处的时候，将会被自动转换为浮点数，这一优化方式对于 SQL 这一层次不可见，并且只有通过检查数据库文件在磁盘扇区上对应的 比特位才能发现它）
    具有 NULL 亲和性的列没有偏好的存储类别，也不会强制转换存储类别。

2.1 列的类型亲和性的决定
   列的亲和性由这一列被声明的类型来决定，它遵从以下顺序规则：
        1.如果声明的类型中含有字符串“INT”，它将具有 INTEGER 亲和性
        2.如果列的类型声明中含有“CHAR”，“CLOB”或者“TEXT”中的任意一个，这一列将具有 TEXT 亲和性。比如 VARCHAR 类型 含有字符串“CHAR”，因此被指定 TEXT 亲和性。
        3.如果列的类型声明中含有字符串“BLOB”或者没有声明类型，它将具有 NULL 亲和性
        4.如果列的类型声明中含有“REAL”，“FLOA”，“DOUB”中的任意一个，它将具有 REAL 亲和性
        5.其他的将具有 NUMERIC 亲和性
   记住这些规则的优先级从上到下，如果一个列的类型被声明为“CHARINT”，它将满足规则1和2，但是按照优先级，它将具有 INTEGER 亲和性。

总结：
    由以上可以看出，SQLite使用的弱数据类型，它将能够存放的数据分：空值（NULL），整数（INTEGER），浮点数（REAL），字符串文本（TEXT），无类型（BLOB），数据的类型是由要存储的数值自身决定的，与列的类型声明无关，事实上，完全可以不声明列的类型

1. create table scr( name, age );
   
2. insert into scr values( "duwei", 20 );
   
3. insert into scr values( 20, "youhongtao");
   
4. select * from scr;
   
5. duwei | 20
   
6. 20 | youhongtao  
   

但是为了代码的可阅读性以及兼容其他数据库引擎，不建议缺省列的类型声明。
   
    为列声明的类型不决定存储在其中的数据的类型，它主要用于兼容其它的数据库引擎以及决定列的 类型亲和性，便于程序阅读。

    列的类型亲和性主要决定了存储数据时的转换动作。

sqlie的官网（http://www.sqlite.org/datatype3.html）
http://blog.csdn.net/jin868/article/details/5961263