关于 网站地图 文档 下载 许可 新闻 支持

常见问题
Frequently Asked Questions

1. 如何创建一个AUTOINCREMENT字段。
   How do I create an AUTOINCREMENT field.
2. SQLite 支持哪些数据类型？
   What datatypes does SQLite support?
3. SQLite居然允许我将一个string插入到integer类型的数据库字段中！
   SQLite lets me insert a string into a database column of type integer!
4. 为什么 SQLite 不允许在一个表中使用 '0' 和 '0.0' 作为两个不同行的主键？
   Why doesn't SQLite allow me to use '0' and '0.0' as the primary key on two different rows of the same table?
5. 多个应用或者一个应用的多个实例是否能同时访问同一个数据库文件？
   Can multiple applications or multiple instances of the same application access a single database file at the same time?
6. SQLite 是线程安全的么？
   Is SQLite threadsafe?
7. 如何列出一个SQLite数据库中的所有表和索引？
   How do I list all tables/indices contained in an SQLite database
8. SQLite数据库有什么大小限制么？
   Are there any known size limits to SQLite databases?
9. SQLite中VARCHAR的最大长度是多少？
   What is the maximum size of a VARCHAR in SQLite?
10. SQLite是否支持BLOB类型？
    Does SQLite support a BLOB type?
11. 在SQLite中如何在一个已有的表上添加或者删除列？
    How do I add or delete columns from an existing table in SQLite.
12. 我删除了大量的数据，但是数据库文件并没有变小，这是bug么？
    I deleted a lot of data but the database file did not get any smaller. Is this a bug?
13. 我能将SQLite用到我的商业软件中，而不交纳版税么？
    Can I use SQLite in my commercial product without paying royalties?
