SQLite第三版中的資料類型

1.存儲類别

第二版把所有列的值都存儲成ASCII文本格式。第三版則可以把資料存儲成整數和實數,還可以存儲BLOB資料.

Each value stored in an SQLite資料庫中存儲的每個值都有一個屬性,都屬于下面所列類中的一種,(被資料庫引擎所控制)

• 空.這個值為空值
• 整數.值被辨別為整數,依據值的大小可以依次被存儲為1,2,3,4,5,6,7,8.
• 實數. 所有值都是浮動的數值,被存儲為8位元組的IEEE浮動标記序号.
• 文本. 值為文本字元串,使用資料庫編碼存儲(TUTF-8, UTF-16BE or UTF-16-LE).
• BLOB. 值是BLOB資料,如何輸入就如何存儲,不改變格式.

像SQLite2.0版一樣,在3.0版中,除了INTEGER PRIMARY KEY,資料庫中的任何列都可以存儲任何類型的資料.這一規則也有例外,在下面的"嚴格相似模式"中将描述.

輸入SQLite的所有值,不管它是嵌入 SQL語句中的文字還是提前編譯好的綁定在SQL語句中的值,在SQL語句執行前都被存儲為一個類.在下面所描述的情況下,資料庫引擎将在執行時檢查并把值在數字存儲類(整數和實數)和文本類間轉換.

存儲的類别最初被分類為如下:

• 具體的值比如SQL語句部分的帶雙引号或單引号的文字被定義為文本,如果文字沒帶引号并沒有小數點或指數則被定義為整數,如果文字沒帶引号但有小數點或指數則被定義為實數,如果值是空則被定義為空值.BLOB資料使用符号X'ABCD'來辨別.
• Values supplied using the 被輸入的值使用sqlite3_bind_* APIs的被分類一個存儲等級,這等級是和原來的類基本相一緻的. (比如sqlite3_bind_blob()綁定一個BLOB的值).

值的分類是SQL分等級操作的結果,決定于最遠的操作表達式.使用者定義的功能也許會把值傳回任意的類.在編譯的時候來确定表達式的存儲類基本是不可能的.

2. 列之間的親和性

在SQLite3.0版中,值被定義為什麼類型隻和值自身有關,和列沒有關系,和變量也沒有關系. (這有時被稱作 ​​弱類型​​.)所有其它的我們所使用的資料庫引擎都受靜态類型系統的限制,其中的所有值的類是由其所屬列的屬性決定的,而和值無關.

為 了最大限度的增加SQLite資料庫和其他資料庫的相容性,SQLite支援列的"類型親和性". 列的親和性是為該列所存儲的資料建議一個類型.我們要注意是建議而不是強迫.在理論上來講,任何列依然是可以存儲任何類型的資料的. 隻是針對某些列,如果給建議類型的話,資料庫将按所建議的類型存儲.這個被優先使用的資料類型則被稱為"親和類型".

在SQLite3.0版中,資料庫中的每一列都被定義為以下親和類型中的一種:

• 文本
• 數字的
• 整數
• 無

一個具有類型親和性的列按照無類型,文本,或BLOB存儲所有的資料.如果數字資料被插入一個具有文本類型親和性的列,在存儲之前數字将被轉換成文本.

一 個具有數字類型親和性的列也許使用所有的五個存儲類型存儲值.當文本資料被插入一個數字列時,在存儲之前,資料庫将嘗試着把文本轉換成整數或實 數.如果能成功轉換的話,值将按證書活實數的類型被存儲. 如果不能 成功轉換的話,值則隻能按文本類型存儲了,而不會被轉換成無類型或BLOB類型來存儲.

一個具有整數親和力的列在轉換方面和具有數字親和力的列是一樣的,但也有些差別 ,比如沒有浮動量的實值(文本值轉換的值)被插入具有整數親和力的列時,它将被轉換成整數并按整數類型存儲.

一個具有無類型親和力的列不會優先選擇使用哪個類型.在資料被輸入前它不會強迫資料轉換類型.

2.1 列的親和性的決定

一個列的親和類型是由該列所宣稱的類型決定的.遵守以下規則:

