📄 pragma.html
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"><html><head><title>Pragma statements supported by SQLite</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> <p>The <a href="pragma.html#syntax">PRAGMA</a> statement is a special SQL statement used to modify the operation of the SQLite library or to query the library for internal (non-table) data. The <a href="pragma.html#syntax">PRAGMA</a> statement is issued using the sameinterface as other SQLite commands (e.g. <a href="lang_select.html">SELECT</a>, <a href="lang_insert.html">INSERT</a>) but isdifferent in the following important respects:</p><ul><li>Specific pragma statements may be removed and others added in future releases of SQLite. Use with caution!<li>No error messages are generated if an unknown pragma is issued. Unknown pragmas are simply ignored. This means if there is a typo in a pragma statement the library does not inform the user of the fact.<li>Some pragmas take effect during the SQL compilation stage, not the execution stage. This means if using the C-language <a href="c3ref/prepare.html">sqlite3_prepare()</a>, <a href="c3ref/step.html">sqlite3_step()</a>, <a href="c3ref/finalize.html">sqlite3_finalize()</a> API (or similar in a wrapper interface), the pragma may be applied to the library during the <a href="c3ref/prepare.html">sqlite3_prepare()</a> call.<li>The pragma command is unlikely to be compatible with any other SQL engine.</ul><p>The available pragmas fall into four basic categories:</p><ul><li>Pragmas used to <a href="#modify">modify the operation</a> of the SQLite library in some manner, or to query for the current mode of operation.<li>Pragmas used to <a href="#schema">query the schema</a> of the current database.<li>Pragmas used to <a href="#version">query or modify the databases two version values</a>, the schema-version and the user-version.<li>Pragmas used to <a href="#debug">debug the library</a> and verify that database files are not corrupted.</ul><hr /><a name="syntax"></a><h1>PRAGMA command syntax</h1><table cellpadding="10"><tr><td align="right" valign="top"><i><font color="#ff3434">sql-statement</font></i> ::=</td><td><b><font color="#2c2cf0">PRAGMA </font></b><i><font color="#ff3434">name</font></i><b><font color="#2c2cf0"> </font></b>[<b><font color="#2c2cf0">= </font></b><i><font color="#ff3434">value</font></i><b><font color="#2c2cf0"></font></b>]<b><font color="#2c2cf0"> </font></b><big>|</big><b><font color="#2c2cf0"><br>PRAGMA </font></b><i><font color="#ff3434">function</font></i><b><font color="#2c2cf0"><big>(</big></font></b><i><font color="#ff3434">arg</font></i><b><font color="#2c2cf0"><big>)</big></font></b></td></tr></table><p>The pragmas that take an integer <b><i>value</i></b> also accept symbolic names. The strings "<b>on</b>", "<b>true</b>", and "<b>yes</b>" are equivalent to <b>1</b>. The strings "<b>off</b>", "<b>false</b>", and "<b>no</b>" are equivalent to <b>0</b>. These strings are case-insensitive, and do not require quotes. An unrecognized string will be treated as <b>1</b>, and will not generate an error. When the <i>value</i> is returned it is as an integer.</p><hr /><a name="modify"></a><h1>Pragmas to modify library operation</h1><ul><a name="pragma_auto_vacuum"></a><li><p><b>PRAGMA auto_vacuum;<br> PRAGMA auto_vacuum = </b> <i>0 | none | 1 | full | 2 | incremental</i><b>;</b></p> <p>Query or set the auto-vacuum flag in the database.</p> <p>Normally, (that is to say when auto_vacuum is 0 or "none") when a transaction that deletes data from a database is committed, the database file remains the same size. Unused database file pages are added to a "freelist" are reused for subsequent inserts. The database file does not shrink. In this mode the <a href="lang_vacuum.html">VACUUM</a> command can be used to reclaim unused space.</p> <p>When the auto-vacuum flag is 1 (full), the freelist pages are moved to the end of the file and the file is truncated to remove the freelist pages at every commit. Note, however, that auto-vacuum only truncates the freelist pages from the file. Auto-vacuum does not defragment the database nor repack individual database pages the way that the <a href="lang_vacuum.html">VACUUM</a> command does. In fact, because it moves pages around within the file, auto-vacuum can actually make fragmentation worse.</p> <p>Auto-vacuuming is only possible if the database stores some additional information that allows each database page to be traced backwards to its referer. Therefore, auto-vacuuming must be turned on before any tables are created. It is not possible to enable or disable auto-vacuum after a table has been created.</p> <p>When the value of auto-vacuum is 2 (incremental) then the additional information needed to do autovacuuming is stored in the database file but autovacuuming does not occur automatically at each commit as it does with auto_vacuum==full. In incremental mode, the separate <a href="pragma.html#pragma_incremental_vacuum">incremental_vacuum</a> pragma must be invoked to cause the vacuum to occur.</p> <p>The database connection can be changed between full and incremental autovacuum mode at will. However, the connection cannot be changed in and out of the "none" mode after any table has been created in the database. </p></li><a name="pragma_cache_size"></a><li><p><b>PRAGMA cache_size; <br>PRAGMA cache_size = </b><i>Number-of-pages</i><b>;</b></p> <p>Query or change the maximum number of database disk pages that SQLite will hold in memory at once. Each page uses about 1.5K of memory. The default cache size is 2000. If you are doing <a href="lang_update.html">UPDATEs</a> or <a href="lang_delete.html">DELETEs</a> that change many rows of a database and you do not mind if SQLite uses more memory, you can increase the cache size for a possible speed improvement.</p> <p>When you change the cache size using the cache_size pragma, the change only endures for the current session. The cache size reverts to the default value when the database is closed and reopened. Use the <a href="pragma.html#pragma_default_cache_size">default_cache_size</a> pragma to check the cache size permanently.</p></li><a name="pragma_case_sensitive_like"></a><li><p><b>PRAGMA case_sensitive_like; <br>PRAGMA case_sensitive_like = </b><i>0 | 1</i><b>;</b></p> <p>The default behavior of the <a href="lang_expr.html#like">LIKE</a> operator is to ignore case for latin1 characters. Hence, by default <b>'a' LIKE 'A'</b> is true. The case_sensitive_like pragma can be turned on to change this behavior. When case_sensitive_like is enabled, <b>'a' LIKE 'A'</b> is false but <b>'a' LIKE 'a'</b> is still true.</p> </li><a name="pragma_count_changes"></a><li><p><b>PRAGMA count_changes; <br>PRAGMA count_changes = </b><i>0 | 1</i><b>;</b></p> <p>Query or change the count-changes flag. Normally, when the count-changes flag is not set, <a href="lang_insert.html">INSERT</a>, <a href="lang_update.html">UPDATE</a> and <a href="lang_delete.html">DELETE</a> statements return no data. When count-changes is set, each of these commands returns a single row of data consisting of one integer value - the number of rows inserted, modified or deleted by the command. The returned change count does not include any insertions, modifications or deletions performed by triggers.</p><a name="pragma_default_cache_size"></a><li><p><b>PRAGMA default_cache_size; <br>PRAGMA default_cache_size = </b><i>Number-of-pages</i><b>;</b></p> <p>Query or change the maximum number of database disk pages that SQLite will hold in memory at once. Each page uses 1K on disk and about 1.5K in memory. This pragma works like the <a href="pragma.html#pragma_cache_size">cache_size</a> pragma with the additional feature that it changes the cache size persistently. With this pragma, you can set the cache size once and that setting is retained and reused every time you reopen the database.</p></li><a name="pragma_empty_result_callbacks"></a><li><p><b>PRAGMA empty_result_callbacks; <br>PRAGMA empty_result_callbacks = </b><i>0 | 1</i><b>;</b></p> <p>Query or change the empty-result-callbacks flag.</p> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -