`
chinamming
  • 浏览: 140630 次
  • 性别: Icon_minigender_1
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

SQLite剖析(4)：数据类型

 
阅读更多
本文整理自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, INTEGER, 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是怎样使用亲和类型来做类型转换的。

[sql] view plaincopyprint?
  1. CREATETABLEt1(
  2. tTEXT,--textaffinitybyrule2
  3. nuNUMERIC,--numericaffinitybyrule5
  4. iINTEGER,--integeraffinitybyrule1
  5. rREAL,--realaffinitybyrule4
  6. noBLOB--noaffinitybyrule3
  7. );
  9. --ValuesstoredasTEXT,INTEGER,INTEGER,REAL,TEXT.
  10. INSERTINTOt1VALUES('500.0','500.0','500.0','500.0','500.0');
  11. SELECTtypeof(t),typeof(nu),typeof(i),typeof(r),typeof(no)FROMt1;
  12. text|integer|integer|real|text
  14. --ValuesstoredasTEXT,INTEGER,INTEGER,REAL,REAL.
  15. DELETEFROMt1;
  16. INSERTINTOt1VALUES(500.0,500.0,500.0,500.0,500.0);
  17. SELECTtypeof(t),typeof(nu),typeof(i),typeof(r),typeof(no)FROMt1;
  18. text|integer|integer|real|real
  20. --ValuesstoredasTEXT,INTEGER,INTEGER,REAL,INTEGER.
  21. DELETEFROMt1;
  22. INSERTINTOt1VALUES(500,500,500,500,500);
  23. SELECTtypeof(t),typeof(nu),typeof(i),typeof(r),typeof(no)FROMt1;
  24. text|integer|integer|real|integer
  26. --BLOBsarealwaysstoredasBLOBsregardlessofcolumnaffinity.
  27. DELETEFROMt1;
  28. INSERTINTOt1VALUES(x'0500',x'0500',x'0500',x'0500',x'0500');
  29. SELECTtypeof(t),typeof(nu),typeof(i),typeof(r),typeof(no)FROMt1;
  30. blob|blob|blob|blob|blob
  32. --NULLsarealsounaffectedbyaffinity
  33. DELETEFROMt1;
  34. INSERTINTOt1VALUES(NULL,NULL,NULL,NULL,NULL);
  35. SELECTtypeof(t),typeof(nu),typeof(i),typeof(r),typeof(no)FROMt1;
  36. null|null|null|null|null
３、比较表达式
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表达式。
下面是一个比较实例。
[sql] view plaincopyprint?
  1. CREATETABLEt1(
  2. aTEXT,--textaffinity
  3. bNUMERIC,--numericaffinity
  4. cBLOB,--noaffinity
  5. d--noaffinity
  6. );
  8. --ValueswillbestoredasTEXT,INTEGER,TEXT,andINTEGERrespectively
  9. INSERTINTOt1VALUES('500','500','500',500);
  10. SELECTtypeof(a),typeof(b),typeof(c),typeof(d)FROMt1;
  11. text|integer|text|integer
  13. --Becausecolumn"a"hastextaffinity,numericvaluesonthe
  14. --right-handsideofthecomparisonsareconvertedtotextbefore
  15. --thecomparisonoccurs.
  16. SELECTa<40,a<60,a<600FROMt1;
  17. 0|1|1
  19. --Textaffinityisappliedtotheright-handoperandsbutsince
  20. --theyarealreadyTEXTthisisano-op;noconversionsoccur.
  21. SELECTa<'40',a<'60',a<'600'FROMt1;
  22. 0|1|1
  24. --Column"b"hasnumericaffinityandsonumericaffinityisapplied
  25. --totheoperandsontheright.Sincetheoperandsarealreadynumeric,
  26. --theapplicationofaffinityisano-op;noconversionsoccur.All
  27. --valuesarecomparednumerically.
  28. SELECTb<40,b<60,b<600FROMt1;
  29. 0|0|1
  31. --Numericaffinityisappliedtooperandsontheright,convertingthem
  32. --fromtexttointegers.Thenanumericcomparisonoccurs.
  33. SELECTb<'40',b<'60',b<'600'FROMt1;
  34. 0|0|1
  36. --Noaffinityconversionsoccur.Right-handsidevaluesallhave
  37. --storageclassINTEGERwhicharealwayslessthantheTEXTvalues
  38. --ontheleft.
  39. SELECTc<40,c<60,c<600FROMt1;
  40. 0|0|0
  42. --Noaffinityconversionsoccur.ValuesarecomparedasTEXT.
  43. SELECTc<'40',c<'60',c<'600'FROMt1;
  44. 0|1|1
  46. --Noaffinityconversionsoccur.Right-handsidevaluesallhave
  47. --storageclassINTEGERwhichcomparenumericallywiththeINTEGER
  48. --valuesontheleft.
  49. SELECTd<40,d<60,d<600FROMt1;
  50. 0|0|1
  52. --Noaffinityconversionsoccur.INTEGERvaluesontheleftare
  53. --alwayslessthanTEXTvaluesontheright.
  54. SELECTd<'40',d<'60',d<'600'FROMt1;
  55. 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, z, ...)"形式的表达式，其比较函数是x的比较函数。
SELECT语句中的ORDER BY子句也可以使用COLLATE声明符来分配一个比较函数，这个比较函数将被用来进行排序。否则，如果ORDER BY子句排序的表达式是一个列，则使用列的比较函数来确定排序顺序。如果表达式不是一个列且没有COLLATE子句，则使用BINARY比较函数。
下面的例子示范用于确定文本比较结果的比较函数，这可用在各种各样的SQL语句中。注意文本比较并不是必需的，如果是数值、BLOB或NULL值，则无需比较函数。
[cpp] view plaincopyprint?
  1. CREATETABLEt1(
  2. xINTEGERPRIMARYKEY,
  3. a,/*collatingsequenceBINARY*/
  4. bCOLLATEBINARY,/*collatingsequenceBINARY*/
  5. cCOLLATERTRIM,/*collatingsequenceRTRIM*/
  6. dCOLLATENOCASE/*collatingsequenceNOCASE*/
  7. );
  8. /*xabcd*/
  9. INSERTINTOt1VALUES(1,'abc','abc','abc','abc');
  10. INSERTINTOt1VALUES(2,'abc','abc','abc','ABC');
  11. INSERTINTOt1VALUES(3,'abc','abc','abc','Abc');
  12. INSERTINTOt1VALUES(4,'abc','abc','ABC','abc');
  14. /*Textcomparisona=bisperformedusingtheBINARYcollatingsequence.*/
  15. SELECTxFROMt1WHEREa=bORDERBYx;
  16. --result123
  18. /*Textcomparisona=bisperformedusingtheRTRIMcollatingsequence.*/
  19. SELECTxFROMt1WHEREa=bCOLLATERTRIMORDERBYx;
  20. --result1234
  22. /*Textcomparisond=aisperformedusingtheNOCASEcollatingsequence.*/
  23. SELECTxFROMt1WHEREd=aORDERBYx;
  24. --result1234
  26. /*Textcomparisona=disperformedusingtheBINARYcollatingsequence.*/
  27. SELECTxFROMt1WHEREa=dORDERBYx;
  28. --result14
  30. /*Textcomparison'abc'=cisperformedusingtheRTRIMcollatingsequence.*/
  31. SELECTxFROMt1WHERE'abc'=cORDERBYx;
  32. --result123
  34. /*Textcomparisonc='abc'isperformedusingtheRTRIMcollatingsequence.*/
  35. SELECTxFROMt1WHEREc='abc'ORDERBYx;
  36. --result123
  38. /*GroupingisperformedusingtheNOCASEcollatingsequence(Values
  39. **'abc','ABC',and'Abc'areplacedinthesamegroup).*/
  40. SELECTcount(*)FROMt1GROUPBYdORDERBY1;
  41. --result4
  43. /*GroupingisperformedusingtheBINARYcollatingsequence.'abc'and
  44. **'ABC'and'Abc'formdifferentgroups*/
  45. SELECTcount(*)FROMt1GROUPBY(d||'')ORDERBY1;
  46. --result112
  48. /*SortingorcolumncisperformedusingtheRTRIMcollatingsequence.*/
  49. SELECTxFROMt1ORDERBYc,x;
  50. --result4123
  52. /*Sortingof(c||'')isperformedusingtheBINARYcollatingsequence.*/
  53. SELECTxFROMt1ORDERBY(c||''),x;
  54. --result4231
  56. /*SortingofcolumncisperformedusingtheNOCASEcollatingsequence.*/
  57. SELECTxFROMt1ORDERBYcCOLLATENOCASE,x;
  58. --result2431

更多0
分享到：
| SQLite剖析(3)：C/C++接口介绍
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics