DatatypesInSQLiteVersion3中文版SQLite3的數(shù)據(jù)類型

1、datatypes in sqlite version 3絕大多數(shù)數(shù)據(jù)庫引擎都是用靜態(tài)(除 sqlite之外的其他所有sql數(shù)據(jù)庫引擎 ), 硬性的數(shù)據(jù)類型. 使用靜態(tài)數(shù)據(jù)類型的情況下, 數(shù)據(jù)類型會被其容器檢查也就是存儲該值的列.sqlite 使用一個更加通用的類型系統(tǒng). 在 sqlite中, 值的數(shù)據(jù)類型是和值本身相關(guān)聯(lián)的, 而不是其容器決定的. sqlite的動態(tài)數(shù)據(jù)類型向后兼容其他較常見的使用靜態(tài)數(shù)據(jù)類型的數(shù)據(jù)庫系統(tǒng) , 即在靜態(tài)數(shù)據(jù)類型數(shù)據(jù)庫系統(tǒng)上使用的sql語句同樣適用于sqlite. 但是在 sqlite上進行的動態(tài)數(shù)據(jù)類型操作卻不可能適用于傳統(tǒng)的靜態(tài)數(shù)據(jù)類型的數(shù)據(jù)庫系統(tǒng).1.0

2、storage classes and datatypes每一個存儲在sqlite(或者被數(shù)據(jù)庫引擎操縱)數(shù)據(jù)庫系統(tǒng)中的值必定屬于以下幾個類型中的一個 :null. 值為 nullinteger . 有符號的整形數(shù)值, 按數(shù)值大小以1,2,3,41,2,3,4,6 或 8 字節(jié)存儲 .real . 浮點數(shù)值 , 以 8 字節(jié) ieee標(biāo)準(zhǔn)浮點數(shù)存儲.text . 文本字符串 , 以數(shù)據(jù)庫的字符編碼方式存儲(utf-8, utf-16be或者 utf-16le).blob . 該值是一個blob數(shù)據(jù) , 將以與輸入時一致的二進制碼存儲.存儲類型相比數(shù)據(jù)類型是更加通用的. 比如說 integer

3、存儲類包含6 種不同長度的整形數(shù)據(jù)類型 . 雖然以上6 種類型是以不同方式存儲在磁盤上, 但 integer 數(shù)據(jù)一旦從磁盤里讀取到內(nèi)存中用于處理是, 所有的 6種類型都會被轉(zhuǎn)換為最通用的數(shù)據(jù)類型(8字節(jié)有符號整數(shù)). 因此大部分情況下, “存儲類型 ” 與” 數(shù)據(jù)類型 ” 并沒有什么分別, 可以相互替換表示.sqlite3中的任何列 , 除 integerprimary key之外 , 可以以任何存儲類型(storage class)存儲一個值 .sql語句中所有的值, 無論他們是以純文本方式嵌入到sql語句中還是以預(yù)編譯方式綁定到sql語句的參數(shù)都有一個隱含的存儲類型. 在下述的氛圍下,

4、數(shù)據(jù)庫引擎可能會在查詢執(zhí)行過程中將值在數(shù)值型存儲類型(integer和 real) 和文本型存儲類型(text) 之間轉(zhuǎn)換 .1.1 boolean datatypesqlite沒有單獨的布爾存儲類型. 在 sqlite 中, 布爾值 , 真被存儲為整形數(shù)值1, 假被存儲為整形數(shù)值 0.1.2 date and time datatypesqlite沒有單獨的日期和時間存儲類型. 作為替代的是 ,sqlite內(nèi)建的日期和時間函數(shù)可用于處理以 text, real 或 integer方式存儲的日期:text 以 iso8601標(biāo)準(zhǔn)表示的字符串( “ yyyy-mm- dd hh:mm:ss.ss

5、s” ).real以 julian day, 公元前 4714 年 11 月 24 日至日期所經(jīng)歷的天數(shù). integer以 unix 時間方式表示 , 即 1970-01-01 00:00:00 utc 至日期所經(jīng)歷的秒數(shù).在應(yīng)用中可以自由地使用以上3種方式存儲日期和時間, 也可以用內(nèi)建的日期和時間函數(shù)在它們之間做任意的轉(zhuǎn)換.2.0 type affinity (親和類型 )為了使 sqlite更加兼容其他的數(shù)據(jù)庫引擎, sqlite支持列 ” 親和類型 ” 概念 . 所謂的列的親和類型就是在該列中推薦使用的存儲類型. 必須注意的是, 這里只是推薦的類型, 而非必須使用的類型 . 任何列依然

