How to Rename a Table in MySQL

This post covers how to rename a table in MySQL.

What is RENAME TABLE in MySQL?

To rename a table in MySQL, you use the following simple SQL statement:

RENAME TABLE old_table_name TO new_table_name;

This command will change the name of old_table_name to new_table_name. Make sure the new table name does not clash with existing names in the database to avoid errors.

Example

Let’s say you want to rename a table called user_data to customer_data. You would execute the following SQL statement:

RENAME TABLE user_data TO customer_data;

Executing this command requires updating all references to user_data in your application code, stored procedures, or scripts to customer_data.

Renaming multiple tables in a single query

MySQL lets you rename several tables in one operation. It’s cleaner and it reduces the risk of mistakes in large databases. Here’s how to do it:

RENAME TABLE old_table_name1 TO new_table_name1,
             old_table_name2 TO new_table_name2;

Renaming multiple tables this way ensures the operation is atomic—all renames succeed together, or none at all, which should keeop your database consistent.

Stuff to keep in mind

• Permissions: Make sure you have the necessary permissions to rename tables. Typically this means needing ALTER and DROP privileges for the old table and CREATE and INSERT privileges for the new table.
• Dependencies: Be aware of any dependencies, like foreign keys, which might be impacted by the renaming. These may need manual adjustments.
• Application Impact: The renaming of tables can disrupt your application if not properly updated. You should review and test your application after making schema changes.