1. 如果資料類型包括字元串"INT"那麼它被定義成具有整數親和性.
2. 如果列中的資料類型包括以下任何的字元串 "CHAR", "CLOB", or "TEXT" 那麼這個列則具有文本親和性.要注意VARCHAR類型包括字元串"CHAR"是以也具有文本類型親和性.
3. 如果一個列的資料類型包括字元串"BLOB"或者如果資料類型被具體化了,那麼這個列具有無類型親和性.
4. 否則就具有數字類型親和性.

如果表格使用If "CREATE TABLE AS SELECT..."語句生成的,那麼所有的列則都沒有具體的資料類型,則沒有類型親和性.

2.2 列的親和性的例子

CREATE TABLE t1(

    t  TEXT,

    nu NUMERIC, 

    i  INTEGER,

    no BLOB

);


-- Storage classes for the following row:

-- TEXT, REAL, INTEGER, TEXT

INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0');


-- Storage classes for the following row:

-- TEXT, REAL, INTEGER, REAL

INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0);      

3.比較表達式

像SQLite2.0版一樣,3.0版的一個特性是二進制比較符'=', '<', '<=', '>=' and '!=',一個操作'IN'可以測試固定的成員資格, 三重的比較操作符'BETWEEN'.

比較的結果決定于被比較的兩個值的存儲類型。遵循以下規則：

• 一個具有空存儲類型的值被認為小于任何值（包括另外一個具有空存儲類型的值）。
• 一個整數值或實數值小于任何文本值和BLOB值。 當一個整數或實數和另一個整數或實數相比較的時候，則按照實際數值來比較。
• 一個文本值小于BLOB值。當兩個文本值相比較的時候，則用C語言類庫中的memcmp()函數來比較。然而，有時候也不是這樣的，比如在下面所描述的“使用者定義的整理順序”情況下。
• 當兩個BLOB文本被比較的時候，結果決定于memcmp()函數。

在開始比較前，SQLite嘗試着把值在數字存儲級（整數和實數）和文本之間互相轉換。下面列舉了關于如何比較二進制值的例子。在着重号below中使用的表達式可以表示SQL标量表達式或是文本但不是一個列值。

• 當一個列值被比拟為表達式結果的時候，在比較開始前，列的親和性将被應用在表達結果中。
• 當兩個列值比較的時候，如果一個列有整數或數字親和性的時候，而另外一列卻沒有，那麼數字親和性适用于從非數字列提取的任何具有文本存儲類型的值. P>
• 當比較兩個表達式的結果時,不發生任何轉換,直接比較結果.如果一個字元串和一個數字比較, 數字總是小于字元串.

在SQLite中, 表達式"a BETWEEN b AND c"等于表達式 "a >= b AND a <= c",在比較表達式時,a可以是具有任何親和性.

表 達式 "a IN (SELECT b ....)" 在比較時遵循上面所提到的三條規則,是二進制比較.(例如, 在一個相似的樣式 "a = b"). 例如,如果'b'是一個列值, 'a' 是一個表達式,那麼,在開始比較前,'b'的親和性就被轉換為'a'的親和性了.

SQLite把表達式 "a IN (x, y, z)" 和 "a = z OR a = y OR a = z"視為相等.

3.1 比較例子

CREATE TABLE t1(

    a TEXT,

    b NUMERIC,

    c BLOB

);


-- Storage classes for the following row:

-- TEXT, REAL, TEXT

INSERT INTO t1 VALUES('500', '500', '500');


-- 60 and 40 are converted to '60' and '40' and values are compared as TEXT.

SELECT a < 60, a < 40 FROM t1;

1|0


-- Comparisons are numeric. No conversions are required.

SELECT b < 60, b < 600 FROM t1;

0|1


-- Both 60 and 600 (storage class NUMERIC) are less than '500'

-- (storage class TEXT).

SELECT c < 60, c < 600 FROM t1;

0|0      

4. 運算符

所有的數學運算符(所有的運算符而不是連鎖作用标記符"||")運算對象首先具有數字親和性, 如果一個或是兩個都不能被轉換為數字那麼操作的結果将是空值。

對于連接配接作用操作符，所有操作符将首先具有文本親和性。如果其中任何一個操作符不能被轉換為文本（因為它是空值或是BLOB）連接配接作用操作符将是空值。

5. 分類，排序，混合挑選

當用子句ORDER挑選值時，空值首先被挑選出來, 然後是整數和實數按順序被挑選出來, 然後是文本值按memcmp()順序被挑選出來, 最後是BLOB值按memcmp()順序被挑選出來.在挑選之前, 沒有存儲類型的值都被轉換了.

