A. SQLite資料庫儲存資料時, 主要區分為以下五種Storage Class, 會比Data Type來的更加簡易及統籌分類, 所以在SQLite Storage Class及 Data Type可以互相替代:
1. NULL. The value is a NULL value.
2. INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes
depending on the magnitude of the value.
3. REAL. The value is a floating point value,
stored as an 8-byte IEEE floating point number.
4. TEXT. The value is a text string,
stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
5. BLOB. The value is a blob of data, stored exactly as it was input.
B. SQLite中並沒有Boolean, 會使用Integer的1和0來代表.
C. 在SQLite中會使用TEXT, REAL, INTEGER代表Date and Time:
1. TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
2. 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.
3. INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UT
Type Affinity
最主要是設計用來能夠更相容於其他資料庫的Data Type, 目的在於找出資料儲存在Column中建議最適合的Storage Class, 所以在使用SQLite設計資料表時, 甚至可以不需要定義資料型態, 資料型態是建議不是必須! 當沒有指定資料型態時, 該Column可以儲存任何Storage Class的資料.
A. Type Affinity有以下五種:
1. TEXT
2. NUMERIC
3. INTEGER
4. REAL
5. NONE
B. Rules規則:
Rule 1: 如果宣告的型態中有包含"INT"字串則會被歸類為INTEGER affinity,
也就是直接使用INTEGER Storage Class.
例如: INT, INTEGER, TINYINT. SMALLINT. MEDIUMINT,
BIGINT. UNSIGNED BIG INT, INT2, INT等
Rule 2: 如果宣告的型態中有包含"CHAR", "CLOB", or "TEXT"字串,
則會被歸類為TEXT affinity,
例如: VARCHAR有包含"CHAR", 所以使用TEXT Storage Class.
也就是直接使用TEXT Storage Class.
例如: CHARACTER(20), VARCHAR(255), VARYING CHARACTER(255),
NCHAR(55), NATIVE CHARACTER(70), NVARCHAR(100). TEXT, CLOB等
Rule 3: 如果宣告的型態中有包含"BLOB"字串, 則會被歸類 NONE affinity,
也就是會直接判斷所塞入的值是適合那一個Storage Class.
例如: BLOB, 塞入的是BLOBs值則不會管Type affinity，
全部都會使用BLOB Storage Class
Rule 4: 如果宣告的型態中有包含"REAL", "FLOA", or "DOUB"字串, 則會被歸類REAL affinity.
也就是直接使用REAL Storage Class.
例如: REAL, DOUBLE, DOUBLE PRECISION, FLOA等
Rule 5: 其他會被歸類為NUMERIC affinity,
會直接判斷所塞入的值是適合那一個Storage Class.
例如: NUMERIC, DECIMAL(10,5), BOOLEAN, DATE, DATETIME等
Rule 6: 如果塞入的是NULL值則不會管Type affinity, 全部都會使用NULL Storage Class.
範例:
--建立資料表
CREATE TABLE t1(
t TEXT, -- 套用Rule 2
nu NUMERIC, -- 套用Rule 5
i INTEGER, -- 套用Rule 1
r REAL, -- 套用Rule 4
no BLOB -- 套用Rule 3
);
-- 塞入字串
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
-- 塞入浮點數1
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
-- 塞入浮點數2
DELETE FROM t1;
INSERT INTO t1 VALUES(500.0, 500.123, 500.0, 500.0, 500.0);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|real|integer|real|real
-- 塞入整數
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
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
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
轉貼請註明出處，最好直接使用聯結轉貼!Thanks~
作者： Samuel - 林靖傑
