官方文檔：http://www.sqlite.org/datatype3.html#affinity

絕大多數(shù)數(shù)據(jù)庫引擎都是用靜態(tài)(除SQLite之外的其他所有SQL數(shù)據(jù)庫引擎), 嚴(yán)格的數(shù)據(jù)類型. 使用靜態(tài)數(shù)據(jù)類型的情況下, 數(shù)據(jù)類型由值的容器決定– 也就是存儲(chǔ)該值的列.
SQLite使用一個(gè)更加通用的類型系統(tǒng). 在SQLite中, 值的數(shù)據(jù)類型是和值本身相關(guān)聯(lián)的, 而不是其容器決定的. SQLite的動(dòng)態(tài)數(shù)據(jù)類型向后兼容其他較常見的使用靜態(tài)數(shù)據(jù)類型的數(shù)據(jù)庫系統(tǒng), 即在靜態(tài)數(shù)據(jù)類型數(shù)據(jù)庫系統(tǒng)上使用的SQL語句同樣適用于SQLite. 但是在SQLite上進(jìn)行的動(dòng)態(tài)數(shù)據(jù)類型操作卻不可能適用于傳統(tǒng)的靜態(tài)數(shù)據(jù)類型的數(shù)據(jù)庫系統(tǒng).

注意：類型名后括號(hào)里的數(shù)值型的參數(shù)聲明(例如: “VARCHAR(255)”)將被SQLite忽略– SQLite并不接受字符串, BLOBs和數(shù)值型數(shù)據(jù)的強(qiáng)制的長(zhǎng)度限制(除了全局的SQLITE_MAX_LENGTH限制規(guī)則).

_db = [FMDatabase databaseWithPath:databasePath];
_dbChar = [FMDatabase databaseWithPath:databaseCharPath];
[_db open];
[_dbChar open];
[_db executeUpdate:@"create table lbx (name text)"];
[_dbChar executeUpdate:@"create table lbx (name char(250))"];
for (NSInteger i = 0; i < 100000; i++) {
[_db executeUpdate:@"insert into lbx values ('a')"];
[_dbChar executeUpdate:@"insert into lbx values ('a')"];
}
[_dbChar close];
[_db close];

Snip20170312_1.png

1、Storage Classes and Datatypes

每一個(gè)存儲(chǔ)在SQLite數(shù)據(jù)庫系統(tǒng)中的值(或者被數(shù)據(jù)庫引擎操縱)必定屬于以下幾個(gè)類型中的一個(gè):

• NULL. 值為NULL
• INTEGER. 有符號(hào)的整形數(shù)值, 按數(shù)值大小以1,2,3,41,2,3,4,6或8字節(jié)存儲(chǔ).
• REAL. 浮點(diǎn)數(shù)值, 以8字節(jié)IEEE標(biāo)準(zhǔn)浮點(diǎn)數(shù)存儲(chǔ).
• TEXT. 文本字符串, 以數(shù)據(jù)庫的字符編碼方式存儲(chǔ)(UTF-8, UTF-16BE或者UTF-16LE).
• BLOB. 該值是一個(gè)BLOB數(shù)據(jù), 將以與輸入時(shí)一致的二進(jìn)制碼存儲(chǔ).

存儲(chǔ)類型相比數(shù)據(jù)類型是更加通用的. 比如說INTEGER存儲(chǔ)類包含6種不同長(zhǎng)度的整形數(shù)據(jù)類型. 雖然6種類型是以不同方式存儲(chǔ)在磁盤上, 但I(xiàn)NTEGER數(shù)據(jù)一旦從磁盤里讀取到內(nèi)存中用于處理是, 所有的6種類型都會(huì)被轉(zhuǎn)換為最通用的數(shù)據(jù)類型(8字節(jié)有符號(hào)整數(shù)). 因此大部分情況下, “存儲(chǔ)類型”與”數(shù)據(jù)類型”并沒有什么分別, 可以相互替換表示.

