大多數(shù) SQL 數(shù)據(jù)庫引擎 （據(jù)我們所知，除 SQLite 之外的所有 SQL 數(shù)據(jù)庫引擎）都使用嚴(yán)格的靜態(tài)類型。使用靜態(tài)類型，值的類型便由它的容器 -- 存儲值的特定的列 -- 來決定。
SQLite 使用更通用的動態(tài)類型系統(tǒng)。在 SQLit 中，值的數(shù)據(jù)類型與值本身相關(guān)，而不是與它的容器。SQLite 的動態(tài)類型系統(tǒng)與其它數(shù)據(jù)庫引擎的常用靜態(tài)類型系統(tǒng)是向后兼容的，在這個意義上，工作在靜態(tài)類型數(shù)據(jù)庫上的 SQL 語句應(yīng)該以同樣的方式工作在 SQLite 中。然而，SQLite 中的動態(tài)類型允許它做傳統(tǒng)的嚴(yán)格類型的數(shù)據(jù)庫所不能做的事。
1.0 存儲類型與數(shù)據(jù)類型
存儲在 SQLite 數(shù)據(jù)庫中的每個值（或是由數(shù)據(jù)庫引擎所操作的值）都有一個以下的存儲類型：
注意，存儲類型比數(shù)據(jù)類型更籠統(tǒng)。以 INTEGER 存儲類型為例，它包括6種不同的長度不等的整數(shù)類型，這在磁盤上是不同的。但是只要 INTEGER 值從磁盤讀取到內(nèi)存進行處理，它們就被轉(zhuǎn)換為更為一般的數(shù)據(jù)類型（8字節(jié)有符號整型）。因此在一般情況下，“存儲類型” 與 “數(shù)據(jù)類型” 沒什么差別，這兩個術(shù)語可以互換使用。
SQLite 版本3數(shù)據(jù)庫中的任何列，除了整型主鍵列，都可用于存儲任何存儲類型的值。
SQL 語句中的任何值，無論它們是嵌入到 SQL 語句中的字面量還是綁定到預(yù)編譯 SQL 語句中的參數(shù)，都有一個隱含的存儲類型。在下述情況下，數(shù)據(jù)庫引擎會在執(zhí)行查詢時在數(shù)值存儲類型（INTEGER 和 REAL）和 TEXT 之間進行轉(zhuǎn)換。
1.1 布爾類型
SQLite 并沒有單獨的布爾存儲類型，而是將布爾值存儲為整數(shù) 0 (false) 和 1 (true)。
1.2 日期和時間類型
SQLite 沒有另外的存儲類型來存儲日期和時間。SQLite 的內(nèi)置的日期和時間函數(shù)能夠?qū)⑷掌诤蜁r間存為 TEXT、REAL 或 INTEGER 值：
應(yīng)用可以選擇這些格式中的任一種存儲日期和時間，并使用內(nèi)置的日期和時間函數(shù)在這些格式間自由轉(zhuǎn)換。
2.0 類型親和性
為了最大限度地提高 SQLite 和其它數(shù)據(jù)庫引擎之間的兼容性，SQLite 支持列的“類型親和性”的概念。列的類型親和性是指數(shù)據(jù)存儲于該列的推薦類型。這里重要的思想是類型是推薦的，而不是必須的。任何列仍可以存儲任何類型的數(shù)據(jù)。這只是讓一些列有選擇性地優(yōu)先使用某種存儲類型。一個列的首選存儲類型被稱為它的“親和性”。
每個 SQLite 3 數(shù)據(jù)庫中的列都?xì)w于以下的類型親和性中的一種：
一個具有 TEXT 親和性的列使用存儲類型 NULL、 TEXT 或 BLOB 存儲所有數(shù)據(jù)。如果數(shù)值數(shù)據(jù)被插入到一個具有 TEXT 親和性的列，則數(shù)據(jù)在存儲前被轉(zhuǎn)換為文本形式。
數(shù)值親和性的列可能包含了使用所有五個存儲類的值。當(dāng)插入文本數(shù)據(jù)到數(shù)值列時，該文本的存儲類型被轉(zhuǎn)換成整型或?qū)崝?shù)（按優(yōu)先級排序）如果這種轉(zhuǎn)換是無損或可逆的的話。對于文本與實數(shù)類型之間的轉(zhuǎn)換，如果前15個重要十進制數(shù)字被保留的話，SQLite認(rèn)為這種轉(zhuǎn)換是無損并可逆的。如果文本不能無損地轉(zhuǎn)換成整型或?qū)崝?shù)，那這個值將以文本類型存儲。不要試圖轉(zhuǎn)換NULL或BLOB值。
一個字符串可能看上去像帶有小數(shù)點和/或指數(shù)符的浮點文字，但只要這個值可以用一個整型表示，數(shù)值親和性就會把它轉(zhuǎn)換成一個整型。因此，字符串‘3.0e+5'以整型300000，而不是浮點值30000.0的形式存儲在一個數(shù)值親和性的列里。
一個使用整型親和性的列與具有數(shù)值親和性的列表現(xiàn)一致。只是在CAST表達式里，它們之間的區(qū)別體現(xiàn)得明顯。
除了強制將整型值轉(zhuǎn)換成浮點表示外，一個具有實數(shù)親和性的列與具有數(shù)值親和性的列表現(xiàn)一致（作為一個內(nèi)部的優(yōu)化，為了少占用空間，無小數(shù)部分且存儲在實數(shù)親和性列上的小浮點值以整型形式寫到磁盤，讀出時自動轉(zhuǎn)換回浮點值。在SQL級別，這種優(yōu)化是完全不可見的，并且只能通過檢查數(shù)據(jù)庫文件的原始比特檢測到）。
一個具有NONE親和性的列不能從一種存儲類型轉(zhuǎn)換成另一種，也不要試圖強制對它進行轉(zhuǎn)換。
2.1 列親和性測定
列的親和性是由它的聲明類型決定的，按照以下順序所示的規(guī)則：
1. 如果聲明類型包含字符串“INT”，那它被指定為整型親和性；
2. 如果列的聲明類型包含任何“CHAR”、“CLOB”或“TEXT”字符串，那么該列具有文本親和性。注意：VARCHAR類型包含“CHAR”并且被指定為文本親和性；
3. 如果列的聲明類型包含“BLOB”或者沒有指定類型，那這列具有NONE親和性；
4. 如果列的聲明類型包含任何“REAL”、“FLOA”或“DOUB”字符串，則該列具有實數(shù)親和性；
5. 否則，它將具有數(shù)值親和性。
注意：判定列親和性規(guī)則的順序是很重要的。一個具有“CHARINT”聲明類型的列將匹配規(guī)則1和2，但是規(guī)則1優(yōu)先所有該列具有整型親和性。
2.2 親和性名字實例
下表顯示了有多少從更傳統(tǒng)的SQL實現(xiàn)的常用數(shù)據(jù)類型名，通過上一節(jié)介紹的五個規(guī)則被轉(zhuǎn)換成各種親和性類型。這張表只顯示了SQLite可接受的一小部分?jǐn)?shù)據(jù)類型名。注意：跟在類型名后，括號內(nèi)數(shù)值參數(shù)（如：VARCHAR（255））將被SQLite忽略 - SQLite不對字符串、BLOBs或數(shù)值的長度強加任何限制（除了大型全局SQLITE_MAX_LENGTH限制）。
注意： 因為在“POINT”末尾的“INT”，一個“ FLOATING POINT”聲明類型 會被賦予整型親和性，而不是實數(shù)親和性。而且“STRING”聲明類型具有數(shù)值親和性，而不是文本親和性。
2.3 列親和性行為實例
以下SQL演示當(dāng)有值插入到一張表時，SQLite如何使用列親和性實現(xiàn)類型轉(zhuǎn)換的：
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, INTEGER, INTEGER, REAL, TEXT.（值分別以文本、整型、整型、實數(shù)、文本形式存儲）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|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, 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 regardless of column affinity. 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;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|null3.0 比較表達式
同標(biāo)準(zhǔn)SQL一樣，SQLite 3支持如下的比較操作符："=", "==", "<", "<=", ">", ">=", "!=", "<>", "IN", "NOT IN", "BETWEEN", "IS", 以及 "IS NOT"。
3.1 排序規(guī)則
比較的結(jié)果與操作數(shù)的存儲類型有關(guān)，同時依據(jù)以下的規(guī)則：
3.2 操作數(shù)進行比較時的相似性
在進行值的比較之前，SQLite會嘗試在存儲類INTEGER、REAL和/或TEXT之間進行值的轉(zhuǎn)換。在比較之前嘗不嘗試進行轉(zhuǎn)換完全取決于操作數(shù)的相似性。操作數(shù)相似性的判定規(guī)則如下：
3.3 比較前的類型轉(zhuǎn)換
“應(yīng)用相似性”（"apply affinity"）的意思是，當(dāng)且僅當(dāng)所涉及的轉(zhuǎn)換是無損且可逆的情況下，將一個操作數(shù)轉(zhuǎn)換為某特定的存儲類型。在進行比較之前對比較運算符的操作數(shù)應(yīng)用相似性的規(guī)則如下按順序所示：
將表達式"a BETWEEN b AND c"看作兩個單獨的二元比較運算"a >= b AND a <= c"，即使這么一來，可能會造成其中的a在兩次比較中會被應(yīng)用不同的相似性，也要這么處理。Datatype conversions in comparisons of the form 在"x IN (SELECT y ...)"這種形式的比較中，數(shù)據(jù)類型的轉(zhuǎn)換完全同"x=y"一樣進行處理。表達式"a IN (x, y, z, ...)" 同"a = +x OR a = +y OR a = +z OR ..."等價。換句話說，IN運算符右側(cè)的值(本例中就是"x", "y", and "z")被看作是無相似性的，即使它們湊巧是某列的值或者是CAST表達式。
3.4 比較示例
CREATE TABLE t1( a TEXT, -- text affinity b NUMERIC, -- numeric affinity c BLOB, -- no affinity d -- 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;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"――所有的結(jié)果依然相同相同。
4.0 操作符
所有的數(shù)學(xué)運算符(+, -, *, /, %, <<, >>, &, and |)在展開前會將兩個操作數(shù)放入 NUMERIC 儲存類。即使這個過程是有損和不可逆轉(zhuǎn)的。一個 NULL 操作數(shù)在數(shù)學(xué)運算符上產(chǎn)生一個 NULL 結(jié)果。在數(shù)算運算符上的操作數(shù)不被視為數(shù)字，NULL 并不會被轉(zhuǎn)為0或0.0。
5.0 排序, 分組 和 組合查詢
當(dāng)查詢結(jié)果使用 ORDER BY 子句排序時, 存儲類型的NULL空值是排在第一位的, 其次是INTEGER和散布在數(shù)字順序的REAL數(shù)據(jù), 其次是按照核對序列順序的TEXT值, 最后為memcmp() order 的BLOB值. 排序之前不會出現(xiàn)任何存儲類型轉(zhuǎn)換.
當(dāng)使用GROUP BY 子句分組時不同類型的值被認(rèn)為是不同的數(shù)據(jù), 除了INTEGER 和 REAL 值如果他們數(shù)值相等則被認(rèn)為是相同的的數(shù)據(jù). 沒有任何親和性適用于GROUP BY 子句結(jié)果的任意值.
組合查詢使用 UNION, INTERSECT 和 EXCEPT 在數(shù)據(jù)之間執(zhí)行隱式的比較. 沒有任何親和性適用于與UNION, INTERSECT, 或者 EXCEPT關(guān)聯(lián)的隱式比較的運算數(shù) - 數(shù)據(jù)的比較就像這樣.
6.0 整理序列
當(dāng) SQLite 比較兩個字符串時，它使用一個整理序列或整理函數(shù)（一物兩表）來決定當(dāng)兩個字符串相同時，哪個字符串值更高。SQLite 擁有三個內(nèi)建整理函數(shù)：BINARY, NOCASE, 和 RTRIM。
應(yīng)用可以通過 sqlite3_create_collation() 接口注冊額外的整理函數(shù)。
6.1 設(shè)定SQL中的排列順序
每個表中的每一個列都具有一個相關(guān)的排序函數(shù)。如果沒有顯式地定義排序函數(shù)，那么，就會缺省使用BINARY作為排序函數(shù)。列定義中的COLLATE子句可為列定義一個可選的排序函數(shù)。
對于二元比較運算符(=, <, >, <=, >=, !=, IS, and IS NOT)來說，判定到底使用哪個排序函數(shù)的規(guī)則按順序如下所列：
比較運算中的操作數(shù)，如果在它的任何子表達式中使用了后綴 COLLATE運算符，就可以認(rèn)為是具有顯式的排序函數(shù)（上文中的規(guī)則1）。 再者，如果在比較表達式中的任何地方使用了 COLLATE運算符，那么該運算符所定義的排序函數(shù)就會用于字符串的比較，而無論在表達式中出現(xiàn)了表中的哪一列。如果在比較中的任何地方出現(xiàn)了兩個或多個 COLLATE運算符子表達式，無論在表達式中嵌入得多深，也無論表達式是怎么使用括號的，都會使用出現(xiàn)在最左側(cè)的顯式排序函數(shù)。
表達式"x BETWEEN y and z"從邏輯上講，同"x >= y AND x <= z"這兩個比較運算完全等價，在使用排序函數(shù)時它們倆要象兩個本來就是獨立的比較運算一樣進行處理。在判定排列順序時，表達式"x IN (SELECT y ...)"處理方式完全同表達式"x = y"一樣，形如"x IN (y, z, ...)"的表達式，排列順序完全同X的排列順序一樣。
作為 SELECT語句的一個部分，ORDER BY子句中排序條件也可以通過使用COLLATE運算符設(shè)定排列順序，如果設(shè)定了排序時就要按照設(shè)定的排序函數(shù)進行排序。否則，如果ORDER BY子句使用的排序表達式是一個列，那么該列的排列順序就用于判定排列順序。如果該排序表達式不是列并且也無COLLATE子句，就會使用BINARY排列順序。
6.2 整理序列示例
下面的示例將識別整理序列，決定 SQL 語句的文本比較結(jié)果。注意，在文本比較時，如果是數(shù)字，二進制或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 ','abc');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'); /* a=b 的文本比較表現(xiàn)為使用 BINARY （二進制）整理序列。 */SELECT x FROM t1 WHERE a = b ORDER BY x;--結(jié)果 1 2 3 /* a=b 的文本比較表現(xiàn)為使用 RTRIM 整理序列。 */SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;--結(jié)果 1 2 3 4 /* d=a 的文本比較表現(xiàn)為使用 NOCASE 整理序列。 */SELECT x FROM t1 WHERE d = a ORDER BY x;--結(jié)果 1 2 3 4 /* a=d 的文本比較表現(xiàn)為使用 BINARY （二進制）整理序列。 */SELECT x FROM t1 WHERE a = d ORDER BY x;--結(jié)果 1 4 /* 'abc'=c 的文本比較表現(xiàn)為使用 RTRIM （二進制）整理序列。 */SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;--結(jié)果 1 2 3 /* c='abc' 的文本比較表現(xiàn)為使用 RTRIM 整理序列。 */SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;--結(jié)果 1 2 3 /* 分組表現(xiàn)為使用 NOCASE 整理序列（值'abc'，'ABC' 和 'Abc'** 被分為同一組）。*/SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;--結(jié)果 4 /* 分組表現(xiàn)為使用 BINARY 整理序列（值'abc'，'ABC' 和 'Abc'** 被分為不同的組）。*/SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;--結(jié)果 1 1 2 /* 列c排序表現(xiàn)為使用 RTRIM 整理序列。*/（譯注：sorting or column c 疑為 sorting of...誤寫）SELECT x FROM t1 ORDER BY c, x;--結(jié)果 4 1 2 3 /* (c||'')排序表現(xiàn)為使用 BINARY 整理序列。*/SELECT x FROM t1 ORDER BY (c||''), x;--結(jié)果 4 2 3 1 /* 列c排序表現(xiàn)為使用 NOCASE 整理序列。*/SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;--結(jié)果 2 4 3 1
新聞熱點
疑難解答
圖片精選
