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

16. 为什么ROUND(9.95,1) 返回 9.9 而不是 10.0? 难道9.95 不该向上进位么
SQLite 内部使用二进制运算，9.95用 64-bit IEEE 浮点数 ( SQLite 内部使用的) 表示为 9.949999999999999289457264239899814128875732421875。所以当你输入 "9.95"时， SQLite 就理解为上述的数字，进而四舍五入得到9.9。这个问题在处理浮点二进制数总会产生。通常的规则是十进制的有限浮点数通常无法表示为二进制有限浮点数，只能由最接近的二进制数来代替。这个近似数会非常接近原数，但总一些细微的不同，所以可能无法得到你预期的结果。

17. 在编译sqlite是遇到成百个警告，会是问题吗？这是不是说明代码质量比较差？
代码质量是通过全覆盖测试来保证的，而不是通过编译器的若干警告和表态代码分析工具来断定的(非常赞同。。)换句话说，我们通过测试保证sqlite能得到正确的答案，而不是通过简单的代码统计分析。sqlite在每次release之前，运行过成百上千的独立测试集，以覆盖所有可能的SQL语句。当发现新的bug以后，新的测试集会用于验证此bug。
 
    在测试期间，sqlite库会以特殊指令编译，允许测试脚本模拟更大范围的失败来进行验证。没有内存泄露，即使在内存分配失败的情况下。VFS和于模拟操作系统crash或断电的情况，以确保事务是原子的。任意注入I/O错误的机制被引入，表明sqlite对这类事件的处理很可靠。(可以做做实验，看看别的数据库遇到此类情况，结果如何)

    我们也运行linux下的valgrind来确保不会有问题发生。有些人认为，我们应该消除所有警告，因为警告意味着将来会有所改变。在我们看来，开发人员已经消除了SQLITE开发过程中的警告(在GCC下)，但是在不常用的编译器下边(如MSVC)则没有做此类工作。

18. unicode字符串的不区分大小写匹配不能工作
SQLITE的缺省配置只支持ASCII字符的不区分大小写比较。原因：实现完全的unicode不区分大小写比较，以及大小写转换，会让SQLite的库大小翻倍。SQLite开发人员称，任何应用，如果需要完全的unicode支持，可能也拥有相关的表和函数，SQLite没有必要重复此工作。

SQLite为弥补这一缺憾，提供了链接外部的Unicode比较转换库的功能。应用程序可以重载NOCASE比较函数以及内建的like(), upper(), lower()函数。源码中"ICU"打头的扩展就实现了这些函数的重载。这样，开发人员可以根据实际需要进行相应的扩展。

19. INSERT速度太慢，每秒钟只能插入几十条数据
实际上在普通的台式机上，SQLITE可以插入近50000条数据。但是它每秒可能只会完整几十笔事务。事务的速度完全被磁盘的旋转速度所限制。一个事务需要两次磁盘的转动，因此在7200RPM转速的磁盘上，你每秒最多也只能执行60笔事务。（有些道理...)
事务速度受限于磁盘，因为sqlite实际上要等数据真正安全的存储到磁盘上，才算是事务真正的完成。只有这样，在断电或者宏机的情况下，数据不会丢失。感兴趣的话，可以看看关于原子提交那一节。

默认情况下，每个insert语句都拥有自己的事务，但是如果你把多条insert放到BEGIN和COMMIT之间，这样，这些INSERT语句会组成一个事务，这样的话，事务只会提交一次，平摊到每个insert上的时间就会大大减少。

另外一个选项是PRAGMA synchronous=OFF，该命令会让SQLite不用等数据安全到达碰盘，这样写操作速度会更快。但是，它有一个缺点，如果事务正执行过程中，如果断电，数据库文件可能会损坏。

20. 我偶然删掉了一些重要信息，能恢复吗？
如果拥有备份，直接从备份恢复。
如果没有备份，恢复是相当困难的。你也许可以在二进制的数据库文件当中找到部分数据。恢复数值型数据，通过专用工具也许可以恢复，尽管我们认为没有这样的工具。SQLITE有时候会使用SQLITE_SECURE_DELETE选项来编译，这样所有删除的内容会使用字节0来代替。如果是这样，想恢复基本上不可能。如果你使用了VACUUM命令来压缩数据库，恢复一样也变得不可能。这两个如果都没用过，恢复已经删除的内容还有些希望，特别是那些被标为可重用的区域。但是，重申一遍，我们没有专门的工具或者过程来帮助恢复已经删除的数据。

21. SQLITE_CORRUPT是什么错，它意味着数据库已经毁坏了吗？为什么会出现这种错误？
当sqlite发现内部结构、格式或者其它控制信息有错时，就会返回SQLITE_CORRUPT。
SQLITE并不破坏数据库文件，除了极少量的bug(see:http://www.sqlite.org/cvstrac/wiki?p=DatabaseCorruption)，甚至这些bug都极难重现。即使你的应用在某次更新操作中crash了，数据库也是安全的。 即使操作系统宕掉了或者掉电了，也会是安全的。这种抗crash能力经过数百万用户的真实环境的测试。

也就是说，外部程序或者硬件/OS的bug，可能导致数据库文件损坏。详细细节可以在原子提交和锁支持相关内容里介绍。

你可以使用PRAGMA_integrity_check来进行数据库的完整性测试。也可以使用PRAGMA_quick_check可以用来执行快速检测。
按照你数据库的损坏程序，可以选择使用CLI(命令行)来恢复部分数据，dump出相关schema和内容，重建数据库。不幸的是，一旦出现此类问题，很难再恢复原状。

22. SQLite支持外键吗？
从3.6.19开始，支持外键约束。
在此版本以前，有外键的解析器，但是并不强制。等价的功能可以用触发器来实现。3.6.12以及后续版本，shell工具提供了.genfkey命令来自动产生此类触发器。

23. 如果使用SQLITE_OMIT_...宏，遇到了编译错误
SQLITE_OMIT_...编译宏，只在编译canonically源文件时有效，而在编译单文件(amalgamation)时无效。

24. 我的where子句里column1="column1"不工作，造成表中每一行都被返回，而不是返回值为"column1"的那些行，这是为何？
使用单引号，不要使用双引号。where子句里，应该用column1='column1'.  SQLite使用双引号来作关键字的标识符界定。因此，column1="column1"将始终为true.

25. 有语法图吗？
see: http://wiki.tcl.tk/21708.

26. SQL标准需要unique约束强制执行，即使有一个或多个列为NULL，但SQLite却不完全这样，难道不是bug吗？

也许你指的是SQL92标准：unique约束，满足的前提是：当且仅当没有两行unique列有相同的非空值。这个语句是模糊的，至少有两种解释：
1. 没有两行数据有相同的值并且是非空值
2. 没有两行数据有相同的值，在unique列的非空子集里
SQLite遵循第1种解释，与Postgre, MySQL,Oracle以及Firebird一样。而Informix和MS SQL使用第2种解释。第1解释的受众要多一些。

27. SQLite的ECCN(出口控制分类号)?
在认真阅读CCL(商务控制列表)之后，我们认为public-domain的SQLITE源码不会被任何ECCN描述，因此ECCN应该申报为EAR99。
当使用核心的public-domain SQLITE，ECCN应该为EAR99，如果你私自添加新代码，或者静态链接SQLITE到你的应用中，应该修改ECCN。