SQLite3中的任何列, 除INTEGER PRIMARY KEY列之外, 可以用來存儲(chǔ)以上提到的任何類型(storage class)的值.

SQL語句中所有的值, 無論他們是以純文本方式嵌入到SQL語句中還是以預(yù)編譯方式綁定到SQL語句的參數(shù)都有一個(gè)隱含的存儲(chǔ)類型.

1.1 Boolean Datatype

SQLite沒有單獨(dú)的布爾存儲(chǔ)類型. 在SQLite中, 布爾值, 真被存儲(chǔ)為整形數(shù)值1, 假被存儲(chǔ)為整形數(shù)值0.

1.2 Date and Time Datatype

SQLite沒有單獨(dú)的日期和時(shí)間存儲(chǔ)類型. 作為替代的是,SQLite內(nèi)建的日期和時(shí)間函數(shù)可用于處理以TEXT, REAL或INTEGER方式存儲(chǔ)的日期:

• TEXT 以ISO8601標(biāo)準(zhǔn)表示的字符串(“YYYY-MM-DD HH:MM:SS.SSS”).
• REAL 以Julian day計(jì)算（儒略日）, 公元前4714年11月24日至日期所經(jīng)歷的天數(shù).
• INTEGER 以Unix時(shí)間方式表示, 即1970-01-01 00:00:00 UTC至日期所經(jīng)歷的秒數(shù).

在應(yīng)用中可以自由地使用以上3種方式存儲(chǔ)日期和時(shí)間, 也可以用內(nèi)建的日期和時(shí)間函數(shù)在它們之間做任意的轉(zhuǎn)換.

2.0 Type Affinity (親和類型)

具有嚴(yán)格數(shù)據(jù)類型的SQL數(shù)據(jù)庫通常會(huì)自動(dòng)將數(shù)值轉(zhuǎn)換為合適的數(shù)據(jù)類型。如下：

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

嚴(yán)格數(shù)據(jù)類型的數(shù)據(jù)庫在插入數(shù)據(jù)前會(huì)將字符串‘123’轉(zhuǎn)換為整型123，將整型456轉(zhuǎn)換為字符串‘456’。

為了使SQLite更加兼容其他的數(shù)據(jù)庫引擎, SQLite支持列”親和類型”概念. 所謂的列的親和類型就是在該列中推薦使用的存儲(chǔ)類型. 必須注意的是, 這里只是推薦的類型, 而非必須使用的類型. 任何列依然可以存儲(chǔ)任何類型的數(shù)據(jù). 這只是說, 在一些列中我們會(huì)給予一些選擇, 這將使得我們會(huì)優(yōu)先使用一種存儲(chǔ)類型. 優(yōu)先使用的存儲(chǔ)類型被叫做該列的”親和(affinity)”.
SQLite3的每列親和類型是一下幾種之一:

• TEXT
• NUMERIC
• INTEGER
• REAL
• BLOB（過去成為NONE）

具有TEXT親和類型的列存儲(chǔ)的數(shù)據(jù)類型有NULL, TEXT和BLOB. 如果將數(shù)值型的數(shù)據(jù)插入到具有TEXT親和類型的列中, 那么在該值被存儲(chǔ)前將會(huì)被轉(zhuǎn)換為字符型.

具有NUMERIC親和類型的列可存儲(chǔ)所有5種存儲(chǔ)類型的值. 當(dāng)把文字信息插入到NUMERIC列中, 在不損失精度且轉(zhuǎn)換可逆的情況下, 該段文字的存儲(chǔ)類型將被轉(zhuǎn)換至INTEGER或REAL類型. 在轉(zhuǎn)換TEXT和REAL時(shí), 如果字段的前15位十進(jìn)制數(shù)能被保留的話, 那么SQLite將認(rèn)為該轉(zhuǎn)換是無損和可逆的. 如果將TEXT無損轉(zhuǎn)換為INTEGER或REAL的過程被判斷為不可能完成的話, 那么該字段信息將會(huì)用TEXT存儲(chǔ)類型存入數(shù)據(jù)庫.SQLite在任何情況下都不會(huì)嘗試轉(zhuǎn)換NULL或BLOB型數(shù)據(jù)信息.

