SQLite3 類型系統

大多數資料庫使用靜态的嚴格的類型系統，列的類型在建立表的時候就已經指定了。SQLite使用動态的類型系統，列的類型由值決定。

1.存儲類型

以下是SQLite的存儲類型，SQLite中存儲的每個值都對應以下一種存儲類型

NULL: NULL value

Integer: 值是signed integer 類型，大小可以是1,2,3,4,6,8bytes

REAL:  浮點類型

TEXT: 以UTF-8, UTF-16BE or UTF-16LE編碼存儲的字元類型

BLOB: 二進制資料

Integer primary key 列是個例外??

布爾類型: SQLite中沒有定義布爾類型，而是以Integer 存儲布爾值，0(false), 1(true)

Date and time 類型

SQLite中也沒有定義日期時間類型，日期時間可以用TEXT, REAL, or INTEGER存儲

TEXT:  存儲為字元串("YYYY-MM-DD HH:MM:SS.SSS").

REAL: as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.

INTEGER: as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

2. SQLite Type Affinity

用于自動檢測值的類型，以下列舉Affinity如何決定類型的規則

(1)    如果類型聲明中有int，則使用INTEGER affinity.

(2)    如果類型聲明中有"CHAR", "CLOB", or "TEXT"， 則使用Text affinity

(3)    如果類型聲明中有BLOB或沒有指定類型，則使用affinity NONE

(4)    如果類型聲明中有"REAL", "FLOA", or "DOUB"， 則使用 REAL affinity

(5)    否則使用Numeric affinity

SQLite 有以下幾種affinity

• TEXT
• NUMERIC
• INTEGER
• REAL
• NONE

例子

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 affinity

DELETE 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. 類型比較

NULL < TEXT< BLOB < INTEGER or REAL, INTEGER,REAL之間用數學方法比較, BLOB之間用memcmp()函數比較

memcmp函數原型

int memcmp ( const void * ptr1, const void * ptr2, size_t num );

比較兩個指針指向記憶體的前num個byte

比較之前的類型轉換

• (INTEGER, REAL or NUMERIC) 和 (TEXT or NONE)比較，則TEXT, NONE會被轉換成NUMERIC
• TEXT和NONE比較，則NONE會被轉換成TEXT
• 其他情況直接比較

參考

http://www.sqlite.org/datatype3.html (Datatypes in SQLite Version 3)