sqlite3的數(shù)據(jù)類型詳解

1、sqlite3中的數(shù)據(jù)類型大多數(shù)的數(shù)據(jù)庫(kù)引擎（到現(xiàn)在據(jù)我們所知的除了sqlite的每個(gè)sql數(shù)據(jù)庫(kù)引擎）都使用靜態(tài)的、剛性的類型，使用靜態(tài)類型，數(shù)據(jù)的類型就由它的容器決定，這個(gè)容器是這個(gè)指被存放的特定列。Sqlite使用一個(gè)更一般的動(dòng)態(tài)類型系統(tǒng)，sqlite中，值的數(shù)據(jù)類型跟值本身相關(guān),而不是與它的容器相關(guān)oSqlite的動(dòng)態(tài)類型系統(tǒng)和其他數(shù)據(jù)庫(kù)的更為一般的靜態(tài)類型系統(tǒng)相兼容，但同時(shí)，sqlite中的動(dòng)態(tài)類型允許它能做到一些傳統(tǒng)剛性類型數(shù)據(jù)庫(kù)所不可能做到的事。1存儲(chǔ)類和數(shù)據(jù)類型每個(gè)存放在sqlite數(shù)據(jù)庫(kù)中（或者由這個(gè)數(shù)據(jù)庫(kù)引擎操作）的值都有下面中的一個(gè)存儲(chǔ)類：NULL，值是NULLINTEG

2、ER，值是有符號(hào)整形，根據(jù)值的大小以1,2,3,4,6或8字節(jié)存放REAL，值是浮點(diǎn)型值，以8字節(jié)IEEE浮點(diǎn)數(shù)存放TEXT，值是文本字符串，使用數(shù)據(jù)庫(kù)編碼（UTF-8,UTF-16BE或者UTF-16LE）存放BLOB，只是一個(gè)數(shù)據(jù)塊，完全按照輸入存放（即沒有準(zhǔn)換）從上可以看出存儲(chǔ)類比數(shù)據(jù)類型更一般化。比如INTEGER存儲(chǔ)類，包括6中不同長(zhǎng)度的不同整形數(shù)據(jù)類型，這在磁盤上造成了差異。但是只要INTEGER值被從磁盤讀出進(jìn)入到內(nèi)存進(jìn)行處理，它們被轉(zhuǎn)換成最一般的數(shù)據(jù)類型（8-字節(jié)有符號(hào)整形）oSqlitev3數(shù)據(jù)庫(kù)中的任何列，除了整形主鍵列，可以用于存儲(chǔ)任何一個(gè)存儲(chǔ)列的值。sql語(yǔ)句中的中所有

3、值，不管它們是嵌入在sql文本中或者是作為參數(shù)綁定到一個(gè)預(yù)編譯的sql語(yǔ)句，它們的存儲(chǔ)類型都是未定的。在下面描述的情況中，數(shù)據(jù)庫(kù)引擎會(huì)在查詢執(zhí)行過(guò)程中在數(shù)值（numeric）存儲(chǔ)類型（INTEGER和REAL）和TEXT之間轉(zhuǎn)換值。1.1布爾類型Sqlite沒有單獨(dú)的布爾存儲(chǔ)類型，它使用INTEGER作為存儲(chǔ)類型，0為false，1為true1.2Date和TimeDatatypeSqlite沒有另外為存儲(chǔ)日期和時(shí)間設(shè)定一個(gè)存儲(chǔ)類集，內(nèi)置的sqlite日期和時(shí)間函數(shù)能夠?qū)⑷掌诤蜁r(shí)間以TEXT，REAL或INTEGER形式存放TEXT作為IS08601字符串（YYYY-MM-DDHH:MM:SS

4、.SSS）REAL從格林威治時(shí)間11月24日，4174B.C中午以來(lái)的天數(shù)INTEGER從1970-01-0100:00:00UTC以來(lái)的秒數(shù)程序可以任意選擇這幾個(gè)存儲(chǔ)類型去存儲(chǔ)日期和時(shí)間，并且能夠使用內(nèi)置的日期和時(shí)間函數(shù)在這些格式間自由轉(zhuǎn)換2.0類型近似為了使sqlite和其他數(shù)據(jù)庫(kù)間的兼容性最大化，sqlite支持列上“類型近似”的觀點(diǎn)，列的類型近似指的是存儲(chǔ)在列上數(shù)據(jù)的推薦類型。這里必須記住一點(diǎn)，這個(gè)類型是被推薦，而不是必須的。任何列仍然能存儲(chǔ)任意類型的數(shù)據(jù)。只是一些列，給予選擇的話，將會(huì)相比于其他的一些類型優(yōu)選選擇一些存儲(chǔ)類型，這個(gè)列優(yōu)先選擇的存儲(chǔ)類型被稱為它的“近似”。每個(gè)sqlit