如果一個(gè)字符串是有小數(shù)點(diǎn)的浮點(diǎn)數(shù)有指數(shù)符號(hào)的浮點(diǎn)數(shù), 但只要該值能被表示成一個(gè)整型數(shù)據(jù), 則NUMERIC親和將會(huì)將其轉(zhuǎn)換成一個(gè)整型數(shù)值. 因此, ‘3.0e+5’在具有NUMERIC親和的列下將被存儲(chǔ)為300000, 而不是值為300000.0浮點(diǎn)數(shù).

具有INTEGER親和類型的列與具有NUMERIC親和類型的列的規(guī)則一致. INTEGER和NUMERIC親和類型的唯一明顯的區(qū)別是在CAST表達(dá)式中.

具有REAL親和類型的列與NUMERIC親和類型表現(xiàn)一致, 除了它會(huì)強(qiáng)制把整型數(shù)據(jù)轉(zhuǎn)換至浮點(diǎn)形式(一個(gè)內(nèi)部的優(yōu)化是, 將絕對(duì)值很小的浮點(diǎn)數(shù)存入具有REAL親和類型的列時(shí), 為了節(jié)省數(shù)據(jù)占用空間, 該值將以整型寫入到磁盤中, 在讀出時(shí)會(huì)被自動(dòng)轉(zhuǎn)換回浮點(diǎn)數(shù). 該優(yōu)化在SQL層面上是完全不可見的, 只能在檢測(cè)原始數(shù)據(jù)庫文件比特序列時(shí)被發(fā)現(xiàn)).
具有BLOB親和類型的列不會(huì)優(yōu)先使用任何一個(gè)存儲(chǔ)類型, 也不會(huì)強(qiáng)制地將一種數(shù)據(jù)存儲(chǔ)類型轉(zhuǎn)換為其他存儲(chǔ)類型.

2.1 Determination Of Column Affinity

一個(gè)列的親和類型將會(huì)被該字段的聲明類型所決定, 按順序地根據(jù)以下規(guī)則將可以得出親和類型:

1. 如果聲明類型包含字符串”INT”, 將會(huì)被賦予INTEGER親和類型.
2. 如果聲明類型包含字符串”CHAR”, “CLOB”或者”TEXT”, 那么該字段將被賦予TEXT親和類型. 注意因?yàn)閂ARCHAR包含”CHAR”, 故也被賦予TEXT親和類型.
3. 如果聲明類型包含字符串”BLOB”或者沒有指定類型, 那么該字段將擁有”NONE”親和類型.
4. 如果聲明類型包含字符串”REAL”, “FLOA”或者”DOUB”, 那么該字段將擁有REAL親和類型.
5. 否則, 親和類型將是NUMERIC.

注意以上的親和類型順序是是十分重要的. 如果一個(gè)字段的聲明類型是”CHARINT”, 它將符合規(guī)則1和規(guī)則2, 但是規(guī)則1的優(yōu)先權(quán)高于規(guī)則2, 所以該字段的親和類型將是INTEGER.

2.2 Affinity Name Examples

下表展示了傳統(tǒng)SQL實(shí)現(xiàn)的通用數(shù)據(jù)類型在上面5條規(guī)則的作用下是如何被轉(zhuǎn)換成相應(yīng)的親和類型的. 下表只展示了SQLite支持的數(shù)據(jù)類型名的一小部分.

2.2 Affinity Of Expressions

表格的所有列都有親和類型，但是表達(dá)式（expression）不一定有親和類型。表達(dá)式的親和由下面的規(guī)則決定：

