SQLite FAQ 中文翻译

SQLite Frequently Asked Questions

未翻译完

Frequently Asked Questions
How do I create an AUTOINCREMENT field.
What datatypes does SQLite support?
SQLite lets me insert a string into a database column of type integer!
Why does SQLite think that the expression '0'=='00' is TRUE?
Why doesn't SQLite allow me to use '0' and '0.0' as the primary key on two different rows of the same table?
My linux box is not able to read an SQLite database that was created on my SparcStation.
Can multiple applications or multiple instances of the same application aclearcase/" target="_blank" >ccess a single database file at the same time?
Is SQLite threadsafe?
How do I list all tables/indices contained in an SQLite database
Are there any known size limits to SQLite databases?
What is the maximum size of a VARCHAR in SQLite?
Does SQLite support a BLOB type?
How do I add or delete columns from an existing table in SQLite.
I deleted a lot of data but the database file did not get any smaller. Is this a bug?
Can I use SQLite in my commercial product without paying royalties?
How do I use a string literal that contains an embedded single-quote (') character?
What is an SQLITE_SCHEMA error, and why am I getting one?
Why does ROUND(9.95,1) return 9.9 instead of 10.0? Shouldn't 9.95 round up?

--------------------------------------------------------------------------------
(1) 如何创建一个 AUTOINCREMENT 字段？

短回答：字段声明为 INTEGER PRIMARY KEY 即可。

长回答：从 SQLite 2.3.4版开始，如果一个字段被声明为 INTEGER PRIMARY KEY，那么当该字段插入一个 NULL 值时，这个 NULL 值会自动该字段内的最大值＋1，如果该字段内没有值即该表为空则 NUll 会变成1。例如：假如你有一个这样的表：

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

在这个表中，命令

INSERT INTO t1 VALUES(NULL,123);

理论上等于：
INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123);

从 SQLite 版本 2.2.0 到 2.3.3，如果你插入一个 NULL 值到一个 INTEGER PRIMARY KEY 字段，这个 NULL值会变成一个具有唯一值的整数，但是半随机的，不连续的。SQLite 版本 2.3.4 及更高版本，这个唯一值是连续的直至达到最大值 2147483647。这是32位有符号整数的最大值，如果再增加则会像以前版本一样产生半随机整数。

从版本 2.2.3起，有一名为 sqlite3_last_insert_rowid() 函数可以返回最近插入操作产生的整数值。详见 API 文档

SQLite 版本 3.0 扩展了 rowid 的尺寸至 64 位。
--------------------------------------------------------------------------------
(2) SQLite 支持什么数据类型？

SQLite 忽略 CREATE TABLE 语句中跟在字段名字后的数据类型信息。你可以在任何字段中放任何信息，而不用管字段声明为什么类型。

但对于 INTEGER PRIMARY KEY 字段例外。这种字段只能存放整数，否则会出错。

这里是更深的解释数据类型的页面：SQLite 版本 2.8 中数据类型 和 版本 3.0 中的数据类型。
--------------------------------------------------------------------------------

(3) SQLite 让我在整数字段中插入了一个字符串！

这是一个功能，不是一个 bug。你可以在任何字段中放任何信息，而不用管字段声明为什么类型。
但对于 INTEGER PRIMARY KEY 字段例外。这种字段只能存放整数，否则会出错。

但是数据类型对值的比较有影响。例如以下两组命令：
第一组：
CREATE TABLE t1(a INTEGER UNIQUE);
INSERT INTO t1 VALUES('0');
INSERT INTO t1 VALUES('0.0');
第二组
CREATE TABLE t2(b TEXT UNIQUE);
INSERT INTO t2 VALUES(0);
INSERT INTO t2 VALUES(0.0);

在第一组中的第二个插入命令会失败。作为整数类型0和0.0是相同的。而第二组命令是可行的，因为数据类型是字符串，而字符串中'0'和'0.0'是不同的。

这里是更深的解释数据类型的页面：SQLite 版本 2.8 中数据类型 和 版本 3.0 中的数据类型。

--------------------------------------------------------------------------------

(4) 为什么 SQLite 认为表达式 '0'=='00' 为真？