5、e3數(shù)據(jù)庫(kù)中的列都被賦予下面類型近似中的一種：TEXTNUMERICINTEGERREALNONE具有TEXT近似的列可以用NULL,TEXT或者BLOB類型存儲(chǔ)數(shù)據(jù)。如果數(shù)值數(shù)據(jù)被插入到具有TEXT近似的列，在被存儲(chǔ)前被轉(zhuǎn)換為文本形式一個(gè)有NUMERIC近似的列可以使用1中的所有5中存儲(chǔ)類來(lái)存儲(chǔ)數(shù)據(jù)。當(dāng)文本數(shù)據(jù)被存放到NUMERIC近似的列中，這個(gè)文本的存儲(chǔ)類被轉(zhuǎn)換到INTEGER或REAL（根據(jù)優(yōu)先級(jí)順序），如果這個(gè)轉(zhuǎn)換是無(wú)損的話。對(duì)于TEXT和REAL存儲(chǔ)類間的轉(zhuǎn)換，如果數(shù)據(jù)的前15位的被保留的話sqlite就認(rèn)為這個(gè)轉(zhuǎn)換是無(wú)損的、可反轉(zhuǎn)的。如果TEXT到INTEGER或REAL的轉(zhuǎn)換不

6、可避免的會(huì)造成損失，那么數(shù)據(jù)將使用TEXT存儲(chǔ)類存儲(chǔ)。不會(huì)企圖去轉(zhuǎn)換NULL或BLOB值。一個(gè)字符串可能看起來(lái)像浮點(diǎn)數(shù)據(jù)，有小數(shù)點(diǎn)或指數(shù)符號(hào)，但是只要這個(gè)數(shù)據(jù)可以使用整形存放，NUMERIC近似就會(huì)將它轉(zhuǎn)換到整形。比如，字符串30e+5存放到一個(gè)具有NUMERIC近似的列中，被存為300000,而不是浮點(diǎn)型值300000.0。具有INTEGER近似的列和具有NUMERIC近似的列表現(xiàn)相同。它們之間的差別僅處于轉(zhuǎn)換描述上。具有REAL近似的列和具有NUMERIC近似的列一樣，除了它將整形數(shù)據(jù)轉(zhuǎn)換成浮點(diǎn)型形式。具有NONE近似的列不會(huì)優(yōu)先選擇一個(gè)存儲(chǔ)列，也不會(huì)強(qiáng)制將數(shù)據(jù)從一個(gè)存儲(chǔ)類轉(zhuǎn)換到另外一個(gè)類

7、。2.1列近似的決定因素列的近似由這個(gè)列的聲明類型所決定，根據(jù)下面的順序的規(guī)則：1如果聲明類型包含INT字符串，那么這個(gè)列被賦予INTEGER近似2如果這個(gè)列的聲明類型包含CHAR，CLOB,或者TEXT中的任意一個(gè)，那么這個(gè)列就有了TEXT近似。注意類型VARCHAR包含了CHAR字符串，那么也就被賦予了TEXT近似3如果列的聲明類型中包含了字符串BLOB或者沒有為其聲明類型，這個(gè)列被賦予NONE近似4其他的情況，列被賦予NUMERIC近似上面規(guī)則額順序?qū)τ跊Q定列的近似很重要。一個(gè)列的聲明類型為CHARINT的話同時(shí)會(huì)匹配規(guī)則1和2，但是第一個(gè)規(guī)則占有優(yōu)先級(jí)所以這個(gè)列的近似將是INTEGER

