When defining a database schema, you may think that the tables you created have perfect names. Over time, this may no longer be true. After spending some time on a database and struggling to find the data you want, you may begin to realize that some tables might have better names. After all, changing the name of a table is as common as renaming a file. Here is why there are different MySQL rename table approaches!
In this guide, you will see three different ways to rename a table in MySQL:
Let’s dive in!
What Happens When You Rename a MySQL Table
Under the hood, MySQL updates the metadata to reflect the new table name, ensuring that references to the specified table use the new name instead of the old name. During this process, the actual table data remains unchanged.
This means that MySQL will rename only those files that match the table_name, without making a copy of them. Consequently, renaming a table in MySQL is an instantaneous operation that does not require disk space.
With that being said, it is worth remembering that ALTER TABLE usually makes a copy of the table on the disk. Then, it applies the required changes to the data on the copy. Next, it then swaps the copy with the original table. So, in many cases, using ALTER TABLE will require disk space, especially if the table is big.
Also, when using ALTER or other queries, remember that privileges granted specifically to a table are not migrated to the new name when renaming that table. You must change them manually.
Now that you know how the MySQL rename table process works, you are ready to see three different approaches to performing it!
Approach #1: Rename a Table With ALTER TABLE
The MySQL ALTER TABLE statement allows you to change the name of an existing table through this syntax:
1
ALTER TABLE table_name RENAME TO new_table_name;
This query will update the name of the table_name table to new_table_name.
For example, assume that your database contains a table called articles and you want to rename it to products. This is the query you need to write:
1
ALTER TABLE products RENAME TO articles;
Wait for the operation to complete, and your MySQL database will now have an articles table instead of products.
Approach #2: Change the Name of One or More Tables With a RENAME TABLE Query
The MySQL RENAME TABLE query enables you to update the name of a table. This is how you can use it:
1
RENAME TABLE table_name TO new_table_name;
This is equivalent to running the ALTER TABLE ... RENAME TO query seen earlier.
What is different from ALTER TABLE is that RENAME TABLE allows you to rename multiple tables with a single query, as follows:
1
RENAME TABLE table_name_1 TO new_table_name_1,
2
table_name_2 TO new_table_name_2,
3
table_name_3 TO new_table_name_3;
Suppose you want to change the name of a table from customers to users. This is how you can do it:
1
RENAME TABLE customers TO users;
At the end of this query, the table customers will now have the new name users.
Approach #3: Rename a Table With Some Clicks In a Database Client
If running queries is not your thing, or you simply prefer to have visual feedback of what you are doing, you can perform the MySQL table rename operation in the database client. This is the easiest way to rename tables in MySQL.
First, choose a powerful database client, such as DbVisualizer. This complete tool equips you with everything you need to visually manage, query, and explore your databases, tables, and data.
In detail, DbVisualizer is a MySQL client with full support for all specific data types and features offered by the MySQL database technology. To rename a table in DbVisualizer, all you have to do is:
Step #1: Connect to your MySQL server as explained in the official documentation.
Step #2: Select your target server in the “Connections” menu on the left, right-click on it, and choose “Connect.”
Step #3: Open the “Databases” dropdown, find the database you want to explore, open its “Schemas” dropdown, and click on the “Tables” option:
Step #4: Identify the table you want to rename, right-click on it, and select the “Rename Table…” option:
Step #5: In the “Rename table” popup, type the new name you want to give to the table in the “New Table Name” input, and press the “Execute” button:
Step #6: Confirm the operation and the newly renamed table will immediately appear in the “Tables” dropdown:
Et voilà! You just learned how to rename a table in MySQL!
MySQL Rename Table: Best Practices
Here is a list of MySQL rename table best practices:
Extra: Move a MySQL Table to Another Database By Renaming
ALTER TABLE ... RENAME TO and RENAME TABLE can also be used to move a table to a different database. To do this, you only have to specify the databases in the table names, as shown below:
1
ALTER TABLE current_db.table_name RENAME TO other_db.table_name;
This will move the table_name table from the database current_db to the database other_db.
Equivalently, you can achieve the same goal with:
1
RENAME TABLE current_db.table_name TO other_db.table_name;
Keep in mind that if a table has triggers, attempting to rename it to a different database will fail with a Trigger in wrong schema (ER_TRG_IN_WRONG_SCHEMA) error. For more information, check out our complete guide on SQL triggers.
Conclusion
In this article, you understood what MySQL does under the scenes when you rename a table and how to do it in three different approaches. The first involves an ALTER TABLE command, the second a RENAME TABLE query, and the third a GUI tool.
In particular, a full-featured database client such as DbVisualizer makes it easy to deal with databases and tables while providing advanced features like query optimization and drag-and-drop query definition capabilities. Download DbVisualizer for free!
FAQ
Is it possible to rename all tables in MySQL Server?
With the right permission, it is possible. However, renaming MySQL system tables is not recommended, as it can lead to instability and data corruption. System tables, which store crucial information about the database structure, should retain their original names for proper functioning.
How to swap the names of two tables in MySQL?
To swap the names of two tables in MySQL, you can use a single RENAME TABLE query. Assuming you have tables named table_1 and table_2 (and that a table with the name tmp_table does not already exist), you can swap their names with:
1
RENAME TABLE table1 TO temp_table,
2
table2 TO table1,
3
temp_table TO table2;
This query renames table1 to temp_table, table2 to table1, and finally, temp_table to table2, effectively swapping their names.
What permissions does a user need to perform the MySQL table rename operation?
To perform a MySQL rename table operation, a user typically needs the ALTER privilege on the database containing the table. With that, the user should have sufficient permissions to modify the structure of tables.
What happens when you try to rename a MySQL table to a name that already exists?
If you try to rename an existing MySQL table to a new name that matches the name of another existing table, the operation will fail with this error:
1
[Code: 1050, SQL State: 42S01] Table 'table_name' already exists
How to rename a view in MySQL?
To rename a view in MySQL, you can use a RENAME TABLE statement as below:
1
RENAME TABLE view_name RENAME TO new_view_name;
This query effectively changes the name of the view from view_name to new_view_name. Thus, RENAME TABLE works for views, except that views cannot be renamed/moved into a different database.
