1. SQLite中的數(shù)據(jù)類型

大多數(shù)SQL數(shù)據(jù)庫引擎(除了SQLite之外的每個SQL數(shù)據(jù)庫引擎，據(jù)我們所知)都使用靜態(tài)，嚴(yán)格的類型。使用靜態(tài)類型時，列的數(shù)據(jù)類型由其容器(存儲值的特定列)確定。

SQLite使用更通用的動態(tài)類型系統(tǒng)。在SQLite中，值的數(shù)據(jù)類型與值本身相關(guān)聯(lián)，而不是與其容器相關(guān)聯(lián)。SQLite的動態(tài)類型系統(tǒng)向后兼容其他數(shù)據(jù)庫引擎的更常見的靜態(tài)類型系統(tǒng)，因為在靜態(tài)類型數(shù)據(jù)庫上工作的SQL語句應(yīng)該在SQLite中以相同的方式運行。但是，SQLite中的動態(tài)類型允許它執(zhí)行傳統(tǒng)的剛性類型數(shù)據(jù)庫中無法實現(xiàn)的操作。

2. 存儲類和數(shù)據(jù)類型

存儲在SQLite數(shù)據(jù)庫中(或由數(shù)據(jù)庫引擎操縱)的每個值都是以下存儲類之一：

• NULL。該值為NULL值。
• INTEGER。該值是有符號整數(shù)，存儲為1,2,3,4,6或8個字節(jié)，具體取決于值的大小。
• REAL。該值是浮點值，存儲為8字節(jié)IEEE浮點數(shù)。
• TEXT。該值是一個文本字符串，使用數(shù)據(jù)庫編碼(UTF-8，UTF-16BE或UTF-16LE)存儲。
• BLOB。該值是一個數(shù)據(jù)塊，可以二進(jìn)制的形式存儲任何數(shù)據(jù)。

存儲類比數(shù)據(jù)類型更通用。例如，INTEGER存儲類包括6種不同長度的不同整數(shù)數(shù)據(jù)類型。這在磁盤上有所不同。但是一旦從磁盤讀取INTEGER值并進(jìn)入內(nèi)存進(jìn)行處理，它們就會轉(zhuǎn)換為最通用的數(shù)據(jù)類型(8字節(jié)有符號整數(shù))。因此，在大多數(shù)情況下，“存儲類”與“數(shù)據(jù)類型”無法區(qū)分，并且這兩個術(shù)語可以互換使用。

除了INTEGER PRIMARY KEY列之外，SQLite版本3數(shù)據(jù)庫中的任何列都可用于存儲任何存儲類的值。

SQL語句中的所有值，無論是嵌入在SQL語句文本中的文字還是綁定到預(yù)編譯SQL語句的參數(shù)， 都具有隱式存儲類。在下面描述的情況下，數(shù)據(jù)庫引擎可以在查詢執(zhí)行期間在數(shù)值存儲類(INTEGER和REAL)和TEXT之間轉(zhuǎn)換值。

(1) 布爾數(shù)據(jù)類型

SQLite沒有單獨的布爾存儲類。相反，布爾值存儲為整數(shù)0(假)和1(真)。

(2) 日期和時間數(shù)據(jù)類型

SQLite沒有為存儲日期和/或時間而預(yù)留的存儲類。相反，SQLite 的內(nèi)置日期和時間函數(shù)能夠?qū)⑷掌诤蜁r間存儲為TEXT，REAL或INTEGER值：

• TEXT為ISO8601字符串(“YYYY-MM-DD HH：MM：SS.SSS”)。
• REAL如朱利安日數(shù)，根據(jù)公歷4714年11月24日格林威治中午以來的天數(shù)。
• INTEGER as Unix Time，自1970-01-01 00:00:00 UTC以來的秒數(shù)。

應(yīng)用程序可以選擇以任何這些格式存儲日期和時間，并使用內(nèi)置的日期和時間函數(shù)在格式之間自由轉(zhuǎn)換 。

