📄 faq.html
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"><html><head><title>SQLite Frequently Asked Questions</title><style type="text/css">body { margin: auto; font-family: "Verdana" "sans-serif"; padding: 8px 1%;}a { color: #45735f }a:visited { color: #734559 }.logo { position:absolute; margin:3px; }.tagline { float:right; text-align:right; font-style:italic; width:240px; margin:12px; margin-top:58px;}.toolbar { font-variant: small-caps; text-align: center; line-height: 1.6em; margin: 0; padding:1px 8px;}.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }.toolbar a:visited { color: white; }.toolbar a:hover { color: #80a796; background: white; }.content { margin: 5%; }.content dt { font-weight:bold; }.content dd { margin-bottom: 25px; margin-left:20%; }.content ul { padding:0px; padding-left: 15px; margin:0px; }/* rounded corners */.se { background: url(images/se.png) 100% 100% no-repeat #80a796}.sw { background: url(images/sw.png) 0% 100% no-repeat }.ne { background: url(images/ne.png) 100% 0% no-repeat }.nw { background: url(images/nw.png) 0% 0% no-repeat }</style><meta http-equiv="content-type" content="text/html; charset=UTF-8"> </head><body><div><!-- container div to satisfy validator --><a href="index.html"><img class="logo" src="images/SQLite.gif" alt="SQLite Logo" border="0"></a><div><!-- IE hack to prevent disappearing logo--></div><div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div><table width=100% style="clear:both"><tr><td> <div class="se"><div class="sw"><div class="ne"><div class="nw"> <div class="toolbar"> <a href="about.html">About</a> <a href="sitemap.html">Sitemap</a> <a href="docs.html">Documentation</a> <a href="download.html">Download</a> <a href="copyright.html">License</a> <a href="news.html">News</a> <a href="http://www.sqlite.org/cvstrac/index">Developers</a> <a href="support.html">Support</a> </div></div></div></div></div></td></tr></table> <h2>Frequently Asked Questions</h2><oL><li><a href="#q1">How do I create an AUTOINCREMENT field.</a></li><li><a href="#q2">What datatypes does SQLite support?</a></li><li><a href="#q3">SQLite lets me insert a string into a database column of type integer!</a></li><li><a href="#q4">Why doesn't SQLite allow me to use '0' and '0.0' as the primary key on two different rows of the same table?</a></li><li><a href="#q5">Can multiple applications or multiple instances of the same application access a single database file at the same time?</a></li><li><a href="#q6">Is SQLite threadsafe?</a></li><li><a href="#q7">How do I list all tables/indices contained in an SQLite database</a></li><li><a href="#q8">Are there any known size limits to SQLite databases?</a></li><li><a href="#q9">What is the maximum size of a VARCHAR in SQLite?</a></li><li><a href="#q10">Does SQLite support a BLOB type?</a></li><li><a href="#q11">How do I add or delete columns from an existing table in SQLite.</a></li><li><a href="#q12">I deleted a lot of data but the database file did not get any smaller. Is this a bug?</a></li><li><a href="#q13">Can I use SQLite in my commercial product without paying royalties?</a></li><li><a href="#q14">How do I use a string literal that contains an embedded single-quote (') character?</a></li><li><a href="#q15">What is an SQLITE_SCHEMA error, and why am I getting one?</a></li><li><a href="#q16">Why does ROUND(9.95,1) return 9.9 instead of 10.0? Shouldn't 9.95 round up?</a></li><li><a href="#q17">I get hundreds of compiler warnings when I compile SQLite. Isn't this a problem? Doesn't it indicate poor code quality?</a></li></ol><a name="q1"></a><p><b>(1) How do I create an AUTOINCREMENT field.</b></p><blockquote><p>Short answer: A column declared INTEGER PRIMARY KEY will autoincrement.</p> <p>Here is the long answer: 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. (If the largest possible integer key, 9223372036854775807, then an unused key value is chosen at random.) 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>There is a function named <a href="c3ref/last_insert_rowid.html">sqlite3_last_insert_rowid()</a> which will return the integer key for the most recent insert operation.</p> <p>Note that the integer key is one greater than the largest key that was in the table just prior to the insert. The new key will be unique over all keys currently in the table, but it might overlap with keys that have been previously deleted from the table. To create keys that are unique over the lifetime of the table, add the AUTOINCREMENT keyword to the INTEGER PRIMARY KEY declaration. Then the key chosen will be one more than than the largest key that has ever existed in that table. If the largest possible key has previously existed in that table, then the INSERT will fail with an SQLITE_FULL error code.</p></blockquote></li><a name="q2"></a><p><b>(2) What datatypes does SQLite support?</b></p><blockquote><p>See <a href="datatype3.html">http://www.sqlite.org/datatype3.html</a>.</p></blockquote></li><a name="q3"></a><p><b>(3) SQLite lets me insert a string into a database column of type integer!</b></p><blockquote><p>This is a feature, not a bug. SQLite does not enforce data type constraints. 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 a 64-bit signed integer. An error will result if you try to put anything other than an integer into an INTEGER PRIMARY KEY column.)</p> <p>But SQLite does use the declared type of a column as a hint that you prefer values in that format. So, for example, if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. If not, it inserts the string. This feature is sometimes call <a href="datatype3.html#affinity">type affinity</a>. </p></blockquote></li><a name="q4"></a><p><b>(4) Why doesn't SQLite allow me to use '0' and '0.0' as the primary key on two different rows of the same table?</b></p><blockquote><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></blockquote></li><a name="q5"></a><p><b>(5) Can multiple applications or multiple instances of the same application access a single database file at the same time?</b></p><blockquote><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 any moment in time, however.</p> <p>SQLite uses reader/writer locks to control access to the database. (Under Win95/98/ME which lacks support for reader/writer locks, a probabilistic simulation is used instead.) But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many 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>We are aware of no other <i>embedded</i> SQL database engine that supports as much concurrancy as SQLite. SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business. Other embedded SQL database engines typically only allow a single process to connect to the database at once.</p> <p>However, client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine. </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 <a href="c3ref/busy_handler.html">sqlite3_busy_handler()</a> or <a href="c3ref/busy_timeout.html">sqlite3_busy_timeout()</a> API functions.</p></blockquote></li><a name="q6"></a><p><b>(6) Is SQLite threadsafe?</b></p><blockquote><p><a href="http://www.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.pdf"> Threads are evil</a>. Avoid them. <p>SQLite is threadsafe. We make this concession since many users choose to ignore the advice given in the previous paragraph. But in order to be thread-safe, SQLite must be compiled with the SQLITE_THREADSAFE preprocessor macro set to 1. Both the windows and linux precompiled binaries in the distribution are compiled this way. If you are unsure if the SQLite library you are linking against is compiled to be threadsafe you can call the <a href="c3ref/threadsafe.html">sqlite3_threadsafe()</a> interface to find out. </p> <p>Prior to <a href="releaselog/3_3_1.html">version 3.3.1</a>, an <b>sqlite3</b> structure could only be used in the same thread that called <a href="c3ref/open.html">sqlite3_open()</a> to create it. You could not open a database in one thread then pass the handle off to another thread for it to use. This was due to limitations (bugs?) in many common threading implementations such as on RedHat9. Specifically, an fcntl() lock created by one thread cannot be removed or modified by a different thread on the troublesome systems. And since SQLite uses fcntl()⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -