MySQL Rename Table

Last Updated: 15 July, 2023

To rename a table in MySQL, you can use the RENAME TABLE statement.

Here's the syntax:

 RENAME TABLE current_table_name TO new_table_name;

Replace current_table_name with the name of the table we want to rename, and new_table_name with the new name we want to assign to the table.

Here's an example to illustrate the usage:

Before Renaming a Table

  mysql> show tables;
  +---------------------+
  | Tables_in_school_db |
  +---------------------+
  | admin               |
  | students            |
  | teachers            |
  +---------------------+
  3 rows in set (0.05 sec)

Now, executing the RENAME TABLE statement.

  mysql> RENAME TABLE admin TO management;
  Query OK, 0 rows affected (4.28 sec)
  
  mysql> show tables;
  +---------------------+
  | Tables_in_school_db |
  +---------------------+
  | management          |
  | students            |
  | teachers            |
  +---------------------+
  3 rows in set (0.04 sec)

As we can see in the above example, the table name admin has been renamed to management using the RENAME TABLE statement.

Rename multiple tables

The RENAME TABLE statement in MySQL also allows to renaming multiple tables in a single command.

The syntax and example for renaming multiple tables are as follows:

Syntax:

  RENAME NAME current_table_name_1 TO new_table_name_1,
              current_table_name_2 TO new_table_name_2,
              current_table_name_3 TO new_table_name_n;

Example:

  mysql> RENAME TABLE management TO school_mng,
                      students TO stu_records,
                      teachers TO teachers_records;
  Query OK, 0 rows affected (1.21 sec)

  mysql> SHOW TABLES;
  +---------------------+
  | Tables_in_school_db |
  +---------------------+
  | school_mng          |
  | staff               |
  | stu_records         |
  | teachers_records    |
  +---------------------+
  4 rows in set (0.03 sec)

Some Important Points:

We need appropriate privileges to rename a table in MySQL.

Any triggers, stored procedures, or views that reference the renamed table will need to be updated to reflect the new table name.

We cannot rename a temporary table using the RENAME TABLE statement; using the ALTER TABLE statement, we can rename the temporary table in MySQL.