注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

云水禅心

淡若秋菊何妨瘦, 清到梅花不畏寒.

 
 
 

日志

 
 

SQLite学习手册(数据类型)  

2012-12-24 13:24:31|  分类: iphone |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |


一、SQLite简介

SQLite,是一款轻型的数据库,是遵守ACID的关联式数据库管理系统,它的设计目标是嵌入式的,而且目前已经在很多嵌入式产品中使用了它,它占用资 源非常的低,在嵌入式设备中,可能只需要几百K的内存就够了。它能够支持Windows、Linux、Unix等等主流的操作系统,同时能够跟很多程序语 言相结合,比如 Tcl、C#、PHP、Java等,还有ODBC接口,同样比起Mysql、PostgreSQL这两款开源世界著名的数据库管理系统来讲,它的处理速度 比他们都快。

二、SQLite数据类型

SQLite除了在字段类型为“Integer Primary Key”时是限制数据类型外,其它情况下SQLite是Typelessness(无类型)的。这意味着你可以保存任何类型的数据到你所想要保存的任何表的任何列中,无论这列声明的数据类型是什么。

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

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

但实际上,SQLite 3也接受如下的数据类型:

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  包含日期时间格式,必须写成“2011-05-23”不能写为“2011-5-23”,否则在读取时会产生错误!

对于SQLite来说对字段不指定类型是完全有效的,如:

1
Create Table ex3(a, b, c);

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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)
);

三、SQLite的类型亲和性分析

(以下引用“上善若水”的分析,仅供学习参考。网址:http://www.cnblogs.com/hustssrs/archive/2009/03/03/1402214.html。)

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

1、类型亲和性优点:

    1)、提高和其它DBMS的兼容性,让用户就像是在用一般的DBMS一样而使用它,提高了容错能力。

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

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

2、类型亲和性缺点:

    1)、在对表中数据进行统计方面如果有不一致的数据存在则运算比较混乱,其实也就是放宽政策为的是让更多人去维护。不过它自己是有处理方法的,如果在运算时出现不同类型的数据时就忽略不计等。

    2)、还有在数据比较方面也存在同样的问题,不过也有相应的补救措施,规定了比较准则:

        a)、 一个具有空存储类型的值被认为小于任何值(包括另外一个具有空存储类型的值)。

        b)、一个整数值或实数值小于任何文本值和BLOB值。当一个整数或实数和另一个整数或实数相比较的时候,则按照实际数值来比较。

        c)、一个文本值小于BLOB值。当两个文本值相比较的时候,则用C语言类库中的memcmp()函数来比较。然而,有时候也不是这样的,比如在下面所描述的“用户定义的整理顺序”情况下。

        d)、当两个BLOB文本被比较的时候,结果决定于memcmp()函数。





一、存储种类和数据类型:

    SQLite将数据值的存储划分为以下几种存储类型:
     NULL: 表示该值为NULL值。
     INTEGER: 无符号整型值。
     REAL: 浮点值。
     TEXT: 文本字符串,存储使用的编码方式为UTF-8、UTF-16BE、UTF-16LE。
     BLOB: 存储Blob数据,该类型数据和输入数据完全相同。
    由于SQLite采用的是动态数据类型,而其他传统的关系型数据库使用的是静态数据类型,即字段可以存储的数据类型是在表声明时即以确定的,因此它们之间 在数据存储方面还是存在着很大的差异。在SQLite中,存储分类和数据类型也有一定的差别,如INTEGER存储类别可以包含6种不同长度的 Integer数据类型,然而这些INTEGER数据一旦被读入到内存后,SQLite会将其全部视为占用8个字节无符号整型。因此对于SQLite而 言,即使在表声明中明确了字段类型,我们仍然可以在该字段中存储其它类型的数据。然而需要特别说明的是,尽管SQLite为我们提供了这种方便,但是一旦 考虑到数据库平台的可移植性问题,我们在实际的开发中还是应该尽可能的保证数据类型的存储和声明的一致性。除非你有极为充分的理由,同时又不再考虑数据库 平台的移植问题,在此种情况下确实可以使用SQLite提供的此种特征。
   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到当前时间所流经的秒数。

