MySQL Rename Tables Tutorial and Examples

In this article, you will learn how to rename a table using MySQL RENAME TABLE and ALTER TABLE statements.

In this article, you will learn how to rename a table using MySQL RENAME TABLE and ALTER TABLE statements.

Occasionally, you will modify the name of an existing table for some reason. for example:

  • You used the wrong table name when creating the table.
  • You need to change the table name to something more meaningful.
  • The requirements of the product change, and the table name needs to be changed to accommodate the new business.
  • Your team uses a new naming convention, and you need to rename tables that do not conform to the new convention.

MySQL provides the RENAME TABLE statement to rename tables. In addition to this, you can also use the ALTER TABLE statement to rename the table.

Notes on renaming tables in MySQL

Renaming a table is a simple action, but it can introduce a series of problems. If you do not synchronously modify the code that uses this table, they may not function properly.

The code you need to modify synchronously might include:

  1. The stored procedures that use this table.
  2. The views that use this table.
  3. The functions that use this table.
  4. The triggers that use this table.
  5. The foreign key constraints that references this table (in older MySQL versions).
  6. The applications that use this table.

Therefore, when you plan to modify a table name, you need to evaluate it as a whole first. Then, decide whether to rename the table. Once you’ve decided to rename a table, you need to organize all the changes that need to be synchronized.

MySQL syntax for renaming a table

You can use the RENAME TABLE statement to rename tables as following:

RENAME TABLE
    old_table_name TO new_table_name
    [, old_table_name2 TO new_table_name2];

You can also use the ALTER TABLE statement as following:

ALTER TABLE old_table_name
RENAME TO new_table_name;

Here:

  • old_table_name is the table that needs to be renamed.
  • new_table_name is the new table name.

Both RENAME TABLE and ALTER TABLE statements can also be used to rename views.

There are also some differences between RENAME TABLE and ALTER TABLE statements:

  • The RENAME TABLE statement are more concise.
  • You can rename multiple tables simultaneously in one RENAME TABLE statement.
  • The RENAME TABLE statement cannot be used to rename temporary tables, but the ALTER TABLE statement can be used to rename temporary tables.

MySQL Rename Table Examples

This example demonstrates how to rename a table in MySQL.

Suppose, we have a user table named user. Because your team used a new naming convention, all entity tables need to be named in plural, so you need to rename the user table to users.

We use the following statement to create a user table in the testdb database to practice our example:

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int NOT NULL DEFAULT '20',
  `email` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

If you do not have a testdb database, create a database and select the database using the following statement first:

CREATE DATABASE testdb;
use testdb;

After the table is created, you can use the SHOW TABLES statement to see if the table was created successfully:

SHOW TABLES LIKE 'user%';

+--------------------------+
| Tables_in_testdb (user%) |
+--------------------------+
| user                     |
+--------------------------+

To rename the user table to users, run the following statement:

RENAME TABLE user TO users;

After the table is created, you can view the table using the SHOW TABLES statement:

SHOW TABLES LIKE 'user%';

+--------------------------+
| Tables_in_testdb (user%) |
+--------------------------+
| users                    |
+--------------------------+

Now, we see that there are no more user tables in the testdb database, only users tables.

Of course, you can also use the ALTER TABLE statement to rename the user table to users as follows:

ALTER TABLE user RENAME TO users;

Conclusion

MySQL provides RENAME TABLE and ALTER TABLE statements to rename tables.

Remember that when renaming a table, the table must not have outstanding transactions and the table must not be locked.