How to Rename a Table in MySQL in Different Ways

The article explores a bunch of popular methods to rename a MySQL table and provides handy worked examples that could be of utility to both beginning and experienced users.

In one of our previous articles, we looked in detail at how to rename a database in MySQL. In this article, we are going to focus specifically on renaming tables in MySQL.

Rename table operations are common. Quite often as business situations and business requirements change, it becomes necessary to change the name of tables in MySQL databases so that they had more meaningful names or better reflected the essence of the information stored in them.

Before starting to change table names in MySQL, remember to secure yourself and carefully evaluate the risks. You need to analyze the processes that are using that table. In case, you rename a table, you need to change the code that refers to that table as well.  And that’s only half the story! Furthermore, you will have to change other database objects such as views, stored procedures, triggers, foreign keys, etc., that reference the table in question.

Sounds complicated? Strap yourself in and hold on tight! In this one, we explore the MySQL Rename Table operation in detail.

Contents
1. MySQL RENAME TABLE query syntax
  1.1 RENAME TABLE rules (max length & lower case problem)
2. Renaming table in MySQL example
  2.1 Rename multiple tables in a database
3. Using ALTER TABLE statement to rename tables in MySQL
4. How to rename a table in MySQL Workbench
5. How to change the table name in dbForge Studio for 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 tables 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:

1. The max length of a table name in MySQL is 64 characters.
2. The old table (table_name_old) must exist in a database, and the new table (table_name_new) must not.
3. To rename a table successfully, you must have ALTER and DROP privileges for the original table, and CREATE and INSERT privileges for the new table.
4. You can not rename a temporary table using the RENAME TABLE statement. Use ALTER TABLE instead.
5. Сase 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 are case-sensitive in most varieties of Unix. However, it is not recommended to refer to the same table name using different cases within the same statement even on the not case-sensitive platforms. To evade problems that might be caused by case differences in table names, it would be better to adopt a consistent convention, such as always creating and referring to tables using lowercase.

How to make MySQL table names case insensitive?

1. Navigate to and open the /etc/mysql/my.cnf file.
2. Set the MySQL variable lower_case_table_names=1.
3. Restart the server.

Renaming table in MySQL example

Now, let’s create and rename a table in our sakila database:

USE sakila
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 have already 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 and easy process, however, there are hidden dangers behind it.

To change the name of a table using the MySQL Workbench tool:
1. In MySQL Workbench Navigator, search the table name you want to change and then click it.
2. Click the wrench icon next to the table you want to rename.
Alternatively, right-click the table you want to rename in Navigator and then click Alter Table.
3. In the Table Editor window that opens, change the name of the table as shown in the screenshot below.
4. Click Apply.

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.

How to change the table name in dbForge Studio

With dbForge Studio for MySQL, you can forget tedious hours of searching references to the renamed object and replacing them. To save your time and effort, dbForge Studio for MySQL automates the renaming process of the following database objects: tables, columns, views, aliases, stored routines, local variables, triggers, events, UDFs, and users and even offers a convenient preview of the changes before applying them.

To rename an object in the script:

1. Right-click it, point to Refactoring, and then click Rename.
Alternatively, place a pointer on the database object and press F2.

2. The database object will be highlighted in grey. Change its name as required.
3. Press F2 to preview changes or press Enter/Tab to apply changes without previewing them.
In the Preview changes – Rename dialog, you can view all references to the renamed object.

4. Select the required references and press Apply.

As you can see, dbForge Studio for MySQL unlike MySQL Workbench delivers the advanced refactoring functionality and thus allows you to quickly and safely rename not only tables but other database objects as well.

Download a 30-day free trial of dbForge Studio for MySQL, give it a fly, and start enjoying all the useful features it offers.