Sqlite3数据类型

NULL

表示一个NULL值

INTEGER

存储一个整数

根据大小可以使用1,2,3,4,6,8位来存储

REAL

IEEE 浮点数

8字节的IEEE浮动标记序号

TEXT:

字符串来存储

使用数据库编码存储(TUTF-8,UTF-16BE or UTF-16-LE)

BLOB

二进制值存储

如何输入就如何存储,不改变格式

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

除了INTEGER PRIMARY KEY,数据库中的任何列都可以存储任何类型的数据。

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形式存放

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

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

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

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

2.亲和类型

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

TEXT

NUMERIC

INTEGER

REAL

NONE

TEXT：可以用NULL，TEXT或者BLOB类型存储数据。

存放数值数据时：存储前被转换为文本形式

NUMERIC：可以使用所有5中存储类数据类型。

存放文本数据时：如果文本到INTEGER或REAL(根据优先级顺序) 转换是无损的话,就存为INTEGER或REAL，反之就存为TEXT。(TEXT和REAL转换时，前15位被保留的话就认为是无损的，可逆的)。不会企图去转换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) 声明类型中包含了字符串”REAL”,"FLOA",or "DOUB”或者没有为其声明类型，这个列具有REAL亲和性。

(5)其他的情况，列具有NUMERIC亲和性。

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

如果表格使用If "CREATE TABLE AS SELECT..."语句生成的,那么所有的列则都没有具体的数据类型,则没有类型亲和性.

2.2 亲和类型例子

下面这个表使用上面的5个规则，显示了一些来自传统的SQL操作的普通数据类型名称与亲和类型的对应。这个表只是显示了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

注意到声明类型为”FLOATINGPOINT”将被赋予INTEGER近似，而不是REAL近似，因为在”POINT”中的”INT”。声明类型为”STRING”的将被赋予NUMERIC，而不是TEXT（因为上述表中定义的类型中不存在STRING这一类型，它被归于到规则(5)中，属于其他情况）。

（从上面可以看出，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');

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);

SELECT typeof(t),typeof(no) FROM t1;

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

DELETE FROM t1;

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

SELECT typeof(t),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');

SELECT typeof(t),typeof(no) FROM t1;

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

// NULLs也不受列近似影响

DELETE FROM t1;

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

SELECT typeof(t),typeof(no) FROM t1;

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

3.比较表达式

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

3.1 排序

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

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

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

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

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

3.2 比较操作数的亲和性

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

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

一个”CAST(expras type)”形式的表达式与用声明类型为”type”的列有相同的亲和性

其他的情况，一个表达式为NONEaffinity

3.3 在比较前的类型转换

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

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

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

其他的情况，不应用亲和性，两个操作数按本来的样子比较。

表达式"a BETWEEN b AND c"表示两个单独的二值比较” a >= b ANDa <= 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);

SELECT typeof(a),typeof(b),typeof(c),typeof(d) FROMt1;

text|integer|text|integer

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

-- right-hand +side ofthe 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 operandsbut since

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

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

0|1|1

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

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

-- the application of affinity is a no-op; no conversionsoccur. 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 comparisonoccurs.

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

0|0|1

-- No affinity conversions occur. Right-hand side valuesall have

-- storage class INTEGER which are always less than theTEXT values

-- on the left.

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

0|0|0

-- No affinity conversions occur. Values are compared asTEXT.

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

0|1|1

-- No affinity conversions occur. Right-hand side valuesall have

-- storage class INTEGER which compare numerically withthe INTEGER

-- values on the left.

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

0|0|1

-- No affinityconversions 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 操作符

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

5. 分类，排序，混合挑选

当用Order By时，首先NULL被挑选出来,然后是integer和real按顺序被挑选出来,然后是text按memcmp()顺序被挑选出来,最后是BLOB按memcmp()顺序被挑选出来.在挑选之前,没有存储类型的值都被转换了。

当用Group By时,具有不同存储类型的值被认为是不同的,但也有例外,比如,一个integer值和一个real值从数字角度来说是相等的,那么它们则是相等的.用Group By子句比较完后,值不具有任何亲和性。

混合挑选操作符UNION,INTERSECT and EXCEPT 在值之间实行绝对的比较,同样的亲和性将被应用于所有的值,这些值将被存储在一个单独的具有混合SELECT的结果组的列中. 被赋予的亲和性是该列的亲和性,这个亲和性是由剩下的大部分的混合SELECTS返回的,这些混合SELECTS在那个位置上有列值(而不是其它类型的表达式). 如果一个给定的混合SELECT列没有SELECTS的量,那么在比较前,该列的值将不具有任何亲和性。

