In one of our previous articles, we discussed how to rename a database in MySQL in detail. In this article, we will focus specifically on renaming tables in MySQL.
Rename table operations are common. Quite often, as business situations and requirements change, it becomes necessary to change the names of tables in MySQL databases.
Contents
- Reasons for renaming tables
- MySQL RENAME TABLE query syntax
- RENAME TABLE rules (max length & lower case problem)
- Renaming table in MySQL example
- Rename multiple tables in a database
- Using ALTER TABLE statement to rename tables in MySQL
- How to rename a table in MySQL Workbench
- How to rename a table in dbForge Studio for MySQL
- How to rename a table in phpMyAdmin
- How to rename tables with zero downtime
- Renaming tables via the command line
- Comparing different table renaming methods
- Precautions before renaming a table in a database
- Conclusion
Reasons for renaming tables
Relational databases store data in tables which have their unique names. As business situations and business requirements change, it becomes necessary to change the names of tables.
The most common reasons for renaming tables in databases are:
- Better readability and clarity: Renaming aims to clarify the specific table’s purpose, especially if the initial name was too vague or if the table’s role has changed.
- Naming conventions: Table names should align with these conventions, ensuring consistency, especially when they become standardized across a database (or multiple databases).
- Avoiding confusion: Renaming reduces the chance of accidental errors in queries or data management.
- Business changes or rebranding: Tables may be renamed to reflect new naming conventions or rebranding works, to synchronize databases with new terminology.
- Database merging or integration: Table names are often changed to avoid conflicts and to align with new database structures.
- Technical upgrades or optimization: Databases frequently require renaming to comply with naming restrictions and recommendations of system standards.
As such cases are quite frequent in daily workflows, the task of renaming a table in a database is among the most common jobs. All major database management systems provide means to perform this task easily and efficiently. Let us explore table renaming in MySQL.
MySQL RENAME TABLE query syntax
The syntax to change the table name in MySQL is as follows:
RENAME TABLE table_name_old TO table_name_new;In case, you have forgotten the exact name of the table you want to rename you can use the SHOW TABLES statement.
Here is the syntax to get all database table names:
SHOW TABLES
{FROM | IN} database_name;Suppose we want to find out the names of all tables in our sakila database.
SHOW TABLES
FROM sakila;Rename table rules (max length & lower case problem)
When renaming tables in MySQL bear in mind some important rules:
- The max length of a table name in MySQL is 64 characters.
- The old table (table_name_old) must exist in a database, and the new table (table_name_new) must not.
- To successfully rename a table, you must have ALTER and DROP privileges for the original table and CREATE and INSERT privileges for the new table.
- You can not rename a temporary table using the RENAME TABLE statement. Use ALTER TABLE instead.
- Case sensitivity of MySQL table names depends on the operating system on the host machine. For example, table names are not case-sensitive in Windows but case-sensitive in most Unix varieties. However, it is not recommended to refer to the same table name using different cases within the same statement even if the platform is not case-sensitive. To evade issues, it is recommended to implement consistent conventions, such as the demand to always create tables and refer to them using lowercase.
How to make MySQL table names case insensitive?
- Navigate to and open the /etc/mysql/my.cnf file.
- Set the MySQL variable lower_case_table_names=1.
- Restart the server.
Renaming table in MySQL example
Now, let’s create and rename a table in our sakila database:
CREATE TABLE IF NOT EXISTS orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
date DATE,
due_date DATE,
status TINYINT NOT NULL,
priority TINYINT NOT NULL,
description TEXT,
quantity INT
) ENGINE=INNODB;And then rename it:
RENAME TABLE orders TO product_orders;Note
In our blog, you can also find out how to rename a column in MySQL.
Rename multiple tables in a database
Remember, that in MySQL you can rename multiple tables within a single statement:
RENAME TABLE table_name_old_1 TO table_name_new_1,
table_name_old_2 TO table_name_new_2,
table_name_old_3 TO table_name_new_3;More than that, you can change the names of all tables in a database using one transaction only. Just get all table names using the SHOW TABLES statement and then rename them as shown in the syntax above.
Using ALTER TABLE statement to rename tables in MySQL
As we mentioned, RENAME TABLE doesn’t work for temporary tables. To avoid possible errors, it’s better to use the ALTER TABLE statement instead.
ALTER TABLE table_name_old
RENAME TO table_name_new;How to rename a table in MySQL Workbench
Renaming tables in MySQL Workbench is a quite simple process:
1. In MySQL Workbench Navigator, search for the table name you want to change and click it. Click the wrench icon next to the table you want to rename. Alternatively, right-click the table you want to rename in Navigator and click Alter Table.
2. In the Table Editor window, change the table’s name as shown in the screenshot below. Click Apply.
The table renaming script will be produced. Click Apply to execute it.
You can view the renamed table in the Navigator:
Now about the pitfalls of using Workbench to rename tables. Unfortunately, the tool lacks refactoring functionality, so you will have to find and edit all references to the renamed table manually. On the other hand, this issue is successfully resolved with the refactoring option in dbForge Studio for MySQL, a powerful alternative to Workbench.
How to rename a table in dbForge Studio for MySQL
dbForge Studio for MySQL is a comprehensive toolset for managing all database tasks in MySQL and MariaDB. Among its many features, the Studio offers an easy way to rename database tables and other objects (such as columns, views, aliases, stored procedures, triggers, and events).
Moreover, dbForge Studio for MySQL uses refactoring, automatically updating all references to renamed items.
To rename a table in dbForge Studio for MySQL, perform the following steps:
1. Select the database and locate the table you wish to rename. Right-click the table, then choose Rename from the menu (or select the object and press F2).
2. Enter the new name. A Preview Changes window will display the modifications applied to the database. You can uncheck any changes you don’t want to apply.
3. Click Apply to save changes. The renamed object will appear in the Database Explorer on the left.
You can also save the refactoring script locally for future reuse if needed.
How to rename a table in phpMyAdmin
phpMyAdmin is a web-based tool for managing MySQL databases, a popular choice among database professionals for standard database tasks. Renaming tables is one of such routine tasks, and it can be done in phpMyAdmin in the following way:
1. Open phpMyAdmin, select the necessary database and choose the table to rename. Go to Operations in the top right corner of the window and proceed to the Table options section.
2. The Rename table to field shows the current table’s name. Edit it to enter a new name and click Go to save changes.
You can see the new table name on the left pane of phpMyAdmin.
Still, this method is less intuitive than in an alternative dbForge Studio for MySQL, and it does not include the refactoring option by default.
How to rename tables with zero downtime
Renaming a table can be challenging as it means updating all the code that uses that table. Renaming large tables frequently causes significant downtimes, while bringing sites and applications down even for a couple of minutes is unacceptable. High availability is a crucial demand for modern systems, therefore, performing various operations with zero downtime becomes a necessity.
The most widely used solution to avoid downtimes is to rename a table with the help of MySQL shadow tables and triggers. This method involves creating a duplicate of the original table (the shadow table) and then synchronizing the data between the two tables before finally switching over to the new table.
Let’s assume we want to rename the actor table in the Sakila database to film_actor. For that, we create a shadow table named film_actor with the same structure as the original actor table:
CREATE TABLE film_actor LIKE actor;The next step is synchronizing the data from the actor table to the film_actor table. The simplest option is to populate the shadow table using the INSERT statement:
INSERT INTO film_actor SELECT * FROM actor;We need to synchronize film_actor continuously with the original actor table whenever any changes take place. We can achieve that by using triggers for real-time synchronization to handle updates, deletes, and inserts on the actor table.
The code for creating triggers is below:
CREATE TRIGGER actor_after_insert AFTER INSERT ON actor
FOR EACH ROW
INSERT INTO film_actor VALUES (NEW.actor_id, NEW.first_name, NEW.last_name, NEW.last_update);
CREATE TRIGGER actor_after_update AFTER UPDATE ON actor
FOR EACH ROW
UPDATE film_actor SET first_name = NEW.first_name, last_name = NEW.last_name, last_update = NEW.last_update WHERE actor_id = NEW.actor_id;
CREATE TRIGGER actor_after_delete AFTER DELETE ON actor
FOR EACH ROW
DELETE FROM film_actor WHERE actor_id = OLD.actor_id;Once we’ve made sure that both the actor and film_actor tables are in sync, we can modify the queries to use film_actor instead of actor. Note: It is recommended to test this switch properly in a staging environment before deployment to production.
After redirecting all queries, we can disable triggers and drop the old actor table or rename it and archive it if necessary.
This process ensures there is no downtime as all operations are conducted on live tables with real-time data synchronization. Additionally, triggers help us maintain data consistency between the original and the shadow tables throughout the transition period.
Renaming tables via the command line
Renaming database tables using the command line tools is convenient in situations where you need to automate the renaming process across multiple databases or as part of a larger batch operation, such as during system migrations, version updates, or when consolidating databases. It’s also useful for enforcing naming conventions or resolving naming discrepancies in an automated, consistent manner.
Before renaming database tables using the command line, ensure the following prerequisites:
- Access permissions: Check the database permissions; this task usually requires admin or DDL alteration rights.
- Database connection: Check the credentials and access methods to connect to the database from the shell environment.
Below you can see the example string:
"C:\Program Files\MySQL\MySQL Server <version>\bin\mysql.exe" -u <username> -p <database_name> -e "RENAME TABLE <old_table_name> TO <new_table_name>;"Placeholder explanations:
- <version>: Replace with your MySQL server version (e.g., 9.1).
- <username>: Replace with your MySQL username (e.g., root).
- <database_name>: Replace with the name of your database (e.g., sakila).
- <old_table_name>: Replace with the current name of the table you want to rename.
- <new_table_name>: Replace with the new name you want for the table.
Assume we want to rename the film_text table to new_film_text. In our test scenario, the username is root, the MySQL server version is 9.1, and the database is sakiladev. The code to rename a table using the command line is as follows:
"C:\Program Files\MySQL\MySQL Server 9.1\bin\mysql.exe" -u root -p sakiladev -e "RENAME TABLE film_actor TO new_film_actor;"Launch the Command Prompt and enter the string. You will be asked to enter the MySQL user password to confirm the action. The table will be renamed.
This string can be used for operation automation in different work scenarios.
Comparing different table renaming methods
| Method | Description | Tool | Advantages |
|---|---|---|---|
| RENAME TABLE | Change table name in MySQL | MySQL Command Line | Simple syntax, quick execution |
| ALTER TABLE | Rename table using ALTER statement | MySQL Command Line | Works for temporary tables |
| MySQL Workbench | GUI method to rename tables | MySQL Workbench | User-friendly interface, visual representation |
| dbForge Studio for MySQL | Advanced refactoring tool for renaming | dbForge Studio for MySQL | Automatically updates references |
| phpMyAdmin | Web-based tool for managing MySQL databases | phpMyAdmin | Accessible interface, widely used |
| Zero Downtime | Rename using shadow tables and triggers | MySQL | Avoids downtime, maintains data consistency |
| Command Line Automation | Automate renaming across multiple databases | Command Line | Efficient for batch operations |
Precautions before renaming a table in a database
Renaming database tables may cause unintended consequences. That’s why, take the following precautions before starting the job:
- Back up the database (make a full backup) to avoid data loss if anything goes wrong.
- Review, list, and update dependencies related to views, stored procedures, foreign keys, etc.
- Check application code to ensure that any application using the table references the new name, including code, configuration files, and any third-party tools or integrations.
- Review user permissions tied to the table, as renaming might impact access rights, especially if roles or permissions are directly linked to table names.
- Inform other team members or departments about the change, especially if the table is used across multiple applications or projects.
- Test the updated code in a development environment to identify and fix issues before applying changes to production.
Taking these steps helps minimize the risk of disruptions and ensures a smooth transition to the new table name.
Conclusion
Renaming a table is one of the most common tasks for database professionals, with various options available, including command-line and GUI tools, and even automation. Though straightforward, renaming a table often requires careful consideration to avoid impacting overall database and application performance. This article offers practical guidelines for renaming database tables, along with essential tips to consider before proceeding with the task.
In particular, it is worth noticing dbForge Studio for MySQL that delivers advanced refactoring functionality and allows us to quickly and safely rename not only tables but other database objects as well. In other aspects, the Studio is one of the most functional toolsets for MySQL and MariaDB, covering all tasks in these RDBMSs, both on-premise and in the cloud.
Download a 30-day free trial of dbForge Studio for MySQL, give it a fly, and enjoy all the useful features it offers.
