SQLite V3的数据类型

最新推荐文章于 2023-09-19 15:26:55 发布

henrik_he

最新推荐文章于 2023-09-19 15:26:55 发布

阅读量3.4k

点赞数

分类专栏： SQLite 文章标签： SQLite

SQLite 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

查看原文

大多数SQL数据库引擎(我们知道的所有不同于SQLite的SQL数据库引擎)使用静态的、严格的类型。使用静态类型，一个值的数据类型取决于它的容器----值保存的特定列.

SQLite使用更通用的动态类型系统。在SQLite中，值的数据类型仅与值本身有关，而不是它的同期。SQLite的动态类型系统向后兼容于其它数据库引擎的通用静态类型系统，也就是说工作于静态类型数据库的SQL语句也可以以相同方式工作于SQLite。然而，SQLite的动态类型准许做一些传统严格类型数据库不可能做的事。

1 存储类与数据类型

存储在SQLite数据库（或这有该数据库引擎操作的）的每一个值具有下列存储类的一个：

NULL。该值是NULL值。
INTEGER。该值是一个有符号整型，根据该值的量级，以1、2、3、4、6或8位存储。
REAL。该值是一个浮点值，以一个8位IEEE浮点数存储。
TEXT。该值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE或UTF-16LE）存储。
BLOB。该值是一个blob数据，完全按照它的输入存储。

注意，一个存储类比一个数据类型更通用。例如，INTEGER存储类包括6中不同长度的不同整数数据类型。这在磁盘上是不同的。不过一旦INTEGER值被读出磁盘且存入内存处理，它们被转换为最通用的数据类型（8位有符号整数）。因此对于大部分，"存储类型“与”数据类型“不易分辨，并且这两个属于可以交互使用。

SQLite V3数据库中的任何列，除了INTEGER 主键列之外，可以用于存储任何存储类的值。

SQL语句中的所有值，不管它们是嵌入到SQL语句文本中的常量或是绑定到预编译SQL语句中的参数，都具有一个隐式存储类。在下面描述的环境中，执行查询时，数据库引擎可以在数值存储类（INTEGER与REAL）与文本间转换值。

1.1 布尔数据类型

SQLite并不具有一个独立的布尔存储类。反而，布尔值作为整型0（false）与1（true）存储。

1.2 日期与时间数据类型

SQLite并不具有专门留作存储日期与/或时间的存储类。反而，SQLite内嵌的日期与时间函数具有将日期与时间作为TEXT、REAL或INTEGER值的能力：

TEXT作为ISO8601字符串（”YYYY-MM-DD HH:MM:SS.SSS"）。
REAL作为Julian日期数字，根据预期的阳历（Gregorian calendar），从阳历4714B.C.11月24日中午算起的天数。
INTEGER作为Unix时间，从1970年1月1日 00:00:00 UTC的秒数。

应用程序可以选择以任何这些格式存储日期与时间，使用内置日期与时间函数可以在各种格式间自由转换。

2 类型类同

为了最大化SQLite与其它数据库引擎的兼容性，SQLite支持列的”类型类同“的概念。一个列的类型类同是存储于那个列的推荐数据类型。这里重要的概念是该类型是推荐的而不是必须的。任何列依旧可以保存任何类型的数据。仅仅是一些列，提供这个选择，将会优先选择使用一个存储类而不是另一个。列的优先存储类被称作”类同“。

SQLite 3数据库中的每一列都被赋予下列类同的一个：

TEXT
NUMERIC
INTEGER
REAL
NONE

一个具有TEXT类同的列存储所有使用存储类NULL、TEXT或BLOB的数据。如果数值数据插入到具有TEXT类同的列中，它在存储之前被转换成文本格式。

一个具有NUMERIC类同的列可以包含室友所有5个存储类的值。当文本数据插入到一个NUMERIC列，如果该转换是无损且可你的，文本的存储类转换成INTEGER或REAL（以优先的顺序）。对于在TEXT与REAL存储类间的转换，如果该值的前15个重要十进制数字被保存，SQLite认为该转换是无损且可逆的。如果不能能进行TEXT到INTEGER或REAL的无损转换，该值被使用TEXT存储类保存。不去尝试转换成NULL或BLOB值。

字符串或许看起来像一个具有小数点与/或指数标记的浮点常量，不过只要该值可以解释成整数，NUMERIC类同就会将它转换成一个整数。因此，字符串‘3.0e+5’保存在NUMERIC类同列为300000，而不是作为浮点数值300000.0。

使用INTEGER类型的列与NUMERIC类同列的行为一样。INTEGER与NUMERIC类同的不同之处仅仅在CAST表达式中明显。

具有REAL类型的列与具有NUMERIC类同列的行为类似，除了它强制将整数值转换为浮点数表示。（作为内部优化，为了占用更少的空间，保存在REAL类同列中且没有小数副本的小浮点值被以整数形式写入磁盘，而在读出的时候自动转换回浮点数。该优化在SQL层面是不可见的并且只有在测试数据库文件的原始位的时候才会检测到。）

