Sqlite的字段类型说明

存储类(Storage Classes)

如前文所述,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> select 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.142      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字段不试图做任何类型转化。所有值按它们本身的存储类存储。

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.142 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.142 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> selectROWID,b,typeof(i),i>'2.9' from domain ORDER BY b;

rowid b typeof(i i>'2.9'

5 NULL null NULL

1 3.142 real 1

3 3142 integer 1

2 3.142 real 1

4 1B blob 1

也算是“强类型(STRICT TYPING)”

如果你需要比类型亲和性更强的域完整性,可以使用CHECK约束。你可以使用一个单独的内置函数和一个CHECK约束来实现一个“假的”强类型。

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值