【转】sqlite3数据类型以及近似类型

大多数的数据库引擎（到现在据我们所知的除了sqlite的每个sql数据库引擎）都使用静态的、刚性的类型，使用静态类型，数据的类型就由它的容器决定，这个容器是这个指被存放的特定列。

Sqlite使用一个更一般的动态类型系统，sqlite中，值的数据类型跟值本身相关，而不是与它的容器相关。Sqlite的动态类型系统和其他数据库的更为一般的静态类型系统相兼容，但同时，sqlite中的动态类型允许它能做到一些传统刚性类型数据库所不可能做到的事。

 

1．  存储类和数据类型

每个存放在sqlite数据库中（或者由这个数据库引擎操作）的值都有下面中的一个存储类：

l  NULL，值是NULL

l  INTEGER，值是有符号整形，根据值的大小以1,2,3,4,6或8字节存放

l  REAL，值是浮点型值，以8字节IEEE浮点数存放

l  TEXT，值是文本字符串，使用数据库编码（UTF-8，UTF-16BE或者UTF-16LE）存放

l  BLOB，只是一个数据块，完全按照输入存放（即没有准换）

从上可以看出存储类比数据类型更一般化。比如INTEGER存储类，包括6中不同长度的不同整形数据类型，这在磁盘上造成了差异。但是只要INTEGER值被从磁盘读出进入到内存进行处理，它们被转换成最一般的数据类型（8-字节有符号整形）。

Sqlite v3数据库中的任何列，除了整形主键列，可以用于存储任何一个存储列的值。sql语句中的中所有值，不管它们是嵌入在sql文本中或者是作为参数绑定到一个预编译的sql语句，它们的存储类型都是未定的。在下面描述的情况中，数据库引擎会在查询执行过程中在数值（numeric）存储类型（INTEGER和REAL）和TEXT之间转换值。

1.1布尔类型

Sqlite没有单独的布尔存储类型，它使用INTEGER作为存储类型，0为false，1为true

 

1.2 Date和Time Datatype

Sqlite没有另外为存储日期和时间设定一个存储类集，内置的sqlite日期和时间函数能够将日期和时间以TEXT，REAL或INTEGER形式存放

l  TEXT 作为IS08601字符串（"YYYY-MM-DD HH:MM:SS.SSS"）

l  REAL 从格林威治时间11月24日，4174 B.C中午以来的天数

l  INTEGER 从 1970-01-01 00:00:00 UTC以来的秒数

程序可以任意选择这几个存储类型去存储日期和时间，并且能够使用内置的日期和时间函数在这些格式间自由转换

 

2.0 类型近似

 

为了使sqlite和其他数据库间的兼容性最大化，sqlite支 持列上“类型近似”的观点，列的类型近似指的是存储在列上数据的推荐类型。这里必须记住一点，这个类型是被推荐，而不是必须的。任何列仍然能存储任意类型 的数据。只是一些列，给予选择的话，将会相比于其他的一些类型优选选择一些存储类型，这个列优先选择的存储类型被称为它的“近似”。

每个sqlite3数据库中的列都被赋予下面类型近似中的一种：

l  TEXT

l  NUMERIC

l  INTEGER

l  REAL

l  NONE

具有TEXT近似的列可以用NULL，TEXT或者BLOB类型存储数据。如果数值数据被插入到具有TEXT近似的列，在被存储前被转换为文本形式

一个有NUMERIC近似的列可以使用1中的所有5中存储类来存储数据。当文本数据被存放到NUMERIC近似的列中，这个文本的存储类被转换到INTEGER或REAL（根据优先级顺序），如果这个转换是无损的话。对于TEXT和REAL存储类间的转换，如果数据的前15位的被保留的话sqlite就认为这个转换是无损的、可反转的。如果TEXT到INTEGER或REAL的转换不可避免的会造成损失，那么数据将使用TEXT存储类存储。不会企图去转换NULL或BLOB值。

一个字符串可能看起来像浮点数据，有小数点或指数符号，但是只要这个数据可以使用整形存放，NUMERIC近似就会将它转换到整形。比如，字符串 '3.0e+5'存放到一个具有NUMERIC近似的列中，被存为300000，而不是浮点型值300000.0。