14. 如何使用一个包含了单引号(')的字符串？
    How do I use a string literal that contains an embedded single-quote (') character?
15. SQLITE_SCHEMA错误是什么意思，为什么会出现？
    What is an SQLITE_SCHEMA error, and why am I getting one?
16. 为什么ROUND(9.95,1) 会返回9.9而不是10.0呢？9.95难道不应该向上取整么？
    >Why does ROUND(9.95,1) return 9.9 instead of 10.0? Shouldn't 9.95 round up?
17. 当我编译SQLite的时候出现了上百个警告，这是因为有问题么？还是表明代码质量较差？
    I get some compiler warnings when I compile SQLite. Isn't this a problem? Doesn't it indicate poor code quality?
18. 无法不区分大小写匹配Unicode字符。
    Case-insensitive matching of Unicode characters does not work.
19. INSERT操作非常慢——一秒钟只能执行十几条INSERT。
    INSERT is really slow - I can only do few dozen INSERTs per second
20. 我意外从SQLite数据库删除了一些重要信息，我该如何恢复呢？
    I accidentally deleted some important information from my SQLite database. How can I recover it?
21. SQLITE_CORRUPT错误是什么意思？是说数据库异常么？为什么会出现这个错误？
    What is an SQLITE_CORRUPT error? What does it mean for the database to be "malformed"? Why am I getting this error?
22. SQLite支持外键么？
    Does SQLite support foreign keys?
23. 当我使用SQLITE_OMIT_...编译期选项编译SQLite时出现了编译错误。
    I get a compiler error if I use the SQLITE_OMIT_... compile-time options when building SQLite.
24. WHERE 子句表达式 column1="column1" 无法正确执行。 会导致表中的所有行全都返回，而不是只有column1值为"column1"的行。
    My WHERE clause expression column1="column1" does not work. It causes every row of the table to be returned, not just the rows where column1 has the value "column1".
25. SQLite的语法图（又称“铁路”图）是如何生成的？
    How are the syntax diagrams (a.k.a. "railroad" diagrams) for SQLite generated?
26. SQL标准要求在一个UNIQUE约束的列中，当有一个或者多个值为NULL时触发UNIQUE约束，但是SQLite不是这样。这是一个bug么？
    The SQL standard requires that a UNIQUE constraint be enforced even if one or more of the columns in the constraint are NULL, but SQLite does not do this. Isn't that a bug?
27. SQLite的 Export Control Classification Number (ECCN) 是什么？
    What is the Export Control Classification Number (ECCN) for SQLite?
28. 无得查询无法返回我期望的列名，这是个bug么？
    My query does not return the column name that I expect. Is this a bug?

(1) 如何创建一个AUTOINCREMENT字段。
(1) How do I create an AUTOINCREMENT field.

简单说：当一列声明为 INTEGER PRIMARY KEY那么就会自动增长。
Short answer: A column declared INTEGER PRIMARY KEY will autoincrement.

详细说: 如果你给一列申明为 INTEGER PRIMARY KEY，那么当你插入一个NULL到这个表的这一列中时，NULL将会自动转换为一个比全表中该字段的最大值大1的一个integer类型数值，或者当表是空表时转换成1。（如果最大的integer数值9223372036854775807已经使用了，那么就会随机选择一个没有使用的数值）例如，假如你有一张这样的表：
Longer answer: If you declare a column of a table to be INTEGER PRIMARY KEY, then whenever you insert a NULL into that column of the table, the NULL is automatically converted into an integer which is one greater than the largest value of that column over all other rows in the table, or 1 if the table is empty. Or, if the largest existing integer key 9223372036854775807 is in use then an unused key value is chosen at random. For example, suppose you have a table like this:

CREATE TABLE t1(
  a INTEGER PRIMARY KEY,
  b INTEGER
);

在这个表中，语句
With this table, the statement

INSERT INTO t1 VALUES(NULL,123);

逻辑上等价于：
is logically equivalent to saying:

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

函数 sqlite3_last_insert_rowid() 能返回最近一次insert的integer类型键
There is a function named sqlite3_last_insert_rowid() which will return the integer key for the most recent insert operation.

注意integer类型的键值是比插入之前表中最大的键还要大的值。新键相对于当前表里所有的键是独一无二的，但是当有键从表中删除时可能会重复出现这个键值。如果想创建一个在表的整个生命期中都是唯一的键，需要为INTEGER PRIMARY KEY添加AUTOINCREMENT关键字。这样当选择键值时会选择比表中曾经出现过的最大值更大的值。如果能取的最大的键曾经在表中存在过，那么这条INSERT就会失败并返回一个SQLITE_FULL错误码。
Note that the integer key is one greater than the largest key that was in the table just prior to the insert. The new key will be unique over all keys currently in the table, but it might overlap with keys that have been previously deleted from the table. To create keys that are unique over the lifetime of the table, add the AUTOINCREMENT keyword to the INTEGER PRIMARY KEY declaration. Then the key chosen will be one more than the largest key that has ever existed in that table. If the largest possible key has previously existed in that table, then the INSERT will fail with an SQLITE_FULL error code.

(2) SQLite 支持哪些数据类型？
(2) What datatypes does SQLite support?

SQLite 使用的是 动态类型。存储内容可以是INTEGER，REAL，TEXT，BLOB或者是NULL
SQLite uses dynamic typing. Content can be stored as INTEGER, REAL, TEXT, BLOB, or as NULL.

(3) SQLite居然允许我将一个string插入到integer类型的数据库字段中！
(3) SQLite lets me insert a string into a database column of type integer!

这是一个特点而不是bug。SQLite使用的是 动态类型。 所以不实习数据类型的约束。任何数据都能被插入到任何列中。你可以插入一个任意长度的string到integer列中，插入一个浮点数到一个bollean列中，或者插入一个date类型到caracter列中。在CREATE TABLE命令中指定的datatype不会限制任何数据插入到列中。每一个列都能存储一个任意长度的string。（有一个例外：一个 INTEGER PRIMARY KEY类型的列只能插入64-bit signed integer。当你试图将一个非integer的值插入到INTEGER PRIMARY KEY列中会得到一个错误。）
This is a feature, not a bug. SQLite uses dynamic typing. It does not enforce data type constraints. Data of any type can (usually) be inserted into any column. You can put arbitrary length strings into integer columns, floating point numbers in boolean columns, or dates in character columns. The datatype you assign to a column in the CREATE TABLE command does not restrict what data can be put into that column. Every column is able to hold an arbitrary length string. (There is one exception: Columns of type INTEGER PRIMARY KEY may only hold a 64-bit signed integer. An error will result if you try to put anything other than an integer into an INTEGER PRIMARY KEY column.)

不过SQLite使用定义的类型作为最佳类型的推荐。所以，例如，如果一个列定义为INTEGER，你试图插入一个string到这一列中，SQLite将会尝试将string转换为一个integer。如果可以转换则插入转换后的integer数值，如果转换失败，则插入string。这个特性被称为 类亲和性。
But SQLite does use the declared type of a column as a hint that you prefer values in that format. So, for example, if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. If not, it inserts the string. This feature is called type affinity.

(4) 为什么 SQLite 不允许在一个表中使用 '0' 和 '0.0' 作为两个不同行的主键？
(4) Why doesn't SQLite allow me to use '0' and '0.0' as the primary key on two different rows of the same table?

当主键为数值类型时会发生这个问题。如果将主键数据类型转为TEXT则可以同时使用。
This problem occurs when your primary key is a numeric type. Change the datatype of your primary key to TEXT and it should work.

每一行都必须有一个唯一的主键。对于一个数值类型的列，SQLite认为'0' 和 '0.0' 是同样的值，因为这两个数字在数值上是相同的。（参考前一个问题）因此会导致值不唯一。
Every row must have a unique primary key. For a column with a numeric type, SQLite thinks that '0' and '0.0' are the same value because they compare equal to one another numerically. (See the previous question.) Hence the values are not unique.

(5) 多个应用或者一个应用的多个实例是否能同时访问同一个数据库文件？
(5) Can multiple applications or multiple instances of the same application access a single database file at the same time?

多个进程可以在同时打开同一个数据库。多个进程也可以同时进行SELECT操作。但是，任何时候都只能有一个进程可以修改数据库。
Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.

SQLite使用读写锁来控制数据库的访问（在Win95/98/ME下，由于缺乏对读写锁的支持，所以使用了一个概率模拟来替代读写锁）。但是需要小心：如果数据库文件是在NFS文件系统中，那么这个锁机制可能会出现异常。这是因为fcntl()文件锁在许多NFS实现上是损坏的。当需要多个进程同时访问数据库文件时，需要避免将SQLite数据库文件放在NFS中。在Windows中，Microsoft的文档中说明如果没有运行Share.exe守护进程，那么无法在FAT文件系统上使用锁。 一些有丰富Windows经验的人告诉我，网络文件的文件锁有非常多的bug，是无法依赖的。如果他说的是真的，那么在两个以上的Windows机器间共享SQLite数据库可能会导致意想不到的问题。
SQLite uses reader/writer locks to control access to the database. (Under Win95/98/ME which lacks support for reader/writer locks, a probabilistic simulation is used instead.) But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time. On Windows, Microsoft's documentation says that locking may not work under FAT filesystems if you are not running the Share.exe daemon. People who have a lot of experience with Windows tell me that file locking of network files is very buggy and is not dependable. If what they say is true, sharing an SQLite database between two or more Windows machines might cause unexpected problems.

我们发现再没有一个其它的嵌入式数据库引擎能支持像SQLite这么多的并发特性。SQLite允许多个进程同时打开一个数据库文件，并且允许多个进程同时读取一个数据库。如果一个进程需要写入时，必须在整个更新过程中锁定整个数据库。但是这通常只需要花费若干毫秒。 其它进程只需要等待写入完成就可以继续进行自身的事务了。 其他嵌入式SQL数据库引擎通常一次只允许一个进程连接到数据库上。
We are aware of no other embedded SQL database engine that supports as much concurrency as SQLite. SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business. Other embedded SQL database engines typically only allow a single process to connect to the database at once.

可是，客户端/服务端数据库引擎（例如PostgreSQL、MySQL或Oracle）通常支持很高级别的并发，并且允许多进程同时写入同一个数据库。 这在客户端/服务端数据库上是可能的，这是因为它们总是一个控制良好的单独服务进程，可以有效的协调访问。如果你的应用需要大量的并发，那么你应当考虑使用一个客户端/服务端数据库。但是经验来说，大多数的应用所需的并发性远小于其设计中的想象。
However, client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine.

如果SQL试图访问一个被其它进程锁定的文件，那么默认的行为是返回SQLITE_BUSY。你可以使用C代码通过sqlite3_busy_handler() 或 sqlite3_busy_timeout()API函数来调整行为。
When SQLite tries to access a file that is locked by another process, the default behavior is to return SQLITE_BUSY. You can adjust this behavior from C code using the sqlite3_busy_handler() or sqlite3_busy_timeout() API functions.

(6) SQLite 是线程安全的么？
(6) Is SQLite threadsafe?

线程是恶魔，应当避开它。
Threads are evil. Avoid them.

SQLite是线程安全的。我们做出这个让步是因为许多用户都选择了忽略前一段给出的忠告。但是为了达到线程安全，SQLite需要在编译时将SQLITE_THREADSAFE宏指令设置为1。Windows和Linux平台的预编译二进制分发包都是使用这个参数编译的。如果你无法确认你连接入的SQLite库是否是线程安全的，你可以调用sqlite3_threadsafe()接口来查询。
SQLite is threadsafe. We make this concession since many users choose to ignore the advice given in the previous paragraph. But in order to be thread-safe, SQLite must be compiled with the SQLITE_THREADSAFE preprocessor macro set to 1. Both the Windows and Linux precompiled binaries in the distribution are compiled this way. If you are unsure if the SQLite library you are linking against is compiled to be threadsafe you can call the sqlite3_threadsafe() interface to find out.

在SQLite 3.3.1版之前，sqlite3结构体只能在调用sqlite3_open()创建它的线程中使用。你不能在一个线程中打开一个数据库，然后将句柄传递给另一个线程使用。这是由于一些通用线程实现的局限性（bug？）所致（例如RedHat9）。具体说，在麻烦的系统中一个线程创建的fcntl()锁无法被其它线程移除或修改。自从SQLite为了实现并发控制而严重依赖fcntl()，如果你试图跨线程移动一个数据库连接，那么会引发一个严重的错误。
Prior to version 3.3.1, an sqlite3 structure could only be used in the same thread that called sqlite3_open() to create it. You could not open a database in one thread then pass the handle off to another thread for it to use. This was due to limitations (bugs?) in many common threading implementations such as on RedHat9. Specifically, an fcntl() lock created by one thread cannot be removed or modified by a different thread on the troublesome systems. And since SQLite uses fcntl() locks heavily for concurrency control, serious problems arose if you start moving database connections across threads.

跨线程移动数据库连接的限制在 3.3.1 版中得以放宽。在这个版本及后续的版本中，只要这个连接没有持有任何fcntl()锁，那么就可以安全的跨线程移动。你可以放心的假设只要没有执行中的事务并且所有的语句都已经完成，那么就不会持有锁。
The restriction on moving database connections across threads was relaxed somewhat in version 3.3.1. With that and subsequent versions, it is safe to move a connection handle across threads as long as the connection is not holding any fcntl() locks. You can safely assume that no locks are being held if no transaction is pending and all statements have been finalized.

在Unix下，你不能通过fork()系统调用在子进程中调用一个打开的SQL数据库。这么做会导致问题。
Under Unix, you should not carry an open SQLite database across a fork() system call into the child process. Problems will result if you do.

(7) 如何列出一个SQLite数据库中的所有表和索引？
(7) How do I list all tables/indices contained in an SQLite database

如果你运行了sqlite3命令行访问程序，你可以输入 " .tables " 来获取所有表的列表。你也可以输入" .schema"来查看所有表格和索引的完整数据库结构。这两个命令都可以跟随一个LIKE 模式，这可以限制表的展示。
If you are running the sqlite3 command-line access program you can type " .tables" to get a list of all tables. Or you can type " .schema" to see the complete database schema including all tables and indices. Either of these commands can be followed by a LIKE pattern that will restrict the tables that are displayed.

对于在C/C++程序中（或者绑定的Tcl/Ruby/PerlPython脚本中）通过SELECT一个个数的表名" SQLITE_MASTER"来访问表和索引名。每个SQLite数据库都有一个SQLITE_MASTER表，用于定义数据库的结构。SQLITE_MASTER表的结构如下：
From within a C/C++ program (or a script using Tcl/Ruby/Perl/Python bindings) you can get access to table and index names by doing a SELECT on a special table named " SQLITE_MASTER". Every SQLite database has an SQLITE_MASTER table that defines the schema for the database. The SQLITE_MASTER table looks like this:

CREATE TABLE sqlite_master (
  type TEXT,
  name TEXT,
  tbl_name TEXT,
  rootpage INTEGER,
  sql TEXT
);

对于表来说，type字段总是'table'，name字段是表名。所以使用下面的SELECT命令可以获取所有表的列表：
For tables, the type field will always be 'table' and the name field will be the name of the table. So to get a list of all tables in the database, use the following SELECT command:

SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;

对于索引来说type字段等于'index'，name字段是索引的名字，tbl_name字段是索引所属的表名。对于表和索引，sql字段是创建表或索引的原始的CREATE TABLE或CREATE INDEX语句文本。对于自动创建的索引（使用PRIMARY KEY或UNIQUE约束实现）sql字段是NULL。
For indices, type is equal to 'index', name is the name of the index and tbl_name is the name of the table to which the index belongs. For both tables and indices, the sql field is the text of the original CREATE TABLE or CREATE INDEX statement that created the table or index. For automatically created indices (used to implement the PRIMARY KEY or UNIQUE constraints) the sql field is NULL.

SQLITE_MASTER表是只读的。你无法使用UPDATE、INSERT或者DELETE来修改表。这个表是通过CREATE TABLE、 CREATE INDEX、 DROP TABLE 和 DROP INDEX命令自动更新的。
The SQLITE_MASTER table is read-only. You cannot change this table using UPDATE, INSERT, or DELETE. The table is automatically updated by CREATE TABLE, CREATE INDEX, DROP TABLE, and DROP INDEX commands.

临时表不会出现在SQLITE_MASTER表中。临时表及其索引和触发器会出现在另一张名为SQLITE_TEMP_MASTER的特殊表中。SQLITE_TEMP_MASTER的功能与SQLITE_MASTER类似，只是SQLITE_TEMP_MASTER表只会对创建了临时表的应用可见。获取包含永久表和临时表的所有表列表可以使用类似下面的命令：
Temporary tables do not appear in the SQLITE_MASTER table. Temporary tables and their indices and triggers occur in another special table named SQLITE_TEMP_MASTER. SQLITE_TEMP_MASTER works just like SQLITE_MASTER except that it is only visible to the application that created the temporary tables. To get a list of all tables, both permanent and temporary, one can use a command similar to the following:

SELECT name FROM 
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE type='table'
ORDER BY name

(8) SQLite数据库有什么大小限制么？
(8) Are there any known size limits to SQLite databases?

关于SQLite中限制的详细讨论参见limits.html。
See limits.html for a full discussion of the limits of SQLite.

(9) SQLite中VARCHAR的最大长度是多少？
(9) What is the maximum size of a VARCHAR in SQLite?

SQLite不会限制VARCHAR的长度。你可以定义个VARCHAR(10)，然后SQLite依然允许你在其中插入500个字符。并且会完整的保存500个字符——永远不会做截断。
SQLite does not enforce the length of a VARCHAR. You can declare a VARCHAR(10) and SQLite will be happy to let you put 500 characters in it. And it will keep all 500 characters intact - it never truncates.

(10) SQLite是否支持BLOB类型？
(10) Does SQLite support a BLOB type?

SQLite第三版之后允许在任何列存储BLOB数据，即使列定义为存储其它类型也可以。
SQLite versions 3.0 and later allow you to store BLOB data in any column, even columns that are declared to hold some other type.

(11) 在SQLite中如何在一个已有的表上添加或者删除列？
(11) How do I add or delete columns from an existing table in SQLite.

SQLite只对ALTER TABLE做了有限的支持，你可以使用它在表的末尾添加一列，或者修改表的名字。如果你想对表结构做其它更复杂的修改，那么你只能重新创建表了。你可以将已有的数据保存到一个临时表中，然后删除旧表，创建新表，最后将数据从临时表中复制回来。
SQLite has limited ALTER TABLE support that you can use to add a column to the end of a table or to change the name of a table. If you want to make more complex changes in the structure of a table, you will have to recreate the table. You can save existing data to a temporary table, drop the old table, create the new table, then copy the data back in from the temporary table.

例如，假设你有一个名为"t1"的表，包含的列名为"a"、 "b"、 and "c" ，然后你希望从表中删除列"c"。下面的步骤演示了如何做：
For example, suppose you have a table named "t1" with columns names "a", "b", and "c" and that you want to delete column "c" from this table. The following steps illustrate how this could be done:

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么？
(12) I deleted a lot of data but the database file did not get any smaller. Is this a bug?

不是。当你从SQLite数据库中删除信息时，未使用的磁盘空间会被加入到一个内部的"空闲列表"中，并且在下次你插入数据的时候重用。磁盘空间没有丢失，只是没有返还给操作系统。
No. When you delete information from an SQLite database, the unused disk space is added to an internal "free-list" and is reused the next time you insert data. The disk space is not lost. But neither is it returned to the operating system.

如果你删除了大量数据，并且希望收缩数据库文件，那么运行VACUUM命令。VACUUM会从磁盘碎片中重构数据库。这会将数据库的空闲列表清空，并将文件尺寸缩到最小。注意，VACUUM命令需要花费一段时间来运行（在开发SQLite的Linux机器上大约每半秒钟一兆字节）并且在运行过程中这需要使用原始文件的两倍大小的临时磁盘空间。在SQLite3.1版中，使用VACUUM命令的的使用方法之一是auto-vacuum模式，使用auto_vacuum pragma来启用。
If you delete a lot of data and want to shrink the database file, run the VACUUM command. VACUUM will reconstruct the database from scratch. This will leave the database with an empty free-list and a file that is minimal in size. Note, however, that the VACUUM can take some time to run (around a half second per megabyte on the Linux box where SQLite is developed) and it can use up to twice as much temporary disk space as the original file while it is running.

As of SQLite version 3.1, an alternative to using the VACUUM command is auto-vacuum mode, enabled using the auto_vacuum pragma.

(13) 我能将SQLite用到我的商业软件中，而不交纳版税么？
(13) Can I use SQLite in my commercial product without paying royalties?

是的。SQLite是在public domain下。在源码的任何部分都没有声明所有权。你可以做任何你想做的事儿。
Yes. SQLite is in the public domain. No claim of ownership is made to any part of the code. You can do anything you want with it.

(14) 如何使用一个包含了单引号(')的字符串？
(14) How do I use a string literal that contains an embedded single-quote (') character?

SQL标准中指出字符串中的单引号使用一行内两个连续的单引号来转义。SQL的处理方式类似Pascal程序语言。SQLite遵循标准文档。例如：
The SQL standard specifies that single-quotes in strings are escaped by putting two single quotes in a row. SQL works like the Pascal programming language in the regard. SQLite follows this standard. Example:

    INSERT INTO xyz VALUES('5 O''clock');
  

(15) SQLITE_SCHEMA错误是什么意思，为什么会出现？
(15) What is an SQLITE_SCHEMA error, and why am I getting one?

当一个预处理SQL语句不再有效并无法执行时会返回一个SQLITE_SCHEMA错误。当发生这个错误时，语句必须使用sqlite3_prepare()API从SQL重新编译。SQLITE_SCHEMA错误只会在使用sqlite3_prepare()和sqlite3_step()接口运行SQL时才会发生。在sqlite3_exec()中永远不会产生SQLITE_SCHEMA错误。如果在预处理语句时使用sqlite3_prepare_v2()替代sqlite3_prepare()，那么也不会收到错误。
An SQLITE_SCHEMA error is returned when a prepared SQL statement is no longer valid and cannot be executed. When this occurs, the statement must be recompiled from SQL using the sqlite3_prepare() API. An SQLITE_SCHEMA error can only occur when using the sqlite3_prepare(), and sqlite3_step() interfaces to run SQL. You will never receive an SQLITE_SCHEMA error from sqlite3_exec(). Nor will you receive an error if you prepare statements using sqlite3_prepare_v2() instead of sqlite3_prepare().

sqlite3_prepare_v2()接口创建的预编译语句会在结构发生变化时自动重新编译自身。处理SQLITE_SCHEMA错误最简单的办法是永远使用sqlite3_prepare_v2()替换sqlite3_prepare()。
The sqlite3_prepare_v2() interface creates a prepared statement that will automatically recompile itself if the schema changes. The easiest way to deal with SQLITE_SCHEMA errors is to always use sqlite3_prepare_v2() instead of sqlite3_prepare().

(16) 为什么ROUND(9.95,1) 会返回9.9而不是10.0呢？9.95难道不应该向上取整么？
(16) Why does ROUND(9.95,1) return 9.9 instead of 10.0? Shouldn't 9.95 round up?

SQLite使用二进制算法，并且在二进制中，无法使用有限位的数字来表示9.95。在64位IEEE浮点数（SQLite中使用的）中能得到的最接近的值是9.949999999999999289457264239899814128875732421875。所以当你输入 "9.95"时SQLite实际认为是上面那个非常长的数字。并且这个值会向下取整。
SQLite uses binary arithmetic and in binary, there is no way to write 9.95 in a finite number of bits. The closest to you can get to 9.95 in a 64-bit IEEE float (which is what SQLite uses) is 9.949999999999999289457264239899814128875732421875. So when you type "9.95", SQLite really understands the number to be the much longer value shown above. And that value rounds down.

这类问题在处理二进制浮点数时总是会发生。需要记住的通常规则是，大多数的小数数值在十进制（a.k.a "base-10"）下能用有限长度表示，但是在二进制（a.k.a "base-2"）下无法使用有限长度来表示。所以使用最接近的可用二进制来表示。这个近似值通常非常接近。但是有一些轻微的区别，在一些示例中结果和你所期望的会有出入。
This kind of problem comes up all the time when dealing with floating point binary numbers. The general rule to remember is that most fractional numbers that have a finite representation in decimal (a.k.a "base-10") do not have a finite representation in binary (a.k.a "base-2"). And so they are approximated using the closest binary number available. That approximation is usually very close, but it will be slightly off and in some cases can cause your results to be a little different from what you might expect.

(17) 当我编译SQLite的时候出现了上百个警告，这是因为有问题么？还是表明代码质量较差？
(17) I get hundreds of compiler warnings when I compile SQLite. Isn't this a problem? Doesn't it indicate poor code quality?

SQLite的质量保证是通过全覆盖测试来完成的，而不是编译警告或者静态代码分析工具。换句话说，我们是验证SQLite实际工作是否能得到正确结果，而不是仅仅是满足格式上的约束。大多数SQLite基础代码都纯粹致力于测试。在每个最终版发布之前，SQLite的测试套件运行数以万计独立的测试用例，而且其中的许多测试用例是参数化的，所以上亿的测试用例调用数十亿条SQL语句运行，然后验证结果的正确性。开发者们使用代码覆盖工具来验证代码中所有的分支都被测试过。一旦在SQLite中发现一个bug，就会为这个bug添加一个新的测试用例用于展示这个bug。所以在未来不会有重复的bug未被发现。
Quality assurance in SQLite is done using full-coverage testing, not by compiler warnings or other static code analysis tools. In other words, we verify that SQLite actually gets the correct answer, not that it merely satisfies stylistic constraints. Most of the SQLite code base is devoted purely to testing. The SQLite test suite runs tens of thousands of separate test cases and many of those test cases are parameterized so that hundreds of millions of tests involving billions of SQL statements are run and evaluated for correctness prior to every release. The developers use code coverage tools to verify that all paths through the code are tested. Whenever a bug is found in SQLite, new test cases are written to exhibit the bug so that the bug cannot recur undetected in the future.

在测试期间，SQLite库使用专用的测试设备编译，这允许测试脚本模拟广泛多样的失败样例，以此确保SQLite能正确的恢复。内存分配器精确的跟踪，以确保不会发生内存泄露和后续内存分配失败。使用一个自定义的VFS层来模拟操作系统崩溃和断电错误，以此确保在这些事件中事务是原子的。使用一个故意注入I/O错误的机制来展示SQLite可以弹性应对这些故障（作为一个实验，可以尝试在其他SQL数据库引擎中触发这些类型的错误，然后看看发生什么！）。
During testing, the SQLite library is compiled with special instrumentation that allows the test scripts to simulate a wide variety of failures in order to verify that SQLite recovers correctly. Memory allocation is carefully tracked and no memory leaks occur, even following memory allocation failures. A custom VFS layer is used to simulate operating system crashes and power failures in order to ensure that transactions are atomic across these events. A mechanism for deliberately injecting I/O errors shows that SQLite is resilient to such malfunctions. (As an experiment, try inducing these kinds of errors on other SQL database engines and see what happens!)

我们还在Linux上使用Valgrind来运行SQLite，确保其没有发现问题。
We also run SQLite using Valgrind on Linux and verify that it detects no problems.

一些人说我们应当消除所有的警告，因为温和的警告会掩盖未来变动中产生的真正警告。确实是这样，但回答是，开发者么注意到所有的警告都已经在SQLite开发的编译器上修复了（各种版本的GCC）。编译警告只会在开发者不是日常使用的编译器中出现（例如MSVC）。
Some people say that we should eliminate all warnings because benign warnings mask real warnings that might arise in future changes. This is true enough. But in reply, the developers observe that all warnings have already been fixed in the compilers used for SQLite development (various versions of GCC). Compiler warnings only arise from compilers that the developers do not use on a daily basis (Ex: MSVC).

(18) 无法不区分大小写匹配Unicode字符。
(18) Case-insensitive matching of Unicode characters does not work.

SQLite的默认配置只支持ASCII字符的不区分大小写比较。这是因为做全部Unicode不区分大小写比较和大小写转换所需的表格和逻辑会使SQLite库的大小几乎增大一倍。SQLite开发者们推测任何一个需哟啊进行完整Unicode大小写支持的应用可能已经拥有必须的函数和表格，所以SQLite不再占用空间复制这个功能。
The default configuration of SQLite only supports case-insensitive comparisons of ASCII characters. The reason for this is that doing full Unicode case-insensitive comparisons and case conversions requires tables and logic that would nearly double the size of the SQLite library. The SQLite developers reason that any application that needs full Unicode case support probably already has the necessary tables and functions and so SQLite should not take up space to duplicate this ability.

取代默认提供完整Unicode大小写支持，SQLite提供了连接外部Unicode比较和转换程序的能力。应用程序可以重载内置的NOCASE排序器（使用sqlite3_create_collation()）和内置的like()、upper()和lower()函数（使用sqlite3_create_function()。SQLite源码中包含了一个"ICU"扩展用于做这些重载。或者，开发者可以依照自己项目中已有的Unicode比较程序的编写自己的重载。
Instead of providing full Unicode case support by default, SQLite provides the ability to link against external Unicode comparison and conversion routines. The application can overload the built-in NOCASE collating sequence (using sqlite3_create_collation()) and the built-in like(), upper(), and lower() functions (using sqlite3_create_function()). The SQLite source code includes an "ICU" extension that does these overloads. Or, developers can write their own overloads based on their own Unicode-aware comparison routines already contained within their project.

(19) INSERT操作非常慢——一秒钟只能执行十几条INSERT。
(19) INSERT is really slow - I can only do few dozen INSERTs per second

事实上，SQLite可以很容易在通常的桌面电脑上做到每秒执行50000或更多条INSERT语句。但是每秒只能执行数十个事务。事务的速度受限于你的磁盘驱动器旋转速度。一次事务通常需要两次完整的磁盘旋转。这在一个7200RPM磁盘驱动器上限制了每秒大约60个事务。
Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive. A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second.

事务速度受限于磁盘驱动器速度，这是因为（默认）SQLite实际上会在事务完成前等待数据真正安全的存储到磁盘上。这样，如果遇到突然断电或者操作系统崩溃，你的数据依然是安全的。详细内容，参见SQLite中的自动提交。
Transaction speed is limited by disk drive speed because (by default) SQLite actually waits until the data really is safely stored on the disk surface before the transaction is complete. That way, if you suddenly lose power or if your OS crashes, your data is still safe. For details, read about atomic commit in SQLite..

默认情况下，每个INSERT语句都包含一个自己的事务，但是如果你使用BEGIN...COMMIT包围多条INSERT语句，那么所有的插入被划分为在了一个事务中。这时提交事务的时间被分摊到所有包含的插入语句中。所以每条插入语句耗费的时间就会大大减少。
By default, each INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN... COMMIT then all the inserts are grouped into a single transaction. The time needed to commit the transaction is amortized over all the enclosed insert statements and so the time per insert statement is greatly reduced.

另一个选项是运行PRAGMA synchronous=OFF。这个命令会使SQLite不等待数据写到磁盘上，这会使写入操作看起来快了很多，但是如果在事务过程中遇到断电，你的数据库文件可能会损坏。
Another option is to run PRAGMA synchronous=OFF. This command will cause SQLite to not wait on data to reach the disk surface, which will make write operations appear to be much faster. But if you lose power in the middle of a transaction, your database file might go corrupt.

(20) 我意外从SQLite数据库删除了一些重要信息，我该如何恢复呢？
(20) I accidentally deleted some important information from my SQLite database. How can I recover it?

如果你有一个数据库文件的备份拷贝，那么从备份中恢复信息即可。
If you have a backup copy of your database file, recover the information from your backup.

如果你没有备份，那么恢复就非常困难了。你可以从原始数据库文件的二进制数据中找到部分字符串数据。通过专用工具来恢复数值数据也同样是可能的，虽然目前还不知道有这种工具存在。SQLite有时是使用SQLITE_SECURE_DELETE选项编译的，这样会将所有删除的内容覆盖上零。如果这样，那么恢复明显是不可能的了。如果在删除数据之后运行了VACUUM，那么同样也是不可能恢复的了。如果没有使用SQLITE_SECURE_DELETE并且没有执行VACUUM，那么因为区域标记重用的原因，一些删除的数据可能还在数据库文件中。不过，再一次强调，就我们所知，目前还没有已有的工具或程序能帮助你恢复数据。
If you do not have a backup, recovery is very difficult. You might be able to find partial string data in a binary dump of the raw database file. Recovering numeric data might also be possible given special tools, though to our knowledge no such tools exist. SQLite is sometimes compiled with the SQLITE_SECURE_DELETE option which overwrites all deleted content with zeros. If that is the case then recovery is clearly impossible. Recovery is also impossible if you have run VACUUM since the data was deleted. If SQLITE_SECURE_DELETE is not used and VACUUM has not been run, then some of the deleted content might still be in the database file, in areas marked for reuse. But, again, there exist no procedures or tools that we know of to help you recover that data.

(21) SQLITE_CORRUPT错误是什么意思？是说数据库异常么？为什么会出现这个错误？
(21) What is an SQLITE_CORRUPT error? What does it mean for the database to be "malformed"? Why am I getting this error?

当SQLite在数据库文件中的结构、格式或者其他控制元素中发现错误时会返回一个SQLITE_CORRUPT错误。
An SQLITE_CORRUPT error is returned when SQLite detects an error in the structure, format, or other control elements of the database file.

除非遇到非常罕见的bug（参见数据库损坏），否则SQLite不会损害数据库文件，而且即使这样，这个bug通常也是很难复现的。即使如果你的应用在一次更新的过程中崩溃了，你的数据库依然是安全的。即使操作系统崩溃或者断电，数据库也都是安全的。SQLite的抗损坏性是被广泛的计划和测试的，并且经过了真实世界中上百万用户多年的使用经验证明的。
SQLite does not corrupt database files, except in the case of very rare bugs (see DatabaseCorruption) and even then the bugs are normally difficult to reproduce. Even if your application crashes in the middle of an update, your database is safe. The database is safe even if your OS crashes or takes a power loss. The crash-resistance of SQLite has been extensively studied and tested and is attested by years of real-world experience by millions of users.

这就是说，一些扩展程序或者OS或硬件的bug才可能导致数据库文件损坏。详细内容可以在SQLite支持的自动提交和锁的讨论中和邮件归档中找到。
That said, there are a number of things that external programs or bugs in your hardware or OS can do to corrupt a database file. Details can be found in the discussions on the atomic commit and locking support in SQLite as well as in the mailing list archives.

你可以使用PRAGMA integrity_check来对数据库完整性做耗时但是彻底的检查。
Your can use PRAGMA integrity_check to do a thorough but time intensive test of the database integrity.

你可以使用PRAGMA quick_check来对数据库完整性做快速但是不完整的检查。
Your can use PRAGMA quick_check to do a faster but less thorough test of the database integrity.

根据你的数据库损坏的程度，你可能可以通过使用CLI将结构和内容导出到一个文件中，然后重建数据库以恢复一些数据库。不幸的是，一旦积重难返，四分五裂，那么通常很难将其恢复到一起。
Depending how badly your database is corrupted, you may be able to recover some of the data by using the CLI to dump the schema and contents to a file and then recreate. Unfortunately, once humpty-dumpty falls off the wall, it is generally not possible to put him back together again.

(22) SQLite支持外键么？
(22) Does SQLite support foreign keys?

自3.6.19版起，SQLite支持外键约束。
As of version 3.6.19, SQLite supports foreign key constraints.

之前版本的SQLite会解析外键约束，但是不会执行约束。等价的功能可以使用SQL 触发器来实现。3.6.12版之后的SQLite命令行工具提供了".genfkey"命令，用于自动生成这种触发器。关于genfkey功能的更多信息参见自述文件。
Prior versions of SQLite parsed foreign key constraints, but did not enforce them. The equivalent functionality could be implemented using SQL triggers. Versions 3.6.12 and later of the SQLite shell tool provided the ".genfkey" command to generate such triggers automatically. The readme for the genfkey utility contains more information.

(23) 当我使用SQLITE_OMIT_...编译期选项编译SQLite时出现了编译错误。
(23) I get a compiler error if I use the SQLITE_OMIT_... compile-time options when building SQLite.

SQLITE_OMIT_...编译选项只能在编译标准源码文件时使用。这不能在联合SQLite或预处理的源文件中使用。
The SQLITE_OMIT_... compile-time options only work when building from canonical source files. They do not work when you build from the SQLite amalgamation or from the pre-processed source files.

可以构建一个特殊的联合版，以便使用预先设定好的SQLITE_OMIT_...选项集。 SQLITE_OMIT_... 文档中有如何做的详细说明。
It is possible to build a special amalgamation that will work with a predetermined set of SQLITE_OMIT_... options. Instructions for doing so can be found with the SQLITE_OMIT_... documentation.

(24) WHERE 子句表达式 column1="column1" 无法正确执行。 会导致表中的所有行全都返回，而不是只有column1值为"column1"的行。
(24) My WHERE clause expression column1="column1" does not work. It causes every row of the table to be returned, not just the rows where column1 has the value "column1".

在SQL中包围字符串的是单引号而不是双引号。这是SQL标准中要求的。你的WHERE子句表达式应当是：column1='column1'
Use single-quotes, not double-quotes, around string literals in SQL. This is what the SQL standard requires. Your WHERE clause expression should read: column1='column1'

SQL里当标识符（列和表名）中含有特殊字符或者是关键词时使用双引号包围标识符。所以双引号是转义标志名的一种方法。因此当你输入column1="column1"时，这等价于column1=column1，这个当然永远都是true了。
SQL uses double-quotes around identifiers (column or table names) that contains special characters or which are keywords. So double-quotes are a way of escaping identifier names. Hence, when you say column1="column1" that is equivalent to column1=column1 which is obviously always true.

(25) SQLite的语法图（又称“铁路”图）是如何生成的？
(25) How are the syntax diagrams (a.k.a. "railroad" diagrams) for SQLite generated?

这个生成过程在http://wiki.tcl.tk/21708中有说明。
The process is explained at http://wiki.tcl.tk/21708.

(26) SQL标准要求在一个UNIQUE约束的列中，当有一个或者多个值为NULL时触发UNIQUE约束，但是SQLite不是这样。这是一个bug么？
(26) The SQL standard requires that a UNIQUE constraint be enforced even if one or more of the columns in the constraint are NULL, but SQLite does not do this. Isn't that a bug?

可能你在SQL92中阅读了如下语句：
Perhaps you are referring to the following statement from SQL92:

在唯一列中，唯一约束要求表中不能存在两行相同的非NULL值。（A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns.）

这句话是有歧义的，至少有以下两种解释：
That statement is ambiguous, having at least two possible interpretations:

1. 在唯一列中，唯一约束要求表中不能存在两行相同的并且值是非NULL的行。（A unique constraint is satisfied if and only if no two rows in a table have the same values and have non-null values in the unique columns.）
2. 在唯一列中，唯一约束要求表中非NULL的子集中不能存在两行相同的值。（A unique constraint is satisfied if and only if no two rows in a table have the same values in the subset of unique columns that are not null.）

SQLite遵循解释(1)，这与PostgreSQL、MySQL、Oracle和Firebird一样。不过Microsoft SQL Server和Informix使用的是解释(2)。不管怎样，SQLite的开发者们认为解释(1)是对这个要求更自然的理解。同时我们也希望能最大化的兼容其他SQL数据库引擎，并且大多数的数据库引擎也同样是遵循(1)的。所以SQLite也这么做了。
SQLite follows interpretation (1), as does PostgreSQL, MySQL, Oracle, and Firebird. It is true that Informix and Microsoft SQL Server use interpretation (2), however we the SQLite developers hold that interpretation (1) is the most natural reading of the requirement and we also want to maximize compatibility with other SQL database engines, and most other database engines also go with (1), so that is what SQLite does.

(27) SQLite的 Export Control Classification Number (ECCN) 是什么？
(27) What is the Export Control Classification Number (ECCN) for SQLite?

在仔细审查了Commerce Control List (CCL)之后，我们确认SQLite源码的核心公共域（public-domain）不是ECCN所描述的，因此，ECCN应当当做EAR99记录。
After careful review of the Commerce Control List (CCL), we are convinced that the core public-domain SQLite source code is not described by any ECCN, hence the ECCN should be reported as EAR99.

以上所述对于核心公共域SQLite是正确的。如果你通过添加新代码扩展SQLite或者将SQLite静态链接入你的应用中，那么在你的特例中可能要修改ECCN。
The above is true for the core public-domain SQLite. If you extend SQLite by adding new code, or if you statically link SQLite with your application, that might change the ECCN in your particular case.

(28) 无得查询无法返回我期望的列名，这是个bug么？
(28) My query does not return the column name that I expect. Is this a bug?

如果结果集中的列使用了AS子句来命名，那么SQLite保证AS关键词右边的标识符当做列名。如果结果集中没有使用AS子句，那么SQLite会随意设置列名。更多信息参见sqlite3_column_name()文档。
If the columns of your result set are named by AS clauses, then SQLite is guaranteed to use the identifer to the right of the AS keyword as the column name. If the result set does not use an AS clause, then SQLite is free to name the column anything it wants. See the sqlite3_column_name() documentation for further information.