二、类型亲缘性:

    为了最大化SQLite和其它数据库引擎之间的数据类型兼容性,SQLite提出了"类型亲缘性(Type Affinity)"的概念。我们可以这样理解"类型亲缘性 ",在表字段被声明之后,SQLite都会根据该字段声明时的类型为其选择一种亲缘类型,当数据插入时,该字段的数据将会优先采用亲缘类型作为该值的存储 方式,除非亲缘类型不匹配或无法转换当前数据到该亲缘类型,这样SQLite才会考虑其它更适合该值的类型存储该值。SQLite目前的版本支持以下五种 亲缘类型:

亲缘类型 描述  
TEXT 数值型数据在被插入之前,需要先被转换为文本格式,之后再插入到目标字段中。
NUMERIC 当文本数据被插入到亲缘性为NUMERIC的 字段中时,如果转换操作不会导致数据信息丢失以及完全可逆,那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据,如果转换失 败,SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据,SQLite将不做任何转换,直接以NULL或BLOB的方式存储 该数据。需要额外说明的是,对于浮点格式的常量文本,如"30000.0",如果该值可以转换为INTEGER同时又不会丢失数值信息,那么SQLite 就会将其转换为INTEGER的存储方式。
INTEGER 对于亲缘类型为INTEGER的字段,其规则等同于NUMERIC,唯一差别是在执行CAST表达式时。
REAL 其规则基本等同于NUMERIC,唯一的差别是不会将"30000.0"这样的文本数据转换为INTEGER存储方式。
NONE 不做任何的转换,直接以该数据所属的数据类型进行存储。  

   1. 决定字段亲缘性的规则:
    字段的亲缘性是根据该字段在声明时被定义的类型来决定的,具体的规则可以参照以下列表。需要注意的是以下列表的顺序,即如果某一字段类型同时符合两种亲缘性,那么排在前面的规则将先产生作用。
    1). 如果类型字符串中包含"INT",那么该字段的亲缘类型是INTEGER。
    2). 如果类型字符串中包含"CHAR"、"CLOB"或"TEXT",那么该字段的亲缘类型是TEXT,如VARCHAR。
    3). 如果类型字符串中包含"BLOB",那么该字段的亲缘类型是NONE。
    4). 如果类型字符串中包含"REAL"、"FLOA"或"DOUB",那么该字段的亲缘类型是REAL。
    5). 其余情况下,字段的亲缘类型为NUMERIC。

    2. 具体示例:

声明类型 亲缘类型 应用规则
INT
INTEGER
TINYINT
SMALLINT
MEDIUMINT
BIGINT
UNSIGNED BIG INT
INT2
INT8
INTEGER 1
CHARACTER(20)
VARCHAR(255)
VARYING CHARACTER(255)
NCHAR(55)
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

    注:在SQLite中,类型VARCHAR(255)的长度信息255没有任何实际意义,仅仅是为了保证与其它数据库的声明一致性。

三、比较表达式:

    在SQLite3中支持的比较表达式有:"=", "==", "<", "<=", ">", ">=", "!=", "<>", "IN", "NOT IN", "BETWEEN", "IS" and "IS NOT"。
    数据的比较结果主要依赖于操作数的存储方式,其规则为:
    1). 存储方式为NULL的数值小于其它存储类型的值。
    2). 存储方式为INTEGER和REAL的数值小于TEXT或BLOB类型的值,如果同为INTEGER或REAL,则基于数值规则进行比较。
    3). 存储方式为TEXT的数值小于BLOB类型的值,如果同为TEXT,则基于文本规则(ASCII值)进行比较。
    4). 如果是两个BLOB类型的数值进行比较,其结果为C运行时函数memcmp()的结果。

四、操作符:

    所有的数学操作符(+, -, *, /, %, <<, >>, &, and |)在执行之前都会先将操作数转换为NUMERIC存储类型,即使在转换过程中可能会造成数据信息的丢失。此外,如果其中一个操作数为NULL,那么它们 的结果亦为NULL。在数学操作符中,如果其中一个操作数看上去并不像数值类型,那么它们结果为0或0.0。



