RENAME TABLE

Syntax:
RENAME TABLE
tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2] ...

RENAME TABLE renames one or more tables. You must have ALTER and DROP
privileges for the original table, and CREATE and INSERT privileges for
the new table.

For example, to rename a table named old_table to new_table, use this
statement:

RENAME TABLE old_table TO new_table;

That statement is equivalent to the following ALTER TABLE statement:

ALTER TABLE old_table RENAME new_table;

RENAME TABLE, unlike ALTER TABLE, can rename multiple tables within a
single statement:

RENAME TABLE old_table1 TO new_table1,
old_table2 TO new_table2,
old_table3 TO new_table3;

Renaming operations are performed left to right. Thus, to swap two
table names, do this (assuming that a table with the intermediary name
tmp_table does not already exist):

RENAME TABLE old_table TO tmp_table,
new_table TO old_table,
tmp_table TO new_table;

Metadata locks on tables are acquired in name order, which in some
cases can make a difference in operation outcome when multiple
transactions execute concurrently. See
https://dev.mysql.com/doc/refman/8.0/en/metadata-locking.html.

As of MySQL 8.0.13, you can rename tables locked with a LOCK TABLES
statement, provided that they are locked with a WRITE lock or are the
product of renaming WRITE-locked tables from earlier steps in a
multiple-table rename operation. For example, this is permitted:

LOCK TABLE old_table1 WRITE;
RENAME TABLE old_table1 TO new_table1,
new_table1 TO new_table2;

This is not permitted:

LOCK TABLE old_table1 READ;
RENAME TABLE old_table1 TO new_table1,
new_table1 TO new_table2;

Prior to MySQL 8.0.13, to execute RENAME TABLE, there must be no tables
locked with LOCK TABLES.

With the transaction table locking conditions satisfied, the rename
operation is done atomically; no other session can access any of the
tables while the rename is in progress.

If any errors occur during a RENAME TABLE, the statement fails and no
changes are made.

You can use RENAME TABLE to move a table from one database to another:

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

Using this method to move all tables from one database to a different
one in effect renames the database (an operation for which MySQL has no
single statement), except that the original database continues to
exist, albeit with no tables.

Like RENAME TABLE, ALTER TABLE ... RENAME can also be used to move a
table to a different database. Regardless of the statement used, if the
rename operation would move the table to a database located on a
different file system, the success of the outcome is platform specific
and depends on the underlying operating system calls used to move table
files.

If a table has triggers, attempts to rename the table into a different
database fail with a Trigger in wrong schema (ER_TRG_IN_WRONG_SCHEMA
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.h
tml#error_er_trg_in_wrong_schema)) error.

An unencrypted table can be moved to an encryption-enabled database and
vice versa. However, if the table_encryption_privilege_check variable
is enabled, the TABLE_ENCRYPTION_ADMIN privilege is required if the
table encryption setting differs from the default database encryption.

To rename TEMPORARY tables, RENAME TABLE does not work. Use ALTER TABLE
instead.

RENAME TABLE works for views, except that views cannot be renamed into
a different database.

Any privileges granted specifically for a renamed table or view are not
migrated to the new name. They must be changed manually.

RENAME TABLE tbl_name TO new_tbl_name changes internally generated
foreign key constraint names and user-defined foreign key constraint
names that begin with the string "tbl_name_ibfk_" to reflect the new
table name. InnoDB interprets foreign key constraint names that begin
with the string "tbl_name_ibfk_" as internally generated names.

Foreign key constraint names that point to the renamed table are
automatically updated unless there is a conflict, in which case the
statement fails with an error. A conflict occurs if the renamed
constraint name already exists. In such cases, you must drop and
re-create the foreign keys for them to function properly.

RENAME TABLE tbl_name TO new_tbl_name changes internally generated and
user-defined CHECK constraint names that begin with the string
"tbl_name_chk_" to reflect the new table name. MySQL interprets CHECK
constraint names that begin with the string "tbl_name_chk_" as
internally generated names. Example:

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`i1` int(11) DEFAULT NULL,
`i2` int(11) DEFAULT NULL,
CONSTRAINT `t1_chk_1` CHECK ((`i1` > 0)),
CONSTRAINT `t1_chk_2` CHECK ((`i2` < 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.02 sec)

mysql> RENAME TABLE t1 TO t3;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW CREATE TABLE t3\G
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`i1` int(11) DEFAULT NULL,
`i2` int(11) DEFAULT NULL,
CONSTRAINT `t3_chk_1` CHECK ((`i1` > 0)),
CONSTRAINT `t3_chk_2` CHECK ((`i2` < 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

URL: https://dev.mysql.com/doc/refman/8.0/en/rename-table.html