When grouping values with the 當用GROUP BY子句給值分組時,具有不同存儲類型的值被認為是不同的, 但也有例外, 比如,一個整數值和一個實數值從數字角度來說是相等的,那麼它們則是相等的.用GROUP by 子句比較完後,值不具有任何親和性.

混合 挑選操作符UNION, INTERSECT and EXCEPT 在值之間實行絕對的比較,同樣的親和性将被應用于所有的值,這些值将被存儲在一個單獨的具有混合SELECT的結果組的列中. 被賦予的親和性是該列的親和性,這個親和性是由剩下的大部分的混合SELECTS傳回的,這些混合SELECTS在那個位置上有列值(而不是其它類型的表 達式). 如果一個給定的混合SELECT列沒有SELECTS的量, 那麼在比較前,該列的值将不具有任何親和性.

6. 其它親和性模式

以上的部分所描述的都是資料庫引擎在正常親和性模式下所進行的操作, SQLite将描述其它兩種親和性模式,如下:

• 嚴格親和性模式.在這種模式下,如果需要值之間互相轉換資料存儲類型的話,資料庫引擎将發送錯誤報告,目前語句也将會重新運作.
• 無親和性模式.在這種模式下,值的資料存儲類型不發生轉換.具有不同存儲類型的值之間不能比較,但整數和實數之間可以比較.

7.使用者定義的校對順序

By default, when 當SQLite比較兩個文本值的時候,通過系統設定,不管字元串的編碼是什麼,用memcmp()來比較. SQLite第三版允許使用者提供任意的函數來代替memcmp(),也就是使用者定義的比較順序.

除了系統預設的BINARY比較順序,它是用memcmp()函數比較,SQLite還包含了兩個額外的内置比較順序函數, NOCASE和REVERSE:

• BINARY -使用memcmp()比較字元串資料, 不考慮文本編碼.
• REVERSE -用倒序比較二進制文本.
• NOCASE - 和二進制一樣,但在比較之前,26位的大寫字母盤要被折合成相應的小寫字母盤.

7.1 配置設定比較順序

每個表格中的每個列都有一個預設的比較類型.如果一個比較類型不是二進制所要求的,比較的子句将被具體化為 ​​列的定義​​ 來定義該列.

當用SQLite比較兩個文本值時,比較順序将按照以下的規則來決定比較的結果.文檔的第三部分和第五部分描述在何種場合下發生這種比較.

對 于二進制比較符(=, <, >, <= and >=),如果每個操作數是一列的話,那麼該列的預設比較類型決定于所使用的比較順序. 如果兩個操作數都是列的話,那麼左邊的操作數的比較類型決定了所要使用的比較順序.如果兩個操作數都不是一列,将使用二進制來比較.

7.2 比較順序的例子

CREATE TABLE t1(

    a,                 -- default collation type BINARY

    b COLLATE BINARY,  -- default collation type BINARY

    c COLLATE REVERSE, -- default collation type REVERSE

    d COLLATE NOCASE   -- default collation type NOCASE

);


-- Text comparison is performed using the BINARY collation sequence.

SELECT (a = b) FROM t1;


-- Text comparison is performed using the NOCASE collation sequence.

SELECT (d = a) FROM t1;


-- Text comparison is performed using the BINARY collation sequence.

SELECT (a = d) FROM t1;


-- Text comparison is performed using the REVERSE collation sequence.

SELECT ('abc' = c) FROM t1;


-- Text comparison is performed using the REVERSE collation sequence.

SELECT (c = 'abc') FROM t1;


-- Grouping is performed using the NOCASE collation sequence (i.e. values

-- 'abc' and 'ABC' are placed in the same group).

SELECT count(*) GROUP BY d FROM t1;


-- Grouping is performed using the BINARY collation sequence.

SELECT count(*) GROUP BY (d || '') FROM t1;


-- Sorting is performed using the REVERSE collation sequence.

SELECT * FROM t1 ORDER BY c;


-- Sorting is performed using the BINARY collation sequence.

SELECT * FROM t1 ORDER BY (c || '');


-- Sorting is performed using the NOCASE collation sequence.

SELECT * FROM t1 ORDER BY c COLLATE NOCASE;