The World of MySQL & MariaDB

Rename MySQL Tables: RENAME TABLE vs ALTER TABLE Tips

Table Management
目次

1. Introduction

When working with MySQL databases, you may encounter situations where you need to rename a table. For example, a table name might become inappropriate due to a project change, or system refactoring may require better organization. In this article, we’ll thoroughly explain how to rename tables in MySQL, covering everything from basic command usage to important considerations.

2. Basic Methods for Renaming Tables

To rename a table, MySQL offers two main methods. The usage and characteristics of each are introduced below.

Using the RENAME TABLE statement

“RENAME TABLE” is the simplest way to rename a table in MySQL. Using this statement changes the table name instantly. It is written as follows.
RENAME TABLE old_table_name TO new_table_name;

Example:

If you want to change the table name “users_old” to “users”, use the following command.
RENAME TABLE users_old TO users;

Notes:

  • When using the RENAME TABLE statement, the change takes effect immediately, but the table is locked during the operation.
  • You need appropriate privileges on the table to use this command.

Using the ALTER TABLE statement

Another method is to use the “ALTER TABLE” statement. This also allows renaming a table. The syntax is as follows.
ALTER TABLE old_table_name RENAME TO new_table_name;

Example:

If you want to change the table “products_old” to “products”, write it as follows.
ALTER TABLE products_old RENAME TO products;

Notes:

  • Note that the ALTER TABLE statement also locks the table.
  • In some MySQL versions, the RENAME TABLE statement may offer better performance.

3. How to Change Multiple Table Names at Once

If you want to rename multiple tables at once, the RENAME TABLE statement is useful. In MySQL, you can rename several tables in a single command, reducing effort and saving time.
RENAME TABLE table1 TO new_table1, table2 TO new_table2;

Example:

For example, if you want to rename the tables “orders_old” and “customers_old” to “orders” and “customers” respectively, you would run the following.
RENAME TABLE orders_old TO orders, customers_old TO customers;

Notes:

  • Even when renaming multiple tables simultaneously, each table is locked, so it’s important to check for impacts from other processes before proceeding.

4. Moving Tables Between Databases

MySQL allows you to move a table between different databases while renaming it. This operation also uses the RENAME TABLE statement.
RENAME TABLE db1.table_name TO db2.table_name;

Example:

If you want to move the “employees” table from the “old_database” database to “new_database”, write it as follows.
RENAME TABLE old_database.employees TO new_database.employees;

Notes:

  • This operation requires appropriate privileges on both the source and destination databases.
  • Since the table is locked during the move, make sure the cross-database move won’t affect operations before executing.

5. Precautions When Renaming a Table

Renaming a table comes with several considerations. Keep the following points in mind to work safely.

Verify Permissions Settings

Changing a table name may require you to re‑check and adjust user permissions. In particular, when performing this operation on a table that only certain users can access, be sure not to overlook permission verification.

Impact on Triggers and Foreign Key Constraints

Renaming a table can affect triggers and foreign key constraints. For example, triggers on a table are not automatically updated. Therefore, after renaming a table, you may need to modify the triggers as required. The same applies to foreign key constraints; you may need to re‑establish them after the rename to maintain relationships with other tables.

Locking Issues

When renaming a table, the table is locked, which can block the execution of other queries. This is especially important to consider in databases with multiple concurrent users, where the timing of locks matters.

6. Summary

Changing table names is one of the essential skills for managing MySQL. By using RENAME TABLE and ALTER TABLE properly, you can rename tables as needed. Also, when renaming tables, you need to consider various factors such as foreign‑key constraints, trigger settings, permission adjustments, and the impact of locks. Properly manage MySQL tables to achieve efficient and secure database operations.