Change Table Name In MySQL
A unique name is assigned to each table in the schema. In MySQL, there are two methods to determine the names of the tables in the schema. The user can either use the show keyword to display the table's names or the query INFORMATION_SCHEMA.
Naming A Table In MySQL
Changing the Name of an Existing Table
In several situations, DBA or the user may require to modify the table's name in MySQL database. This action is performed to assign a more relatable name to the particular table, mostly after adding a new column of information to the table.
There are two methods to change the name of an existing table in MySQL. Both these methods require the user to implement the RENAME query. In the first method, the RENAME TABLE command is implemented with ALTER table statement in MySQL.
Any database user with suitable permission can easily change the table's name using MySQL's RENAME TABLE command and ALTER TABLE statement.
USING RENAME TABLE STATEMENT
The syntax for using RENAME TABLE is as follows:
The advantage of using RENAME TABLE is that the user can simultaneously change the name of multiple tables.
The user requires certain permission over the database to remain in the table. The user must have ALTER and DROP privileges for the existing table and CREATE and INSERT privileges for the new table.
For example, if the user wants to maintain an existing table with the name old_t to new_t, then the user can execute this statement:
The above statement can be replaced with an ALTER TABLE statement to change the name of the table:
RENAME TABLE statement is preferred over the ALTER TABLE because unlike RENAME TABLE, ALTER TABLE can only change the name of one table at a time. RENAME TABLE can change name of multiple table simultaneously.
RENAME TABLE ensures more flexibility as the user can change the names of several tables by executing a single statement. It is more readable as the user can track the table whose name is changed. It is especially popular when modifying the name of a table with a new pre-populated version:
The above statement is executed from left to right. This means that the renaming starts from the left side then the next table name is changed. It ensures there is no conflict when changing the name of the existing table whose name was already changed in the same RENAME statement. The user does not need to concern themselves with this operation as it is done atomically.
It also enables the user to swap two tables' names. It could be done by simply creating a temporary table. Let us consider a scenario where the intermediary table with the name temp_table does not already exist:
Then the user can simply swap the names of the two tables by executing the following statement in MySQL:
Metadata locks on the table are acquired in the sequence of the name; this could be an issue as, in some scenarios, it may result in differences in operation outcomes when several transactions are implemented simultaneously.
In the latter versions of MySQL 8.0.13, the user can change the name of the locked table by using the LOCK TABLES statement. The only requisite was that the tables should have been locked with a WRITE lock or the product of modifying WRITE-LOCKED tables from previous steps in a multiple table RENAME operation. For instance, the user can implement this:
The user cannot perform RENAME command in the below statements:
In the previous versions of MySQL 8.0.13, the user could implement the RENAME TABLE; there should not be any lock already acquired by the LOCK TABLES.
With the locking conditions of the transaction table being met, the RENAME operation is performed atomically; this ensures that no other session can access any of the tables for which rename command is being implemented. If any error occurs during the execution of the RENAME TABLE, then the whole statement fails, and no modification is performed on the table.
The user can also implement RENAME TABLE to move the table from one database to another:
Using the above statement to move all the tables from one db to another, in effect, renames the name of the database. The original database is not modified, and it exists unchanged.
Implementation of ALTER and RENAME Command
The user can use ALTER and RENAME commands to change the table's name one at a time.
The user can use the RENAME statement to change the names of multiple table by using a single command. Given below is an example query to implement RENAME query.