6、可以存儲任何類型的數(shù)據(jù). 這只是說 , 在一些列中我們會給予一些選擇, 這將使得我們會優(yōu)先使用一種存儲類型. 優(yōu)先使用的存儲類型被叫做該列的” 親和(affinity)”.sqlite3的每一列都會被傳入如下幾個類型親和:textnumericintegerrealnone具有 text親和類型的列中的所有數(shù)據(jù)使用null, text 和 blob存儲類型 . 如果將數(shù)值型的數(shù)據(jù)插入到具有text親和類型的列中, 那么在該值被存儲前將會被轉(zhuǎn)換為字符型.具有 numeric親和類型可能含有所有5 種存儲類型的值. 當(dāng)把文字信息插入到numeric列中, 在不損失精度且轉(zhuǎn)換可逆的情況下, 該段文字

7、的存儲類型將被轉(zhuǎn)換至integer 或 real類型 . 在轉(zhuǎn)換 text和 real時, 如果字段的前15 位十進制數(shù)能被保留的話, 那么 sqlite將認(rèn)為該轉(zhuǎn)換時無損和可逆的. 如果將 text無損轉(zhuǎn)換為integer 或 real的過程被判斷為不可能完成的話 , 那么該字段信息將會用text存儲類型存入數(shù)據(jù)庫. sqlite在任何情況下都不會嘗試轉(zhuǎn)換 null或 blob型數(shù)據(jù)信息 .如果一個字符串是有小數(shù)點的浮點數(shù)有指數(shù)符號的浮點數(shù), 但只要該值能被表示成一個整型數(shù)據(jù) , 則 numeric親和將會將其轉(zhuǎn)換成一個整型數(shù)值. 因此 , 3.0e+5在具有 numeric親和的列下將被存

8、儲為300000, 而不是值為300000.0 浮點數(shù) .具有 integer 親和類型的列與具有numeric親和類型的列的規(guī)則一直. integer 和 numeric親和類型的唯一明顯的區(qū)別是在cast表達式中 (譯者注 , 請參閱 sql as understood by sqlite章節(jié)中的 expression 小節(jié) ).具有 real親和類型的列與numeric親和類型表現(xiàn)一致, 除了它會強制把整型數(shù)據(jù)轉(zhuǎn)換至浮點形式 (一個內(nèi)部的優(yōu)化是, 將絕對值很小的浮點數(shù)存入具有real親和類型的列時, 為了節(jié)省數(shù)據(jù)占用空間, 該值將以整型寫入到磁盤中, 在讀出時會被自動轉(zhuǎn)換回浮點數(shù). 該優(yōu)

9、化在sql層面上是完全不可見的, 只能在檢測原始數(shù)據(jù)庫文件比特序列時被發(fā)現(xiàn)).具有 none親和類型的列不會優(yōu)先使用任何一個存儲類型, 也不會強制地將一種數(shù)據(jù)存儲類型轉(zhuǎn)換為其他存儲類型.2.1 determination of column affinity一個字段的親和類型將會被該字段聲明時的類型所決定, 按順序地根據(jù)以下規(guī)則將可以得出親和類型 :1. 如果聲明時的類型包含字符串” int” , 將會被賦予integer親和類型 .2. 如果聲明是的類型包含字符串” char ” , “clob ” 或者 ” text ” , 那么該字段將被賦予text親和類型 . 注意因為varchar

10、包含 ”char ”, 故也被賦予text親和類型 .3. 如果聲明是的類型包含字符串” blob ” 或者 沒有指定類型, 那么該字段將擁有” none ”親和類型 .4. 如果聲明是的類型包含字符串” real ” , “floa ” 或者 ” doub” , 那么該字段將擁有real親和類型 .5. 否則 , 親和類型將是numeric.注意以上的親和類型順序是是十分重要的. 如果一個字段的聲明類型是”charint ”, 它將符合規(guī)則 1 和規(guī)則 2, 但是規(guī)則1 的優(yōu)先權(quán)高于規(guī)則2, 所以該字段的親和類型將是integer.2.2 affinity name examples下表展示

11、了傳統(tǒng)sql實現(xiàn)的通用數(shù)據(jù)類型在上面5 條規(guī)則的作用下是如何被轉(zhuǎn)換成相應(yīng)的親和類型的 . 下表只展示了sqlite支持的數(shù)據(jù)類型名的一小部分. 注意類型名后括號里的數(shù)值型的參數(shù)聲明(例如 : “varchar(255)”)將被 sqlite忽略 sqlite并不接受字符串, blobs和數(shù)值型數(shù)據(jù)的強制的長度限制(除了全局的sqlite_max_length 限制規(guī)則 ).example typenames from the create table statement or cast expression resulting affinity rule used to determine a

