📄 faq.tcl
字号:
## Run this script to generated a faq.html output file#set rcsid {$Id: faq.tcl,v 1.23 2003/05/29 17:43:08 drh Exp $}puts {<html><head> <title>SQLite Frequently Asked Questions</title></head><body bgcolor="white"><h1 align="center">Frequently Asked Questions</h1>}puts "<p align=center>(This page was last modified on [lrange $rcsid 3 4] UTC)</p>"set cnt 1proc faq {question answer} { set ::faq($::cnt) [list [string trim $question] [string trim $answer]] incr ::cnt}############## Enter questions and answers here.faq { How do I create an AUTOINCREMENT field.} { <p>Short answer: A column declared INTEGER PRIMARY KEY will autoincrement.</p> <p>Here is the long answer: Beginning with version SQLite 2.3.4, 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. For example, suppose you have a table like this:<blockquote><pre>CREATE TABLE t1( a INTEGER PRIMARY KEY, b INTEGER);</pre></blockquote> <p>With this table, the statement</p><blockquote><pre>INSERT INTO t1 VALUES(NULL,123);</pre></blockquote> <p>is logically equivalent to saying:</p><blockquote><pre>INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123);</pre></blockquote> <p>For SQLite version 2.2.0 through 2.3.3, if you insert a NULL into an INTEGER PRIMARY KEY column, the NULL will be changed to a unique integer, but it will a semi-random integer. Unique keys generated this way will not be sequential. For SQLite version 2.3.4 and beyond, the unique keys will be sequential until the largest key reaches a value of 2147483647. That is the largest 32-bit signed integer and cannot be incremented, so subsequent insert attempts will revert to the semi-random key generation algorithm of SQLite version 2.3.3 and earlier.</p> <p>Beginning with version 2.2.3, there is a new API function named <b>sqlite_last_insert_rowid()</b> which will return the integer key for the most recent insert operation. See the API documentation for details.</p>}faq { What datatypes does SQLite support?} { <p>SQLite is typeless. All data is stored as null-terminated strings. The datatype information that follows the column name in CREATE TABLE statements is ignored (mostly). You can put any type of data you want into any column, without regard to the declared datatype of that column. </p> <p>An exception to this rule is a column of type INTEGER PRIMARY KEY. Such columns must hold an integer. An attempt to put a non-integer value into an INTEGER PRIMARY KEY column will generate an error.</p> <p>There is a page on <a href="datatypes.html">datatypes in SQLite</a> that explains this concept further.</p>}faq { SQLite lets me insert a string into a database column of type integer!} { <p>This is a feature, not a bug. SQLite is typeless. Any data can 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 an integer. An error will result if you try to put anything other than an integer into an INTEGER PRIMARY KEY column.)</p> <p>The datatype does effect how values are compared, however. For columns with a numeric type (such as "integer") any string that looks like a number is treated as a number for comparison and sorting purposes. Consider these two command sequences:</p> <blockquote><pre>CREATE TABLE t1(a INTEGER UNIQUE); CREATE TABLE t2(b TEXT UNIQUE);INSERT INTO t1 VALUES('0'); INSERT INTO t2 VALUES(0);INSERT INTO t1 VALUES('0.0'); INSERT INTO t2 VALUES(0.0);</pre></blockquote> <p>In the sequence on the left, the second insert will fail. In this case, the strings '0' and '0.0' are treated as numbers since they are being inserted into a numeric column and 0==0.0 which violates the uniqueness constraint. But the second insert in the right-hand sequence works. In this case, the constants 0 and 0.0 are treated a strings which means that they are distinct.</p> <p>There is a page on <a href="datatypes.html">datatypes in SQLite</a> that explains this concept further.</p>}faq { Why does SQLite think that the expression '0'=='00' is TRUE?} { <p>As of version 2.7.0, it doesn't.</p> <p>But if one of the two values being compared is stored in a column that has a numeric type, the the other value is treated as a number, not a string and the result succeeds. For example:</p><blockquote><pre>CREATE TABLE t3(a INTEGER, b TEXT);INSERT INTO t3 VALUES(0,0);SELECT count(*) FROM t3 WHERE a=='00';</pre></blockquote> <p>The SELECT in the above series of commands returns 1. The "a" column is numeric so in the WHERE clause the string '00' is converted into a number for comparison against "a". 0==00 so the test is true. Now consider a different SELECT:</p><blockquote><pre>SELECT count(*) FROM t3 WHERE b=='00';</pre></blockquote> <p>In this case the answer is 0. B is a text column so a text comparison is done against '00'. '0'!='00' so the WHERE clause returns FALSE and the count is zero.</p> <p>There is a page on <a href="datatypes.html">datatypes in SQLite</a> that explains this concept further.</p>}faq { Why doesn't SQLite allow me to use '0' and '0.0' as the primary key on two different rows of the same table?} { <p>Your primary key must have a numeric type. Change the datatype of your primary key to TEXT and it should work.</p> <p>Every row must have a unique primary key. For a column with a numeric type, SQLite thinks that <b>'0'</b> and <b>'0.0'</b> are the same value because they compare equal to one another numerically. (See the previous question.) Hence the values are not unique.</p>} faq { My linux box is not able to read an SQLite database that was created on my SparcStation.} { <p>You need to upgrade your SQLite library to version 2.6.3 or later.</p> <p>The x86 processor on your linux box is little-endian (meaning that the least significant byte of integers comes first) but the Sparc is big-endian (the most significant bytes comes first). SQLite databases created on a little-endian architecture cannot be on a big-endian machine by version 2.6.2 or earlier of SQLite. Beginning with version 2.6.3, SQLite should be able to read and write database files regardless of byte order of the machine on which the file was created.</p>}faq { Can multiple applications or multiple instances of the same application access a single database file at the same time?} { <p>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 once.</p> <p>Win95/98/ME lacks support for reader/writer locks in the operating system. Prior to version 2.7.0, this meant that under windows you could only have a single process reading the database at one time. This problem was resolved in version 2.7.0 by implementing a user-space probabilistic reader/writer locking strategy in the windows interface code file. Windows now works like Unix in allowing multiple simultaneous readers.</p> <p>The locking mechanism used to control simultaneous access might not work correctly if the database file is kept on an NFS filesystem. This is because file locking is broken on some 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.</p> <p>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.</p> <p>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 <b>sqlite_busy_handler()</b> or <b>sqlite_busy_timeout()</b> API functions. See the API documentation for details.</p> <p>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⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -