关于女神SQLite的疑惑（1）

SQLite的大名就不做广告了，反正嵌入式设备、手机等领域中，凡是用到数据库的地方她几乎都是主角。这个系列的推文，就来总结日常使用时我们都可能会遇到一些问题。

1.问：怎么创建一个自动递增的域？

1.答：对于这个问题，简短的回答是：任何一个被声明为INTEGER PRIMARY KEY的域都将是自动递增的。

而更完整的回答是：如果你在一个表中，声明了一个INTEGER PRIMARY KEY的域，那么无论何时当你插入一个NULL到该域时，NULL都将被自动转换为一个整数，并且其值为该域中的最大值+1，当然如果表为空时，将被设置为1。再者，如果当前该域中的最大值已经达到9223372036854775807（天知道你在干什么！）的话，那将会随机挑选一个未使用过的值来用。

例如，你有个表长成这个样：

CREATE TABLE t1(

aINTEGER PRIMARY KEY,

bINTEGER);

那么以下两条语句，在逻辑上将会是等价的：

INSERT INTO t1 VALUES(NULL, 123);

INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1, 123);

函数 sqlite3_last_insert_rowid() 可以返回最近一次插入操作的整数主键的值。

还有一点要注意，新建的主键的值等于原先存在的最大的主键的值+1，这个新的主键当然是当前全表唯一的，但却有可能跟之前已经被删除的记录的键值相等，如此一来可能会导致查询时不必要的误会。如果要创建一个表全生命周期唯一的键值，就要在声明中再加上这个约束关键字：AUTOINCREMENT。这样一来，新建的主键键值就不仅是当前全表唯一，并且在表的全生命周期内也具备唯一性，即：是所有创建过的最大的键值+1。另外，如果最大的键值已经被使用过了无法在递增，那么此时的 INSERT 操作将会失败，并且返回错误码 SQLITE_FULL 。

2.问：SQLite究竟支持什么数据类型？

2.答：

SQLite有所谓动态类型匹配机制，数据库中的数据可以被储存为INTEGER（整数）,REAL（实数）,TEXT（文本字符串）,BLOB（二进制数据）, 或者NULL。

3.问：我刚刚将一个文本字符串插入了一个整型（INTEGER）域中！怎么回事？

3.答：别紧张，相信我这绝对是一个特色，而不是一个BUG。

SQLite 支持所谓动态类型匹配。这意味着它并不会对数据类型做强制性约束，一般而言，任意类型的数据，都可以被插入到任意一个域中，例如你可以将任意长度的字符串插入到一个整数域中，将一个浮点实数插入到一个文本域，或者将一个日期插入到字符域中。

在你使用命令 CREATE TABLE 来创建表时对域的类型的定义，并不成为日后插入数据的约束条件。所有的域都可以储存任意长度的文本字符串。

这种情况只有一个例外：被声明为 INTEGER PRIMARY KEY 的域只能存储一个 64-bit 的有符号整数。如果你试图将一个非整数强行插入到这样的整数主键域中，恭喜你，你将收获一个关于类型不匹配的大大的error。

这么说来，创建 table 时指定的数据类型还有什么鸟用呢？严格说来还是有用的，SQLite会将你声明时指定的类型，作为该域的“倾向性”类型的依据。比如，如果一个域的类型被声明为 INTEGER 但是你正试图插入一串文本，那么SQLite会倾向于将此文本转换为整数，如果成功了，那么实际存储的就是一个整数，否则就存储这串文本。

4.问：为什么SQLite不准我使用 '' 和 '0.0' 作为两个不同记录的主键？

4.答：是的，'0' 和 '0.0' 的确是两个完全不同的文本字符串，但是当表的主键是一个数字类型的时候，SQLite不允许你这么做。非要这么干的话，可以将主键的类型修改为 TEXT 。

这个疑惑，实际上可以从上面的第3个问题得到指引和解答。

对数据库而言，每一个行记录必须有一个唯一的主键是，这是最基本的要求。但当一个域的类型是一个数字型（包括整数、实数），而你要插入'' 和 '0.0' 时，SQLite将会倾向于把它们视为数字型数据，因此他们都将被记录成无法区分的零值，这，显然违反了主键的基本定义。

5.问：可不可以让多个程序同时访问同一个数据库文件？

5.答：这没什么不可以。

多个程序可以安全地同时执行SELECT的动作。但是，任何时候都只能有一个程序可以对数据库做出修改性的行为。

实际上，SQLite使用了读写锁来控制对数据库的访问。但这里必须给出警告：这个机制在NFS（网络文件系统）中工作得并不理想。

因此，你需要避免在NFS中使用多任务同时并发访问 SQLite 数据库。在 Windows 的FAT文件系统中，据说，运行一个叫Share.exe的后台精灵进程可以解决这个问题，否则锁机制将不稳定。而据我的经验，以上场景是一个货真价实的大坑，你有一万个理由不要碰它。关于这个话题，早已有无数的 Windows 砖家们给出过警告，任何想用锁机制来锁住网络文件的人都必定会被无数的莫名其妙的错误、崩溃、异常折磨成精神病，陷入噩梦般的抑郁之中。简而言之吧，避免在多端 Windows 中共享 SQLite 数据库是你先要绕过去的火坑。

而在嵌入式当中，据我所知还没有任何一款 SQL 数据库引擎在并发性上可以和 SQLite 匹敌。SQLite 允许多任务同时连接到同一个数据库文件，并且允许多任务并发读操作。当任意一个任务试图进行写操作时，它必须将整个数据库锁起来直到操作完毕，这听起来貌似不是很屌炸天，但一般而言这仅需几个毫秒而已，其他的任务只需要等待这么一小段时间即可做它们该做的事情。其他的嵌入式 SQL 数据库引擎，一般都只能做到每次让一个任务连接到一个数据库文件。

当然，基于 C/S 模型的大型数据库引擎（例如 PostgreSQL、MySQL或者Oracle）一般能支持更大程度上的并发性，支持多任务同时并发写操作。这对于 C/S 模型而言是可以办到的，因为它们有一个强大的 Server 来协调所有的访问。

如果你有如此高并发的需求，那么你应该考虑使用这样的 C/S 模型的数据库引擎，但一般而言，也许项目的真正并发需求比你想象的要低得多得多。

当 SQLite 试图对一个已经被其他任务加了锁的数据库访问时，将会得到一个 SQLITE_BUSY 的错误，你可以使用以下两个函数来控制此时你的程序的下一步行为。

sqlite3_busy_handler( )

sqlite3_busy_timeout( )

今天就聊到这，后续关于SQLite的常见问题会陆续更新。欢迎小伙伴关注、转发、点赞、收藏、吐槽、扔鸡蛋……