SQLite FAQ Chinese Version

1) how to create an auto-increment field?

A simple answer: a field declared as integer primary key will be automatically added.

Here is a detailed answer: Starting from SQLite 2.3.4, if you declare a field in a table as integer primary
Key, so no matter when you insert a null value to the field in the table, the null value will be automatically replaced with the maximum value of all rows of the field in the table.
An integer of 1. If the table is empty, it is replaced with 1. For example, suppose you have a data table like this:

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

In this data table, declare

INSERT INTO t1 VALUES(NULL,123);

Logically, it is equivalent:

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

A new API function sqlite3_last_insert_rowid ()
Returns the integer key of the most recent insert operation.

Note that this integer key is always 1 larger than the last key in the previously inserted Table. The new key is unique to the existing key in the table,
However, it may overlap the key values that were previously deleted from the table. Always obtain the unique key in the entire table.
Before the key declaration, add the keyword autoincrement. In this way, the selected key will always be 1 larger than the existing maximum key in the table. If possible
If the maximum key already exists in the table, the insert operation fails and a sqlite_full error code is returned.

(2) what data types does SQLite support?

See http://www.sqlite.org/datatype3.html
.

(3) Why can I insert a string into the integer field of the SQLite database?

This is a feature, not a bug. You can put any information in any field without worrying about the type of the field declaration.
You can insert a string of any length to an integer field, insert a floating point to a Boolean field, or insert a date to a character field. In create
The data type you specify for this field in the table command does not limit the data inserted into this field.
All fields can be inserted into strings of any length. Except for the integer primary key field. This field can only
Store a 64-bit integer. Otherwise, an error occurs.

However, SQLite will use the declared field type by default. Therefore, for example, if you want to declare an integer field
And SQLite tries to convert it into an integer. If the conversion is successful, the integer is inserted. No
Inserts a string. This feature is sometimes called type or column.
Affinity
.

(4) Why does SQLite think the expression '0' = '00' is true?

The expression is invalid after 2.7.0. See datatypes in
SQLite Version 3

(5) Why does SQLite not allow '0' and '0. 0' to be used as the primary keys of two different rows in the same table?

Your primary key must be of the numeric type. You can change the type to text.

Each row must have a unique primary key. As a numeric field, SQLite considers'0'
And
'0. 0'
The values are the same, because their comparison on numbers is equal (see the previous problem), so the values are not unique.

(6) Why can't I read the SQLite database created in sparcstation in Linux box?

You need to upgrade your SQLite library to 2.6.3 or later.

X86 processors are of the little-Endian type, while those of the world's big-Endian type. The new version of SQLite solves this problem.

Note: Big endian and little
Endian is a different method for CPU to process the number of multi-word segments. For example, the Unicode code of the Chinese character is 6c49. When I write a file, do I write 6C in front or 49 in front? If you write 6C in front, it's big.
Endian. Write 49 in front, that is, little endian.

(7) can multiple examples of multiple applications or the same application simultaneously access the same database file?

Multi-process can open the same database at the same time, or select at the same time. However, only one process can change the database immediately.

SQLite uses read/write locks to control database access. (The Win95/98/me operating system does not support read/write locking, and is below 2.7.0.
In Windows, this means that only one process can read the database at a time in windows. In version 2.7.0
The read/write lock policy is fixed by executing a user interval probability in the interface code .) However, if the database file is in an NFS
In the file system, the locking mechanism for controlling concurrent reading may cause errors. Because NFS fcntl ()
File Locking sometimes causes problems. If multiple processes may read the database concurrently, avoid placing the database file in the NFS file system.
According to Microsoft's documentation, if you do not run the cmd.exe background program, the lock in the FAT file system may not work. For Windows
Very experienced people tell me that the locking of network files has many problems and is not reliable. If so, share one of the two or more Windows systems
SQLite database files can cause unpredictable problems.

