SQLite教程（七）：資料類型詳解，sqlite資料類型

SQLite教程（七）：資料類型詳解，sqlite資料類型

一、儲存種類和資料類型：

    SQLite將資料值的儲存劃分為以下幾種儲存類型：
 複製代碼 代碼如下:
     NULL: 表示該值為NULL值。
     INTEGER: 無符號整型值。
     REAL: 浮點值。
     TEXT: 文本字串，儲存使用的編碼方式為UTF-8、UTF-16BE、UTF-16LE。
     BLOB: 儲存Blob資料，該類型資料和輸入資料完全相同。
 
    由於SQLite採用的是動態資料類型，而其他傳統的關係型資料庫使用的是待用資料類型，即欄位可以儲存的資料類型是在表聲明時即以確定的，因此它們之間在資料存放區方面還是存在著很大的差異。在SQLite中，存放裝置分類和資料類型也有一定的差別，如INTEGER儲存類別可以包含6種不同長度的Integer資料類型，然而這些INTEGER資料一旦被讀入到記憶體後，SQLite會將其全部視為佔用8個位元組無符號整型。因此對於SQLite而言，即使在表聲明中明確了欄位類型，我們仍然可以在該欄位中儲存其它類型的資料。然而需要特別說明的是，儘管SQLite為我們提供了這種方便，但是一旦考慮到資料庫平台的可移植性問題，我們在實際的開發中還是應該儘可能的保證資料類型的儲存和聲明的一致性。除非你有極為充分的理由，同時又不再考慮資料庫平台的移植問題，在此種情況下確實可以使用SQLite提供的此種特徵。

   1. 布林值資料型別：

    SQLite並沒有提供專門的布爾儲存類型，取而代之的是儲存整型1表示true，0表示false。

   2. 日期和時間資料類型：

    和布爾類型一樣，SQLite也同樣沒有提供專門的日期時間儲存類型，而是以TEXT、REAL和INTEGER類型分別不同的格式表示該類型，如：
 複製代碼 代碼如下:
    TEXT: "YYYY-MM-DD HH:MM:SS.SSS"
    REAL: 以Julian日期格式儲存
    INTEGER: 以Unix時間形式儲存資料值，即從1970-01-01 00:00:00到目前時間所流經的秒數。
 

二、類型親緣性：

    為了最大化SQLite和其它資料庫引擎之間的資料類型相容性，SQLite提出了"類型親緣性(Type Affinity)"的概念。我們可以這樣理解"類型親緣性 "，在表欄位被聲明之後，SQLite都會根據該欄位聲明時的類型為其選擇一種親緣類型，當資料插入時，該欄位的資料將會優先採用親緣類型作為該值的儲存方式，除非親緣類型不符或無法轉換當前資料到該親緣類型，這樣SQLite才會考慮其它更適合該值的類型儲存該值。SQLite目前的版本支援以下五種親緣類型：

親緣類型	描述
TEXT	數值型資料在被插入之前，需要先被轉換為文字格式設定，之後再插入到目標欄位中。
NUMERIC	當文本資料被插入到親緣性為NUMERIC的欄位中時，如果轉換操作不會導致資料資訊丟失以及完全可逆，那麼SQLite就會將該文本資料轉換為INTEGER或REAL類型的資料，如果轉換失敗，SQLite仍會以TEXT方式儲存該資料。對於NULL或BLOB類型的新資料，SQLite將不做任何轉換，直接以NULL或BLOB的方式儲存該資料。需要額外說明的是，對於浮點格式的常量文本，如"30000.0"，如果該值可以轉換為INTEGER同時又不會丟失數值資訊，那麼SQLite就會將其轉換為INTEGER的儲存方式。
INTEGER	對於親緣類型為INTEGER的欄位，其規則等同於NUMERIC，唯一差別是在執行CAST運算式時。
REAL	其規則基本等同於NUMERIC，唯一的差別是不會將"30000.0"這樣的文本資料轉換為INTEGER儲存方式。
NONE	不做任何的轉換，直接以該資料所屬的資料類型進行儲存。

1. 決定欄位親緣性的規則：

    欄位的親緣性是根據該欄位在聲明時被定義的類型來決定的，具體的規則可以參照以下列表。需要注意的是以下列表的順序，即如果某一欄位類型同時符合兩種親緣性，那麼排在前面的規則將先產生作用。
    1). 如果類型字串中包含"INT"，那麼該欄位的親緣類型是INTEGER。
    2). 如果類型字串中包含"CHAR"、"CLOB"或"TEXT"，那麼該欄位的親緣類型是TEXT，如VARCHAR。
    3). 如果類型字串中包含"BLOB"，那麼該欄位的親緣類型是NONE。
    4). 如果類型字串中包含"REAL"、"FLOA"或"DOUB"，那麼該欄位的親緣類型是REAL。
    5). 其餘情況下，欄位的親緣類型為NUMERIC。

    2. 具體樣本：

宣告類型	親緣類型	應用規則
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	NONE	3
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL	4
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	NUMERIC	5

註：在SQLite中，類型VARCHAR(255)的長度資訊255沒有任何實際意義，僅僅是為了保證與其它資料庫的聲明一致性。

三、比較運算式：

    在SQLite3中支援的比較運算式有："=", "==", "<", "<=", ">", ">=", "!=", "<>", "IN", "NOT IN", "BETWEEN", "IS" and "IS NOT"。
    資料的比較結果主要依賴於運算元的儲存方式，其規則為：
    1). 儲存方式為NULL的數值小於其它儲存類型的值。
    2). 儲存方式為INTEGER和REAL的數值小於TEXT或BLOB類型的值，如果同為INTEGER或REAL，則基於數值規則進行比較。
    3). 儲存方式為TEXT的數值小於BLOB類型的值，如果同為TEXT，則基於文本規則(ASCII值)進行比較。
    4). 如果是兩個BLOB類型的數值進行比較，其結果為C運行時函數memcmp()的結果。

四、操作符：

    所有的數學操作符(+, -, *, /, %, <<, >>, &, and |)在執行之前都會先將運算元轉換為NUMERIC儲存類型，即使在轉換過程中可能會造成資料資訊的丟失。此外，如果其中一個運算元為NULL，那麼它們的結果亦為NULL。在數學操作符中，如果其中一個運算元看上去並不像數實值型別，那麼它們結果為0或0.0。