• 如果IN或NOT IN表達(dá)式的操作數(shù)是個(gè)列表，則該操作數(shù)沒有親和，如果操作數(shù)是select語句，則操作數(shù)的親和為select結(jié)果集的親和。
• 當(dāng)一個(gè)表達(dá)式引用一個(gè)表格（真正的表格，不是視觀表或者子查詢）的一個(gè)列的時(shí)候，表達(dá)式的親和與表格的列的親和一樣。
• 括著列名的括號(hào)是被忽略的，所以如果X和Y.Z是列名，則（X）和（Y.Z）也被當(dāng)作列名，從而表達(dá)式擁有和對(duì)應(yīng)的列相同的親和
• 所有作用在列名上的運(yùn)算符（包括單目運(yùn)算符‘+’），會(huì) 將列名轉(zhuǎn)換為沒有親和的表達(dá)式。所以，即使X和Y.Z是列名，表達(dá)式+X和+Y.Z不是列名，也沒有親和。
• “CAST（expr AS type）”結(jié)構(gòu)的表達(dá)式的親和與定義為type類型的列的親和一樣。
• 此外，表達(dá)式都沒有親和

2.3. Column Affinity Behavior Example

視觀表的列或者from結(jié)構(gòu)的子查詢都是實(shí)現(xiàn)視觀表或者子查詢的select語句的結(jié)果集中的表達(dá)式。所以，視觀表中的列或者子查詢的親和由上面表達(dá)式親和規(guī)則決定。

e.g
CREATE TABLE t1(a INT, b TEXT, c REAL);
CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;
v1.x的親和與t1.b(INTEGER)的親和一樣，因?yàn)関1.x和t1.b對(duì)應(yīng)，而v1.y和v1.z沒有親和，因?yàn)樗麄兎謩e對(duì)應(yīng)表達(dá)式a+c和42，而表達(dá)式是沒有親和的。

2.4. Column Affinity Behavior Example

下面的sql示范了在向表格插入數(shù)據(jù)的時(shí)候，sqlite怎樣利用親和進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換
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.0 Comparison Expressions

     Sqlite v3有一系列有用的比較操作符，包括 "=", "==", "<", "<=", ">", ">=", "!=", "<>", "IN", "NOT IN", "BETWEEN", "IS", 和 "IS NOT"

3.1 Sort Order

比較操作的結(jié)果基于操作數(shù)的存儲(chǔ)類型，根據(jù)下面的規(guī)則：

• 存儲(chǔ)類型為NULL的值被認(rèn)為小于其他任何的值（包括另一個(gè)存儲(chǔ)類型為NULL的值）
  *一個(gè)INTEGER或REAL值小于任何TEXT或BLOB值。當(dāng)一個(gè)INTEGER或REAL值與另外一個(gè)INTEGER或REAL值比較的話，就執(zhí)行數(shù)值比較
• TEXT值小于BLOB值。當(dāng)兩個(gè)TEXT值比較的時(shí)候，就根據(jù)序列的比較來決定結(jié)果
• 當(dāng)兩個(gè)BLOB值比較的時(shí)候，使用memcmp來決定結(jié)果

3.2Type Conversions Prior To Comparison（Affinity）

Sqlite可能在執(zhí)行一個(gè)比較之前會(huì)在INTEGER，REAL或TEXT之間轉(zhuǎn)換比較值。
只有在轉(zhuǎn)換是無損、可逆轉(zhuǎn)的時(shí)候，“apply affinity”才意味著將操作數(shù)轉(zhuǎn)換到一個(gè)特定的存儲(chǔ)類。是否在比較操作之前發(fā)生轉(zhuǎn)換基于下面的規(guī)則決定：