We know there are no otherEmbedded
The SQL database engine supports more concurrency than SQLite. SQLite allows multiple processes
Open and read databases at the same time. When any process needs to be written, the entire database will be locked in this process. However, this is generally time-consuming.
Milliseconds. Other processes only need to wait and continue other transactions. Other embedded SQL database engines often only allow single-process access to the database.

However, Client/Server database engines (such as PostgreSQL, MySQL, and Oracle) usually support higher concurrency,
Multiple processes can be written to the same database at the same time. Since there is always a well-controlled server that coordinates database access, this ensures the above
Feature implementation. If your application requires high concurrency, you should consider using the Client/Server database. In fact, experience tells
Most of our applications require less concurrency than their designers think.

When SQLite attempts to operate a file locked by another process, the default action is to return sqlite_busy. You can use
C code changes this line. Use sqlite3_busy_handler ()
Or sqlite3_busy_timeout ()
API functions.

If two or more processes open the same database at the same time, and one of the processes creates a new table or index, other processes may not immediately see the new table. Other processes may need to be shut down and re-linked to the database.

(8) is SQLite thread-safe?

Sometimes yes. To ensure thread security, SQLite must set the threadsafe preprocessing macro to 1 during compilation. In the default released compiled version
Windows is thread-safe, but Linux is not. If thread security is required, the Linux version must be re-compiled.

"Thread security" means that two or three threads can simultaneously call different independent sqlite3_open ()
Returned"Sqlite3
"Structure. Instead of using the sameSqlite3
Structure pointer.

OneSqlite3
The structure can only be called in sqlite3_open
Used in the process where it is created. You cannot open a database in one thread and pass the pointer to another thread for use. This is because of the limitations of most multithreading systems (or
Bugs ?) For example, on redhat9. In these problematic systems
The fcntl () Lock created by the thread cannot be deleted or modified by another thread. Because SQLite relies on the fcntl () lock for concurrency control, serious problems may occur when database connections are transferred between threads.

Maybe there is a way to solve the fcntl () lock problem in Linux, but it is very complicated and it will be extremely difficult to test the correctness. Therefore, SQLite currently does not allow sharing handles between threads.

In UNIX, you cannot use a fork () system call to put an open SQLite database into a sub-process. Otherwise, an error occurs.

(9) How to list all the tables/indexes in an SQLite database?

InSqlite3
In the command line program, you can use the command". Tables
"To display all tables or use
". Schema
"To display all the table structures and indexes. But do not follow the like statement after the command; otherwise, the display of the table will be restricted.

In C/C ++ programs (or use
In the scripts bound to Tcl/Ruby/perl/Python), you can access the script named"Sqlite_master
. Each SQLite
The database has a sqlite_master table with the database structure. The sqlite_master table is as follows:

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

For tables,Type
The field value is'Table'
,Name
Field is the table name. Use the following statement to wait for the list of all tables:

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

For indexes,Type
='Index'
,Name
Is the index name,Tbl_name
Is the name of the table to which the index belongs. For tables and indexes,SQL
A field is the original statement text used to create a table or index. For automatically created indexes (generally using primary key or unique
Created ),SQL
The field is null.

Sqlite_master table is read-only. You cannot use update, insert, or delete for this table. This table is automatically created by create table, create
Index, drop table, and drop index commands are updated.

The temporary table and its indexes are not in the sqlite_master table but in sqlite_temp_master. Sqlite_temp_master and
Sqlite_master tables work the same way, but are only visible to programs that create temporary tables. To obtain a temporary table, run the following command:

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

(10) Does the SQLite database have a known size limit?

The database size is limited to 2 TB (241
Bytes). This is a theoretical limitation. In fact, you should
The size of the SQLite database is limited to less than GB to avoid running performance problems. If you need to store GB or more data in a database,
Consider using the enterprise database designed for this purpose.

The theoretical row limit for a database is
264
-1. Obviously, you will first exceed the file size limit before reaching the limit on the number of lines. Currently, one row can store 230
Bytes
Data. The basic file format supports the row size to about 262
Bytes.

