SQLite由于其体积小，可方便移植，因此被广泛用于嵌入式及移动开发领域。记得自己学习是MSSQL，理论性很强，上下本两书好像学了一年。目前工作要做的是更向工业界看齐，从SQLite
Official Website上看了看，再回顾之前看的O’really 那本原版介绍的书。算是再次复习SQLite的存储类型。

SQLite的存储格式

参考官方这一段

Most SQL database engines (every SQL database engine
other than SQLite, as far as we know) uses static, rigid typing. With static
typing, the datatype of a value is determined by its container - the particular
column in which the value is stored.

SQLite uses a more general dynamic type system. In
SQLite, the datatype of a value is associated with the value itself, not with
its container. The dynamic type system of SQLite is backwards compatible with
the more common static type systems of other database engines in the sense that
SQL statements that work on statically typed databases should work the same way
in SQLite. However, the dynamic typing in SQLite allows it to do things which
are not possible in traditional rigidly typed databases.

也就是说SQLite不像其它那些静态，严格限制性的数据库管理软件，能够在插入数据时动态进行判断，同时兼容其它的数据库静态式的方式。

存储类及数据类型

Each value stored in an SQLite database (or manipulated
by the database engine) has one of the following storage classes:

• NULL.
  The value is a NULL value.

• INTEGER.
  The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on
  the magnitude of the value.

• REAL.
  The value is a floating point value, stored as an 8-byte IEEE floating point
  number.

• TEXT.
  The value is a text string, stored using the database encoding (UTF-8, UTF-16BE
  or UTF-16LE).

• BLOB.
  The value is a blob of data, stored exactly as it was
  input.

A storage class is more general than a datatype. The
INTEGER storage class, for example, includes 6 different integer datatypes of
different lengths. This makes a difference on disk. But as soon as INTEGER
values are read off of disk and into memory for processing, they are converted
to the most general datatype (8-byte signed integer). And so for the most part,
“storage class” is indistinguishable from “datatype” and the two terms can be
used interchangeably.

Any column in an SQLite version 3 database, except an
INTEGER PRIMARY KEY column, may be used to store a value of any storage
class.

All values in SQL statements, whether they are literals
embedded in SQL statement text or parameters bound to precompiled SQL statements
have an implicit storage class. Under circumstances described below, the
database engine may convert values between numeric storage classes (INTEGER and
REAL) and TEXT during query execution.

也就是说SQLite存储类型为以上5种.另外Boolean会处理为0，1，关于时间则会依据是IOS时间还是Julian天数或者是Unix时间戳来转换成TEXT,REAL,INTEGER的类型。

类型结合

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

1
2

考虑上述的SQL在SQLite下执行是怎么进行转换的？如同其它数据库软件为将123转换为integer
123，而456转换为string ‘456’，这些操作是在插入前进行的。

Determination Of Column Affinity

1. If the declared type contains the string “INT” then it
   is assigned INTEGER affinity.

2. If the declared type of the column contains any of the
   strings “CHAR”, “CLOB”, or “TEXT” then that column has TEXT affinity. Notice
   that the type VARCHAR contains the string “CHAR” and is thus assigned TEXT
   affinity.

3. If the declared type for a column contains the string
   “BLOB” or if no type is specified then the column has affinity
   BLOB.

4. If the declared type for a column contains any of the
   strings “REAL”, “FLOA”, or “DOUB” then the column has REAL
   affinity.

5. Otherwise, the affinity is
   NUMERIC.

Column Affinity Behavior Example

下面是依据上面的规则展示的一个例子。

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
);

1
2
3
4
5
6
7

– 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