詳解SQLite中的資料類型，詳解sqlite資料類型

詳解SQLite中的資料類型，詳解sqlite資料類型

 大多數 SQL 資料庫引擎 （據我們所知，除 SQLite 之外的所有 SQL 資料庫引擎）都使用嚴格的靜態類型。使用靜態類型，值的類型便由它的容器 -- 儲存值的特定的列 -- 來決定。

SQLite 使用更通用的動態類型系統。在 SQLit 中，值的資料類型與值本身相關，而不是與它的容器。SQLite 的動態類型系統與其它資料庫引擎的常用靜態類型系統是向後相容的，在這個意義上，工作在靜態類型資料庫上的 SQL 陳述式應該以同樣的方式工作在 SQLite 中。然而，SQLite 中的動態類型允許它做傳統的嚴格類型的資料庫所不能做的事。

1.0 儲存類型與資料類型

儲存在 SQLite 資料庫中的每個值（或是由資料庫引擎所操作的值）都有一個以下的儲存類型：

•     NULL. 值是空值。
•     INTEGER. 值是有符號整數，根據值的大小以1，2，3，4，6 或8位元組儲存。
•     REAL. 值是浮點數，以8位元組 IEEE 浮點數儲存。
•     TEXT. 值是文本字串，使用資料庫編碼（UTF-8, UTF-16BE 或 UTF-16LE）進行儲存。
•     BLOB. 值是一個資料區塊，按它的輸入原樣儲存。

注意，儲存類型比資料類型更籠統。以 INTEGER 儲存類型為例，它包括6種不同的長度不等的整數類型，這在磁碟上是不同的。但是只要 INTEGER 值從磁碟讀取到記憶體進行處理，它們就被轉換為更為一般的資料類型（8位元組有符號整型）。因此在一般情況下，“儲存類型” 與 “資料類型” 沒什麼差別，這兩個術語可以互換使用。

SQLite 版本3資料庫中的任何列，除了整型主鍵列，都可用於儲存任何儲存類型的值。

SQL 陳述式中的任何值，無論它們是嵌入到 SQL 陳述式中的字面量還是綁定到先行編譯 SQL 陳述式中的參數，都有一個隱含的儲存類型。在下述情況下，資料庫引擎會在執行查詢時在數值儲存類型（INTEGER 和 REAL）和 TEXT 之間進行轉換。

1.1 布爾類型

SQLite 並沒有單獨的布爾儲存類型，而是將布爾值儲存為整數 0 (false) 和 1 (true)。
1.2 日期和時間類型

SQLite 沒有另外的儲存類型來儲存日期和時間。SQLite 的內建的日期和時間函數能夠將日期和時間存為 TEXT、REAL 或 INTEGER 值：

•     TEXT  ISO8601 字串 ("YYYY-MM-DD HH:MM:SS.SSS")。
•     REAL 儒略日數 (Julian Day Numbers)，按照前西曆，自格林威治時間公元前4714年11月24日中午以來的天數。
•     INTEGER Unix 時間，自 1970-01-01 00:00:00 UTC 以來的秒數。

應用可以選擇這些格式中的任一種儲存日期和時間，並使用內建的日期和時間函數在這些格式間自由轉換。

2.0 類型親和性

為了最大限度地提高 SQLite 和其它資料庫引擎之間的相容性，SQLite 支援列的“類型親和性”的概念。列的類型親和性是指資料存放區於該列的推薦類型。這裡重要的思想是類型是推薦的，而不是必須的。任何列仍可以儲存任何類型的資料。這隻是讓一些列有選擇性地優先使用某種儲存類型。一個列的首選儲存類型被稱為它的“親和性”。

每個 SQLite 3 資料庫中的列都歸於以下的類型親和性中的一種：

•     TEXT
•     NUMERIC
•     INTEGER
•     REAL
•     NONE

一個具有 TEXT 親和性的列使用儲存類型 NULL、 TEXT 或 BLOB 儲存所有資料。如果數值資料被插入到一個具有 TEXT 親和性的列，則資料在儲存前被轉換為文本形式。

數值親和性的列可能包含了使用所有五個儲存類的值。當插入文本資料到數值列時，該文本的儲存類型被轉換成整型或實數（按優先順序排序）如果這種轉換是無損或可逆的的話。對於文本與實數類型之間的轉換，如果前15個重要十進位數字被保留的話，SQLite認為這種轉換是無損並可逆的。如果文本不能無損地轉換成整型或實數，那這個值將以文本類型儲存。不要試圖轉換NULL或BLOB值。

一個字串可能看上去像帶有小數點和/或指數符的浮點文字，但只要這個值可以用一個整型表示，數值親和性就會把它轉換成一個整型。因此，字串‘3.0e+5'以整型300000，而不是浮點值30000.0的形式儲存在一個數值親和性的列裡。

一個使用整型親和性的列與具有數值親和性的列表現一致。只是在CAST運算式裡，它們之間的區別體現得明顯。

