SQLite3的数据类型
大多数SQL数据库引擎(据我所知,所有除SQLite外的SQL数据库引擎)使用静态固定类型。使用静态类型,数据的类型由它的容器——即存储数据的列决定。
SQLite使用一个更通用的动态类型系统。在SQLite中,数据的类型与数据本身绑定,而不是与存储数据的列绑定。其它数据库引擎使用的静态类型系统更为普遍。在其它静态类型系统中工作的SQL语句在SQLite中以同样的方式工作,在这点上,SQLite的动态类型系统向后兼容静态类型系统。
1.0 Storage Classes andDatatypes
在SQLite数据库中存储(或由数据库引擎操作)的数据都有一个存储类(storage class)。如下是SQLite的所有存储类:- NULL:数据为空值。
- INTEGER:数据为带符号整数,按数值大小存储为1、2、3、4、6或8个字节。
- REAL:数据为浮点数,存储为8字节的IEEE浮点数。
- TEXT:数据为文本字符串。按数据库编码(UTF-8、UTF-16BE、UTF-16LE)存储。
- BLOB:数据为无格式二进制数据,按输入的格式存储。
注意,存储类比数据类型(datatype)更通用(general)。例如,INTEGER存储类就包含6中不同长度的数据类型。这在存储在磁盘上是不同的。一旦INTEGER数据从磁盘读到内存,它们就转换为更为通用的数据类型(8字节带符号整数)。因此大多数情况下,不区分存储类和数据类型,这两个术语可以互换使用[。
只要不是INTEGER类型的主键列(INTEGER PRIMARY KEY),任何其它的SQLite版本3的数据列,可以存储任何存储类型的数据。
SQL语句中的数值,不管是内嵌在SQL语句文本中的字面量(literals)还是绑定到预编译SQL语句中的参变量,都有一个默认的存储类。如下描述的情形中,数据库引擎会在数值存储类(INTEGER和REAL)和TEXT存储类之间转换数值。
1.1 Boolean Datatype
SQLite没有一个单独的布尔存储类。布尔值存储为整数0(false)和1(true)。
1.2 Date and Time Datatype
SQLite没有用来存储日期和时间值的存储类。替代的,SQLite的内置日期时间函数可以用来把日期时间值存储为TEXT,REAL或INTEGER数值。- TEXT:ISO8601字符串(“YYYY-MM-DD HH:MM:SS.SSS”)。
- REAL:Julian日数(the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar)。
- INTEGER:Unix时间,自1970-01-01 00:00:00 UTC以来的秒数。
使用内置日期时间函数,应用可以选择以上任意一种格式存储日期时间值,并在以上格式间自由转换。
2.0 Type Affinity
为了使SQLite最大化的与其它数据库兼容,SQLite支持列的“类型关联(type affinity)”。列的类型关联指数据存储在该列的推荐类型。重要的思想是,类型是推荐的,而不是必须的。任何列仍然可以存储任何类型的数据。类型关联只不过是给一些列机会优先选择某些存储类。列优先选择的存储类被称为列的“关联(affinity)”。(wayz注,关联和类型关联是一回事,可以认为关联是类型关联的简称)。
SQLite 3数据库中的每列都被赋予下面的一个类型关联:
- TEXT
- NUMERIC
- INTEGER
- REAL
- NONE
具有TEXT类型关联的列使用存储类NULL,TEXT或BLOB存储数据。如果数值数据插入到TEXT类型关联的列,则数据在存储前首先转换成文本格式。
具有NUMERIC类型关联的列使用所有五种存储类型存储数据。当文本数据插入到NUMERIC类型关联的列时,在无损(lossless)且可逆(reversible)的情况下,文本数据的存储类型转化为INTEGER或REAL(选择存储类时INTEGER优先于REAL)。对于TEXT和REAL存储类型的转化,如果能保留前15个有效数字,则认为转换是无损且可逆的。如果TEXT不能无损的转化为INTEGER或REAL,则使用TEXT存储类型。对于NULL或BLOB数据,不进行转化。
一个含有小数点或指数符号的字符串可能看起来像是一个浮点数,但是只要数值能表示为一个整数,NUMERIC类型关联就会把它转化为整数。因此,字符串‘3.0e+5’在存入到NUMERIC类型关联的列时,会被存储为整数300000,而不是浮点数300000.0。
具有INTEGER类型关联的列处理方式和NUMERIC类型关联的列类似。区别仅仅在于CAST表达式(CAST expression)。
除了把所有的整数类型数据强制转化为浮点数,具有REAL类型关联的列处理方式和NUMERIC类型关联的列类似。(作为内部优化,无小数部分的小浮点数,存储在REAL类型关联的列时,为了节省空间,作为整数写入磁盘,在读出时自动转化为浮点数。这一优化在SQL层完全不可见,只有在检查数据库文件的原始比特时可见。)
具有NONE类型关联的列不会选择或转化数据的存储类。
2.1 Determination Of ColumnAffinity
类型关联由列声明的数据类型决定,按如下的规则顺序决定:- 如果声明类型包含字符串“INT”,则列的类型关联为INTEGER。
- 如果声明类型包含字符串“CHAR”、“CLOB”或“TEXT”,则类型关联为TEXT。注意,类型VARCHAR因包含字符串“CHAR”,因此列被赋予TEXT类型关联。
- 如果声明类型包含字符串“BLOB”或未指定列类型,则类型关联为NONE。
- 如果声明类型包含字符串“REAL”、“FLOA”或“DOUB”,则类型关联为REAL。
- 否则,类型关联为NUMURIC。
注意决定类型关联的规则的顺序是重要的。如果一个列的声明类型为“CHARINT”,则同时满足规则1和规则2,由于规则1在先,所以列的类型关联为INTEGER。
2.2 Affinity Name Examples
下表展现了传统SQL中使用的许多普通数据类型如何根据上节描述的5条规则决定类型关联。这个表只展现SQLite结束数据类型名的一个子集。注意类型名后括号括起来的数值参数(例如:“VARCHAR(255)”)将被SQLite忽略——(只要不超过宏SQLITE_MAX_LENGTH大小的限制(wayz注:这是一个非常大的数,由编译宏SQLITE_MAX_LENGTH指定))SQLite不对字符串,BLOB或数值数据的长度施加限制。
Example Typenames From The | Resulting Affinity | Rule Used To Determine Affinity |
INT | INTEGER | 1 |
CHARACTER(20) | TEXT | 2 |
BLOB | NONE | 3 |
REAL | REAL | 4 |
NUMERIC | NUMERIC | 5 |
注意,声明类型“FLOATING POINT”将指定INTEGER类型关联而不是REAL类型关联,因为“POINT”结尾的“INT”子串。声明类型“STRING”指定NUMERIC类型关联而不是TEXT。
2.3 Column Affinity Behavior Example
下面的SQL示例说明SQLite如何使用列类型关联,在表中插入数据时进行类型转换。
CREATE TABLE t1(
t TEXT, -- text affinity by rule 2
nuNUMERIC, -- 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
);
-- 数据存储为 TEXT, INTEGER, INTEGER, REAL, TEXT.
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
-- 数据存储为 TEXT, INTEGER, INTEGER, REAL, REAL.
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
-- 数据存储为 TEXT, INTEGER, INTEGER, REAL,INTEGER.
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
-- 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
-- NULL数据不受列类型关联的影响
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.0Comparison Expressions
SQLite 版本 3 包含通常的SQL比较运算符集合,包括“=”, “==”, “<”,“<=”, “>”, “>=”, “!=”, “<>”,“IN”, “NOT IN”, “BETWEEN”, “IS” 和 “ISNOT”。
3.1 Sort Order
比较结果依赖于操作数的存储类型,参见如下规则:- 存储类型为NULL的数据小于任何其它类型的数据(包括另一个存储类型为NULL的数据)。
- INTEGER或REAL存储类型的数据小于任何TEXT或BLOB类型的数据。如果一个INTEGER或REAL数据与另一个INTEGER或REAL数据进行比较,使用数值比较运算符。
- TEXT存储类型数据小于BLOB存储类型数据。两个TEXT类型的数据进行比较,使用内置的collating sequence(wayz注:collating sequence就是一组内置的函数,后面会有介绍)。
- 两个BLOB存储类型数据进行比较时,使用memcmp ()函数进行比较。
3.2 Affinity Of ComparisonOperands
SQLite在进行比较前可能会在INTEGER,REAL和TEXT间转化数据。是否需要转化取决于操作数的类型关联。操作数的类型关联由以下规则决定:- 简单引用一个列数据的表达式具有与列相同的类型关联。注意,如果X和Y.Z是列名,那么+X和+Y.Z用来表示希望用规则决定类型关联的表达式(wayz注:即使用NONE类型关联)。
- 具有“CAST (exp AS type)”形式的表达式,其类型关联与使用type声明的列相同。
- 否则,表达式具有NONE类型关联。
3.3 Type Conversions Prior ToComparison
“应用类型关联”指在无损且可逆的情况下把操作数转化为一个特定的存储类型。按如下顺序指定的规则,在比较前,对比较运算符的操作数应用类型关联:- 如果一个操作数具有INTEGER、REAL或NUMERIC类型关联,另一个操作数具有TEXT或NONE类型关联,则对TEXT或NONE类型关联的操作数应用NUMERIC类型关联。
- 如果一个操作数具有TEXT类型关联,另一个操作数具有NONE类型关联,则对NONE类型关联的操作数应用TEXT类型关联。
- 否则,不对比较运算符的两个操作数应用类型关联。
表达式“a BETWEEN b AND c”被当做两个独立的比较“a >= b AND a<= c”,即使a在两个比较中应用了不同的类型关联。表达式“x IN (SELECT y ...)”中的类型转换,被当做“x=y”处理。表达式“a IN (x, y, z, ...)”等价于“a = +x OR a = +y ORa = +z OR ...”。换句话说,IN的运算符右边的数据(上例中的数据“x”,“y”和“z”)被认为没有类型关联,即使它们是列数据或CAST表达式。
3.4 Comparison Example
CREATE TABLE t1(
a TEXT, -- text affinity
b NUMERIC, -- numeric affinity
c BLOB, -- no affinity
d -- no affinity
);
-- Values will be stored as TEXT, INTEGER, TEXT, andINTEGER respectively
INSERT INTO t1 VALUES('500', '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 of the comparisons are converted totext 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 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 theTEXT 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 withthe 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
All of the result in the example are the same if the comparisons arecommuted - if expressions of the form "a<40" are rewritten as"40>a".
4.0 Operators
所有的算术运算符(+,-,*,/,%,<<,>>,& 和 |)在计算前都会把两个操作数转化为NUMERIC存储类型。即使是有损或不可逆,这种转化也会被执行。如果算术运算符有一个NULL操作数,则运算结果为NULL。如果算术运算符的操作数不能合理的转化为数值,且不是NULL,则操作数被转化为0或0.0。
5.0 Sorting, Grouping and Compound SELECTs
当查询结果使用ORDER BY子句排序时,NULL数据排在最前面,接下来是按数值大小排序的INTERGER和REAL数据,然后是按collatingsequence顺序排序的TEXT数据,最后使用memcmp()排序的BLOB数据。在排序前不进行存储类的转化。
当使用GROUP BY子句对数据进行分组时,除了数值相等的INTEGER和REAL数据外,不同存储类型的数据被认为是不同的。在GROUP BY子句中不应用类型关联。
与SELECT配合使用的UNION,INTERSECT和EXCEPT运算符暗含对数据的比较。在这些比较中也不应用类型关联。
6.0 Collating Sequences
SQLite使用排序序列(collating sequence)或称为排序函数(collating function)比较字符串的大小。SQLite有3个内置的排序函数:BINARY,NOCASE和RTRIM。- BINARY:使用memcmp ()比较字符,忽略字符编码。
- NOCASE:除了在比较前,将26个ASCII英文大写字母转换为小写外,与BINARY相同。注意,这里只对ASCII编码的大写字母进行转换。
- RTRIM:除了忽略尾部空格外,与BINARY相同。
使用sqlite3_create_collation()接口,应用可以注册自己的排序函数。
6.1 Assigning Collating Sequences from SQL
表中每列都有一个与之关联的排序函数。如果没有明确指定,排序函数默认为BINARY。列定义中的COLLATE子句用于指定列的排序函数。
按如下顺序指定的规则,决定在二进制比较运算符(=,<,>,<=,>=,!=,IS,和IS NOT)使用哪个排序函数:- 如果操作数使用COLLATE子句明确指定了排序函数,则使用明确指定的排序函数。如果两个操作数都指定了排序函数,使用左操作数的排序函数。
- 如果某个操作数是列名,则使用列关联的排序函数。如果两个操作数都是列名,则使用左操作列的排序函数。如果列名前有一个或多个一元操作符“+”,则认为是放弃使用此列的排序函数。
- 否则,使用BINARY排序函数。
如果比较运算符的操作数的子表达式中使用了COLLATE运算符,则认为此操作数明确指定了排序函数(规则1)。因此,如果比较表达式中包含了COLLATE操作符,则字符串比较时将使用此操作符指定的排序函数,而不管比较表达式中是否有列名。如果一个比较表达式中包含2个或以上COLLATE运算符子表达式,则使用最左端的COLLATE指定的排序函数,而忽略比较表达式的嵌套方式和括号。
表达式“x BETWEEN y and z”逻辑上等价于有2个比较的表达式“x >= y AND x <= z”,并且这2个比较使用各自的排序函数,就仿佛是2个独立的比较。表达式“x IN (SELECT y ...)”决定排序函数时与表达式“x = y”处理方式相同。表达式“x IN (y, z, ...)”使用x的排序函数。
ORDER BY术语作为SELECT语句的一部分,可以使用COLLATE运算符指定一个排序函数。此时,在排序时将使用指定的排序函数。否则,如果由ORDERBY子句排序的表达式是一个列,则使用列的排序函数;如果排序表达式不是一个列,且没有COLATE子句,则使用BINARY排序函数。
6.2 Collation Sequence Examples
下面的例子确定排序函数。排序函数用于文本比较。各种SQL语句中都可能用到文本比较。注意,在数据类型是NUMERIC、BLOB或NULL时,将不使用文本比较。
CREATE TABLE t1(
x INTEGERPRIMARY KEY,
a, /* collating sequence BINARY*/
b COLLATE BINARY, /* collating sequence BINARY */
c COLLATERTRIM, /* collating sequence RTRIM */
d COLLATENOCASE /* collating sequence NOCASE */
);
/* x a b c d */
INSERT INTO t1 VALUES(1,'abc','abc', 'abc ','abc');
INSERT INTO t1 VALUES(2,'abc','abc', 'abc', 'ABC');
INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');
INSERT INTO t1 VALUES(4,'abc','abc ','ABC', 'abc');
/* Text comparison a=b is performed using the BINARYcollating sequence. */
SELECT x FROM t1 WHERE a = b ORDER BY x;
--result 1 2 3
/* Text comparison a=b is performed using the RTRIMcollating sequence. */
SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;
--result 1 2 3 4
/* Text comparison d=a is performed using the NOCASE collatingsequence. */
SELECT x FROM t1 WHERE d = a ORDER BY x;
--result 1 2 3 4
/* Text comparison a=d is performed using the BINARYcollating sequence. */
SELECT x FROM t1 WHERE a = d ORDER BY x;
--result 1 4
/* Text comparison 'abc'=c is performed using the RTRIMcollating sequence. */
SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
--result 1 2 3
/* Text comparison c='abc' is performed using the RTRIMcollating sequence. */
SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;
--result 1 2 3
/* Grouping is performed using the NOCASE collatingsequence (Values
** 'abc', 'ABC', and 'Abc' are placed in the same group).*/
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
--result 4
/* Grouping is performed using the BINARY collatingsequence. 'abc' and
** 'ABC' and 'Abc' form different groups */
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
--result 1 1 2
/* Sorting or column c is performed using the RTRIMcollating sequence. */
SELECT x FROM t1 ORDER BY c, x;
--result 4 1 2 3
/* Sorting of (c||'') is performed using the BINARYcollating sequence. */
SELECT x FROM t1 ORDER BY (c||''), x;
--result 4 2 3 1
/* Sorting of column c is performed using the NOCASEcollating sequence. */
SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;
--result 2 4 3 1