<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="truncate-table">
      </a>
      15.1.37 TRUNCATE TABLE Statement
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045183132608">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa85013474"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">TRUNCATE</span> <span class="token punctuation">[</span><span class="token keyword">TABLE</span><span class="token punctuation">]</span> <em class="replaceable">tbl_name</em></code></pre>
  </div>
  <p>
   <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
    <code class="literal">
     TRUNCATE TABLE
    </code>
   </a>
   empties a table
      completely. It requires the
   <a class="link" href="privileges-provided.html#priv_drop">
    <code class="literal">
     DROP
    </code>
   </a>
   privilege. Logically,
   <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
    <code class="literal">
     TRUNCATE
      TABLE
    </code>
   </a>
   is similar to a
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   statement that deletes all
      rows, or a sequence of
   <a class="link" href="drop-table.html" title="15.1.32 DROP TABLE Statement">
    <code class="literal">
     DROP TABLE
    </code>
   </a>
   and
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statements.
  </p>
  <p>
   To achieve high performance,
   <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
    <code class="literal">
     TRUNCATE
      TABLE
    </code>
   </a>
   bypasses the DML method of deleting data. Thus, it
      does not cause
   <code class="literal">
    ON DELETE
   </code>
   triggers to fire, it
      cannot be performed for
   <code class="literal">
    InnoDB
   </code>
   tables with
      parent-child foreign key relationships, and it cannot be rolled
      back like a DML operation. However,
   <code class="literal">
    TRUNCATE
      TABLE
   </code>
   operations on tables that use a storage engine
      which supports atomic DDL are either fully committed or rolled
      back if the server halts during their operation. For more
      information, see
   <a class="xref" href="atomic-ddl.html" title="15.1.1 Atomic Data Definition Statement Support">
    Section 15.1.1, “Atomic Data Definition Statement Support”
   </a>
   .
  </p>
  <p>
   Although
   <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
    <code class="literal">
     TRUNCATE TABLE
    </code>
   </a>
   is similar
      to
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   , it is classified as a
      DDL statement rather than a DML statement. It differs from
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   in the following ways:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Truncate operations drop and re-create the table, which is
          much faster than deleting rows one by one, particularly for
          large tables.
     </p>
    </li>
    <li class="listitem">
     <p>
      Truncate operations cause an implicit commit, and so cannot be
          rolled back. See
      <a class="xref" href="implicit-commit.html" title="15.3.3 Statements That Cause an Implicit Commit">
       Section 15.3.3, “Statements That Cause an Implicit Commit”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Truncation operations cannot be performed if the session holds
          an active table lock.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
       <code class="literal">
        TRUNCATE TABLE
       </code>
      </a>
      fails for an
      <code class="literal">
       InnoDB
      </code>
      table or
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      table if there are any
      <code class="literal">
       FOREIGN KEY
      </code>
      constraints from other tables
          that reference the table. Foreign key constraints between
          columns of the same table are permitted.
     </p>
    </li>
    <li class="listitem">
     <p>
      Truncation operations do not return a meaningful value for the
          number of deleted rows. The usual result is
      <span class="quote">
       “
       <span class="quote">
        0 rows
          affected,
       </span>
       ”
      </span>
      which should be interpreted as
      <span class="quote">
       “
       <span class="quote">
        no
          information.
       </span>
       ”
      </span>
     </p>
    </li>
    <li class="listitem">
     <p>
      As long as the table definition is valid, the table can be
          re-created as an empty table with
      <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
       <code class="literal">
        TRUNCATE TABLE
       </code>
      </a>
      , even if the
          data or index files have become corrupted.
     </p>
    </li>
    <li class="listitem">
     <p>
      Any
      <code class="literal">
       AUTO_INCREMENT
      </code>
      value is reset to its
          start value. This is true even for
      <code class="literal">
       MyISAM
      </code>
      and
      <code class="literal">
       InnoDB
      </code>
      , which normally do not reuse
          sequence values.
     </p>
    </li>
    <li class="listitem">
     <p>
      When used with partitioned tables,
      <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
       <code class="literal">
        TRUNCATE TABLE
       </code>
      </a>
      preserves the
          partitioning; that is, the data and index files are dropped
          and re-created, while the partition definitions are
          unaffected.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
       <code class="literal">
        TRUNCATE TABLE
       </code>
      </a>
      statement
          does not invoke
      <code class="literal">
       ON DELETE
      </code>
      triggers.
     </p>
    </li>
    <li class="listitem">
     <p>
      Truncating a corrupted
      <code class="literal">
       InnoDB
      </code>
      table is
          supported.
     </p>
    </li>
   </ul>
  </div>
  <p>
   <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
    <code class="literal">
     TRUNCATE TABLE
    </code>
   </a>
   is treated for
      purposes of binary logging and replication as DDL rather than DML,
      and is always logged as a statement.
  </p>
  <p>
   <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
    <code class="literal">
     TRUNCATE TABLE
    </code>
   </a>
   for a table closes
      all handlers for the table that were opened with
   <a class="link" href="handler.html" title="15.2.5 HANDLER Statement">
    <code class="literal">
     HANDLER OPEN
    </code>
   </a>
   .
  </p>
  <p>
   <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
    <code class="literal">
     TRUNCATE TABLE
    </code>
   </a>
   can be used with
      Performance Schema summary tables, but the effect is to reset the
      summary columns to 0 or
   <code class="literal">
    NULL
   </code>
   , not to remove
      rows. See
   <a class="xref" href="performance-schema-summary-tables.html" title="29.12.20 Performance Schema Summary Tables">
    Section 29.12.20, “Performance Schema Summary Tables”
   </a>
   .
  </p>
  <p>
   Truncating an
   <code class="literal">
    InnoDB
   </code>
   table that resides in a
      file-per-table tablespace drops the existing tablespace and
      creates a new one. If the tablespace was created with an earlier
      version and resides in an unknown directory,
   <code class="literal">
    InnoDB
   </code>
   creates the new tablespace in the
      default location and writes the following warning to the error
      log:
   <span class="errortext">
    The DATA DIRECTORY location must be in a known
      directory. The DATA DIRECTORY location will be ignored and the
      file will be put into the default datadir location
   </span>
   .
      Known directories are those defined by the
   <a class="link" href="server-system-variables.html#sysvar_datadir">
    <code class="literal">
     datadir
    </code>
   </a>
   ,
   <a class="link" href="innodb-parameters.html#sysvar_innodb_data_home_dir">
    <code class="literal">
     innodb_data_home_dir
    </code>
   </a>
   , and
   <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
    <code class="literal">
     innodb_directories
    </code>
   </a>
   variables. To
      have
   <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
    <code class="literal">
     TRUNCATE TABLE
    </code>
   </a>
   create the
      tablespace in its current location, add the directory to the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
    <code class="literal">
     innodb_directories
    </code>
   </a>
   setting before
      running
   <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
    <code class="literal">
     TRUNCATE TABLE
    </code>
   </a>
   .
  </p>
 </div>
 <br/>
</div>
