<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="rename-table">
      </a>
      15.1.36 RENAME TABLE Statement
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045183202928">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa89532646"><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">RENAME</span> <span class="token keyword">TABLE</span>
    <em class="replaceable">tbl_name</em> <span class="token keyword">TO</span> <em class="replaceable">new_tbl_name</em>
    <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">tbl_name2</em> <span class="token keyword">TO</span> <em class="replaceable">new_tbl_name2</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
  </div>
  <p>
   <a class="link" href="rename-table.html" title="15.1.36 RENAME TABLE Statement">
    <code class="literal">
     RENAME TABLE
    </code>
   </a>
   renames one or more
      tables. You must have
   <a class="link" href="privileges-provided.html#priv_alter">
    <code class="literal">
     ALTER
    </code>
   </a>
   and
   <a class="link" href="privileges-provided.html#priv_drop">
    <code class="literal">
     DROP
    </code>
   </a>
   privileges for the original
      table, and
   <a class="link" href="privileges-provided.html#priv_create">
    <code class="literal">
     CREATE
    </code>
   </a>
   and
   <a class="link" href="privileges-provided.html#priv_insert">
    <code class="literal">
     INSERT
    </code>
   </a>
   privileges for the new
      table.
  </p>
  <p>
   For example, to rename a table named
   <code class="literal">
    old_table
   </code>
   to
   <code class="literal">
    new_table
   </code>
   , use this statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa52049950"><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">RENAME</span> <span class="token keyword">TABLE</span> old_table <span class="token keyword">TO</span> new_table<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   That statement is equivalent to the following
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa26542964"><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">ALTER</span> <span class="token keyword">TABLE</span> old_table <span class="token keyword">RENAME</span> new_table<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   <code class="literal">
    RENAME TABLE
   </code>
   , unlike
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER
      TABLE
    </code>
   </a>
   , can rename multiple tables within a single
      statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa42857512"><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">RENAME</span> <span class="token keyword">TABLE</span> old_table1 <span class="token keyword">TO</span> new_table1<span class="token punctuation">,</span>
             old_table2 <span class="token keyword">TO</span> new_table2<span class="token punctuation">,</span>
             old_table3 <span class="token keyword">TO</span> new_table3<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Renaming operations are performed left to right. Thus, to swap two
      table names, do this (assuming that a table with the intermediary
      name
   <code class="literal">
    tmp_table
   </code>
   does not already exist):
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa1303115"><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">RENAME</span> <span class="token keyword">TABLE</span> old_table <span class="token keyword">TO</span> tmp_table<span class="token punctuation">,</span>
             new_table <span class="token keyword">TO</span> old_table<span class="token punctuation">,</span>
             tmp_table <span class="token keyword">TO</span> new_table<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Metadata locks on tables are acquired in name order, which in some
      cases can make a difference in operation outcome when multiple
      transactions execute concurrently. See
   <a class="xref" href="metadata-locking.html" title="10.11.4 Metadata Locking">
    Section 10.11.4, “Metadata Locking”
   </a>
   .
  </p>
  <p>
   You can rename tables locked with a
   <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
    <code class="literal">
     LOCK
      TABLES
    </code>
   </a>
   statement, provided that they are locked with a
   <code class="literal">
    WRITE
   </code>
   lock or are the product of renaming
   <code class="literal">
    WRITE
   </code>
   -locked tables from earlier steps in a
      multiple-table rename operation. For example, this is permitted:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa87318334"><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">LOCK</span> <span class="token keyword">TABLE</span> old_table1 <span class="token keyword">WRITE</span><span class="token punctuation">;</span>
<span class="token keyword">RENAME</span> <span class="token keyword">TABLE</span> old_table1 <span class="token keyword">TO</span> new_table1<span class="token punctuation">,</span>
             new_table1 <span class="token keyword">TO</span> new_table2<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   This is not permitted:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa78025772"><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">LOCK</span> <span class="token keyword">TABLE</span> old_table1 <span class="token keyword">READ</span><span class="token punctuation">;</span>
