MySQL rename table method

Foreword:

Sometimes we will encounter the need to rename tables, for example, due to business changes, we need to rename table a to table b. At this time, you can execute the RENAME TABLE statement or the ALTER TABLE statement to rename the table. In this article, let's learn about renaming the table together.

1. Rename table method

You can use RENAME TABLE statement or ALTER TABLE statement to rename the table, the basic syntax is as follows:

# RENAME TABLE 语法：
RENAME TABLE
    tbl_name TO new_tbl_name
    [, tbl_name2 TO new_tbl_name2] ...
    
# ALTER TABLE 语法：
ALTER TABLE old_table RENAME new_table;

# 具体示例：
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| tb1              |
| tb2              |
+------------------+
2 rows in set (0.00 sec)

mysql> rename table tb1 to new_tb1;
Query OK, 0 rows affected (0.03 sec)

mysql> alter table tb2 rename new_tb2;
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| new_tb1          |
| new_tb2          |
+------------------+
2 rows in set (0.00 sec)

Obviously, when the table is renamed, the old table (old_table_name) must exist, and the new table (new_table_name) must not exist. If the new table new_table_name does exist, the statement will fail.

The user performing the renaming of the table must have the ALTER and DROP permissions of the original table, and the CREATE and INSERT permissions of the new table. Unlike ALTER TABLE, RENAME TABLE can rename multiple tables in a single statement:

RENAME TABLE old_table1 TO new_table1,
             old_table2 TO new_table2,
             old_table3 TO new_table3;

If multiple tables are renamed at once, the rename operation is performed from left to right. Therefore, to exchange two table names, you can perform this operation (assuming that the intermediate table name is tmp_table and does not exist):

RENAME TABLE old_table TO tmp_table,
             new_table TO old_table,
             tmp_table TO new_table;

By renaming the table, we can also move a table from one database to another, the syntax is as follows:

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
ALTER TABLE current_db.tbl_name rename other_db.tbl_name;

# 拼接SQL 实现将某个数据库中的表全部转移至另一个数据库中
SELECT
    CONCAT( 'rename table old_db.', TABLE_NAME, ' to new_db.', TABLE_NAME, ';' ) 
FROM
    information_schema.TABLES 
WHERE
    TABLE_SCHEMA = 'old_db';

In fact, MySQL does not provide an operation to rename a database. We can rename a database indirectly by transferring all tables of a certain database to another database by renaming, but the original database still exists.

2. Matters needing attention

It is worth noting that the renaming operation is done atomically and needs to acquire the metadata lock of the table. Therefore, before executing RENAME TABLE, we must ensure that the table has no active transactions and is not locked. Because only the metadata needs to be changed, it is also very fast to rename large tables. In addition, if the table has triggers, the table cannot be renamed to another library.

In fact, there are still some differences between the RENAME TABLE statement and the ALTER TABLE statement. To query official documents, the main points are as follows:

• RENAME TABLE is suitable for views, but cannot be renamed and transferred to other databases, and ALTER TABLE cannot rename views.
• ALTER TABLE can rename temporary tables (TEMPORARY TABLE), but RENAME TABLE cannot.
• RENAME TABLE can rename multiple tables in a single statement, and ALTER TABLE can only rename one.

Although the renaming operation is quick and fast, in actual production scenarios, the table renaming should be carefully considered. Maybe your renaming operation is okay, but there may be problems with subsequent dependent calls between objects. For example, if you rename a table tb1 to new_tb1, if there are views and functions that depend on tb1, and you do not modify these views and functions in time, then calling these views and functions again may report an error that tb1 does not exist, because in these views and functions In the definition of, the name tb1 is still used. In addition, after renaming a table or view, pay attention to user permissions. If you explicitly specify a user's permissions on the table, you need to re-grant permissions on the new table. If there are constraints such as foreign keys in the table, perform the renaming operation with extreme caution and do a good job of checking.

summary:

This article mainly introduces the operation method and precautions of renaming the table. The main points of this article are summarized as follows:

• Both the RENAME TABLE statement and the ALTER TABLE statement can rename the table. There is a slight difference between the two. The RENAME TABLE statement is more recommended.
• The rename operation needs to acquire a metadata lock, and it must be ensured that no active transactions are occupied before execution.
• By renaming a table, you can transfer a table from one database to another, indirectly renaming the database.
• In actual production scenarios, renaming tables should be carefully considered, especially if there are views and functional dependencies.
• After the renaming operation is completed, check user permissions and related dependency issues, and change the table name in the dependency relationship to the new table name in time.
• If there are constraints such as triggers or foreign keys in the table, be careful when renaming it.
• The rename operation is generally completed in seconds. If the execution time is too long, please check the link status.