12、ffinity 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 specifiednone 3 real double double precision float real 4 numeric decimal(10,5)

13、boolean date datetime numeric 5 注意 : “floating point”將被給予integer 親和類型 , 而不是 real親和類型 , 因為 ”point ”結(jié)尾有 ”int”子串 . 聲明類型 “string ”將具有 numeric親和類型 , 而不是 text.2.3 column affinity behavior example以下 sql語句描繪了在向表中插入數(shù)據(jù)時sqlite 如何應(yīng)用字段親和類型進行類型轉(zhuǎn)換.create table t1(t text, - text affinity by rule 2nunumeric, - numer

14、ic affinity by rule 5i integer, - integer affinity by rule 1r real, - real affinity by rule 4noblob - no affinity by rule 3);- values stored as 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

15、 t1;text|integer|integer|real|text- values stored as 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- values stored as text, integer, integer,

16、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 are always stored as blobs regardlessof column affinity.delete from t1;insert into t1 values(x0500, x0500, x0500,

17、x0500, x0500);select typeof(t), typeof(nu), typeof(i),typeof(r), typeof(no) from t1;blob|blob|blob|blob|blob- nulls are also unaffected by affinitydelete 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|

18、null3.0 comparison expressionssqlite 版本 3 具有一般的sql比較運算符集, 包括 =,=, , ,=, !=, , in,not in, between, is, and isnot, .3.1 sort order比較的結(jié)果取決于操作數(shù)的存儲類型, 根據(jù)以下規(guī)則 :null存儲類型的值將比其他任何值都小(包括其他同樣是null存儲類型的值).integer或 real值比任何 text或 blob值小 . 當(dāng)一個 integer 或 real跟另一個integer或real值比較時 , 那么將進行數(shù)值比較.text值比 blob值小 . 當(dāng)兩個 tex

19、t值比較時將會使用一個合適的整理序列(譯者注 , 可能是ascii或其他字符編碼方式)去比較然后得出結(jié)果.兩個 blob值比較時 , 結(jié)果將由memcmp() 函數(shù)得出 .3.2 affinity of comparison operandssqlite 在比較值時也許會在integer,real 或 text存儲類型之間轉(zhuǎn)換. 嘗試轉(zhuǎn)換操作是否會發(fā)生依賴于操作數(shù)的親和類型. 操作數(shù)親和類型被以下規(guī)則決定:一個簡單地引用字段值的表達式將與該字段具有相同的親和類型. 比如 : 若 x和 y.z是字段名稱 , 那么 +x 和+y.z將被認(rèn)為是偵測親和類型的表達式.具有 ”cast(expras t

20、ype) ”形式的表達式將于聲明字段時所用的”type ”一樣的親和類型.3.3 type conversions prior to comparison“ apply affinity”表示在且僅在該轉(zhuǎn)換是無損和可逆的情況下, 才將一個操作數(shù)轉(zhuǎn)換為一個特定的存儲類型 . 將根據(jù)以下規(guī)則在比較操作進行之前親和類型將被優(yōu)先賦予給比較操作符:如果一個操作具有integer,real 或者 numeric親和類型 , 另一個操作數(shù)有text或 none親和類型 , 那么 numeric親和類型將被應(yīng)用于另一個操作數(shù).如果一個操作數(shù)具有text親和類型 , 另一個操作數(shù)有none親和類型 , 那么 t

21、ext親和類型將被應(yīng)用于另一個操作數(shù).否則 , 不會應(yīng)用任何親和類型, 而且兩個操作數(shù)將按原值比較.“ a between b and c ”表達式將被當(dāng)成2 個分開的二進制比較運算” a= and a=c ” , 即使在每個比較中應(yīng)用了不同的親和類型規(guī)則. 以”x in (select y.)”形式的比較中的數(shù)據(jù)類型轉(zhuǎn)換將被轉(zhuǎn)換為 ”x=y”. 表達式 ”a in(x, y, z, .)”與”a = +x or a = +y or a = +z or.”. 換句話說 , in 操作符右邊的值 (本例中的 x, y, and z) 將被認(rèn)為是無親和類型的, 即使它們必須是字段值或在 cast表

22、達式下 .3.4 comparison examplecreate table t1(atext, - text affinitybnumeric, - numeric affinitycblob, - no affinityd - no affinity);- values will be stored as text, integer, text,and integer respectivelyinsert into t1 values(500, 500, 500, 500);select typeof(a), typeof(b), typeof(c),typeof(d) from t1;

23、text|integer|text|integer- because column a has text affinity,numeric values on the- right-hand side of the comparisons areconverted 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-handoperands but since- they are already text this

