How to rename the table in MySQL and what to pay attention to

1. Rename table method

You can rename a table using the RENAME TABLE statement or the ALTER TABLE statement. The basic syntax is as follows:

# RENAME TABLE Syntax:
RENAME TABLE
    tbl_name TO new_tbl_name
    [, tbl_name2 TO new_tbl_name2] ...

    # ALTER TABLE syntax:
ALTER TABLE old_table RENAME new_table;

# Specific example:
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 performing a table rename, 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 who performs the table rename operation must have ALTER and DROP privileges on the original table, and CREATE and INSERT privileges on 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 you rename multiple tables at once, the renaming operation is performed from left to right. So, to swap two table names, you can do this (assuming 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;

We can also move a table from one database to another by renaming the table. 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;

# Splice SQL to transfer all tables in one database to another database 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 indirectly rename a database by transferring all tables of a database to another database through renaming, but the original database still exists.

2. Notes

It is worth noting that the rename operation is done atomically and requires acquiring the metadata lock of the table. Therefore, before executing RENAME TABLE, we must ensure that there is no active transaction on the table and that it is not locked. Renaming large tables is also fast because only the metadata needs to be changed. In addition, if the table has triggers, you cannot transfer the table to another library by renaming it.

In fact, there are some differences between the RENAME TABLE statement and the ALTER TABLE statement. According to the official documentation, there are several main differences:

• RENAME TABLE applies to views, but you cannot transfer a view rename to another database, and ALTER TABLE cannot rename a view.
• ALTER TABLE can rename a temporary table (TEMPORARY TABLE), but RENAME TABLE cannot.
• RENAME TABLE can rename multiple tables in a single statement, ALTER TABLE can rename only one.

Although the renaming operation is quick and efficient, you should still carefully consider renaming the table in actual production scenarios. Maybe your renaming operation is fine, but there may be problems with the 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 when you call these views and functions again, you may get an error message that tb1 does not exist, because the name tb1 is still used in the definition of these views and functions. In addition, after renaming a table or view, pay attention to user permissions. If a user is explicitly assigned permissions for the table, you need to re-grant permissions to the new table. If there are constraints such as foreign keys in the table, be extra careful and check carefully when performing the rename operation.

Summarize:

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

Both the RENAME TABLE statement and the ALTER TABLE statement can be used to rename a table. There is a slight difference between the two, and the RENAME TABLE statement is more recommended.

The rename operation requires obtaining a metadata lock. Before executing the operation, ensure that there are no active transactions.

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 when there are view and function 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 a timely manner.

If there are constraints such as triggers or foreign keys in the table, be extra careful when renaming.

The rename operation is usually completed in seconds. If it takes too long, please check the connection status.

The above is the detailed content of the summary of MySQL rename table related knowledge. For more information about MySQL rename table, please pay attention to other related articles on 123WORDPRESS.COM!