8、。2.2近似名稱例子下面這個(gè)表顯示了多少來(lái)自更傳統(tǒng)的SQL操作的普通數(shù)據(jù)類型名稱，使用上一節(jié)中的5個(gè)規(guī)則，被轉(zhuǎn)換到近似類型。這個(gè)表只顯示了sqlite能夠接受的數(shù)據(jù)類名稱的一個(gè)子集。注意到跟隨類型名的圓括號(hào)內(nèi)的數(shù)值參數(shù)（如：VARCHAR（255）被sqlite忽略sqlite不在字符串、BLOBS或者數(shù)值的長(zhǎng)度上強(qiáng)加任何長(zhǎng)度限制（除了一個(gè)全局的SQLITEMAXLENGTH限制）。來(lái)自createtable語(yǔ)句或者強(qiáng)轉(zhuǎn)語(yǔ)句的范例類型名產(chǎn)生的近似用于決定近似的規(guī)則INTINTEGERTINYINTSMALLINTMEDIUMINTBIGINTUNSIGNEDBIGINTINT2INT8INTE

9、GER1CHARACTER(20)VARCHAR(255)VARYINGCHARACTER(255)NCHAR(55)NATIVECHARACTER(70)NVARCHAR(100)TEXTCLOBTEXT2BLOBnodatatypespecifiedNONE3REALDOUBLEDOUBLEPRECISIONFLOATREAL4NUMERICDECIMAL(10,5)BOOLEANDATEDATETIMENUMERIC5（numeric和real的數(shù)據(jù)長(zhǎng)度不一樣,REAL是浮點(diǎn)型,numeric和decimal一樣,好處是可定義長(zhǎng)度和小數(shù)點(diǎn)后位數(shù)34.421用NUMERIC好.）注意到聲明

10、類型為FLOATINGPOINT將被賦予INTEGER近似,而不是REAL近似,因?yàn)樵赑OINT中的INT。聲明類型為STRING的將被賦予NUMERIC,而不是TEXT（因?yàn)樯鲜霰碇卸x的類型中不存在STRING這一類型，它被歸于到規(guī)則4中，屬于其他情況）。（從上面可以看出，sqlite3只是從聲明類型字符串中去查找它知道的聲明類型,比如XINT將被賦予INTEGER近似因?yàn)檫@個(gè)字符串里面有INT，所以這里并不需要一個(gè)單獨(dú)的正確的聲明類型，而是只要聲明類型字符串里面包含了sqlite所知道的聲明類型即可）2.3列近似操作例子CREATETABLEt1(tTEXT,-textaffinityb

11、yrule2nuNUMERIC,-numericaffinitybyrule5iINTEGER,-integeraffinitybyrule1rREAL,-realaffinitybyrule4noBLOB-noaffinitybyrule3);/這里根據(jù)聲明類型確定了列的類型近似INSERTINTOt1VALUES(500.0,500.0,500.0,500.0,500.0);SELECTtypeof(t),typeof(nu),typeof(i),typeof(r),typeof(no)FROMt1;結(jié)果：text|integer|integer|real|textDELETEFROMt1

12、;INSERTINTOt1VALUES(500.0,500.0,500.0,500.0,500.0);SELECTtypeof(t),typeof(nu),typeof(i),typeof(r),typeof(no)FROMt1;結(jié)果：text|integer|integer|real|realDELETEFROMt1;INSERTINTOt1VALUES(500,500,500,500,500);SELECTtypeof(t),typeof(nu),typeof(i),typeof(r),typeof(no)FROMt1;結(jié)果：text|integer|integer|real|intege

13、r(這里的第四個(gè)值，對(duì)應(yīng)的列是REAL近似的，傳輸?shù)闹嫡蔚?，但是根?jù)REAL近似的規(guī)則它會(huì)將它轉(zhuǎn)換為real型數(shù)據(jù))/數(shù)據(jù)塊(BLOB)不管是什么列近似都一直存為BLOB類型DELETEFROMt1;INSERTINTOt1VALUES(x0500,x0500,x0500,x0500,x0500);SELECTtypeof(t),typeof(nu),typeof(i),typeof(r),typeof(no)FROMt1;結(jié)果：blob|blob|blob|blob|blob/NULLs也不受列近似影響DELETEFROMt1;INSERTINTOt1VALUES(NULL,NULL,NU

14、LL,NULL,NULL);SELECTtypeof(t),typeof(nu),typeof(i),typeof(r),typeof(no)FROMt1;結(jié)果：null|null|null|null|null3.0比較表達(dá)式Sqlitev3有一系列有用的比較操作符，包括=,=,=,!=,IN,NOTIN,BETWEEN,IS,和ISNOT3.1排序比較操作的結(jié)果基于操作數(shù)的存儲(chǔ)類型，根據(jù)下面的規(guī)則：存儲(chǔ)類型為NULL的值被認(rèn)為小于其他任何的值（包括另一個(gè)存儲(chǔ)類型為NULL的值）一個(gè)INTEGER或REAL值小于任何TEXT或BLOB值。當(dāng)一個(gè)INTEGER或REAL值與另外一個(gè)INTEGER

