官方文档：http://www.sqlite.org/datatype3.html#affinity
绝大多数数据库引擎都是用静态(除SQLite之外的其他所有SQL数据库引擎), 严格的数据类型. 使用静态数据类型的情况下, 数据类型由值的容器决定– 也就是存储该值的列.
SQLite使用一个更加通用的类型系统. 在SQLite中, 值的数据类型是和值本身相关联的, 而不是其容器决定的. SQLite的动态数据类型向后兼容其他较常见的使用静态数据类型的数据库系统, 即在静态数据类型数据库系统上使用的SQL语句同样适用于SQLite. 但是在SQLite上进行的动态数据类型操作却不可能适用于传统的静态数据类型的数据库系统.
注意：类型名后括号里的数值型的参数声明(例如: “VARCHAR(255)”)将被SQLite忽略– SQLite并不接受字符串, BLOBs和数值型数据的强制的长度限制(除了全局的SQLITE_MAX_LENGTH限制规则).
_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];
1、Storage Classes and Datatypes
每一个存储在SQLite数据库系统中的值(或者被数据库引擎操纵)必定属于以下几个类型中的一个:
- NULL. 值为NULL
- INTEGER. 有符号的整形数值, 按数值大小以1,2,3,41,2,3,4,6或8字节存储.
- REAL. 浮点数值, 以8字节IEEE标准浮点数存储.
- TEXT. 文本字符串, 以数据库的字符编码方式存储(UTF-8, UTF-16BE或者UTF-16LE).
- BLOB. 该值是一个BLOB数据, 将以与输入时一致的二进制码存储.
存储类型相比数据类型是更加通用的. 比如说INTEGER存储类包含6种不同长度的整形数据类型. 虽然6种类型是以不同方式存储在磁盘上, 但INTEGER数据一旦从磁盘里读取到内存中用于处理是, 所有的6种类型都会被转换为最通用的数据类型(8字节有符号整数). 因此大部分情况下, “存储类型”与”数据类型”并没有什么分别, 可以相互替换表示.
SQLite3中的任何列, 除INTEGER PRIMARY KEY列之外, 可以用来存储以上提到的任何类型(storage class)的值.
SQL语句中所有的值, 无论他们是以纯文本方式嵌入到SQL语句中还是以预编译方式绑定到SQL语句的参数都有一个隐含的存储类型.
1.1 Boolean Datatype
SQLite没有单独的布尔存储类型. 在SQLite中, 布尔值, 真被存储为整形数值1, 假被存储为整形数值0.
1.2 Date and Time Datatype
SQLite没有单独的日期和时间存储类型. 作为替代的是,SQLite内建的日期和时间函数可用于处理以TEXT, REAL或INTEGER方式存储的日期:
- TEXT 以ISO8601标准表示的字符串(“YYYY-MM-DD HH:MM:SS.SSS”).
- REAL 以Julian day计算（儒略日）, 公元前4714年11月24日至日期所经历的天数.
- INTEGER 以Unix时间方式表示, 即1970-01-01 00:00:00 UTC至日期所经历的秒数.
在应用中可以自由地使用以上3种方式存储日期和时间, 也可以用内建的日期和时间函数在它们之间做任意的转换.
2.0 Type Affinity (亲和类型)
具有严格数据类型的SQL数据库通常会自动将数值转换为合适的数据类型。如下：
CREATE TABLE t1(a INT, b VARCHAR(10));
INSERT INTO t1(a,b) VALUES('123',456);
严格数据类型的数据库在插入数据前会将字符串‘123’转换为整型123，将整型456转换为字符串‘456’。
为了使SQLite更加兼容其他的数据库引擎, SQLite支持列”亲和类型”概念. 所谓的列的亲和类型就是在该列中推荐使用的存储类型. 必须注意的是, 这里只是推荐的类型, 而非必须使用的类型. 任何列依然可以存储任何类型的数据. 这只是说, 在一些列中我们会给予一些选择, 这将使得我们会优先使用一种存储类型. 优先使用的存储类型被叫做该列的”亲和(affinity)”.
SQLite3的每列亲和类型是一下几种之一:
- TEXT
- NUMERIC
- INTEGER
- REAL
- BLOB（过去成为NONE）
具有TEXT亲和类型的列存储的数据类型有NULL, TEXT和BLOB. 如果将数值型的数据插入到具有TEXT亲和类型的列中, 那么在该值被存储前将会被转换为字符型.
具有NUMERIC亲和类型的列可存储所有5种存储类型的值. 当把文字信息插入到NUMERIC列中, 在不损失精度且转换可逆的情况下, 该段文字的存储类型将被转换至INTEGER或REAL类型. 在转换TEXT和REAL时, 如果字段的前15位十进制数能被保留的话, 那么SQLite将认为该转换是无损和可逆的. 如果将TEXT无损转换为INTEGER或REAL的过程被判断为不可能完成的话, 那么该字段信息将会用TEXT存储类型存入数据库.SQLite在任何情况下都不会尝试转换NULL或BLOB型数据信息.
如果一个字符串是有小数点的浮点数有指数符号的浮点数, 但只要该值能被表示成一个整型数据, 则NUMERIC亲和将会将其转换成一个整型数值. 因此, ‘3.0e+5’在具有NUMERIC亲和的列下将被存储为300000, 而不是值为300000.0浮点数.
具有INTEGER亲和类型的列与具有NUMERIC亲和类型的列的规则一致. INTEGER和NUMERIC亲和类型的唯一明显的区别是在CAST表达式中.
具有REAL亲和类型的列与NUMERIC亲和类型表现一致, 除了它会强制把整型数据转换至浮点形式(一个内部的优化是, 将绝对值很小的浮点数存入具有REAL亲和类型的列时, 为了节省数据占用空间, 该值将以整型写入到磁盘中, 在读出时会被自动转换回浮点数. 该优化在SQL层面上是完全不可见的, 只能在检测原始数据库文件比特序列时被发现).
具有BLOB亲和类型的列不会优先使用任何一个存储类型, 也不会强制地将一种数据存储类型转换为其他存储类型.
2.1 Determination Of Column Affinity
一个列的亲和类型将会被该字段的声明类型所决定, 按顺序地根据以下规则将可以得出亲和类型:
- 如果声明类型包含字符串”INT”, 将会被赋予INTEGER亲和类型.
- 如果声明类型包含字符串”CHAR”, “CLOB”或者”TEXT”, 那么该字段将被赋予TEXT亲和类型. 注意因为VARCHAR包含”CHAR”, 故也被赋予TEXT亲和类型.
- 如果声明类型包含字符串”BLOB”或者没有指定类型, 那么该字段将拥有”NONE”亲和类型.
- 如果声明类型包含字符串”REAL”, “FLOA”或者”DOUB”, 那么该字段将拥有REAL亲和类型.
- 否则, 亲和类型将是NUMERIC.
注意以上的亲和类型顺序是是十分重要的. 如果一个字段的声明类型是”CHARINT”, 它将符合规则1和规则2, 但是规则1的优先权高于规则2, 所以该字段的亲和类型将是INTEGER.
2.2 Affinity Name Examples
下表展示了传统SQL实现的通用数据类型在上面5条规则的作用下是如何被转换成相应的亲和类型的. 下表只展示了SQLite支持的数据类型名的一小部分.
| Example Typenames From The CREATE TABLE Statement or CAST Expression | Resulting Affinity | Rule Used To Determine Affinity |
|---|---|---|
| INT、INTEGER、TINYINT、SMALLINT、MEDIUMINT、BIGINT、UNSIGNED BIG INT、INT2、INT | INTEGER | 1 |
| CHARACTER(20)、VARCHAR(255)、VARYING CHARACTER(255) 、NCHAR(55)、NATIVE CHARACTER(70)、NVARCHAR(100)、TEXT、CLOB | TEXT | 2 |
| BLOB、no datatype specified | BLOB | 3 |
| REAL、DOUBLE、DOUBLE PRECISION、FLOAT | REAL | 4 |
| NUMERIC、DECIMAL(10,5)、BOOLEAN、DATE、DATETIME | NUMERIC | 5 |
2.2 Affinity Of Expressions
表格的所有列都有亲和类型，但是表达式（expression）不一定有亲和类型。表达式的亲和由下面的规则决定：
- 如果IN或NOT IN表达式的操作数是个列表，则该操作数没有亲和，如果操作数是select语句，则操作数的亲和为select结果集的亲和。
- 当一个表达式引用一个表格（真正的表格，不是视观表或者子查询）的一个列的时候，表达式的亲和与表格的列的亲和一样。
- 括着列名的括号是被忽略的，所以如果X和Y.Z是列名，则（X）和（Y.Z）也被当作列名，从而表达式拥有和对应的列相同的亲和
- 所有作用在列名上的运算符（包括单目运算符‘+’），会 将列名转换为没有亲和的表达式。所以，即使X和Y.Z是列名，表达式+X和+Y.Z不是列名，也没有亲和。
- “CAST（expr AS type）”结构的表达式的亲和与定义为type类型的列的亲和一样。
- 此外，表达式都没有亲和
2.3. Column Affinity Behavior Example
视观表的列或者from结构的子查询都是实现视观表或者子查询的select语句的结果集中的表达式。所以，视观表中的列或者子查询的亲和由上面表达式亲和规则决定。
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)的亲和一样，因为v1.x和t1.b对应，而v1.y和v1.z没有亲和，因为他们分别对应表达式a+c和42，而表达式是没有亲和的。
2.4. Column Affinity Behavior Example
下面的sql示范了在向表格插入数据的时候，sqlite怎样利用亲和进行数据类型转换
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
比较操作的结果基于操作数的存储类型，根据下面的规则：
- 存储类型为NULL的值被认为小于其他任何的值（包括另一个存储类型为NULL的值）
*一个INTEGER或REAL值小于任何TEXT或BLOB值。当一个INTEGER或REAL值与另外一个INTEGER或REAL值比较的话，就执行数值比较 - TEXT值小于BLOB值。当两个TEXT值比较的时候，就根据序列的比较来决定结果
- 当两个BLOB值比较的时候，使用memcmp来决定结果
3.2Type Conversions Prior To Comparison（Affinity）
Sqlite可能在执行一个比较之前会在INTEGER，REAL或TEXT之间转换比较值。
只有在转换是无损、可逆转的时候，“apply affinity”才意味着将操作数转换到一个特定的存储类。是否在比较操作之前发生转换基于下面的规则决定：
- 如果一个操作数的亲和类型为INTEGER，REAL或NUMERIC，另一个操作数亲和类型为TEXT或BLOB或无亲和类型，那么NUMERIC亲和类型被应用到另一个操作数
- 如果一个操作数亲和类型为TEXT，另一个无亲和类型，那么TEXT亲和类型被应用到另一个操作数
- 其他的情况，亲和类型不被应用，两个操作数按本来的样子比较
表达式"a BETWEEN b AND c"表示两个单独的二值比较” a >= b AND a <= c”，即使在两个比较中不同的近似被应用到’a’。“x IN (SELECT y ...)”形式的比较的数据类型转换就像“x = y”的比较一样。表达式“a IN (x, y , z, ...)”等于"a = +x OR a = +y OR a = +z OR ..."，也就是说IN运算符右侧的值是没有亲和类型的。
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
如果上面的比较反过来，结果不变。如：“a < 40” 写成 “40 > a”，结果不变。
4.0 Operators
所有的数学操作符(+, -, *, /, %, <<, >>, &, |)，在被执行前，都会将两个操作数都转换为数值存储类型（INTEGER和REAL）。即使这个转换是有损和不可逆的，转换仍然会执行。一个数学操作符上的NULL操作数将产生NULL结果。一个数学操作符上的操作数，如果以任何方式看都不像数字，并且又不为空的话，将被转换为0或0.0。