除了強制將整型值轉換成浮點表示外，一個具有實數親和性的列與具有數值親和性的列表現一致（作為一個內部的最佳化，為了少佔用空間，無小數部分且儲存在實數親和性列上的小浮點值以整型形式寫到磁碟，讀出時自動轉換回浮點值。在SQL層級，這種最佳化是完全不可見的，並且只能通過檢查資料庫檔案的原始位元檢測到）。

一個具有NONE親和性的列不能從一種儲存類型轉換成另一種，也不要試圖強制對它進行轉換。

2.1 列親和性測定

列的親和性是由它的宣告類型決定的，按照以下順序所示的規則：

1. 如果宣告類型包含字串“INT”，那它被指定為整型親和性；

2. 如果列的宣告類型包含任何“CHAR”、“CLOB”或“TEXT”字串，那麼該列具有文本親和性。注意：VARCHAR類型包含“CHAR”並且被指定為文本親和性；

3. 如果列的宣告類型包含“BLOB”或者沒有指定類型，那這列具有NONE親和性；

4. 如果列的宣告類型包含任何“REAL”、“FLOA”或“DOUB”字串，則該列具有實數親和性；

5. 否則，它將具有數值親和性。

注意：判定列親和性規則的順序是很重要的。一個具有“CHARINT”宣告類型的列將匹配規則1和2，但是規則1優先所有該列具有整型親和性。

2.2 親和性名字執行個體

下表顯示了有多少從更傳統的SQL實現的常用資料類型名，通過上一節介紹的五個規則被轉換成各種親和性類型。這張表只顯示了SQLite可接受的一小部分資料類型名。注意：跟在類型名後，括弧內數值參數（如：VARCHAR（255））將被SQLite忽略 - SQLite不對字串、BLOBs或數值的長度強加任何限制（除了大型全域SQLITE_MAX_LENGTH限制）。

注意： 因為在“POINT”末尾的“INT”，一個“ FLOATING POINT”宣告類型 會被賦予整型親和性，而不是實數親和性。而且“STRING”宣告類型具有數值親和性，而不是文本親和性。

2.3 列親和性行為執行個體

以下SQL示範當有值插入到一張表時，SQLite如何使用列親和性實作類別型轉換的：
 

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.（值分別以文本、整型、整型、實數、文本形式儲存）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|null

3.0 比較運算式

同標準SQL一樣，SQLite 3支援如下的比較操作符："=", "==", "<", "<=", ">", ">=", "!=", "<>", "IN", "NOT IN", "BETWEEN", "IS", 以及 "IS NOT"。
3.1 定序

比較的結果與運算元的儲存類型有關，同時依據以下的規則：

•     NULL值小於其他任何值（包括另外一個NULL）
•     INTEGER或REAL小於TEXT，BLOB值；若兩個INTEGER(或者REAL)比較，則按照實際的數值進行。
•     TEXT小於BLOB，若兩個TEXT比較，結果則由適當的整理順序決定
•     若兩個BLOD比較，與memcmp()的結果一致

3.2 運算元進行比較時的相似性

在進行值的比較之前，SQLite會嘗試在儲存類INTEGER、REAL和/或TEXT之間進行值的轉換。在比較之前嘗不嘗試進行轉換完全取決於運算元的相似性。運算元相似性的判定規則如下：

•     只是對一個列中的值進行引用的運算式同被引用的列具有完全相同的相似性。注意，如果X、Y.Z代表的是列的名稱，那麼+X和+Y.Z可以認為是為了判定其相似性的運算式。
•     "CAST(expr AS type)"所表示的運算式同類型定義為"type"的列具有完全相同的相似性。
•     其它情況下的運算式具有NONE相似性。

3.3 比較前的類型轉換

“應用相似性”（"apply affinity"）的意思是，若且唯若所涉及的轉換是無損且可逆的情況下，將一個運算元轉換為某特定的儲存類型。在進行比較之前對比較子的運算元應用相似性的規則如下按順序所示：

•     如果其中的一個運算元具有INTEGER、REAL或者NUMERIC相似性而另外一個運算元具有TEXT或者NONE相似性，那麼就要對這另外一個運算元應用NUMERIC 相似性。
•     如果其中的一個運算元具有TEXT相似性而另外一個具有NONE相似性，那麼就要對這另外一個運算元應用TEXT相似性。
•     其它情況下不會應用任何相似性，兩個運算元按照各自的原樣進行比較。

