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

RENAME TABLE 重命名一个或多个表。你必须为原表设置 ALTER 和 DROP 权限，以及 CREATE 和 INSERT 权限。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.

例如，要将名为 old_table为 new_table，请使用以下语句：For example, to rename a table named old_table to new_table, use this statement:

RENAME TABLE old_table TO new_table;

该语句等价于以下 ALTER TABLE 语句：That statement is equivalent to the following ALTER TABLE statement:

ALTER TABLE old_table RENAME new_table;

RENAME TABLE与 ALTER 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;

重命名操作由左到右执行。因此，交换两个表名时，请这样做（假设中介名tmp_table不存在）：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;

表上的元数据锁按名称顺序获取，在某些情况下，当多个事务同时执行时，这会影响操作结果。参见 第 10.11.4 节，“元数据锁定”。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 Section 10.11.4, “Metadata Locking”.

你可以用 LOCK TABLES 语句“来重命名的表，前提是它们被 WRITE或者是多表重命名操作中重命名 WRITE锁定表的产物。例如，允许这样做：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;

满足事务表锁定条件后，重命名操作以原子方式完成;重命名过程中，其他会话无法访问任何表。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.

如果在RENAME TABLE，该语句失败，且不进行任何更改。If any errors occur during a RENAME TABLE, the statement fails and no changes are made.

你可以使用 RENAME TABLE将表从一个数据库移动到另一个数据库：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;

使用此方法将所有表从一个数据库移动到另一个数据库，实际上会重命名数据库（MySQL 没有单一语句的操作），但原始数据库仍然存在，尽管没有表。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.

如RENAME TABLEALTER TABLE ... RENAME也可以用来将表移动到另一个数据库。无论使用哪种语句，如果重命名操作能将表移动到位于不同文件系统的数据库，结果是否成功取决于具体平台，并取决于用于移动表文件的底层操作系统调用。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.

如果一个表有触发器，尝试将表重命名到不同的数据库会失败，并出现触发器在错误模式 (ER_TRG_IN_WRONG_SCHEMA）错误。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) error.

未加密表可以移动到支持加密的数据库，反之亦然。然而，如果启用了 table_encryption_privilege_check 变量，则需要 TABLE_ENCRYPTION_ADMIN 权限，且表加密设置与默认数据库加密不同。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.

要重命名 TEMPORARY tables，RENAME TABLE不工作。请使用 ALTER TABLE“。To rename TEMPORARY tables, RENAME TABLE does not work. Use ALTER TABLE instead.

RENAME TABLE 适用于视图，但视图不能被重命名到其他数据库中。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_”内部生成的名称。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 会更改以字符串 “tbl_name_chk_” 开头的内部生成和用户定义的 CHECK 约束名称，以反映新的表名。MySQL 将以字符串 “tbl_name_chk_” 开头的 CHECK 约束名称解释为内部生成的名称。示例：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)