从版本 2.7.0 开始就不是这样的了。

但是两值中的一个是储存在数值类型的字段中，那么另一个就会被看作数值类型，那么结果为真。例如：

CREATE TABLE t3(a INTEGER, b TEXT);
INSERT INTO t3 VALUES(0,0);
SELECT count(*) FROM t3 WHERE a=='00';

上例中 SELECT 返回 1。因为 "a" 字段是数值类型，所以 WHERE 子句中的字符串 '00' 被转换成为一个数值。表达式 0==00 为真。现在换一个不同的 SELECT：

SELECT count(*) FROM t3 WHERE b=='00';

结果返回 0。"B"字段类型为 text，所以 '0'!='00' 。

这里是更深的解释数据类型的页面：SQLite 版本 2.8 中数据类型 和 版本 3.0 中的数据类型。

--------------------------------------------------------------------------------

(5) 为什么 SQLite 不允许我用 '0' 和 '0.0' 在一个表中不同行里作为主键值？

你的主键一定是数值类型的，把类型改为 TEXT 就可以了。

主键值是唯一的，而在数值类型字段中，SQLite 认为 '0' 和 '0.0' 是相同的（参考前一个问题）。

--------------------------------------------------------------------------------

(6) 我的 linux 不能读 Sparc 工作站创建的 SQLite 数据库。

你需要升级你的 SQLite 库到版本 2.6.3 或更高。

x86 处理器是 little-endian 型的而 Sparc 是 big-endian 型的。新版本的 SQLite 解决了这个问题。

译者注：
　　big endian和little endian是CPU处理多字节数的不同方式。例如“汉”字的Unicode编码是6C49。那么写到文件里时，究竟是将6C写在前面，还是将49写在前面？如果将6C写在前面，就是big endian。还是将49写在前面，就是little endian。

　　“endian”这个词出自《格列佛游记》。小人国的内战就源于吃鸡蛋时是究竟从大头(Big-Endian)敲开还是从小头(Little-Endian)敲开，由此曾发生过六次叛乱，其中一个皇帝送了命，另一个丢了王位。

　　我们一般将endian翻译成“字节序”，将big endian和little endian称作“大尾”和“小尾”。

--------------------------------------------------------------------------------

(7) 多个程序或一个程序的多个实例能够同进访问一个数据库文件吗？

多进程可以同时打开同一个数据库，也可以同时 SELECT 。但只有一个进程可以立即改数据库。

Win95/98/ME 操作系统缺乏读书锁定，在低于 2.7.0 的版本中，这意味着在 windows 下在同一时间内只能有一个进程读数据库。在版本 2.7.0 中这个问题通过在 windows 接口代码中执行一个用户间隔几率读写锁定策略解决了。Windows 现在像 Unix 一样允许并发读取了。

如果数据库文件在一个 NFS 文件系统中，控制并发读书的锁定机制可以会出错。因为 NFS 的锁定有时会坏掉。如果有多进程可以并发读书数据库则因当避免把数据库文件放在 NFS 文件系统中。根据微软的文档，如果不运行 Share.exe 守护程序则 FAT 文件系统中的锁定可能不工作。对 Windows 非常有经验的人告诉我网络文件的锁定有许多问题并且不可靠。如果是这样在二个或以上 Windows 系统中共享一个 SQLite 数据库文件会导致不可预知的问题。

Locking in SQLite is very course-grained. SQLite locks the entire database. Big database servers (PostgreSQL, Oracle, etc.) generally have finer grained locking, such as locking on a single table or a single row within a table. If you have a massively parallel database application, you should consider using a big database server instead of SQLite.

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. See the API documentation for details.

If two or more processes have the same database open and one process creates a new table or index, the other processes might not be able to see the new table right away. You might have to get the other processes to close and reopen their connection to the database before they will be able to see the new table.

--------------------------------------------------------------------------------

(8) Is SQLite threadsafe?

Yes. Sometimes. In order to be thread-safe, SQLite must be compiled with the THREADSAFE preprocessor macro set to 1. In the default distribution, the windows binaries are compiled to be threadsafe but the linux binaries are not. If you want to change this, you'll have to recompile.