6. 其它亲和性模式
以上的部分所描述的都是数据库引擎在正常亲和性模式下所进行的操作,SQLite将描述其它两种亲和性模式,如下:

严格亲和性模式.在这种模式下,如果需要值之间相互转换数据存储类型的话,数据库引擎将发送错误报告,当前语句也将会重新运行.

无亲和性模式.在这种模式下,值的数据存储类型不发生转换.具有不同存储类型的值之间不能比较,但integer和real之间可以比较.

7.用户定义的校对顺序
By default,when 当SQLite比较两个文本值的时候,通过系统设定,不管字符串的编码是什么,用memcmp()来比较. SQLite第三版允许用户提供任意的函数来代替memcmp(),也就是用户定义的比较顺序.

除了系统预设的BINARY比较顺序,它是用memcmp()函数比较,SQLite还包含了两个额外的内置比较顺序函数,NOCASE和REVERSE:

BINARY -使用memcmp()比较字符串数据,不考虑文本编码.
REVERSE -用倒序比较二进制文本.
NOCASE - 和二进制一样,但在比较之前,26位的大写字母盘要被折合成相应的小写字母盘.

7.1 分配比较顺序

每个表格中的每个列都有一个预设的比较类型.如果一个比较类型不是二进制所要求的,比较的子句将被具体化为 列的定义 来定义该列.

当用SQLite比较两个文本值时,比较顺序将按照以下的规则来决定比较的结果.文档的第三部分和第五部分描述在何种场合下发生这种比较.

对于二进制比较符(=,<,>,<= and >=),如果每个操作数是一列的话,那么该列的默认比较类型决定于所使用的比较顺序. 如果两个操作数都是列的话,那么左边的操作数的比较类型决定了所要使用的比较顺序.如果两个操作数都不是一列,将使用二进制来比较.

表达式"x BETWEEN y and z"和 "x >=y AND x <= z"是相同的. 表达式"xIN (SELECT y ...)" 和表达式 "x = y" 使用同样的方法来操作,这是为了决定所要使用的比较顺序.如果X是一列或者二进制的,则"x IN (y,z ...)" 形式的表达式所使用的比较顺序是X的默认的比较类型.

ORDER BY clause that is part of aSELECT statement may be assigned a collation sequence to be used for the sortoperation explicitly. In this case the explicit collation sequence is alwaysused. Otherwise,if the expression sorted by an ORDER BY clause is a column,then the default collation type of the column is used to determine sort order.If the expression is not a column,then the BINARY collation sequence is used.

7.2 比较顺序的例子

下面的例子介绍了The examples below identify the collation sequences that would beused to determine the results of text comparisons that may be performed byvarious SQL statements. Note that a text comparison may not be required,and nocollation sequence used,in the case of numeric,blob or NULL values.

CREATE TABLE t1(

 a,-- default collation type BINARY

 b COLLATE BINARY,-- default collation type BINARY

 c COLLATE REVERSE,-- default collation type REVERSE

 d COLLATE NOCASE -- default collation type NOCASE

);

-- Text comparison is performed using the BINARY collation sequence.

SELECT (a = b) FROM t1;

-- Text comparison is performed using the NOCASE collation sequence.

SELECT (d = a) FROM t1;

-- Text comparison is performed using the BINARY collation sequence.

SELECT (a = d) FROM t1;

-- Text comparison is performed using the REVERSE collation sequence.

SELECT ('abc' = c) FROM t1;

-- Text comparison is performed using the REVERSE collation sequence.

SELECT (c = 'abc') FROM t1;

-- Grouping is performed using the NOCASE collation sequence (i.e. values

-- 'abc' and 'ABC' are placed in the same group).

SELECT count(*) GROUP BY d FROM t1;

-- Grouping is performed using the BINARY collation sequence.

SELECT count(*) GROUP BY (d || '') FROM t1;

-- Sorting is performed using the REVERSE collation sequence.

SELECT * FROM t1 ORDER BY c;

-- Sorting is performed using the BINARY collation sequence.

SELECT * FROM t1 ORDER BY (c || '');

-- Sorting is performed using the NOCASE collation sequence.

SELECT * FROM t1 ORDER BY c COLLATE NOCASE;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!