具有INTEGER近似的列和具有NUMERIC近似的列表现相同。它们之间的差别仅处于转换描述上。

具有REAL近似的列和具有NUMERIC近似的列一样，除了它将整形数据转换成浮点型形式。

具有NONE近似的列不会优先选择一个存储列，也不会强制将数据从一个存储类转换到另外一个类。

 

2.1 列近似的决定因素

列的近似由这个列的声明类型所决定，根据下面的顺序的规则：

<1> 如果声明类型包含”INT”字符串，那么这个列被赋予INTEGER近似

<2> 如果这个列的声明类型包含”CHAR”，”CLOB”，或者”TEXT”中的任意一个，那么这个列就有了TEXT近似。注意类型VARCHAR包含了”CHAR”字符串，那么也就被赋予了TEXT近似

<3> 如果列的声明类型中包含了字符串”BLOB”或者没有为其声明类型，这个列被赋予NONE近似

<4> 其他的情况，列被赋予NUMERIC近似

上面规则额顺序对于决定列的近似很重要。一个列的声明类型为”CHARINT”的话同时会匹配规则<1>和<2>，但是第一个规则占有优先级所以这个列的近似将是INTEGER。

 

2.2 近似名称例子

         下面这个表显示了多少来自更传统的SQL操作的普通数据类型名称，使用上一节中的5个规则，被转换到近似类型。这个表只显示了sqlite能够接受的数据类名称的一个子集。注意到跟随类型名的圆括号内的数值参数（如：”VARCHAR(255)”）被sqlite忽略—sqlite不在字符串、BLOBS或者数值的长度上强加任何长度限制（除了一个全局的SQLITE_MAX_LENGTH限制）。

来自create table语句或者强转语句的范例类型名	产生的近似	用于决定近似的规则
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 no datatype specified	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（因为上述表中定义的类型中不存在STRING这一类型，它被归于到规则<4>中，属于其他情况）。

（从上面可以看出，sqlite3只是从声明类型字符串中去查找它知道的声明类型，比如”XINT”将被赋予INTEGER近似因为这个字符串里面有”INT”，所以这里并不需要一个单独的正确的声明类型，而是只要声明类型字符串里面包含了sqlite所知道的声明类型即可）

 

2.3 列近似操作例子

CREATE TABLE t1(

    t  TEXT,     -- text affinity by rule 2

    nu NUMERIC,  -- numeric affinity by rule 5

    i  INTEGER,  -- integer affinity by rule 1

    r  REAL,     -- real affinity by rule 4

    no BLOB      -- no affinity by rule 3

); //这里根据声明类型确定了列的类型近似

 

INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0');

SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;

//结果：text|integer|integer|real|text

DELETE FROM t1;

INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0);

SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;

//结果：text|integer|integer|real|real

DELETE FROM t1;

INSERT INTO t1 VALUES(500, 500, 500, 500, 500);

SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;

//结果：text|integer|integer|real|integer

（这里的第四个值，对应的列是REAL近似的，传输的值整形的，但是根据REAL近似的规则它会将它转换为real型数据）

// 数据块(BLOB)不管是什么列近似都一直存为BLOB类型

DELETE FROM t1;

INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500');

SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;

//结果：blob|blob|blob|blob|blob

 

// NULLs也不受列近似影响

DELETE FROM t1;

INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);

SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;

//结果：null|null|null|null|null

 

 

3.0 比较表达式

         Sqlite v3有一系列有用的比较操作符，包括 "=", "==", "<", "<=", ">", ">=", "!=", "<>", "IN", "NOT IN", "BETWEEN", "IS", 和 "IS NOT"

 

3.1 排序

比较操作的结果基于操作数的存储类型，根据下面的规则：

l  存储类型为NULL的值被认为小于其他任何的值（包括另一个存储类型为NULL的值）

l  一个INTEGER或REAL值小于任何TEXT或BLOB值。当一个INTEGER或REAL值与另外一个INTEGER或REAL值比较的话，就执行数值比较

l  TEXT值小于BLOB值。当两个TEXT值比较的时候，就根据序列的比较来决定结果

