How to rename a table in MySQL or MariaDB

Renaming a table keeps a schema readable when an old name no longer matches the data it stores, and it also enables clean cutovers when a rebuilt table is ready to replace a live one.

In MySQL and MariaDB, RENAME TABLE changes the stored table name in server metadata without copying the rows. The same statement can rename multiple tables in one atomic operation, which makes it useful for controlled swaps such as replacing orders with orders_new during a deployment.

The rename still waits on metadata locks, so long-running transactions can delay the change and stall other DDL. Moving a table to another database works only inside the same server instance, cross-database moves fail when the table has triggers, and stored SQL text or table-specific grants that still reference the old name must be reviewed manually after the rename.

1. Start the current MySQL or MariaDB command-line client.

   $ mysql --user=root --password
   Enter password:
   mysql>

   Current MariaDB documentation names the client mariadb and also ships mysql as a compatibility name on many systems. The same SQL statements work in both clients.

2. Select the database that currently owns the table.

   mysql> USE table_demo;
   DATABASE changed

3. Confirm the source table name exactly as stored.

   mysql> SHOW FULL TABLES LIKE 'rename_demo';
   +------------------------------------+------------+
   | Tables_in_table_demo (rename_demo) | Table_type |
   +------------------------------------+------------+
   | rename_demo                        | BASE TABLE |
   +------------------------------------+------------+
   1 ROW IN SET (0.00 sec)

   This check catches misspelled names, view-versus-table confusion, and case mismatches before the rename runs.

4. Confirm the destination name is still unused.

   mysql> SHOW FULL TABLES LIKE 'rename_demo_archive';
   Empty SET (0.00 sec)

   Current MariaDB also supports IF EXISTS on RENAME TABLE, but the explicit existence check stays portable across current MySQL and MariaDB releases.

5. Rename the table with RENAME TABLE.

   mysql> RENAME TABLE rename_demo TO rename_demo_archive;
   Query OK, 0 ROWS affected (0.00 sec)

   The statement waits on a metadata lock. A long-running query or transaction that still touches the table can block the rename until that session finishes.

   Atomic cutover example: RENAME TABLE orders TO orders_old, orders_new TO orders; swaps both names in one statement.

   Quote identifiers when a table name contains special characters or a reserved word: RENAME TABLE `order` TO `order_archive`;

   Current MariaDB adds optional WAIT n and NOWAIT clauses to RENAME TABLE. Current MySQL 8.4 documents the portable syntax without those clauses, so the shared syntax above stays safest across both products.

6. Verify that the new name now appears in the database.

   mysql> SHOW TABLES LIKE 'rename_demo_archive';
   +--------------------------------------------+
   | Tables_in_table_demo (rename_demo_archive) |
   +--------------------------------------------+
   | rename_demo_archive                        |
   +--------------------------------------------+
   1 ROW IN SET (0.00 sec)

7. Confirm that the old name no longer resolves.

   mysql> DESCRIBE rename_demo;
   ERROR 1146 (42S02): TABLE 'table_demo.rename_demo' doesn't exist

8. Move the table to another database only when both databases are on the same server and the table has no triggers.

   mysql> RENAME TABLE table_demo.move_demo TO archive_db.move_demo;
   Query OK, 0 ROWS affected (0.00 sec)
    
   mysql> SHOW TABLES FROM archive_db LIKE 'move_demo';
   +----------------------------------+
   | Tables_in_archive_db (move_demo) |
   +----------------------------------+
   | move_demo                        |
   +----------------------------------+
   1 ROW IN SET (0.00 sec)

   Current MySQL and MariaDB documentation both note that a cross-database move works only when the source and target databases are on the same underlying filesystem, and it fails when the table has triggers. A verified example returns ERROR 1435 (HY000): Trigger in wrong schema. Views also cannot be moved to another database with RENAME TABLE.

9. Review dependent SQL text and table-specific grants that still mention the old name.

   RENAME TABLE does not rewrite view definitions, stored routines, triggers, events, or application queries that embed the old table name. Current MySQL documentation also notes that privileges granted specifically for the old table name are not migrated automatically, and current MariaDB documentation likewise says the privileges associated with the table are not renamed.

10. Use an intermediate temporary name for case-only renames when the server or filesystem treats table names case-insensitively.

    lower_case_table_names and filesystem case rules can make Orders to orders renames behave differently across platforms. Rename through a temporary name first when needed, then verify the final name with SHOW TABLES.

    Related: How to set MySQL or MariaDB table name case sensitivity on Linux