sqlite3中的數(shù)據(jù)類型
大多數(shù)的數(shù)據(jù)庫引擎（到現(xiàn)在據(jù)我們所知的除了sqlite的每個sql數(shù)據(jù)庫引擎）都使用靜態(tài)的、剛性的類型，使用靜態(tài)類型，數(shù)據(jù)的類型就由它的容器決定，這個容器是這個指被存放的特定列。
Sqlite使用一個更一般的動態(tài)類型系統(tǒng)，sqlite中，值的數(shù)據(jù)類型跟值本身相關(guān)，而不是與它的容器相關(guān)。Sqlite的動態(tài)類型系統(tǒng)和其他數(shù)據(jù)庫的更為一般的靜態(tài)類型系統(tǒng)相兼容，但同時，sqlite中的動態(tài)類型允許它能做到一些傳統(tǒng)剛性類型數(shù)據(jù)庫所不可能做到的事。
?
1．??存儲類和數(shù)據(jù)類型
每個存放在sqlite數(shù)據(jù)庫中（或者由這個數(shù)據(jù)庫引擎操作）的值都有下面中的一個存儲類：
l??NULL，值是NULL
l??INTEGER，值是有符號整形，根據(jù)值的大小以1,2,3,4,6或8字節(jié)存放
l??REAL，值是浮點型值，以8字節(jié)IEEE浮點數(shù)存放
l??TEXT，值是文本字符串，使用數(shù)據(jù)庫編碼（UTF-8，UTF-16BE或者UTF-16LE）存放
l??BLOB，只是一個數(shù)據(jù)塊，完全按照輸入存放（即沒有準(zhǔn)換）
從上可以看出存儲類比數(shù)據(jù)類型更一般化。比如INTEGER存儲類，包括6中不同長度的不同整形數(shù)據(jù)類型，這在磁盤上造成了差異。但是只要INTEGER值被從磁盤讀出進入到內(nèi)存進行處理，它們被轉(zhuǎn)換成最一般的數(shù)據(jù)類型（8-字節(jié)有符號整形）。
Sqlite v3數(shù)據(jù)庫中的任何列，除了整形主鍵列，可以用于存儲任何一個存儲列的值。sql語句中的中所有值，不管它們是嵌入在sql文本中或者是作為參數(shù)綁定到一個預(yù)編譯的sql語句，它們的存儲類型都是未定的。在下面描述的情況中，數(shù)據(jù)庫引擎會在查詢執(zhí)行過程中在數(shù)值（numeric）存儲類型（INTEGER和REAL）和TEXT之間轉(zhuǎn)換值。
1.1布爾類型
Sqlite沒有單獨的布爾存儲類型，它使用INTEGER作為存儲類型，0為false，1為true
?
1.2 Date和Time Datatype
Sqlite沒有另外為存儲日期和時間設(shè)定一個存儲類集，內(nèi)置的sqlite日期和時間函數(shù)能夠?qū)⑷掌诤蜁r間以TEXT，REAL或INTEGER形式存放
l??TEXT?作為IS08601字符串（"YYYY-MM-DD HH:MM:SS.SSS"）
l??REAL?從格林威治時間11月24日，4174 B.C中午以來的天數(shù)
l??INTEGER?從?1970-01-01 00:00:00 UTC以來的秒數(shù)
程序可以任意選擇這幾個存儲類型去存儲日期和時間，并且能夠使用內(nèi)置的日期和時間函數(shù)在這些格式間自由轉(zhuǎn)換
?
2.0?類型近似
?
為了使sqlite和其他數(shù)據(jù)庫間的兼容性最大化，sqlite支持列上“類型近似”的觀點，列的類型近似指的是存儲在列上數(shù)據(jù)的推薦類型。這里必須記住一點，這個類型是被推薦，而不是必須的。任何列仍然能存儲任意類型的數(shù)據(jù)。只是一些列，給予選擇的話，將會相比于其他的一些類型優(yōu)選選擇一些存儲類型，這個列優(yōu)先選擇的存儲類型被稱為它的“近似”。
每個sqlite3數(shù)據(jù)庫中的列都被賦予下面類型近似中的一種：
l??TEXT
l??NUMERIC
l??INTEGER
l??REAL
l??NONE
具有TEXT近似的列可以用NULL，TEXT或者BLOB類型存儲數(shù)據(jù)。如果數(shù)值數(shù)據(jù)被插入到具有TEXT近似的列，在被存儲前被轉(zhuǎn)換為文本形式
一個有NUMERIC近似的列可以使用1中的所有5中存儲類來存儲數(shù)據(jù)。當(dāng)文本數(shù)據(jù)被存放到NUMERIC近似的列中，這個文本的存儲類被轉(zhuǎn)換到INTEGER或REAL（根據(jù)優(yōu)先級順序），如果這個轉(zhuǎn)換是無損的話。對于TEXT和REAL存儲類間的轉(zhuǎn)換，如果數(shù)據(jù)的前15位的被保留的話sqlite就認(rèn)為這個轉(zhuǎn)換是無損的、可反轉(zhuǎn)的。如果TEXT到INTEGER或REAL的轉(zhuǎn)換不可避免的會造成損失，那么數(shù)據(jù)將使用TEXT存儲類存儲。不會企圖去轉(zhuǎn)換NULL或BLOB值。
一個字符串可能看起來像浮點數(shù)據(jù)，有小數(shù)點或指數(shù)符號，但是只要這個數(shù)據(jù)可以使用整形存放，NUMERIC近似就會將它轉(zhuǎn)換到整形。比如，字符串?'3.0e+5'存放到一個具有NUMERIC近似的列中，被存為300000，而不是浮點型值300000.0。
具有INTEGER近似的列和具有NUMERIC近似的列表現(xiàn)相同。它們之間的差別僅處于轉(zhuǎn)換描述上。
具有REAL近似的列和具有NUMERIC近似的列一樣，除了它將整形數(shù)據(jù)轉(zhuǎn)換成浮點型形式。
具有NONE近似的列不會優(yōu)先選擇一個存儲列，也不會強制將數(shù)據(jù)從一個存儲類轉(zhuǎn)換到另外一個類。
?
2.1?列近似的決定因素
列的近似由這個列的聲明類型所決定，根據(jù)下面的順序的規(guī)則：
上面規(guī)則額順序?qū)τ跊Q定列的近似很重要。一個列的聲明類型為”CHARINT”的話同時會匹配規(guī)則
?
2.2?近似名稱例子
?????????下面這個表顯示了多少來自更傳統(tǒng)的SQL操作的普通數(shù)據(jù)類型名稱，使用上一節(jié)中的5個規(guī)則，被轉(zhuǎn)換到近似類型。這個表只顯示了sqlite能夠接受的數(shù)據(jù)類名稱的一個子集。注意到跟隨類型名的圓括號內(nèi)的數(shù)值參數(shù)（如：”VARCHAR(255)”）被sqlite忽略—sqlite不在字符串、BLOBS或者數(shù)值的長度上強加任何長度限制（除了一個全局的SQLITE_MAX_LENGTH限制）。
來自create table語句或者強轉(zhuǎn)語句的范例類型名
產(chǎn)生的近似
用于決定近似的規(guī)則
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 specified
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（因為上述表中定義的類型中不存在STRING這一類型，它被歸于到規(guī)則
（從上面可以看出，sqlite3只是從聲明類型字符串中去查找它知道的聲明類型，比如”XINT”將被賦予INTEGER近似因為這個字符串里面有”INT”，所以這里并不需要一個單獨的正確的聲明類型，而是只要聲明類型字符串里面包含了sqlite所知道的聲明類型即可）
?
2.3?列近似操作例子
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
);?//這里根據(jù)聲明類型確定了列的類型近似
?
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;
//結(jié)果：text|integer|integer|real|text
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;
//結(jié)果：text|integer|integer|real|real
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;
//結(jié)果：text|integer|integer|real|integer
（這里的第四個值，對應(yīng)的列是REAL近似的，傳輸?shù)闹嫡蔚?，但是根?jù)REAL近似的規(guī)則它會將它轉(zhuǎn)換為real型數(shù)據(jù)）
//?數(shù)據(jù)塊(BLOB)不管是什么列近似都一直存為BLOB類型
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;
//結(jié)果：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;
//結(jié)果：null|null|null|null|null
?
?
3.0?比較表達式
?????????Sqlite v3有一系列有用的比較操作符，包括?"=", "==", "<", "
?
3.1?排序
比較操作的結(jié)果基于操作數(shù)的存儲類型，根據(jù)下面的規(guī)則：
l??存儲類型為NULL的值被認(rèn)為小于其他任何的值（包括另一個存儲類型為NULL的值）
l??一個INTEGER或REAL值小于任何TEXT或BLOB值。當(dāng)一個INTEGER或REAL值與另外一個INTEGER或REAL值比較的話，就執(zhí)行數(shù)值比較
l??TEXT值小于BLOB值。當(dāng)兩個TEXT值比較的時候，就根據(jù)序列的比較來決定結(jié)果
l??當(dāng)兩個BLOB值比較的時候，使用memcmp來決定結(jié)果
?
3.2?比較操作數(shù)的近似（Affinity）
?????????Sqlite可能在執(zhí)行一個比較之前會在INTEGER，REAL或TEXT之間轉(zhuǎn)換比較值。是否在比較操作之前發(fā)生轉(zhuǎn)換基于操作數(shù)的近似（類型）。操作數(shù)近似（類型）由下面的規(guī)則決定：
l??對一個列的簡單引用的表達式與這個列有相同的affinity，注意如果X和Y.Z是列名，那么+X和+Y.Z均被認(rèn)為是用于決定affinity的表達式
l??一個”CAST(expr as type)”形式的表達式與用聲明類型為”type”的列有相同的affinity
l??其他的情況，一個表達式為NONE affinity
?
3.3?在比較前的類型轉(zhuǎn)換
?????????只有在轉(zhuǎn)換是無損、可逆轉(zhuǎn)的時候“應(yīng)用近似”才意味著將操作數(shù)轉(zhuǎn)換到一個特定的存儲類。近似在比較之前被應(yīng)用到比較的操作數(shù)，遵循下面的規(guī)則（根據(jù)先后順序）:
l??如果一個操作數(shù)有INTEGER，REAL或NUMERIC近似，另一個操作數(shù)有TEXT或NONE近似，那么NUMERIC近似被應(yīng)用到另一個操作數(shù)
l??如果一個操作數(shù)有TEXT近似，另一個有NONE近似，那么TEXT近似被應(yīng)用到另一個操作數(shù)
l??其他的情況，不應(yīng)用近似，兩個操作數(shù)按本來的樣子比較
表達式"a BETWEEN b AND c"表示兩個單獨的二值比較”?a >= b AND a <= c”，即使在兩個比較中不同的近似被應(yīng)用到’a’。
?
3.4?比較舉例
CREATE TABLE t1(
????a TEXT,??????-- text affinity
????b NUMERIC,???-- numeric affinity
????c BLOB,??????-- no affinity
????d????????????-- no affinity
);
?
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
?
從這里可以看出，假如可以使用3.1中的規(guī)則進行比較的話，就不需要進行類型轉(zhuǎn)換，否則的話就要進行類型轉(zhuǎn)換
?
4.0?操作符
所有的數(shù)學(xué)操作符(+, -, *, /, %, <
下載文檔
