MySQL RENAME TABLE Statement

The RENAME TABLE statement in MySQL is used to change the name of one or more tables. This operation allows database administrators to reorganize or re-label tables without altering their content.

Usage

The RENAME TABLE statement is employed when you need to change the name of an existing table to a new name. It can also be used to rename multiple tables simultaneously.

RENAME TABLE old_table_name TO new_table_name;

In this syntax, old_table_name is the current name of the table, and new_table_name is the desired new name.

Examples

1. Basic Table Renaming

RENAME TABLE customers TO clients;

This simple example renames the customers table to clients.

2. Renaming Multiple Tables

RENAME TABLE orders TO customer_orders, products TO inventory_items;

In this example, two tables are renamed in a single statement: orders becomes customer_orders and products becomes inventory_items.

3. Renaming with Reserved Keywords

RENAME TABLE `order` TO `purchase_order`;

Here, a table named order, which is a reserved keyword, is renamed to purchase_order using backticks to handle the special case.

4. Renaming Tables Across Databases

RENAME TABLE db1.old_table_name TO db2.new_table_name;

This example demonstrates how to rename a table from one database to another, effectively moving it while renaming.

Tips and Best Practices

• Atomic Operation. RENAME TABLE is an atomic operation, ensuring that either all rename actions are completed successfully or none are applied, maintaining database consistency.
• Check for Dependencies. Ensure that no application logic or database constraints rely on the old table name before renaming.
• Use Backticks for Reserved Words. If a table name is a reserved keyword, wrap it in backticks to avoid syntax errors.
• Update References. After renaming, update any stored procedures, functions, or application code that refers to the old table name. Note that foreign key references need to be manually updated as they are not automatically adjusted.
• Backup Before Renaming. Always have a backup of your database to prevent data loss in case of errors during renaming.
• Privilege Requirements. Ensure you have the necessary privileges to rename the table, as this operation requires ownership or specific permissions.
• Consider Ongoing Transactions. Be aware that renaming a table can affect ongoing transactions or queries. Plan such operations during maintenance windows if possible.
• Replication and Triggers. Be cautious of potential impacts on replication or triggers, as these may require additional adjustments after renaming tables.