具有类同NONE的列并不优先选择一个存储类而不是另一个，并且并不强迫从一种存储类到另一存储类转换数据。

2.1 决定列类同

列的类同决定于列的声明类型，依照下面显示的顺序规则：

如果声明类型包含字符串”INT"，它被赋予INTEGER类同。
如果列的声明类型包含字符串“CHAR"、”CLOB"或“TEXT”的任何一个，该列具有TEXT类同。注意，类型VARCHAR包含字符串“CHAR"并且因此它被赋予TEXT类同。
如果列的声明类型包含字符串”BLOB“或没有指定任何类型，该列具有类同NONE。
如果列的声明类型包含字符串”REAL"、“FLOA"或”DOUB"的任何一个，该列具有REAL类同。
否则，该类同是NUMERIC。

注意，决定列类同的规则顺序是非常重要的。一个声明类型为“CHARINT”的列会匹配规则1与2，不过第一个规则具有优先权，因此该列类同将为INTEGER。

2.2 类同名实例

下表显示多少个更传统的SQL实现中的通用数据类型名根据前一节的这5个规则转换成类同。该表仅仅显示SQLite可以接受的数据类型的小集合。注意，紧随类型名的括号中的数值参数（如，“VARCHAR(255)”）被SQLite忽略----SQLite并不强加任何长度限制(而是使用大的全局SQLITE_MAX_LENGTH)于字符串、BLOBs或数值的长度。