3. Type Affinity

使用嚴(yán)格類型的SQL數(shù)據(jù)庫引擎通常會嘗試自動將值轉(zhuǎn)換為適當(dāng)?shù)臄?shù)據(jù)類型。考慮一下：

CREATE TABLE t1(a INT, b VARCHAR(10)); 
INSERT INTO t1(a,b) VALUES('123',456); 

在執(zhí)行插入之前，剛性類型的數(shù)據(jù)庫將字符串'123'轉(zhuǎn)換為整數(shù)123，將整數(shù)456轉(zhuǎn)換為字符串'456'。

為了最大化SQLite和其他數(shù)據(jù)庫引擎之間的兼容性，上面的示例將像在其他SQL數(shù)據(jù)庫引擎上一樣對SQLite起作用，SQLite支持列上的“類型親和性”概念。列的類型親緣關(guān)系是存儲在該列中的數(shù)據(jù)的推薦類型。這里的重要思想是建議使用類型，而不是必需的。任何列仍然可以存儲任何類型的數(shù)據(jù)。根據(jù)選擇，某些列更傾向于使用一個存儲類而不是另一個存儲類。列的首選存儲類稱為“親和性”。

SQLite 3數(shù)據(jù)庫中的每一列都分配了以下類型之一：

• TEXT
• NUMERIC
• INTEGER
• REAL
• BLOB

(歷史記錄：“BLOB”類型的親和力曾被稱為“NULL”。但該術(shù)語容易與“無親和力”混淆，因此它被重命名。)

具有TEXT親緣關(guān)系的列使用存儲類NULL，TEXT或BLOB存儲所有數(shù)據(jù)。如果將數(shù)值數(shù)據(jù)插入到具有TEXT親和力的列中，則在存儲之前將其轉(zhuǎn)換為文本形式。

具有NUMERIC親緣關(guān)系的列可能包含使用所有五個存儲類的值。當(dāng)文本數(shù)據(jù)插入NUMERIC列時，如果此類轉(zhuǎn)換是無損且可逆的，則文本的存儲類將轉(zhuǎn)換為INTEGER或REAL(按優(yōu)先順序)。對于TEXT和REAL存儲類之間的轉(zhuǎn)換，如果保留數(shù)字的前15個有效十進(jìn)制數(shù)字，SQLite認(rèn)為轉(zhuǎn)換是無損且可逆的。如果無法將TEXT無損轉(zhuǎn)換為INTEGER或REAL，則使用TEXT存儲類存儲該值。不嘗試轉(zhuǎn)換NULL或BLOB值。

字符串可能看起來像帶有小數(shù)點和/或指數(shù)表示法的浮點字面值，但只要該值可以表示為整數(shù)，NUMERIC親和關(guān)系就會將其轉(zhuǎn)換為整數(shù)。因此，字符串'3.0e + 5'存儲在具有NUMERIC親和度作為整數(shù)300000的列中，而不是作為浮點值300000.0。

使用INTEGER關(guān)聯(lián)的列與具有NUMERIC關(guān)聯(lián)的列的行為相同。INTEGER和NUMERIC親和力之間的區(qū)別僅在CAST表達(dá)式中很明顯。

具有REAL親和性的列的行為類似于具有NUMERIC親和力的列，除了它將整數(shù)值強(qiáng)制為浮點表示形式。(作為內(nèi)部優(yōu)化，沒有小數(shù)組件并存儲在具有REAL關(guān)聯(lián)性的列中的小浮點值將作為整數(shù)寫入磁盤，以便占用更少的空間，并在讀取值時自動轉(zhuǎn)換回浮點。優(yōu)化在SQL級別完全不可見，只能通過檢查數(shù)據(jù)庫文件的原始位來檢測。)

具有關(guān)聯(lián)性BLOB的列不優(yōu)選一個存儲類而不是另一個存儲類，并且不會嘗試將數(shù)據(jù)從一個存儲類強(qiáng)制轉(zhuǎn)換為另一個存儲類。