SQLite的資料型態

1 篇文章 / 0 new
發表回應前，請先登入
週四, 2011-03-24 09:49
#1
author
SQLite的資料型態

多數的資料庫都使用靜態欄位型態。而 SQLite 則是使用動態欄位型態。在 SQLite 一個資料的型態會根據資料本身而定，不是依所處的欄位。SQLite 的動態欄位型態可以向後相容多數的靜態欄位型態，也就是 SQLite 的 SQL 字串可以使用舊有的靜態欄位型態。

儲存類別 (Class)

在 SQLite 是依下列五種類別儲存。在 SQLite3 里的欄位(除了 INTEGER PRIMARY KEY) 都被設定成儲存任一類別，不區分類別及型態。

  1. NULL: 就是 NULL 值。
  2. INTEGER: 有號整數，欄位大小有 1,2,3,4,6,8 byte(s)，依數值大小而定。
  3. REAL: 浮點數，欄位大小 8 bytes。
  4. TEXT: 不固定長度字串，字串編碼格式分 UTF-8/UTF-16BE/UTF16LE。
  5. BLOB: 二進制資料。

資料型態 (Dataype)

  1. Boolean (布林數)
    布林數會儲存成 INTEGER，0 or 1。
  2. Date and Time (日期時間)
    日期時間資料會依據 SQLite 內建的日期時間函數而存成 TEXT, REAL or INTEGER。
    1. TEXT
      ISO8601 字串("YYYY-MM-DD HH:MM:SS.SSS)。
    2. REAL
      Julian時間，以日為單位，計算開始時間依公曆(proleptic Gregorian)，格林威治時間，西元前 4714 年 11 月 24 日中午12點起。
    3. INTEGER
      unix 時間，以秒為單位，起始時間 1970-01-01 00:00:00 UTC。

相似性型態 (Type Affinity)

為了相容其它資料庫，SQLite 提供一種概念叫"欄位相似性型態"。"欄位相似性型態"是指儲存在欄位內資料的建議資料型態。重要的是型態是建議而非必需。任何欄位仍然可以儲存任何型態的資料。

在 SQLite3 的每一個欄位可以設定下列的相似型態。

  1. TEXT
    TEXT 儲存資料是使用 NULL, TEXT, BLOB 類別。假如數值資料填入 TEXT 欄位，則會先將數值轉換成字串再存入。
  2. NUMERIC
    NUMERIC 儲存資料是使用 NULL, INTEGER, REAL, TEXT, BLOB 所有類別。當字串資料填入 NUMERIC 欄位，如字串可以無誤差轉換成數值且可以再從數值無誤差轉換成文字，則字串會轉換成 INTEGER or REAL。若無法做到字串無差異轉換成數值，則把字串存成 TEXT。NULL 及 BLOB 資料不會做轉換，直接存入。 一個看似浮點數的字串，例如有小數點的字串或指數的字串，則轉換成數值後只是整數，NUMERIC 就會把字串轉換成 INTEGER。如 '3.0e+5' 存入 NUMERIC 欄位，會被轉成整數 300000，而非浮點數 300000.0。
  3. INTEGER
    INTEGER 的處理方式跟 NUMERIC 一樣，唯一的不同處只在 CAST。
  4. REAL
    REAL 的處理方式跟 NUMERIC 一樣，只差在 REAL 會把整數資料強制轉換成浮點數。(因為內部最佳化，很小的浮點數值且沒有帶小數點，在寫入硬碟會轉成換 INTEGER(為了省空間)，在被讀取時會自動轉換成浮點數。這個最佳化在 SQLite 是完全看不出來，只能在計算 raw bits 才能發現。)
  5. NONE
    NONE 就是不做任何轉換動作，輸入什麼資料就存入什麼資料。

欄位型態相容性的計算，主要根據下列規則順序。注意下列規則是有順序的，比如欄位型態名稱宣告為 "FLOATING POINT" 時，是採用 "INTEGER"，因為字尾 "INT" 符合第一項規則，而開頭 "FLOA" 符合第四項規則不必採納。還有 "STRING" 會被轉換成 "NUMERIC" 而非 "TEXT"。

  1. 型態名稱包含字串 "INT" 會被指定成 "INTEGER"。
  2. 型態名稱包含字串 "CAHR", "CLOB", "TEXT" 都會指定成 "TEXT"。所以 "VARCHAR" 包含 "CHAR" 也是屬於 "TEXT" 的一種。
  3. 型態名稱包含字串 "BLOB" 或沒有名稱則指定成 "NONE"。
  4. 型態名稱包含字串 "REAL", "FLOA", "DOUB" 都會指定成 "REAL"。
  5. 其它則都指定成 "NUMERIC"。

比較運算式 (Comparison Expressions)

SQLite3 的比較運算子(operator)包含 "=", "<", "<=", ">=", "!=", "IN", "BETWEEN", "IS"。不同儲存類別的比較，需依據下列規則。

  1. "NULL" 被視為比其它值都小，包含其它值也是 "NULL"。(???)
  2. "INTEGER" or "REAL" 被視為比 "TEXT" or "BLOB" 小。當 "INTEGER" or "REAL" 互相比較時，則根據數值比較方式。
  3. "TEXT" 被視為比 "BLOB" 小。當 "TEXT" 互相比較時，則會採用一個適當的排序方法。
  4. 當 "BLOB" 互相比較時，則依據 memcmp() 來比較。
  5. NULL < INTEGER, REAL < TEXT < BLOB。

SQLite 可能企圖在 INTEGER, REAL, TEXT 互相比較前轉換成相同儲存類別。

  1. An expression that is a simple reference to a column value has the same affinity as the column. Note that if X and Y.Z are column names, then +X and +Y.Z are considered expressions for the purpose of determining affinity.
  2. "CAST(expr as type)" 里的運算式(expr)則擁有 type 相似性。
  3. 以上二者條件都不合，則運算式則為 "NONE" 相似性。

"apply affinity" 指轉換運算元成一個特定的儲存類別，當且僅當此轉換是無損且可逆轉的。此轉換是在比較之前發生，而且根據下列規格。

  1. 假如運算元為 "INTEGER", "REAL", "NUMERIC"，另一個運算元為 "TEXT", "NONE"，則另一個運算元會被轉換成 "NUMERIC"。
  2. 假如運算元為 "TEXT"，另一個運算元為 "NONE"，則另一個運算元會被轉換成 "TEXT"。
  3. 以上二者條件都不合，則不做任何轉換。

"a BETWEEN b and c"會被視做二個運算式 "a>=b AND a<=c"，即使這意味著不同的運算式，'a' 會有不同的 affinity。"x IN (SELECT y ...)" 被視做 "x=y"，但 "a IN (x, y, z, ...)則被看做 "a=+x OR a=+y OR a=+z OR ...)，也就是說 x,y,z 這些值是不會有 affinity，即使它們是欄位儲存值或使用 CAST 轉換。

 

參考網址 jyhshin.pixnet.net/blog/post/31988011 , www.sqlite.org/datatype3.html

頂
Free Web Hosting