SQLite指南(4) - FAQ列表(important)
如若转载，请加上本文链接，以示尊重个人劳动，谢谢。
本文严格整理自最新的：http://www.sqlite.org/faq.html, (3.7.8) (多说一句，看一个系统的发布，经常首先要看的就是，readme, 新特性，然后就是FAQ列表, 即所谓的常见问题列表)
1. 如何创建一个自增字段(autoincrement)?
在sqlite中，创建一个integer primary key,即可让它自增，太变态了。创建了该类型字段以后，即算你插入NULL值，NULL也会自动转为最后一次插入的值＋1。最大可能的整数值为9223372036854775807，越过此值后，将会随机生成一个值。
sqlite> create table t1(a integer primary key, b integer);
sqlite> insert into t1 values(NULL, 123);
sqlite> select * from t1;
1|123
这里的insert，逻辑上等价于INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123);
sqlite> insert into t1 values(9223372036854775807, 100);
sqlite> select * from t1;
1|123
9223372036854775807|100
sqlite> insert into t1 values(NULL, 101);
sqlite> select * from t1;
1|123
12064371|101
9223372036854775807|100
sqlite> insert into t1 values(NULL, 102);
sqlite> select * from t1 where b = 102;
12064372|102
我们可以看到插入最大的整数之后，再插入NULL，它会取一个随机值作为起点值（这种情况发生的可能性极小)
加上关键字AUTOINCREMENT，可以保证整个表在其生命期内有唯一的键值。当超过最大值之后，再插入，就会出错：SQLITE_FULL.
2. SQLite支持哪些数据类型
它使用的是动态类型，可以存储为INTEGER, REAL, TEXT, BLOB或NULL这五大类型。
3. SQLite居然可以在整型字段中插入string类型
这是一个特性，不是BUG。因为使用的是动态类型，只要能转换，都可以插入。你可以在整型字段里放入任意长度的字符串，把浮点值放到boolean列里，在字符串型字段里放入日期值。在建表语句里的列类型并不严格限制存放数据的实际类型。每列都可以放入任意长度的字符串。（这里只有一个例外，就是integer primary key，只能放入64位带符号整数，如果放入的不是整数，则会出错)
但是SQLite定义的列类型会给你关于值格式的提示。如果一个列是Integer类型，而你插入一个string, sqlite会尝试将string转成integer。如果可以转，则转成integer，否则，它会插入string。这种特性叫type affinity.
4.为什么sqlite不允许使用'0'和'0.0'作为同一个表不同的两行的PK值。
当你的表的主键是numeric类型时，将其改为text类型，将会有效。如果是numeric类型，它会认为'0'和'0.0'是同一个值。因为两者确实相等。
5. 是否允许多个应用程序或者同一个应用程序的多个实例同时访问一个sqlite数据库？
多个进程可以同时打开相同的sqlite数据库文件。可以同时SELECT(读操作)。但是同一时刻，只允许一个进程改变数据库。
sqlite使用读写锁来控制对数据库的访问。使用的时候要注意：如果sqlite处于NFS文件系统下，这种锁机制可能会出错。因为fcntl()文件锁在很多NFS的实现里头会被中断。如果有多个进程访问SQLITE数据库，应该避免把sqlite数据库放到NFS下边。在windows下，MS官方文档声称，如果不运行share.exe后台进程，则FAT文件系统中的锁一样会出问题。有人称，网络中的文件锁机制确实不怎么可靠。如果这是真的，把sqlite数据库置于两个或多台windows主机上，会造成不可预料的问题。
我们意识到，没有别的嵌入式数据库引擎支持有SQLITE这么多的并发特性。它允许多个进程立刻打开数据库文件，并立即执行读操作。当有进程要写时，会在(写或更新)期间锁定整个数据库文件。但那种锁定通常只会持续几个微秒。其它等待写操作完成的进程会接着执行它们的操作。而其它嵌入式数据库引擎只支持一个进程访问数据库。
C/S结构的数据库引擎通常支持更高的并发，允许多上进程同时进行写操作。因为有一个服务器进程从中协调。在高并发的需求下，你应该考虑使用C/S结构的数据库。
当SQLITE试图访问一个被其它进程锁定的文件时，缺省行为是返回状态值：SQLITE_BUSY，你可以通过调用sqlite3_busy_handler()和sqlite3_busy_timeout()来改变这个结果。
6. SQLite是线程安全的吗？
多线程是恶魔(这个我可不敢认同)。
SQLite是线程安全的。达到这个要求，SQLITE在编译的时候，必须加上预处理宏SQLITE_THREADSAFE，并置为1。Windows和Linux的发行版本，都加上了此宏。当你不能确定是否线程安全，你可以调用sqlite3_threadsafe()接口来间接判断。
在sqlite3.3.1以前，sqlite3结构体只能在与sqlite3_open()同一个线程里创建。你不能在某一个线程里打开数据库之后，然后?句柄传给另一个线程去使用。这会出问题(在Redhat9下)。特别是，fcntl()锁在一个线程里创建之后，就不能被移走或者在另一个线程里改动。当你试图将数据库连接跨线程传递时，经常会出问题。
在3.3.1里，这种情况有所缓解，在此版本及其以后版本里，只要连接不是被fcntl()锁定，则可以跨线程移动。你可以假定，如果没有事务在pending，并且所有的语句都被释放，则不会有锁定。
在Unix下，不能把打开的sqlite数据库，通过fork()系统调用传递至子进程里。这样做会出问题。
7. 如何列出所有的表和索引
命令行下，使用.tables，列出所有表名
.schema，则会列出有表和索引的元信息。它们还可以带like子句
在应用程里，你可以访问表sqlite_master得到你要的信息。
其结构如下：
CREATE TABLE sqlite_master (
type TEXT,
name TEXT,
tbl_name TEXT,
rootpage INTEGER,
sql TEXT
);
type字段，总是'table'，name字段存储表名。获取表的列表：
SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;
获取索引信息：
type值为'index', name字段为索引表，tbl_name为创建了索引的表名，sql字段存储的是索引的原始创建语句。对于自动创建(隐含创建，如primary key等)的索引，sql值为NULL.
sqlite_master表是只读表，你不能通过CUD语句对其进行修改。DDL语句会自动触发对该表的读写。
临时表不会出现在SQLITE_MASTER表当中。如果想列出所有的表，包括临时表，可用下面的SQL语句：
SELECT name FROM
(SELECT * FROM sqlite_master UNION ALL
SELECT * FROM sqlite_temp_master)
WHERE type='table'
ORDER BY name;
8. sqlite数据库在大小方面有哪些限制？ (important)
这个就要参照http://www.sqlite.org/limits.html. 所谓limit指的是量化指标的上下界，最大值之类的限制，如一个BLOB字段最长多长，一个表最多拥有多少列。
SQLITE一开始，就在设计上避免了随意的限制。当然，在有限内存和磁盘空间的物理环境下，访问SQLITE的进程还是有相关限制的。但是在SQLITE中，那些限制并没有相关规定。策略就是，它可以适应内存大小，并且可以使用32位整数。
不幸的是，这种没有限制的策略也会带来问题，因为没有上界定义，就不好进行相关测试。一些bug会把SQLITE推到风口上。基于这个原因，新版本的sqlite对一些限制还是作出了定义。
SQLITE中的很多限制是可以自定义的(这是好事情，能让用户定制)，使用sqlite3_limit()就可以达到此目的。
1) string或BLOB最大长度
预定义的最大长度是SQLITE_MAX_LENGTH，其缺省值是1个billion也就是1,000,000,000，你可以在编译时，指定自己喜欢的一个值，如-DSQLITE_MAX_LENGTH=123456789。目前的实现，只支持最大值到2^31-1即2147483647.所有内建函数，如hex()在达到此极限值之前就可能失败。在insert和delete时，一个数据行的内容全部编码成单个BLOB，因此该值也确定了一行的最大字节数。
我们也可以通过sqlite3_limit(db, SQLITE_LIMIT_LENGTH, size)来减小一个BLOB的实际最大长度。
2) 最大列数支持
SQLITE_MAX_COLUMN编译期参数用于决定上限.
.表的最大列数
.索引的最大列数
.视图的最大列数
.Update语句的set子句的最大列数
.select语句的列数
.group by 或者 order by子句的最大列数
.insert语句中value涉及的最大列数
缺省的SQLITE_MAX_COLUMN值为2000，你可以把它改大成32767. 另外，有经验的数据库工程师会告诉我们，一个经过良好的规范化的数据库表，从来不需要超过100个列。
在大多数应用中，列数比较少，几十列足矣。SQLITE的代码生成器，有的算法复杂度为O(N*N)，这里N为实际的列数。因此，当你重定义SQLITE_MAX_COLUMN为一个更大的值时，当你使用一个含有更多列的SQL时，会发现sqlite3_prepare_v2()运行的很慢。
使用sqlite3_limit(db, SQLITE_LIMIT_COLUMN, size)可以对列数进行自定义。
3) SQL语句的最大长度
宏：SQLITE_MAX_SQL_LENGTH，缺省值为1000000。你可以自定义其值，小于SQLITE_MAX_LENGTH和1073741824即可。
基于此定义，你显然不能使用超过1MB的SQL语句。也不能在insert语句中嵌入MB级字符串。但你可以用变量绑定的方法来插入数据，如ISNERT INTO tab1 VALUES(?,?,?)
接着调用sqlite3_bind_XXXX()来绑定那些大字符串。绑定还有一个好处，是不用对引号之类的东西进行escape.运行的也更快。
自定义大小：sqlite3_limit(db, SQLITE_LIMIT_SQL_LENGTH,size)
4)Join查询中最多能连接多少个表
不能超过64。因为它使用了bitmap，每个连接表占用了一个bit.
5)表达式树的最大深度
sqlite以递归方式来访问表达式树，因此要尽量避免过大的树深度，否则会使用太多的栈空间。SQLITE_MAX_EXPR_DEPT参数决定了树的最大深度值。如果是0，则没有限制。目前的缺省值是1000。使用sqlite3_limit(db, SQLITE_MAX_EXPR_DEPT, size)可以定制此值。
6)函数的最大参数个数
使用SQLITE_MAX_FUNCTION_ARG来预定义，缺省值是100。由于一个函数的参数个数有时候使用带符号字符型来存储，所以最大值不能超过127。定制：sqlite3_limit(db,SQLITE_MAX_FUNCTION_ARG, size)
7)SELECT语句 中最大组合数目
就是说可以最多有多少个SELECT子句进行组合查询，包括UNION, UNION ALL, EXCEPT, INTERSECT. 这里使用到了递归算法，最大默认值是500，使用预定义宏： SQLITE_MAX_COMPOUND_SELECT.定制: sqlite3_limit(db,SQLITE_MAX_COMPOUND_SELECT, size)
LIKE或GLOB模式的最大长度
复杂度是O(N*N)，N是模式中字符的个数，最大值：SQLITE_MAX_LIKE_PATTERN_LENGTH，默认值为50000。一般都不会超过几十个字节。偏执的开发者甚至会定制此值，让它更小。
定制：sqlite3_limit(db,SQLITE_MAX_LIKE_PATTERN_LENGTH, size)
9) SQL语句中宿主变量的最大个数
即占位符"?"在SQL语句中的个数，以用于sqlite3_bind_XXXX()进行绑定。sqlite也支持命名占位符，以":", "$", "@"打头的都支持，如一个占位符定义为: "?123"
每个宿主变量在sqlite语句中都被赋上一个数，从1开始，依次递增，当然，如果是"?123"，这个数就是123了。
SQLITE要为这些变量分配所有的空间。因此?1000000000将需要分配上G的存储空间，很容易就超出限制。最大值SQLITE_MAX_VARIABLE_NUMBER，默认为999。同样可以定制：
sqlite3_limit(db,SQLITE_LIMIT_VARIABLE_NUMBER,size)
10) 触发器递归的最大深度
在3.6.18以前，触发器不是递归的，这个限制是没有意义的。但是从那以后，默认都是递归的，想禁用递归，得用PRAGMA recursive_triggers子句来定义。 SQLITE_MAX_TRIGGER_DEPTH 的默认值为1000。只在递归启用的时候有意义。
11) 同时启用数据库的最大个数
说启用有点不太准确，就是同时绑定启动的数据库最大能有多少个。 ATTACH语句，是一种扩展，允许两个或多个数据库关联到同一个数据库连接，让它们看起来像是一个数据库。使用宏SQLITE_MAX_ATTACHED，默认值是10。代码生成器使用位图来跟踪绑定的数据库，这意味着最大值不能超过62。(为什么是62，不是别的值？)
定制：sqlite3_limit(db,SQLITE_LIMIT_ATTACHED,size)
这让我也想起了ASA数据库，它也可以同时启动多个数据库。
12) 数据库文件最大页数
SQLITE_MAX_PAGE_COUNT, 默认值是1073741823 (2^30-1), 再多的话，则会返回SQLITE_FULL. 而最大的可定制的可能值是2147483646, 当配置此值，并且让page size达到最大值65546(64K)，则会让sqlite数据库可达到最大大小14T。(这里似乎有问题，应该是2**47 约为128T, 是不是搞错了??)
max_page_count PRAGMA可以定制此值
13) 单表中最大多少行
理论最大值为2^64 (18446744073709551616 or about 1.8e+19)，这个值显然永远达不到。就依上边的14T最大大小，最多也只能有1e+13行，而这必须还是没有索引，并且很行只有很少的数据的情况下。
9. 在 SQLite 中 VARCHAR 的最大长度是多少？
SQLite不强制VARCHAR的长度。你可以声明一个VARCHAR(10)，SQLite一样可以让你存放500个字符在里面。 并且它们会始终完整无缺——决不会被截断。
10. SQLite 是否支持 BLOB 类型
SQLite 3.0 版支持在任何字段存放 BLOB 数据，不管字段声明为什么类型。
11. 如何从一个已存在的 SQLite 数据表中添加/删除字段
SQLite有有限的ALTER TABLE支持，可以用于添加字段到表的末尾 或更改表名。如果你要对表的结构作更复杂的修改，你需要重新创建表。你可以在一个临时表中备份数据，撤销旧表，重建新表后再恢复数据。
例如，假设你有一个名为 "t1" 的表，有名为 "a", "b", 和 "c" 三个字段，你要删除字段 "c" 。可按如下步骤操作：
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;
12. 我删除了很多数据但是数据库文件并没有减小，是不是 Bug？
不是的。当你从 SQLite 删除数据之后，未使用的磁盘空间被添加到一个内在的“空闲列表”中用于存储你下次插入的数据。磁盘空间并没有丢失，但是也不向操作系统返回磁盘空间。
如果你删除了大量的数据且想要减小数据库文件，执行 VACUUM命令。VACUUM 命令会清空“空闲列表”，把数据库尺寸缩到最小。注意， VACUUM 会耗费一些时间（在 Linux 系统下大约0.5秒/兆）并且要使用两倍于数据库文件大小的磁盘空间。
对于SQLite version 3.1, 替代VACUUM命令的一个方法是auto-vacuum模式，用 auto_vacuum pragma语法开启该模式。
13. 是否能将 SQLite 用于商业用途而不用交版权费用
可以。SQLite 是公开的(public domain)。代码的任何部分都没有声明所有权。你可以用它来做你想要的任何事情。
14. 如何插入有单引号（'）的字符串
使用双单引号即可，例如： INSERT INTO xyz valueS('5 O''clock');
插入数据库的是：5 0'clock。
15. SQLITE_SCHEMA 错误代表什么？
在 SQLite 版本3中，当一个预处理 SQL 语句不合法不能执行时就会返回一个 SQLITE_SCHEMA 错误。当这个错误发生时，该语句应当用 sqlite3_prepare() API函数重新编译。在 SQLite 版本3中，只有使用 sqlite3_prepare ()/sqlite3_step()/sqlite3_finalize() API函数执行 SQL 才会发生这个错误，而使用 sqlite3_exec(). 则不会。这与版本2不同。
大部分发生这个错误的原因是当 SQL 预处理完时数据库已经改变了（可能是被另一个进程改变的）。还可能有如下原因：
* 对一个数据库进行DETACH操作
* 对一个数据库进行VACUUM操作
* 一个用户函数定义被删除或改变了。
* 一个排序定义被删除或改变了。
* 一个授权函数改变了。
解决的办法是重新编译并再次尝试执行。所有涉及 sqlite3_prepare()/sqlite3_step()/sqlite3_finalize() API 函数的都应当重新编译。参见下例：
int rc; sqlite3_stmt *pStmt; char zSql[] = "SELECT ....."; do { /* Compile the statement from SQL. Assume success. */ sqlite3_prepare(pDb, zSql, -1, &pStmt, 0); while( SQLITE_ROW==sqlite3_step(pStmt) ){ /* Do something with the row of available data */ } /* Finalize the statement. If an SQLITE_SCHEMA error has ** occured, then the above call to sqlite3_step() will have ** returned SQLITE_ERROR. sqlite3_finalize() will return ** SQLITE_SCHEMA. In this case the loop will execute again. */ rc = sqlite3_finalize(pStmt); } while( rc==SQLITE_SCHEMA );