15、或REAL值比較的話，就執(zhí)行數(shù)值比較TEXT值小于BLOB值。當(dāng)兩個(gè)TEXT值比較的時(shí)候，就根據(jù)序列的比較來(lái)決定結(jié)果當(dāng)兩個(gè)BLOB值比較的時(shí)候，使用memcmp來(lái)決定結(jié)果3.2比較操作數(shù)的近似（Affinity）Sqlite可能在執(zhí)行一個(gè)比較之前會(huì)在INTEGER，REAL或TEXT之間轉(zhuǎn)換比較值。是否在比較操作之前發(fā)生轉(zhuǎn)換基于操作數(shù)的近似（類型）。操作數(shù)近似（類型）由下面的規(guī)則決定：對(duì)一個(gè)列的簡(jiǎn)單引用的表達(dá)式與這個(gè)列有相同的affinity,注意如果X和Y.Z是列名，那么+X和+Y.Z均被認(rèn)為是用于決定affinity的表達(dá)式一個(gè)CAST（expratyp形式的表達(dá)式與用聲明類型為type的

16、列有相同的affinity其他的情況，一個(gè)表達(dá)式為NONEaffinity3.3在比較前的類型轉(zhuǎn)換只有在轉(zhuǎn)換是無(wú)損、可逆轉(zhuǎn)的時(shí)候“應(yīng)用近似”才意味著將操作數(shù)轉(zhuǎn)換到一個(gè)特定的存儲(chǔ)類。近似在比較之前被應(yīng)用到比較的操作數(shù)，遵循下面的規(guī)則（根據(jù)先后順序）:如果一個(gè)操作數(shù)有INTEGER，REAL或NUMERIC近似，另一個(gè)操作數(shù)有TEXT或NONE近似，那么NUMERIC近似被應(yīng)用到另一個(gè)操作數(shù)如果一個(gè)操作數(shù)有TEXT近似，另一個(gè)有NONE近似，那么TEXT近似被應(yīng)用到另一個(gè)操作數(shù)其他的情況，不應(yīng)用近似，兩個(gè)操作數(shù)按本來(lái)的樣子比較表達(dá)式aBETWEENbANDc表示兩個(gè)單獨(dú)的二值比較abANDa=即使

17、在兩個(gè)比較中不同的近似被應(yīng)用到a3.4比較舉例CREATETABLEt1（aTEXT,-textaffinitybNUMERIC,-numericaffinitycBLOB,-noaffinityd-noaffinity）;INSERTINTOt1VALUES(500,500,500,500);SELECTtypeof(a),typeof(b),typeof(c),typeof(d)FROMt1;text|integer|text|integer-Becausecolumnahastextaffinity,numericvaluesonthe-right-hand+sideofthecompa

18、risonsareconvertedtotextbefore-thecomparisonoccurs.SELECTa40,a60,a600FROMt1;0|1|1-Textaffinityisappliedtotheright-handoperandsbutsince-theyarealreadyTEXTthisisano-op;noconversionsoccur.SELECTa40,a60,a600FROMt1;0|1|1-Columnbhasnumericaffinityandsonumericaffinityisapplied-totheoperandsontheright.Since

19、theoperandsarealreadynumeric,-theapplicationofaffinityisano-op;noconversionsoccur.All-valuesarecomparednumerically.SELECTb40,b60,b600FROMt1;0|0|1-Numericaffinityisappliedtooperandsontheright,convertingthem-fromtexttointegers.Thenanumericcomparisonoccurs.SELECTb40,b60,b600FROMt1;0|0|1-Noaffinityconversionsoccur.Right-handsidevaluesallhave-storageclassINTEGERwhicharealw