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">&#8216;TableName&#8217;</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">&#8216;TableName&#8217;</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>
  
  

    &nbsp;
  

</div>