"Threadsafe" in the previous paragraph means that two or more threads can run SQLite at the same time on different "sqlite3" structures returned from separate calls to sqlite3_open(). It is never safe to use the same sqlite3 structure pointer simultaneously in two or more threads.

An sqlite3 structure can only be used in the same thread that called sqlite3_open to create it. You cannot open a database in one thread then pass the handle off to another thread for it to use. This is due to limitations (bugs?) in many common threading implementations such as on RedHat9.

Note that if two or more threads have the same database open and one thread creates a new table or index, the other threads might not be able to see the new table right away. You might have to get the other threads to close and reopen their connection to the database before they will be able to see the new table.

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.

--------------------------------------------------------------------------------

(9) How do I list all tables/indices contained in an SQLite database

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.

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

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;

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.

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.

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

--------------------------------------------------------------------------------

(10) Are there any known size limits to SQLite databases?

As of version 2.7.4, SQLite can handle databases up to 241 bytes (2 terabytes) in size on both Windows and Unix. Older version of SQLite were limited to databases of 231 bytes (2 gigabytes).

SQLite version 2.8 limits the amount of data in one row to 1 megabyte. SQLite version 3.0 has no limit on the amount of data that can be stored in a single row.

The names of tables, indices, view, triggers, and columns can be as long as desired. However, the names of SQL functions (as created by the sqlite3_create_function() API) may not exceed 255 characters in length.

--------------------------------------------------------------------------------

(11) What is the maximum size of a VARCHAR in SQLite?

SQLite does not enforce datatype constraints. A VARCHAR column can hold as much data as you care to put in it.

--------------------------------------------------------------------------------

(12) SQLite 支持 BLOB 类型字段吗？

SQLite 3.0 版支持在任何字段存放 BLOB 数据，不管字段声明为什么类型。

SQLite version 2.8 will store any text data without embedded '0' characters. If you need to store BLOB data in SQLite version 2.8 you'll want to encode that data first. There is a source file named "src/encode.c" in the SQLite version 2.8 distribution that contains implementations of functions named "sqlite_encode_binary() and sqlite_decode_binary() that can be used for converting binary data to ASCII and back again, if you like.

--------------------------------------------------------------------------------

(13) 在 SQLite 中如何在一个表中增减字段？

SQLite 不支持 "ALTER TABLE" SQL 命令。如果你要改变表的结构，只能重建一个表。你可以在一个临时表中备份数据，重建新表后再恢复数据。

例如，假设你有一个名为 "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;

--------------------------------------------------------------------------------

(14) 我删除了许多数据，可是数据库文件并没有减小，这是 bug 吗？

不是。当你从一个 SQLite 数据库中删除数据后，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.

If you delete a lot of data and want to shrink the database file, run the VACUUM command (version 2.8.1 and later). 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.

--------------------------------------------------------------------------------

(15) Can I use SQLite in my commercial product without paying royalties?

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.

--------------------------------------------------------------------------------

(16) How do I use a string literal that contains an embedded single-quote (') character?

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

--------------------------------------------------------------------------------

(17) What is an SQLITE_SCHEMA error, and why am I getting one?

In version 3 of SQLite, 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. In SQLite 3, an SQLITE_SCHEMA error can only occur when using the sqlite3_prepare()/sqlite3_step()/sqlite3_finalize() API to execute SQL, not when using the sqlite3_exec(). This was not the case in version 2.

The most common reason for a prepared statement to become invalid is that the schema of the database was modified after the SQL was prepared (possibly by another process). The other reasons this can happen are:

A database was DETACHed.
A user-function definition was deleted or changed.
A collation sequence definition was deleted or changed.
The authorization function was changed.
In all cases, the solution is to recompile the statement from SQL and attempt to execute it again. Because a prepared statement can be invalidated by another process changing the database schema, all code that uses the sqlite3_prepare()/sqlite3_step()/sqlite3_finalize() API should be prepared to handle SQLITE_SCHEMA errors. An example of one approach to this follows:

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

--------------------------------------------------------------------------------

(18) 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 do not have a finite representation in binary. 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.

This page last modified on 2005/08/31 02:46:21

原文转自：http://www.ltesting.net