<span class="token keyword">RENAME</span> <span class="token keyword">TABLE</span> old_table1 <span class="token keyword">TO</span> new_table1<span class="token punctuation">,</span>
             new_table1 <span class="token keyword">TO</span> new_table2<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   With the transaction table locking conditions satisfied, the
      rename operation is done atomically; no other session can access
      any of the tables while the rename is in progress.
  </p>
  <p>
   If any errors occur during a
   <code class="literal">
    RENAME TABLE
   </code>
   , the
      statement fails and no changes are made.
  </p>
  <p>
   You can use
   <code class="literal">
    RENAME TABLE
   </code>
   to move a table from
      one database to another:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa87316880"><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">RENAME</span> <span class="token keyword">TABLE</span> <em class="replaceable">current_db<span class="token punctuation">.</span>tbl_name</em> <span class="token keyword">TO</span> <em class="replaceable">other_db<span class="token punctuation">.</span>tbl_name<span class="token punctuation">;</span></em><span class="token punctuation"></span></code></pre>
  </div>
  <a class="indexterm" name="idm46045183166896">
  </a>
  <a class="indexterm" name="idm46045183165408">
  </a>
  <p>
   Using this method to move all tables from one database to a
      different one in effect renames the database (an operation for
      which MySQL has no single statement), except that the original
      database continues to exist, albeit with no tables.
  </p>
  <p>
   Like
   <code class="literal">
    RENAME TABLE
   </code>
   ,
   <code class="literal">
    ALTER TABLE ...
      RENAME
   </code>
   can also be used to move a table to a different
      database. Regardless of the statement used, if the rename
      operation would move the table to a database located on a
      different file system, the success of the outcome is platform
      specific and depends on the underlying operating system calls used
      to move table files.
  </p>
  <p>
   If a table has triggers, attempts to rename the table into a
      different database fail with a
   <span class="errortext">
    Trigger in wrong
      schema
   </span>
   (
   <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_trg_in_wrong_schema" target="_top">
    <code class="literal">
     ER_TRG_IN_WRONG_SCHEMA
    </code>
   </a>
   ) error.
  </p>
  <p>
   An unencrypted table can be moved to an encryption-enabled
      database and vice versa. However, if the
   <a class="link" href="server-system-variables.html#sysvar_table_encryption_privilege_check">
    <code class="literal">
     table_encryption_privilege_check
    </code>
   </a>
   variable is enabled, the
   <a class="link" href="privileges-provided.html#priv_table-encryption-admin">
    <code class="literal">
     TABLE_ENCRYPTION_ADMIN
    </code>
   </a>
   privilege is
      required if the table encryption setting differs from the default
      database encryption.
  </p>
  <a class="indexterm" name="idm46045183156096">
  </a>
  <p>
   To rename
   <code class="literal">
    TEMPORARY
   </code>
   tables,
   <code class="literal">
    RENAME
      TABLE
   </code>
   does not work. Use
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER
      TABLE
    </code>
   </a>
   instead.
  </p>
  <p>
   <code class="literal">
    RENAME TABLE
   </code>
   works for views, except that views
      cannot be renamed into a different database.
  </p>
  <p>
   Any privileges granted specifically for a renamed table or view
      are not migrated to the new name. They must be changed manually.
  </p>
  <p>
   <code class="literal">
    RENAME TABLE
    <em class="replaceable">
     <code>
      tbl_name
     </code>
    </em>
    TO
    <em class="replaceable">
     <code>
      new_tbl_name
     </code>
    </em>
   </code>
   changes
      internally generated foreign key constraint names and user-defined
      foreign key constraint names that begin with the string
   <span class="quote">
    “
    <span class="quote">
     <em class="replaceable">
      <code>
       tbl_name
      </code>
     </em>
     _ibfk_
    </span>
    ”
   </span>
   to
      reflect the new table name.
   <code class="literal">
    InnoDB
   </code>
   interprets
      foreign key constraint names that begin with the string
   <span class="quote">
    “
    <span class="quote">
     <em class="replaceable">
      <code>
       tbl_name
      </code>
     </em>
     _ibfk_
    </span>
    ”
   </span>
   as
      internally generated names.
  </p>
  <p>
   Foreign key constraint names that point to the renamed table are
      automatically updated unless there is a conflict, in which case
      the statement fails with an error. A conflict occurs if the
      renamed constraint name already exists. In such cases, you must
      drop and re-create the foreign keys for them to function properly.
  </p>
  <a class="indexterm" name="idm46045183144816">
  </a>
  <p>
   <code class="literal">
    RENAME TABLE
    <em class="replaceable">
     <code>
      tbl_name
     </code>
    </em>
    TO
    <em class="replaceable">
     <code>
      new_tbl_name
     </code>
    </em>
   </code>
   changes
      internally generated and user-defined
   <code class="literal">
    CHECK
   </code>
   constraint names that begin with the string
   <span class="quote">
    “
    <span class="quote">
     <em class="replaceable">
      <code>
       tbl_name
      </code>
     </em>
     _chk_
    </span>
    ”
   </span>
   to reflect
      the new table name. MySQL interprets
   <code class="literal">
    CHECK
   </code>
   constraint names that begin with the string
   <span class="quote">
    “
    <span class="quote">
     <em class="replaceable">
      <code>
       tbl_name
      </code>
     </em>
     _chk_
    </span>
    ”
   </span>
   as
      internally generated names. Example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa51145263"><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 prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
       Table<span class="token punctuation">:</span> t1
Create Table<span class="token punctuation">:</span> CREATE TABLE `t1` (
  `i1` int(11) DEFAULT NULL,
  `i2` int(11) DEFAULT NULL,
  CONSTRAINT `t1_chk_1` CHECK ((`i1` &gt; 0)),
  CONSTRAINT `t1_chk_2` CHECK ((`i2` &lt; 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
</span><span class="token output">1 row in set (0.02 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">RENAME</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">TO</span> t3<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.03 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t3\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
       Table<span class="token punctuation">:</span> t3
Create Table<span class="token punctuation">:</span> CREATE TABLE `t3` (
  `i1` int(11) DEFAULT NULL,
  `i2` int(11) DEFAULT NULL,
  CONSTRAINT `t3_chk_1` CHECK ((`i1` &gt; 0)),
  CONSTRAINT `t3_chk_2` CHECK ((`i2` &lt; 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
</span><span class="token output">1 row in set (0.01 sec)</span></code></pre>
  </div>
 </div>
 <br/>
</div>
