Preface:

Sometimes we encounter the need to rename a table. For example, table A needs to be renamed to table B due to business changes. At this point, the RENAME TABLE statement or ALTER TABLE statement can be executed to RENAME the TABLE. In this article we will learn about renaming tables.

1. Rename table methods

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

# RENAME TABLEGrammar: RENAMETABLE
    tbl_name TO new_tbl_name
    [, tbl_name2 TO new_tbl_name2] ...
    
# ALTER TABLEGrammar:ALTER TABLEold_table RENAME new_table; # 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)
Copy the code

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

The user performing the renaming of the table must have ALTER and DROP permissions on the original table and CREATE and INSERT permissions 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;
Copy the code

If multiple tables are renamed at once, the renaming operation is performed from left to right. Therefore, 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;
Copy the code

By renaming tables, we can also move a table from one database to another with the following syntax:

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
ALTER TABLEcurrent_db.tbl_name rename other_db.tbl_name; # stitchingSQLThe implementation moves all tables in one database to another databaseSELECT
	CONCAT( 'rename table old_db.', TABLE_NAME, ' to new_db.', TABLE_NAME, '; ' ) 
FROM
	information_schema.TABLES 
WHERE
	TABLE_SCHEMA = 'old_db';
Copy the code

In fact, MySQL does not provide the operation of renaming a database, we can indirectly rename a library by renaming all the tables in one library into another library, but the original library still exists.

2. Precautions

It is worth noting that the RENAME operation is atomic and requires the metadata lock for the TABLE, so we need to ensure that the TABLE has no active transactions and is not locked before we RENAME the TABLE. Renaming large tables is also fast because only metadata needs to be changed. In addition, if the table has triggers, you cannot rename it to another library.

ALTER TABLE; ALTER TABLE; RENAME TABLE; ALTER TABLE;

  • RENAME TABLE applies to a view, but cannot RENAME a view to another database. ALTER TABLE cannot RENAME a view.
  • ALTER TABLE can RENAME a TEMPORARY TABLE, but RENAME TABLE cannot.
  • RENAME TABLE can RENAME multiple tables in a single statement. ALTER TABLE can RENAME only one TABLE.

Although renaming is quick and fast, in a real production scenario, you should be careful about renaming tables. Your renaming operation may be fine, but subsequent dependency calls between objects may be problematic. For example, if you rename a table tb1 to new_tb1, if there are views or functions that depend on tb1 and you do not modify those views or functions in time, then you may get an error that Tb1 does not exist because the views and functions are still defined with the name Tb1. In addition, after renaming a table or view, pay attention to user permissions. If the permission of a user on the table is explicitly specified, assign the permission to the new table again. If constraints such as foreign keys exist in the table, be careful when renaming the table.

Conclusion:

This article mainly introduces the operation method and precautions of renaming tables, summarizes the key points of this article as follows:

  • Both the RENAME TABLE statement and the ALTER TABLE statement can RENAME a TABLE. The RENAME TABLE statement is recommended because the two statements are slightly different.
  • The rename operation requires the acquisition of the metadata lock and ensures that there are no active transactions before performing it.
  • By renaming a table, you can move a table from one database to another, indirectly renaming a database.
  • In real production scenarios, renaming tables should be considered carefully, especially if there are view and function dependencies.
  • After the rename operation is complete, check user permissions and dependencies, and change the table name in the dependency relationship to the new one in a timely manner.
  • If the table has constraints such as triggers or foreign keys, be careful when renaming the table.
  • The renaming operation is usually completed in seconds. If the renaming operation takes too long, check the link status.