SQLite剖析(4):数据类型

SQLite剖析(4):数据类型,第1张

概述    本文整理自http://sqlite.org/datatype3.html。     许多SQL数据库引擎(除SQLite之外的各种SQL数据库引擎)使用静态、严格的数据类型。对于静态类型,一个值的数据类型由它的容器,即存储这个值的列来决定。SQLite则使用更加通用的动态类型系统。在SQLite中,一个值的数据类型被关联到这个值本身,而不是它的容器。SQLite的动态类型系统向后兼容一般 本文整理自http://sqlite.org/datatype3.HTML。
许多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-16-LE。
* BLOB: 值是数据的二进制对象,如何输入就如何存储,不改变格式。
注意一个存储类别比一个数据类型更通用。例如INTEGER存储类别就包括6个不同长度的整型数据类型,这在磁盘上是不同的。不过只要INTEGER值从磁盘上读到内存中进行处理,它们会转换成最通用的数据类型(8字节的整型),因此在大多数情况下,对“存储类别”和“数据类型”并不做严格区分,这两个术语可交换使用。
在sqlite 3数据库中,除了INTEGER PRIMARY KEY这一列,任何列都可以存储任何类型的数据。SQL语句中的所有值,不管是嵌入到SQL语句文本中的字面值还是绑定到预先编译好的SQL语句中的参数值,都有一个隐式存储类别。在下述情况中,数据库引擎将在执行查询时,可以让存储的值在数值类型(INTEGER和REAL)和文本类型之间转换。
(1)Boolean数据类型
sqlite没有单独的布尔数据类型。相应的,布尔值被存储为整数0(false)和1(true)。
(2)日期和时间数据类型
sqlite没有单独的日期/时间数据类型。相应的,内建的日期和时间函数能够把日期和时间存储为文本、实数或整数值:
* 文本值为ISO8601字符串("YYYY-MM-DD HH:MM:SS.SSS")。
* 实数值为儒略日数,即从公元前4714年11月24日格林威治正午时刻开始的天数,按公历来算。
* 整数值为Unix时间,即从1970-01-01 00:00:00 UTC开始的秒数。
应用程序可以选择其中的一种格式来存储日期和时间,也可以通过内建的日期和时间函数在这些格式之间转换。
2、列的亲和类型
在sqlite 3中,值被定义为什么类型只和值自身有关,和列没有关系,和变量也没有关系(这有时被称作弱类型)。所有其它的我们所使用的数据库引擎都受静态类型系统的限制,其值的类型是由其所属列的属性决定的,而与值本身无关。为了最大限度的增加sqlite数据库和其他数据库的兼容性,sqlite支持列的“亲和类型”概念。列的亲和类型是指为该列所存储的数据建议一个类型,要注意这个类型是建议而不是强迫。任何列依然是可以存储任何类型的数据的。只是针对某些列,如果有建议类型的话,数据库将优先按所建议的类型存储。这个被优先使用的数据类型称为“亲和类型”。
sqlite 3的每个列可以使用以下亲和类型中的一种:TEXT,NUMERIC,INTEGER,REAL,NONE。
带有文本亲和类型的列可以使用NulL,TEXT或BLOB类型来存储所有数据。如果数值数据被插入到这样的列中,会在存储之前转换成文本类型。
带有数值亲和类型的列可以使用所有五种类型来存储值。当文本数据被插入到数据值型的列中时,如果转换是无损的且可逆的,则文本会被转换成INTEGER或REAL(按优先顺序)。为了在TEXT和REAL之间转换,sqlite尝试无损且可逆地转换文本的开头15个有效十进制数字。如果不能成功转换的话,值则只能按文本类型存储了,而不会被转换成NulL类型或BLOB类型来存储。带有小数点或指数记法的字符串可能看起来像一个浮点字面值,但只要值能表示为一个整数,数值亲和类型将把它转换成一个整数。因此,字符串'3.0e+5'会被转换成整数300000,而不是浮点数300000.0。
使用整数亲和类型的列,其行为与数值亲和类型的列一样。但也有些区别,比如没有小数部分的实数字面值被插入整数亲和类型的列时,它将被转换成整数并按整数类型存储。
使用实数亲和类型的列,其行为与数值亲和类型的列一样。有一个区别就是整数会强制用浮点数来表示。(作为一个内部优化,没有小数部分的小浮点数会当作整数写入磁盘,以占用更少的空间。当读出这个值时会自动转换回浮点数。这个优化在sql级别完全不可见,并且只有通过检测数据库文件的原始比特位才能发现)。
使用NONE亲和类型的列不会优先选择使用哪个类型,在数据被存储前也不会强迫转换它的类型。而是直接按它声明时的原始类型来存储。
(1)列亲和类型的确定
列的亲和类型由列的声明类型(在写SQL语句时指定)来确定,根据以下规则顺序来判断:
1)如果声明类型包含字符中"INT",则被定义为整数亲和类型。
2)如果列的声明类型包含字符串"CHAR","CLOB"或"TEXT"中的某一个,则列具有文本亲和类型。注意VARCHAR类型包含字符串"CHAR",因此被定义为文本亲和类型。
3)如果列的声明类型包含字符串"BLOB",或者没有为列声明数据类型,则列具有NONE亲和类型。
4)如果列的声明类型包含字符串"REAL","FLOA"或"DOUB"中的某一个,则列具有REAL亲和类型。
5)否则,列的亲和类型为NUMERIC。
注意确定列亲和类型的规则顺序非常重要。声明类型为"CHARINT"的列匹配规则1和2,但按顺序优先使用规则1,因此列被定义为整数亲和类型。
(2)亲和类型名称实例
按照上面5条规则,下面例子显示传统sql实现中的各种通用数据类型(CREATE table语句或CAST表达式中的数据类型)怎样被转化成sqlite中的亲和类型。这里只是所有传统数据类型中的一部分,它们能够被sqlite接受。注意跟在类型名后面的括号中的数字参数在sqlite中被忽略。sqlite不在字符串、BLOB对象或数值上强加任何长度限制(除了大的全局sqlITE_MAX_LENGTH限制)。
* INT,tinyint,SMALliNT,MEDIUMINT,BIGINT,UNSIGNED BIG INT,INT2,INT8: 定义为INTEGER亲和类型(按规则1)。
* CHaraCTER(20),VARCHAR(255),varying CHaraCTER(255),NCHAR(255),NATIVE CHaraCTER(70),NVARCHAR(100),TEXT,CLOB: 定义为TEXT亲和类型(按规则2)。
* BLOB,不声明类型: 定义为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。
(3)列亲和类型转化实例

