How to Rename Tables in MySQL

How do I rename a table in MySQL without losing data?

RENAME TABLE instantly changes a table’s name or schema without copying data.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does RENAME TABLE do in MySQL?

RENAME TABLE changes a table’s name in the data dictionary instantly; no rows are moved or copied. The command can also relocate a table to a different database by adding a target schema prefix.

What is the basic syntax?

Use RENAME TABLE old_name TO new_name;.Supply multiple old TO new pairs, comma-separated, to execute several renames atomically in one statement.

How to rename a single table?

Rename Orders to Orders_2024:

RENAME TABLE Orders TO Orders_2024;

How to move a table between databases?

Prefix each name with its schema:

RENAME TABLE shop.Orders TO archive.Orders_2023;

Can I rename multiple tables in one statement?

Yes. Example:

RENAME TABLE Customers TO Customers_old, Products TO Products_backup;

Is RENAME TABLE atomic?

All rename pairs run in a single transaction.If any pair fails, none are applied, ensuring consistency.

Best practices for renaming production tables

Lock out dependent code, update foreign keys, views, and stored procedures, and schedule during low traffic. Backup first and verify InnoDB for transactional safety.

Common mistakes and fixes

See details below to avoid broken dependencies and lock timeouts.

Why How to Rename Tables in MySQL is important

How to Rename Tables in MySQL Example Usage


-- Archive last quarter’s orders
RENAME TABLE Orders TO Orders_Q2_2024;
-- Application code must now reference Orders_Q2_2024; data and indexes remain intact.

How to Rename Tables in MySQL Syntax


RENAME TABLE [IF EXISTS] old_table_name TO new_table_name [, old_table_name2 TO new_table_name2 ...];

-- Ecommerce examples
RENAME TABLE Orders TO Orders_2024;
RENAME TABLE shop.Products TO archive.Products_backup;
RENAME TABLE Customers TO Customers_old, Orders TO Orders_hist;

Common Mistakes

Ignoring dependent objects: Views, triggers, and stored procedures still point to the old name, causing runtime errors. Fix by searching for the old table name and updating dependencies immediately after the rename.
Renaming a locked table: Long-running transactions or sessions with open cursors can hold locks and block RENAME TABLE, leading to deadlocks. Resolve by checking <code>information_schema.innodb_locks</code> and killing or waiting for conflicting sessions before issuing the command.