详解SQLite3的column type affinity

-- TOC --

首先要理解，SQLite3数据库表的column，是可以没有type的，它这是Dynamic typing的数据库，跟其它别的数据都不一样，他们都是static typing。

Most databases use strong, static column typing. This means that the elements of a column can only hold values compatible with the column’s defined type. SQLite utilizes a dynamic typing technique known as manifest typing. For each row value, manifest typing records the value's type along with the value data. This allows nearly any element of any row to hold almost any type of value. （manifest typing，数据类型和值一起存储）

下面是一个小测试，定义一个table，然后写入各种type的数据，都OK：

>>> import sqlite3
>>>
>>> conn = sqlite3.connect(':memory:')
>>> conn.execute('create table aa(id)')
>>> conn.execute('insert into aa values (1)')
>>> conn.execute('insert into aa values (1.234)')
>>> conn.execute('insert into aa values ("12345")')
>>> conn.execute('insert into aa values ("abcde")')
>>> conn.execute('insert into aa values (null)')
>>> conn.execute('insert into aa values (?)', (b'\x01\x02',))
>>> conn.commit()
>>>
>>> c = conn.execute('select * from aa')
>>> c.fetchall()
[(1,), (1.234,), ('12345',),('abcde',), (None,), (b'\x01\x02',)]

create table的时候，不指定type；不管插入什么数据类型，SQLite3的column都照单全收。在不指定type affinity的情况下，如上，输入的type，就是输出的（select）的type！（这个叫 None Affinity）

当 create table 的时候指定type，这个时候，就开始 type affinity了。所谓 type affinity，就是SQLite3数据库会尝试将输入的数据，按照定义时的type进行转换，然后存入column，这种转换的前提是，没有信息和精度的损失。

比如： column定义的type是int，而输入是float，此时SQLite3就会按照float来存数据，不会进行转换；但是如果输入是"12345"，这样的字符串，就会转成int存储。

还是要做测试，才能理解：

>>> conn.execute('create table bb(id int)')
>>> conn.execute('insert into bb values (1)')
>>> conn.execute('insert into bb values (1.2345)')
>>> conn.execute('insert into bb values ("12345")')
>>> conn.execute('insert into bb values (null)')
>>> conn.execute('insert into bb values ("abcde")')
>>> c = conn.execute('select * from bb')
>>> c.fetchall()
[(1,), (1.2345,), (12345,), (None,), ('abcde',)]

"12345"在select后，就成为了int类型，而"abcde"还是字符串！

看到了把，就算定义了 int 类型，依然是什么数据类型都可以存储！这就是SQLite的特性。有人说这是个bug，我看到的解释是，由于SQLite应用实在太广泛了，没法改了，改了可能会造成很多应用的崩溃。

理解了 type affinity，现在来总结SQLite3中，5种具体的数据类型：

NULL，表示没有数据；
INTEGER，整数；
FLOAT，浮点数；
TEXT，变长字符串；
BLOB，变长 raw bytes；

type affinity 也有 5种：

Text，A column with a text affinity will only store values of type NULL, text, or BLOB. If you attempt to store a value with a numeric type (float or integer) it will be converted into a text representation before being stored as a text value type.
Numeric，A column with a numeric affinity will store any of the five types. Values with integer and float types, along with NULL and BLOB types, are stored without conversion. Any time a value with a text type is stored, an attempt is made to convert the value to a numeric type (integer or float). Assuming the conversion works, the value is stored in an appropriate numeric type. If the conversion fails, the text value is stored without any type of conversion.
Integer，A column with an integer affinity works essentially the same as a numeric affinity. The only difference is that any value with a float type that lacks a fractional component will be converted into an integer type.
Float，A column with a floating-point affinity also works essentially the same as a numeric affinity. The only difference is that most values with integer types are converted into floating-point values and stored as a float type.
None，A column with a none affinity has no preference over storage class. Each value is stored as the type provided, with no attempt to convert anything.

够明白了！

SQLite3的type affinity不是SQL标准，因此它有一套自己的将其它type转换成affinity的规则（SQL语句的兼容）：

如果 column 没有指定type，就是None Affinity，前面有例子；
如果 column 类型含有 int 子串，就是 Integer Affinity；
如果 column 类型含有 char，text，clob子串，就是Text Affinity；
如果 column 类型含有blob子串，就是 None Affinity；
如果 column 类型含有 real，floa，doub 子串，就是 Float Affinity；
如果以上条件都不满足，column就是 Numeric Affinity。

理解到这里，我们可以发现，在SQLite3中可以不指定column type，也可以随意指定任意的名称column type，总有一个 Affinity 类别在那里。

本文链接：https://cs.pynote.net/sf/sqlite/202203202/

-- EOF --

-- MORE --