SQLite FAQ

sqlite3官方的常见问题

随便看了看，留着存个档。如果大家看到有什么错误，欢迎大家提出，谢谢
1. 怎么创建一个自增字段
把字段声明成 interger primary key 就可以了
例子 
        create table t1 ( a integer primary key , b integer);
        insert into t1 values (NULL, 10);
sqlite3_last_insert_rowid() 这个函数获得最后插入的记录的id
2. sqlite支持哪些数据类型
integer, real, text, blob, null 
3. sqlite为什么能让我插入一个字符串到一个整形字段
这个特性不是bug. SQLite uses dynamic typing.
不做数据类型的强制转换，任何数据都能被插入到任何列。
除了primary key限定的字段做整形检查，其他字段都不检查插入数据类型.
insert into t1 values(NULL, 'lskdf');
4. 为什么不让我使用'0'和'0.0'在一张表中同时做主键
你肯定是用的integer做的主键类型，换成text类型做主键就行了
5. 多个应用程序或同一个应用程序的多个线程能同时访问一个数据库文件吗
可以。但是只能同时访问只能做select，只能有1个进程去修改数据
sqlite使用读写锁控制数据库的访问权限.
注意: nfs文件系统支持有问题
6. sqlite线程安全吗
是安全的.
编译的时候SQLITE_THREADSAFE的参数设置成1，就支持线程安全
如果不确定是否支持可以调用sqlite3_threadsafe() 函数来检测，返回0就是支持
7. 怎样列出数据库中所有的表和索引
在命令行模式使用 ".tables"列出所有的表和索引
也可以使用 ".shema" 列出所有的表的概要 类似于mysql中的(show create table xxx)
".schema tablename/LIKE patern" 可以直接跟表名字，也可以跟一个LIKE的匹配
例子".schema tab%"
在c或c++中，我们可以通过一个select语句来获取数据库中的所有的表
sqlite默认有一个sqlite_master表，里面存了所有的已经创建的表，这个表的定义如下:
sqlite> .schema sqlite_master
CREATE TABLE sqlite_master (
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);
索引的类型是"index"
当type是索引的时， name字段是索引名, tbl_name是表名, sql字段是创建表/索引的语句
sqlite> select name from sqlite_master where type='table';
sqlite_sequence
t1
t2
8. sqlite数据库的大小限制
好长，看了下，反正肯定我用不完
9. VARCHAR类型的大小限制
就算你声明的是varchar(10), 你也可以存500个字符进去. 最多允许5000个字符.
10. 是否支持BLOB类型
3.0以及以上版本都支持, 不管你的列定义的是什么类型都能插入BLOB类型
11. 我怎么删除或添加表中的列
sqlite只能添加字段在表的最后或rename这个字段名
如果你要更多的修改,建议你建个新表 例子如下
如果你有一个表 "t1", 有"a", "b", "c" 3列, 然后你想删除列"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吗？
这不是bug.  删除完后的空闲磁盘都被添加到内部的"free_list"中, 用来存储下次插入的数据.
如果你想释放空间可以用 "vacuum" 命令.
在3.1版本，有一个auto-vaccum模式
查看这个模式 "PRAGMA auto_vacuum" 
0 none        (default) 
1 full        每次删除都清空数据库的空间(必须在表被创建的之前打开这个选项才有效)
2 incremental  附加信息需要被存储到数据库中, 但是每次任务完成后是不会像 设置成 1那样自动执行. 需要单独的执行"PRAGMA incremental_vacuum" 这个命令来产生事件(不懂什么意思)
13. 是否可以免费商业化
可以.
14. 怎样逃逸单引号
2个单引号就可以转义一个单引号 例子
INSERT INTO xyz VALUES('5 O''clock');
 
15. SQLITE_SCHEMA错误是什么
当一个预备好的sql语句不再有效或不能执行就会返回这个错误. 
出现这个错误必须重新编译一下sql语句
这个错误只可能在调用sqlite3_prepare(), and sqlite3_step() 出现, 不可能从sqlite3_exec()函数中返回这个错误
如果你用sqlite3_prepare_v2()替换sqlite3_prepare(), 你也不会收到这个错误
(16) Why does ROUND(9.95,1) return 9.9 instead of 10.0? Shouldn't 9.95 round up?
    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.
    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) I get hundreds of compiler warnings when I compile SQLite. Isn't this a problem? Doesn't it indicate poor code quality?
    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.
    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!)
    We also run SQLite using Valgrind on Linux and verify that it detects no problems.
    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 have access to.
18. 不能匹配unicode字符
19. 插入十分慢 - I can only do few dozen INSERTs per second( dozen == 累计到一定量再插入吗？)
实际上sqlite能够在普通的电脑上很容易的每秒插入50,000或更多的数据
But it will only do a few dozen transactions per second.
20. 如果我删除了很重要的数据，我怎么恢复？
如果你有有备份 可以从备份中恢复. 
如果你没有备份 恢复数据将非常困难. 你可以能从裸数据库文件中的dump中找到一些数据 云云 
(21) What is an SQLITE_CORRUPT error? What does it mean for the database to be "malformed"? Why am I getting this error?
    An SQLITE_CORRUPT error is returned when SQLite detects an error in the structure, format, or other control elements of the database file.
    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."
    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.
    Your can use PRAGMA integrity_check to do a thorough but time intensive test of the database integrity.
    Your can use PRAGMA quick_check to do a faster but less thorough test of the database integrity.
    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. 外键支持
在3.6.19 就支持外键
(23) I get a compiler error if I use the SQLITE_OMIT_... compile-time options when building SQLite.
    The SQLITE_OMIT_... compile-time options only work when building from canonically source files. They do not work when you build from the SQLite amalgamation or from the pre-processed source files.
    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" 不工作, 会返回表中的所有行
把双引号换成单引号, 
(25) How are the syntax diagrams (a.k.a. "railroad" diagrams) for SQLite generated?
    The process is explained at http://wiki.tcl.tk/21708.
26没看明白, 反正sqlite能够在unique字段可以为NULL的时候插入多个NULL，mysql也可以
(26) The SQL standard requires that a UNIQUE constraint be enforced even of one or more of the columns in the constraint are NULL, but SQLite does not do this. Isn't that a bug?
    Perhaps you are referring to the following statement from SQL92:
        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. 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. 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 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.