將運算式"a BETWEEN b AND c"看作兩個單獨的二元比較運算"a >= b AND a <= c"，即使這麼一來，可能會造成其中的a在兩次比較中會被應用不同的相似性，也要這麼處理。Datatype conversions in comparisons of the form 在"x IN (SELECT y ...)"這種形式的比較中，資料類型的轉換完全同"x=y"一樣進行處理。運算式"a IN (x, y, z, ...)" 同"a = +x OR a = +y OR a = +z OR ..."等價。換句話說，IN運算子右側的值(本例中就是"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"——所有的結果依然相同相同。
4.0 操作符

所有的數學運算子(+, -, *, /, %, <<, >>, &, and |)在展開前會將兩個運算元放入 NUMERIC 儲存類。即使這個過程是有損和無法復原轉的。一個 NULL 運算元在數學運算子上產生一個 NULL 結果。在數算運算子上的運算元不被視為數字，NULL 並不會被轉為0或0.0。

5.0 排序, 分組 和 組合查詢

當查詢結果使用 ORDER BY 子句排序時, 儲存類型的NULL空值是排在第一位的, 其次是INTEGER和散布在數字順序的REAL資料, 其次是按照核對序列順序的TEXT值, 最後為memcmp() order 的BLOB值. 排序之前不會出現任何儲存類型轉換.

當使用GROUP BY 子句分組時不同類型的值被認為是不同的資料, 除了INTEGER 和 REAL 值如果他們數值相等則被認為是相同的的資料. 沒有任何親和性適用於GROUP BY 子句結果的任意值.

組合查詢使用 UNION, INTERSECT 和 EXCEPT 在資料之間執行隱式的比較. 沒有任何親和性適用於與UNION, INTERSECT, 或者 EXCEPT關聯的隱式比較的運算數  - 資料的比較就像這樣.

6.0 整理序列

當 SQLite 比較兩個字串時，它使用一個整理序列或整理函數（一物兩表）來決定當兩個字串相同時，哪個字串值更高。SQLite 擁有三個內建整理函數：BINARY, NOCASE, 和 RTRIM。

•     BINARY - 使用 memcmp() 比較字串，無視文本編碼。
•     NOCASE - 與二進位比較相同，除了 ASCII 的26個大寫字母在比較前將會轉為其小寫形勢。注意，只有 ASCII 字元會大小寫轉化。 由於表大小的需求，SQLite 並不會嘗試 UTF 大小寫轉化。
•     RTRIM - 與二進位比較相同，除了尾部空格符將被忽略。

應用可以通過 sqlite3_create_collation() 介面註冊額外的整理函數。

6.1 設定SQL中的排列順序

每個表中的每一個列都具有一個相關的排序函數。如果沒有顯式地定義排序函數，那麼，就會預設使用BINARY作為排序函數。列定義中的COLLATE子句可為列定義一個可選的排序函數。

對於二元比較子(=, <, >, <=, >=, !=, IS, and IS NOT)來說，判定到底使用哪個排序函數的規則按順序如下所列：

•     如果兩個運算元中有任意一個運算元具有使用尾碼COLLATE運算子顯式定義的排序函數，那麼就會用該函數進行比較，如果兩個運算元都有的情況下，優先使用左運算元的排序函數。
•     如果兩個運算元中任意一個運算元是一個列，那麼就會使用該列的排序函數進行比較，但在兩個運算元都是列的情況下，優先使用左運算元對應的列的排序函數。為了達到這句話的目的，列名前帶有1個或多個一元運算子"+"的，仍然按原列名處理。
•     其它情況下，採用BINARY排序函數進行比較。

比較運算中的運算元，如果在它的任何子運算式中使用了尾碼 COLLATE運算子，就可以認為是具有顯式的排序函數（上文中的規則1）。 再者，如果在比較運算式中的任何地方使用了 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排列順序。

6.2 整理序列樣本

下面的樣本將識別整理序列，決定 SQL 陳述式的文本比較結果。注意，在文本比較時，如果是數字，二進位或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 的文本比較表現為使用 BINARY （二進位）整理序列。 */SELECT x FROM t1 WHERE a = b ORDER BY x;--結果 1 2 3 /* a=b 的文本比較表現為使用 RTRIM 整理序列。 */SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;--結果 1 2 3 4 /* d=a 的文本比較表現為使用 NOCASE 整理序列。 */SELECT x FROM t1 WHERE d = a ORDER BY x;--結果 1 2 3 4 /* a=d 的文本比較表現為使用 BINARY （二進位）整理序列。 */SELECT x FROM t1 WHERE a = d ORDER BY x;--結果 1 4 /* 'abc'=c 的文本比較表現為使用 RTRIM （二進位）整理序列。 */SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;--結果 1 2 3 /* c='abc' 的文本比較表現為使用 RTRIM 整理序列。 */SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;--結果 1 2 3 /* 分組表現為使用 NOCASE 整理序列（值'abc'，'ABC' 和 'Abc'** 被分為同一組）。*/SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;--結果 4 /* 分組表現為使用 BINARY 整理序列（值'abc'，'ABC' 和 'Abc'** 被分為不同的組）。*/SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;--結果 1 1 2 /* 列c排序表現為使用 RTRIM 整理序列。*/（譯註：sorting or column c 疑為 sorting of...誤寫）SELECT x FROM t1 ORDER BY c, x;--結果 4 1 2 3 /* (c||'')排序表現為使用 BINARY 整理序列。*/SELECT x FROM t1 ORDER BY (c||''), x;--結果 4 2 3 1 /* 列c排序表現為使用 NOCASE 整理序列。*/SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;--結果 2 4 3 1