sqlite3自增key设定(创建自增字段)

在用sqlite设计表时,每个表都有一个自己的整形id值作为主键,其实可以不 指定这么一个id值,sqlite内部本来就会为每个表加上一个 rowid,这个rowid可以当成一个隐含的字段使用,但是由sqlite引擎来维护 的,在3.0以前rowid是32位的整数,3.0以后是 64位的整数,为什么不直接使用这个内部的rowid作为每个表的id主键呢。

相关的文档在这里:?http://www.sqlite.org/autoinc.html?http://www.sqlite.org/faq.html

 

用指定INTEGER PRIMARY KEY AUTOINCREMENT 和不指定自增长字段用rowid有什么区别:

使用自增长字段为主键有不少问题,比如维护或是在大型分布应用中主键冲突的解决等。在一些大型分布应用中主键一般选用guid,这可以有效的避免主键冲突,减少对主键维护的工程。当然,对于中小型的应用,自增长字段的好处更多一些,简单、快速。

Sqlite中,一个自增长字段定义为INTEGER PRIMARY KEY AUTOINCREMENT,那么在插入一个新数据时,只需要将这个字段的值指定为NULL,即可由引擎自动设定其值,引擎会设定为最大的 rowid+1。当然,也可以设置为非NULL的数字来自己指定这个值,但这样就必须自己小心,不要引起冲突。当这个rowid的值大于所能表达的最大值 9223372036854775807 (3.0及以后版本的rowid最大值)后,rowid的新值会这个最大数之前随机找一个没被使用了的值。所以在rowid达到最大值前,rowid的值 是严格单调增加的。
INTEGER PRIMARY KEY AUTOINCREMENT 自增长字段的算法与rowid稍微有些不同。
 第一,在达到最大值后,rowid会找已被删除的字段对应的rowid作为新值,而自增长字段则会丢出一个SQLITE_FULL的错误。
 第二,自增长字段在增加新值时,是找一个从没被使用过的rowid作为新值,而rowid则是找最大已存在的rowid+1。这里对应用的影响会比较大,尤其是一些对id值有依赖的元记录,只适合使用自增长字段而不能用rowid。
 
 比如,我们设计一个元记录表:
drop table test;
create table test (
    [tkid]            integer PRIMARY KEY autoincrement,                -- 设置主键
    [tktype]          int default 0,
    [tableid]         varchar (50),
    [createdate]      datetime default (datetime('now', 'localtime'))    -- 时间
);


 第三,使用自增长字段,引擎会自动产生一个sqlite_sequence表,用于记录每个表的自增长字段的已使用的最大值,用户可以看 到,并可以用使用 Update、Delete和Insert操作,但不建议这么使用,这会让引擎混乱。如果使用rowid,也会有这么一个内部表,用户可以维护rowid 值,但看不到。
这么看来,如果直接使用rowid来代替自增加字段,根据两者的细微的差别,需要注意是否与自己的应用冲突,如果没有冲突,那么用rowid会更快一点。

 

SQLite中创建自增字段:

简单的回答:一个声明为 INTEGER PRIMARY KEY 的字段将自动增加。

从 SQLite 的 2.3.4 版本开始,如果你将一个表中的一个字段声明为 INTEGER PRIMARY KEY,那么无论你何时向该表的该字段插入一个 NULL 值,这个 NULL 值将自动被更换为比表中该字段所有行的最大值大 1 的整数;如果表为空,那么将被更换为 1。

一个新的API函数 sqlite3_last_insert_rowid() 返回最近的插入操作的整形键.

注意这个整型键始终比之前插入表中的最后一个键大1。新键相对于表中的已有键来说是唯一的,但它可能与之前从表中删除的键值重叠。要始终得到在整个表中唯 一的键,在INTEGER PRIMARY KEY的声明之前加关键词AUTOINCREMENT.这样被选的键将总是比表中已存在的最大键大1。若可能的最大键已存在于表中,INSERT操作将失 败并返回一个SQLITE_FULL错误码.

  评论这张
 
阅读(2215)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018