24、is a no-op; noconversions occur.select a 40, a 60, a 600from t1;0|1|1- column b has numeric affinity andso numeric affinity is applied- to the operands on the right. since the operands are already numeric,- the application of affinity is a no-op; noconversions occur. all- values are compared numeric

25、ally.select b 40, b 60, b 600 from t1;0|0|1- numeric affinity is applied to operands onthe right, converting them- from text to integers. then a numeric comparison occurs.select b 40, b 60, b 600from t1;0|0|1- no affinity conversions occur. right-hand side values all have- storage class integer whic

26、h are always lessthan 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 600from t1;0|1|1- no affinity conversions occur. right-hand side values all have- storage class integer which comparenumerically

27、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 600from t1;1|1|1示例即使在比較運算中被改變形式, 其所有結(jié)果也將保持不變比如說 aa.4.0 operators所有的數(shù)學(xué)運算符(+, -, *, /, %, , &,

28、and |) 在數(shù)據(jù)被讀出之前將數(shù)據(jù)造型為numeric存儲類型 . 即使造型過程是損失精度的, 也會繼續(xù) . 在數(shù)學(xué)運算中使用一個null值將得到一個 null結(jié)果 . 在數(shù)學(xué)運算中, 既不是數(shù)值也不是null的數(shù)據(jù)將被轉(zhuǎn)換為0 或者 sorting, grouping and compound selects當(dāng)查詢結(jié)果被order by 控制排序 , 存儲類型為null的數(shù)據(jù)排在最前面, 其后是 integer和real型按數(shù)值大小依次排列, 其后是根據(jù)整理順序(collating sequence)排列的 text數(shù)據(jù) , 最后是根據(jù)memcmp() 排列的 blob數(shù)據(jù)

29、 . 排序時不會發(fā)生存儲類型轉(zhuǎn)換.使用 group by 群組不同存儲類型的數(shù)據(jù)時不同的存儲類型被認(rèn)為是互異的, 但是 integer和 real數(shù)據(jù)如果數(shù)值上是相等的才被認(rèn)為是相等的. 在 group的結(jié)果中不會應(yīng)用親和類型規(guī)則 .復(fù)合 select 運算符 union, intersect 和 except 將在數(shù)據(jù)之間進行隱含的比較. 親和類型規(guī)則不會被在有union, intersect, 或 except 的比較操作運算中應(yīng)用所有的數(shù)據(jù)按原值比較.6.0 collating sequencessqlite 在比較兩個字符串時, 會用整理序列(collatingsequence) 或整

30、理函數(shù) (collating function)比較哪個字符串更大或二者是否相等. sqlite有 3 個內(nèi)建的整理函數(shù): binary, nocase, rtrim.binary - 使用 memcmp() 比較字符串?dāng)?shù)據(jù), 忽略字符編碼 .nocase - 與 binary 相似 , 除了 ascii里 26 個大寫字母在比較之前被轉(zhuǎn)換為相同字母的小寫形式 . 注意只有ascii字符才會進行轉(zhuǎn)換. sqlite不會嘗試做完整的utf大小寫轉(zhuǎn)換 , 因為表尺寸的限制 ?rtrim - 與 binary 相似 , 但會忽略尾部的空格字符.應(yīng)用程序可以使用sqlite3_create_colla

31、tion() 接口注冊自定義的整理函數(shù).6.1 assigning collating sequences from sql每個表格的每個字段都有一個相關(guān)聯(lián)的整理函數(shù). 如果沒有硬性定義一個整理函數(shù), 那么默認(rèn)使用 binary. 字段定義里的collate 子句是用來定義字段的替代整理函數(shù)的.二元比較操作符(=, , =, !=, is, and is not) 使用哪個整理函數(shù)的規(guī)則如下(按列出的先后順序具有相同的優(yōu)先級):1. todo6.2 collation sequence examples下面的例子用于說明通過各種類型sql語句執(zhí)行時確定的整理序列的文本比較結(jié)果. 有時文字比較是

32、必須的. 在數(shù)值型 , blob和 null數(shù)據(jù)上時不會是用整理序列的.create table t1(xinteger primary key,a, /* collatingsequence binary */bcollate binary, /* collating sequencebinary */ccollate rtrim, /* collating sequencertrim */dcollate nocase /* collating sequencenocase */);/* x a b c d */insert into t1 values(1,abc,abc, abc ,a

33、bc);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 thebinary collating sequence. */select x from t1 where a = b order by x;-result 1 2 3/* text comparison a=b is performed using thertrim collating sequence. */select x from t1 where a = b collate rtrim orderby x;-result 1 2 3 4/* text comparison d=a is performed using thenocase collating sequence. */select x from t1 where d = a order by x;-result 1 2 3 4/* text comparison a=