SQLite清空表并将自增列归零
SQL标准中有**TRUNCATE TABLE**语句,用来清空表的所有内容。但SQLite不支持这个语句。在SQLite中直接使用“**DELETE FROM TableName**”就可以了。对于大多数DBMS来说,用DELETE不如用TRUNCATE 速度快,因为TRUNCATE 不用访问整个表,不用记录数据的变动。 SQLite虽然不支持TRUNCATE,但它对DELETE做了优化:“When the WHERE is omitted(略去) from a DELETE statement and the table being deleted has no triggers(触发器), SQLite uses an optimization(优化) to erase the entire table content without having to visit each row of the table individually. **This “truncate” optimization makes the delete run much faster**.” 通常在清空表的时候,还需要把自增列归零。在SQLite中定义自增列的方法如下: <div id="crayon-51c50d9e7aa71" class="crayon-syntax crayon-theme-classic crayon-font-monaco crayon-os-pc print-yes"> <div class="crayon-plain-wrap"> </div> <div class="crayon-main"> <table class="crayon-table "> <tr class="crayon-row"> <td class="crayon-nums "> <div class="crayon-nums-content"> <div class="crayon-num"> 1 </div> </div> </td> <td class="crayon-code"> <div class="crayon-pre"> <div id="crayon-51c50d9e7aa71-1" class="crayon-line"> <span class="k">CREATE</span> <span class="k">TABLE</span> <span class="e">TableName </span><span class="sy">(</span> <span class="i">id</span> <span class="k">INTEGER</span> <span class="k">PRIMARY</span> <span class="k">KEY</span> <span class="i">AUTOINCREMENT</span><span class="sy">,</span> <span class="sy">.</span><span class="sy">.</span><span class="sy">.</span> <span class="sy">)</span><span class="sy">;</span> </div> </div> </td> </tr> </table> </div> </div> 当SQLite数据库中包含自增列时,会自动建立一个名为 sqlite_sequence 的表。这个表包含两个列:name和seq。name记录自增列所在的表,seq记录当前序号(下一条记录的编号就是当前序号加1)。如果想把某个自增列的序号归零,只需要修改 sqlite_sequence表就可以了。 <div id="crayon-51c50d9e7aa8a" class="crayon-syntax crayon-theme-classic crayon-font-monaco crayon-os-pc print-yes"> <div class="crayon-plain-wrap"> </div> <div class="crayon-main"> <table class="crayon-table "> <tr class="crayon-row"> <td class="crayon-nums "> <div class="crayon-nums-content"> <div class="crayon-num"> 1 </div> </div> </td> <td class="crayon-code"> <div class="crayon-pre"> <div id="crayon-51c50d9e7aa8a-1" class="crayon-line"> <span class="k">UPDATE</span> <span class="i">sqlite_sequence</span> <span class="k">SET</span> <span class="i">seq</span> <span class="o">=</span> <span class="cn"></span> <span class="k">WHERE</span> <span class="i">name</span> <span class="o">=</span> <span class="s">‘TableName’</span><span class="sy">;</span> </div> </div> </td> </tr> </table> </div> </div> 也可以直接把该记录删掉: <div id="crayon-51c50d9e7aab0" class="crayon-syntax crayon-theme-classic crayon-font-monaco crayon-os-pc print-yes"> <div class="crayon-plain-wrap"> </div> <div class="crayon-main"> <table class="crayon-table "> <tr class="crayon-row"> <td class="crayon-nums "> <div class="crayon-nums-content"> <div class="crayon-num"> 1 </div> </div> </td> <td class="crayon-code"> <div class="crayon-pre"> <div id="crayon-51c50d9e7aab0-1" class="crayon-line"> <span class="k">DELETE</span> <span class="k">FROM</span> <span class="i">sqlite_sequence</span> <span class="k">WHERE</span> <span class="i">name</span> <span class="o">=</span> <span class="s">‘TableName’</span><span class="sy">;</span> </div> </div> </td> </tr> </table> </div> </div> 要想将所有表的自增列都归零,直接清空sqlite_sequence表就可以了: <div id="crayon-51c50d9e7aac0" class="crayon-syntax crayon-theme-classic crayon-font-monaco crayon-os-pc print-yes"> <div class="crayon-toolbar"> <div class="crayon-tools"> <span class="crayon-language">Transact-SQL</span> </div> </div> <div class="crayon-plain-wrap"> </div> <div class="crayon-main"> <table class="crayon-table "> <tr class="crayon-row"> <td class="crayon-nums "> <div class="crayon-nums-content"> <div class="crayon-num"> 1 </div> </div> </td> <td class="crayon-code"> <div class="crayon-pre"> <div id="crayon-51c50d9e7aac0-1" class="crayon-line"> <span class="k">DELETE</span> <span class="k">FROM</span> <span class="i">sqlite_sequence</span><span class="sy">;</span> </div> </div> </td> </tr> </table> </div> </div> </div> 来自:http://zhuyanfeng.com/archives/516 ...