从CREATE TABLE语句或CAST表达式而来的类型名示例	结果类同	决定类同的规则
INT INTERGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER	1
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) VARYING CAHRACTER(255) NCHAE(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 DATETIME	NUMERIC	5

注意，“FLOATING POINT”的声明类型会给予INTEGER类同，而不是REAL类同，因为“INT"在”POINT"的后面。“STRING”声明类型具有NUMERIC类同，而不是TEXT。

2.3 列类同行为示例

下面的SQL展示SQLite如何在值插入到表的时候使用列类同进行类型转换。

CREATE TABLE t1(
    t  TEXT,     -- text类同，规则2
    nu NUMERIC,  -- numeric类同，规则 5
    i  INTEGER,  -- integer类同，规则 1
    r  REAL,     -- real类同， 规则 4
    no BLOB      -- no类同，规则 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

-- BLOBs 总是存储为 BLOBs 而不管列类同如何。
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 比较表达式

SQLite V3具有SQL比较操作符的通用集合，包括"="、“==”、“<“、"<="、">"、">="、"!="、"<>"、"IN"、"NOT IN"、"BETWEEN"、"IS"与"IS NOT"。

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(expr AS type)"格式的表达式具有与声明”type“的类型的列相同的类同。
否则，表达式具有NONE类同

3.3 类型转换优于比较

”运用类同"意思是转换一个操作数到一个特定的存储类，当且仅当该转换是无损且可逆的。作用于比较操作符的操作数上的类同优于该比较，依据下列的顺序规则：

如果一个操作数具有INTEGER、REAL或NUMERIC类同，且另一个操作数具有TEXT或NONE类同，NUMERIC作用于另一个操作数。
如果一个操作数具有TEXT类同，且另一个操作数具有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 OR a =+z OR ...)"。换句话说，IN操作符右边的值（该例子中的“x”、“y”与“z”）被认为是没有类同的，即使它们碰巧是列值或CAST表达式。

3.4 比较示例



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, and INTEGER respectively
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


如果交换比较，该例子中的所有结果都是相同的。如果“a < 40"格式的表达式重写为”40 > a"。



4 操作符
所有数据操作符（+、-、*、/、%、<<、>>、&与|）转换两个操作符为NUMERIC存储类优于计算。即使是有损失且不可返回的，该转换也被执行。作用于数学操作符上的NULL操作数返回一个NULL结果。作用域数学操作符上的那些不想任何数值或NULL的操作数被转换为0或0.0。



5 排序、分组与复合查询
当查询结果通过ORDER BY分句排序时，保存在类NULL中的值第一个出来，随后是以数值排序的INTEGER与REAL值，接着是在核对序列顺序的TEXT值，最后 是以memcmp()顺序的BLOB值。在该排序之前没有存储类转换发生。



当以GROUP BY分句对值进行分组时，具有不同存储类的值被认为是不同的，除了INTEGER与REAL值之外，如果它们的值相当，它们被认为是相同的。作为GROUP by分居的结果，没有类同作用于任何值。



组合SELECT操作符UNION、INTERSECT与EXCEPT执行隐式的值比较。没有类同作用于与UNION、INTERSECT或EXCEPT相关的隐式比较的比较操作数---值按原始进行比较。



6 核对序列
当SQLite比较两个字符串时，它使用核对序列或核对函数（相同东西的两个单词）去决定那个字符串大或两个字符串是否相等。SQLite具有三个内嵌的核对函数：BINARY、NOCASE与RTRIM。


 
 BINARY -- 使用memcmp()比较两个字符串数据，而不考虑文本编码。
NOCASE -- 与binary相同，除了在比较执行之前，ASCII的26个大小字母字符被转换为它们的小写字母等价物。注意仅仅ASCII字符被大小写转换。由于表达大小的需要，SQLite并不尝试完整的UTF大小写转换。
RTRIM --  与binary相同，除了尾部空间字符被忽略之外。

应用程序可以通过使用sqlite3_create_collation()接口注册额外的核对函数。



6.1 从SQL中分配核对序列
每个表的每一列都具有相关的核对函数。如果没有核对函数显示定义，该核对函数默认为BINARY。列定义的COLLATE分语句用于为列定义一个可选的核对函数。



决定哪个核对函数用于二元比较操作符（=、<、>、<=、>=、!=、IS与IS NOT）的规则如下的顺序显示：


 
 如果操作数具有一个显示的核对函数通过使用后缀COLLATE操作符分配，则该显示核对分配函数用于比较，具有优于左边操作数的核对函数。
如果操作数是一个列，则该列的核对函数优先于左边操作数使用。为了前一个句子的目的，在一个或多个一元“+”操作符前面的列明依旧认为是列明。
否则，BINARY核对函数用于比较。

如果该操作数的任何自表达式使用前缀COLLATE操作符，比较的一个操作数被认为是具有显示核对函数分配(前面的规则1)。因此，如果COLLATE操作符用在比较表达式的任何地方，由那个操作符定义的该核对函数用于字符串比较而不考虑那个表列会是该表达式的一部分。如果两个或多个COLLATE操作符自表达式出现在比较的任何地方，剩下的最明显的核对函数被使用，而不考虑COLLATE操作符在该表达式中有多深且不考虑该表达式如何被加括号。



表达式“x BETWEEN y and z”逻辑上等价于两个比较“x >= y AND x <= z"，并且对待核对函数就像它是两个单独的比较。表达式”x IN (SELECT y...)"通过相同的方式处理，就像表达式“x = y“为了决定核对序列的目的。用于格式”x IN(y, z, ...)"格式的表达式的核对序列是核对序列x。



作为SELECT语句一部分的ORDER BY子句可以通过使用COLLATE操作符分配一个核对序列，这种情况下指定的核对函数用于排序。否则，如果通过OEDER BY子句排序的表达式是一个列，则该列的核对序列用于决定排序顺序。如果该表达式不是一个列并且没有COLLATE子句，则使用BINARY核对序列。



6.2 核对序列示例
下面的例子标识该核对序列，它可以用于决定文本比较的结果，该文本比较被不同的SQL语句执行。注意，在numeric、blob或NULL值的情况下，文本比较或许并不需要，且不用使用核对序列。
CREATE TABLE t1(
    x INTEGER PRIMARY KEY,
    a,                 /* collating sequence BINARY */
    b COLLATE BINARY,  /* collating sequence BINARY */
    c COLLATE RTRIM,   /* collating sequence RTRIM  */
    d COLLATE NOCASE   /* 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 BINARY collating sequence. */
SELECT x FROM t1 WHERE a = b ORDER BY x;
--result 1 2 3

/* Text comparison a=b is performed using the RTRIM collating 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 collating sequence. */
SELECT x FROM t1 WHERE d = a ORDER BY x;
--result 1 2 3 4

/* Text comparison a=d is performed using the BINARY collating sequence. */
SELECT x FROM t1 WHERE a = d ORDER BY x;
--result 1 4

/* Text comparison 'abc'=c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
--result 1 2 3

/* Text comparison c='abc' is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;
--result 1 2 3

/* Grouping is performed using the NOCASE collating sequence (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 collating sequence.  '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 RTRIM collating sequence. */
SELECT x FROM t1 ORDER BY c, x;
--result 4 1 2 3

/* Sorting of (c||'') is performed using the BINARY collating sequence. */
SELECT x FROM t1 ORDER BY (c||''), x;
--result 4 2 3 1

/* Sorting of column c is performed using the NOCASE collating sequence. */
SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;
--result 2 4 3 1

henrik_he

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQLite V3的数据类型

查看原文大多数SQL数据库引擎(我们知道的所有不同于SQLite的SQL数据库引擎)使用静态的、严格的类型。使用静态类型，一个值的数据类型取决于它的容器----值保存的特定列. SQLite使用更通用的动态类型系统。在SQLite中，值的数据类型仅与值本身有关，而不是它的同期。SQLite的动态类型系统向后兼容于其它数据库引擎的通用静态类型系统，也就是
复制链接

扫一扫