l  当两个BLOB值比较的时候，使用memcmp来决定结果

 

3.2 比较操作数的近似（Affinity）

         Sqlite可能在执行一个比较之前会在INTEGER，REAL或TEXT之间转换比较值。是否在比较操作之前发生转换基于操作数的近似（类型）。操作数近似（类型）由下面的规则决定：

l  对一个列的简单引用的表达式与这个列有相同的affinity，注意如果X和Y.Z是列名，那么+X和+Y.Z均被认为是用于决定affinity的表达式

l  一个”CAST(expr as type)”形式的表达式与用声明类型为”type”的列有相同的affinity

l  其他的情况，一个表达式为NONE affinity

 

3.3 在比较前的类型转换

         只有在转换是无损、可逆转的时候“应用近似”才意味着将操作数转换到一个特定的存储类。近似在比较之前被应用到比较的操作数，遵循下面的规则（根据先后顺序）:

l  如果一个操作数有INTEGER，REAL或NUMERIC近似，另一个操作数有TEXT或NONE近似，那么NUMERIC近似被应用到另一个操作数

l  如果一个操作数有TEXT近似，另一个有NONE近似，那么TEXT近似被应用到另一个操作数

l  其他的情况，不应用近似，两个操作数按本来的样子比较

表达式"a BETWEEN b AND c"表示两个单独的二值比较” a >= b AND a <= c”，即使在两个比较中不同的近似被应用到’a’。

 

3.4 比较举例

CREATE TABLE t1(

    a TEXT,      -- text affinity

    b NUMERIC,   -- numeric affinity

    c BLOB,      -- no affinity

    d            -- no affinity

);

 

INSERT INTO t1 VALUES('500', '500', '500', 500);

SELECT typeof(a), typeof(b), typeof(c), typeof(d) FROM t1;

text|integer|text|integer

 

-- Because column "a" has text affinity, numeric values on the

-- right-hand +side of the comparisons are converted to text before

-- the comparison occurs.

SELECT a < 40,   a < 60,   a < 600 FROM t1;

0|1|1

 

-- Text affinity is applied to the right-hand operands but since

-- they are already TEXT this is a no-op; no conversions occur.

SELECT a < '40', a < '60', a < '600' FROM t1;

0|1|1

 

-- Column "b" has numeric affinity and so numeric affinity is applied

-- to the operands on the right.  Since the operands are already numeric,

-- the application of affinity is a no-op; no conversions occur.  All

-- values are compared numerically.

SELECT b < 40,   b < 60,   b < 600 FROM t1;

0|0|1

 

-- Numeric affinity is applied to operands on the right, converting them

-- from text to integers.  Then a numeric comparison occurs.

SELECT b < '40', b < '60', b < '600' FROM t1;

0|0|1

 

-- No affinity conversions occur.  Right-hand side values all have

-- storage class INTEGER which are always less than the TEXT values

-- on the left.

SELECT c < 40,   c < 60,   c < 600 FROM t1;

0|0|0

 

-- No affinity conversions occur.  Values are compared as TEXT.

SELECT c < '40', c < '60', c < '600' FROM t1;

0|1|1

 

-- No affinity conversions occur.  Right-hand side values all have

-- storage class INTEGER which compare numerically with the INTEGER

-- values on the left.

SELECT d < 40,   d < 60,   d < 600 FROM t1;

0|0|1

 

-- No affinity conversions occur.  INTEGER values on the left are

-- always less than TEXT values on the right.

SELECT d < '40', d < '60', d < '600' FROM t1;

1|1|1

 

从这里可以看出，假如可以使用3.1中的规则进行比较的话，就不需要进行类型转换，否则的话就要进行类型转换

 

4.0 操作符

所有的数学操作符(+, -, *, /, %, <<, >>, &, |)，在被执行前，都会将两个操作数都转换为数值存储类型（INTEGER和REAL）。即使这个转换是有损和不可逆的，转换仍然会执行。一个数学操作符上的NULL操作数将产生NULL结果。一个数学操作符上的操作数，如果以任何方式看都不像数字，并且又不为空的话，将被转换为0或0.0。