下面的sql示范当值被插入到表中时,sqlite是怎样使用亲和类型来做类型转换的。

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);-- Values stored as TEXT,TEXT.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-- Values stored as TEXT,REAL.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-- Values stored as TEXT,INTEGER.DELETE FROM t1;INSERT INTO t1 VALUES(500,500,500);SELECT typeof(t),typeof(no) FROM t1;text|integer|integer|real|integer-- BLOBs are always stored as BLOBs regardless of column affinity.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 are also unaffected by affinityDELETE FROM t1;INSERT INTO t1 VALUES(NulL,NulL,NulL);SELECT typeof(t),typeof(no) FROM t1;null|null|null|null|null
3、比较表达式
sqlite版本3有常用的sql比较运算符集,包括"=","==","<","<=",">",">=","!=","<>","IN","NOT IN","BETWEEN","IS"和"IS NOT"。
比较的结果取决于 *** 作数的存储类型,根据以下规则来判断:
* NulL类型的值被认为小于任何值(包括另外一个值也是NulL类型时)。
* INTEGER或REAL类型的值小于任何TEXT或BLOB类型的值。当一个INTEGER或REAL与另一个INTEGER或REAL比较时,则按照实际数值来比较。
* TEXT值小于BLOB值。当两个文本值进行比较时,指定的比较序列(默认规则为使用memcmp())会被用来确定比较结果。
* 当两个BLOB值比较时,由memcmp()确定比较结果。
在开始比较前,sqlite尝试着把值在数值类型(整数和实数)和文本之间相互转换。转换是否要进行取决于 *** 作数的亲和类型。 *** 作数亲和类型根据以下规则确定:
* 若表达式只是列值的简单引用,则亲和类型与列的亲和类型一样。注意如果X和Y.Z是列名,则+X和+Y.Z是用来确定亲和类型的表达式。
* "CAST(expr AS type)"形式的表达式,其亲和类型与声明类型为"type"的列相同。
* 否则,表达式为NONE亲和类型。
“应用亲和类型”表示转换一个 *** 作数为特定的存储类型,当且仅当转换是无损且可逆的。应用于比较运算符的 *** 作数的亲和类型根据下面规则来确定:
* 如果一个 *** 作数是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"。例如如果'b'是一个列值,'a'是一个表达式,那么在开始比较前,'b'的亲和性就被转换为'a'的亲和性了。"a IN(x,y,z,...)"形式的表达式等价于"a = +x OR a = +y OR a = +z OR ..."。也就是说,IN运算符右边的值(这里的"x","y"和"z")的类型被认为没有亲和性,即使它们是列值或CAST表达式。
下面是一个比较实例。
CREATE table t1(    a TEXT,-- text affinity    b NUMERIC,-- numeric affinity    c BLOB,-- no affinity    d            -- no affinity);-- Values will be stored as TEXT,and INTEGER respectivelyINSERT INTO t1 VALUES('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、排序、分组和复合式SELECT
当查询结果被一个ORDER BY子句排序时,NulL类型的值排在最前面,然后是INTERGER和REAL值,按数值大小排序。接着时文本值,按指定的比较序列排序。最后是BLOB值,按memcmp()比较来排序。在排序时没有存储类型的转换。
当用GROUP BY子句对值进行分组时,不同存储类型的值是分开来的,但对INTERGER和REAL值,如果它们数值上相等,则被认为是相等的。对GROUP BY子句的结果,不会应用任何亲和类型。
复合式SELECT *** 作符,包括UNION,INTERSECT和EXCEPT在值之间执行隐式的比较,在比较时不使用亲和类型转换,只是按照它们原有类型进行比较。
6、比较序列
当sqlite比较两个字符串时,它使用一个比较序列(即比较函数)来确定哪个字符串更大,或者相等。sqlite有3个内建的比较函数:BINARY,NOCASE和RTRIM。
* BINARY: 使用memcmp()来比较字符串,即进行二进制比较,不管什么文本编码。
* NOCASE: 与BINARY相同,但在比较前26个大写的ASCII字母会被折叠成对应小写字母。注意只有ASCII字母才会做大小写折叠。由于UTF字符表比较庞大,因此sqlite并不尝试做全部UTF字符的大小写折叠。
* RTRIM: 与BINARY相同,但是忽略掉尾部的空格。
每个表的每列都有一个关联的比较函数。如果没有显式地自定义比较函数,则默认使用BINARY。列定义的ColLATE子句用来为列定义可选的比较函数。
要确定二进制比较运算符(=,<,>,<=,>=,!=,IS和IS NOT)使用哪个比较函数,可根据以下规则来判断:
1)如果有一个 *** 作数通过尾部的ColLATE声明符分配了显式的比较函数,则用这个显式的比较函数来做比较,并且在左边 *** 作数上优先使用这个比较函数。
2)如果有一个 *** 作数是列,则在左边 *** 作数上优先使用这个列的比较函数。注意这时带有"+"的列名仍然被认为是一个列名。
3)否则,使用默认的BINARY比较函数来进行比较。
如果不是 *** 作数,而是 *** 作数的某个子表达式通过尾部的ColLATE声明符分配了显式的比较函数,则这个 *** 作数也被认为有一个显式的比较函数。因此,单个ColLATE声明符可用在一个比较表达式任何地方,由它定义的比较函数用于字符串比较,无论这个比较表达式使用了表的哪个列。如果一个表达式中有多个带ColLATE声明的子表达式,则使用最左边的显式比较函数,无论ColLATE声明符嵌套得有多深,也无论表达式有多少嵌套的括号。
表达式"x BETWEEN y and z"在逻辑上与"x >= y AND x <= z"等价,因此使用两个独立比较的比较函数。表达式"x IN (SELECT y ...)"在确定比较函数时与表达式"x = y"的方法相同。 "x IN (y,...)"形式的表达式,其比较函数是x的比较函数。
SELECT语句中的ORDER BY子句也可以使用ColLATE声明符来分配一个比较函数,这个比较函数将被用来进行排序。否则,如果ORDER BY子句排序的表达式是一个列,则使用列的比较函数来确定排序顺序。如果表达式不是一个列且没有ColLATE子句,则使用BINARY比较函数。
下面的例子示范用于确定文本比较结果的比较函数,这可用在各种各样的SQL语句中。注意文本比较并不是必需的,如果是数值、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');INSERT INTO t1 VALUES(2,'ABC');INSERT INTO t1 VALUES(3,'abc ','Abc');INSERT INTO t1 VALUES(4,'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',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
总结

以上是内存溢出为你收集整理的SQLite剖析(4):数据类型全部内容,希望文章能够帮你解决SQLite剖析(4):数据类型所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。

欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/sjk/1166002.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-06-01
下一篇 2022-06-01

发表评论

登录后才能评论

评论列表(0条)

保存