SQLite Chinese guide FAQ page 1/6

1. How to create an auto-increment field?
2. What data types does SQLite support?
3. Why can I insert a string into the integer field of the SQLite database?
4. Why does SQLite think the expression '0' = '00' is true?
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?
6. Why can't I read the SQLite database created in sparcstation in Linux box?
7. Multiple applications Program Or can multiple examples of the same application simultaneously access the same database file?
8. Is SQLite thread-safe?
9. How to list all the tables/indexes in an SQLite database?
10. Does the SQLite database have a known size limit?
11. What is the maximum length of varchar in SQLite?
12. Does SQLite support the Blob type?
13. How to add/delete fields from an existing SQLite data table?
14. I deleted a lot of data but the database file was not reduced. Is it a bug?
15. Can I use SQLite for commercial purposes without Copyright charges?
16. How do I use a string containing single quotes?
17. What does the sqlite_schema error mean?
18. Why does round (9.95, 1) return 9.9 instead of 10.0? Shouldn't 9.95 be carried up?

(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, no matter when you insert a null value to the field in the table, the null value will be automatically replaced with an integer greater than the maximum value of 1 for all rows of the field in the table; 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 valuees (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 relative to the existing key in the table, but it may overlap the key value that was previously deleted from the table. To always obtain the unique key in the entire table, add the keyword autoincrement before the declaration of the integer primary key. In this way, the selected key will always be 1 larger than the existing maximum key in the table. If the maximum key already exists in the table, the insert operation fails and returns a sqlite_full error code.

(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. The data type you specify for this field in the create 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 one 64-bit integer; otherwise, an error occurs.

However, SQLite will use the declared field type by default. Therefore, for example, if you want to insert a string into a field declared as integer, SQLite will try to convert it into an integer. If the conversion is successful, the integer is inserted. Otherwise, the string is inserted. This feature is sometimes called type or column affinity.