• 如果一個(gè)操作數(shù)的親和類型為INTEGER，REAL或NUMERIC，另一個(gè)操作數(shù)親和類型為TEXT或BLOB或無親和類型，那么NUMERIC親和類型被應(yīng)用到另一個(gè)操作數(shù)
• 如果一個(gè)操作數(shù)親和類型為TEXT，另一個(gè)無親和類型，那么TEXT親和類型被應(yīng)用到另一個(gè)操作數(shù)
• 其他的情況，親和類型不被應(yīng)用，兩個(gè)操作數(shù)按本來的樣子比較

表達(dá)式"a BETWEEN b AND c"表示兩個(gè)單獨(dú)的二值比較” a >= b AND a <= c”，即使在兩個(gè)比較中不同的近似被應(yīng)用到’a’。“x IN (SELECT y ...)”形式的比較的數(shù)據(jù)類型轉(zhuǎn)換就像“x = y”的比較一樣。表達(dá)式“a IN (x, y , z, ...)”等于"a = +x OR a = +y OR a = +z OR ..."，也就是說IN運(yùn)算符右側(cè)的值是沒有親和類型的。

3.3. Comparison Example

CREATE TABLE t1(
a TEXT, -- text affinity
b NUMERIC, -- numeric affinity
c BLOB, -- no affinity
d -- no affinity
);

-- Values will be stored as TEXT, INTEGER, TEXT, and INTEGER respectively
INSERT INTO t1 VALUES('500', '500', '500', 500);
SELECT typeof(a), typeof(b), typeof(c), typeof(d) FROM t1;
text|integer|text|integer

-- Because column "a" has text affinity, numeric values on the
-- right-hand side of the comparisons are converted to text before
-- the comparison occurs.
SELECT a < 40, a < 60, a < 600 FROM t1;
0|1|1

-- Text affinity is applied to the right-hand operands but since
-- they are already TEXT this is a no-op; no conversions occur.
SELECT a < '40', a < '60', a < '600' FROM t1;
0|1|1

-- Column "b" has numeric affinity and so numeric affinity is applied
-- to the operands on the right. Since the operands are already numeric,
-- the application of affinity is a no-op; no conversions occur. All
-- values are compared numerically.
SELECT b < 40, b < 60, b < 600 FROM t1;
0|0|1

-- Numeric affinity is applied to operands on the right, converting them
-- from text to integers. Then a numeric comparison occurs.
SELECT b < '40', b < '60', b < '600' FROM t1;
0|0|1

-- No affinity conversions occur. Right-hand side values all have
-- storage class INTEGER which are always less than the TEXT values
-- on the left.
SELECT c < 40, c < 60, c < 600 FROM t1;
0|0|0

-- No affinity conversions occur. Values are compared as TEXT.
SELECT c < '40', c < '60', c < '600' FROM t1;
0|1|1

-- No affinity conversions occur. Right-hand side values all have
-- storage class INTEGER which compare numerically with the INTEGER
-- values on the left.
SELECT d < 40, d < 60, d < 600 FROM t1;
0|0|1

-- No affinity conversions occur. INTEGER values on the left are
-- always less than TEXT values on the right.
SELECT d < '40', d < '60', d < '600' FROM t1;
1|1|1

如果上面的比較反過來，結(jié)果不變。如：“a < 40” 寫成 “40 > a”，結(jié)果不變。

4.0 Operators

所有的數(shù)學(xué)操作符(+, -, *, /, %, <<, >>, &, |)，在被執(zhí)行前，都會(huì)將兩個(gè)操作數(shù)都轉(zhuǎn)換為數(shù)值存儲(chǔ)類型（INTEGER和REAL）。即使這個(gè)轉(zhuǎn)換是有損和不可逆的，轉(zhuǎn)換仍然會(huì)執(zhí)行。一個(gè)數(shù)學(xué)操作符上的NULL操作數(shù)將產(chǎn)生NULL結(jié)果。一個(gè)數(shù)學(xué)操作符上的操作數(shù)，如果以任何方式看都不像數(shù)字，并且又不為空的話，將被轉(zhuǎn)換為0或0.0。