There may also be restrictions on the number of tables, indexes, or the number of fields in the tables and indexes, but no one knows what it is. In fact, SQLite needs to read and
Analyze the initial SQL statements for all tables and index declarations. Therefore, to call sqlite3_open ()
It is recommended to reduce the number of declared tables. Similarly, even if there is no limit on the number of fields in the table, more than 100 fields appear too much.
Only 31 fields starting with the table are optimized. You can add any number of fields to an index, but indexes with more than 30 fields are not used to optimize the query.

The table, index, view, trigger, and field names can be arbitrarily long, but the SQL function name (by sqlite3_create_function ()
API creation) cannot exceed 255 characters.

(11) What is the maximum varchar length in SQLite?

SQLite does not force the length of varchar. You can declare a varchar (10). SQLite allows you to store 500 characters in it.
And they will remain intact-they will never be truncated.

(12) Does SQLite support the Blob type?

SQLite 3.0 supports storing BLOB data in any field, regardless of the type of field declaration.

(13) how to add/delete fields from an existing SQLite data table?

SQLite has limited alter
Table
Yes. It can be used to add fields to the end of a table.
Or change the table name. If you want to make more complex changes to the table structure, you need to recreate the table. You can back up data in a temporary table, undo the old table, and recreate the new table Before restoring the data.

For example, if you have a table named "T1" named "A", "B", and "C", you must delete the field "C"
. Follow these steps:

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) I deleted a lot of data but the database file was not reduced. Is it a bug?

No. When you migrate data from SQLite
After the data is deleted, unused disk space is added to an internal "Idle list" to store the data you insert next time. The disk space is not lost, but the disk space is not returned to the operating system.

If you delete a large amount of data and want to reduce the database file, run vacuum
Command. Vacuum
The command clears the "Idle list" and minimizes the database size. Note that vacuum takes some time (in Linux
In the system, the disk space is about 0.5 s/MB. the disk space must be twice the size of the database file.

For SQLite version 3.1, one method to replace the VACUUM command is the auto-vacuum mode, with auto_vacuum Pragma
This mode is enabled by syntax.

(15) can SQLite be used for commercial purposes without Copyright charges?

Yes. SQLite is public
. No part of the code is declared as ownership. You can use it to do whatever you want.

(16) how to insert a string with single quotes?

Use double single quotes, for example:

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

The database to be inserted is: 5 0' clock.

(17) what are sqlite_schema errors?

In SQLite version 3, when a pre-processing SQL statement is invalid and cannot be executed, a sqlite_schema is returned.
Error. When this error occurs, this statement should use sqlite3_prepare ()
Re-compile API functions. In SQLite
In version 3, only sqlite3_prepare () is used ()
/Sqlite3_step ()
/Sqlite3_finalize ()
API function execution SQL
This error occurs only when sqlite3_exec () is used ()
.
No. This is different from version 2.

Most of the causes of this error are:
After preprocessing, the database has changed (possibly changed by another process ). There may also be the following reasons:

• Detach a database
  Operation
• Vacuum a database
  Operation
• A user function definition has been deleted or changed.
• A sorting definition is deleted or changed.
• An authorization function has changed.

The solution is to re-compile and try again. All sqlite3_prepare ()
/Sqlite3_step ()
/Sqlite3_finalize ()
API
All functions should be re-compiled. See the following example:

    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 be carried up?

SQLite uses binary computation internally, and 9.95 uses 64-bit IEEE floating point number (which is used internally by SQLite)
9.949999999999999289457264239899814128875732421875. So when you enter "9.95", SQLite
It is understood as the above number, and then rounded to 9.9. This problem will always occur when dealing with floating-point binary numbers. The general rule is that a decimal finite floating point number cannot be expressed as a binary finite floating point number. It can only be replaced by the nearest binary number. This approximate number is very close to the original number, but it is slightly different, so you may not get the expected result.