在本教程中，您将学习如何使用MySQL RENAME TABLE语句和ALTER TABLE语句重命名表。

MySQL RENAME TABLE语句简介

由于业务需求变化，我们需要将当前表重新命名为新表，以更好地反映或表示新情况。 MySQL提供了一个非常有用的语句来更改一个或多个表的名称。

要更改一个或多个表，我们使用RENAME TABLE语句如下：

RENAME TABLE old_table_name TO new_table_name;

旧表(old_table_name)必须存在，新表(new_table_name)必须不存在。 如果新表new_table_name存在，则该语句将失败。

除了表之外，我们还可以使用RENAME TABLE语句来重命名视图。

在执行RENAME TABLE语句之前，必须确保没有活动事务或锁定表。

请注意，不能使用RENAME TABLE语句来重命名临时表，但可以使用ALTER TABLE语句重命名临时表。

在安全性方面，我们授予旧表的任何权限必须手动迁移到新表。

在重命名表之前，应该彻底地评估影响。 例如，应该调查哪些应用程序正在使用该表。 如果表的名称更改，那么引用表名的应用程序代码也需要更改。 此外，您必须手动调整引用该表的其他数据库对象，如视图，存储过程，触发器，外键约束等。 我们将在下面的例子中更详细地讨论。

MySQL RENAME TABLE示例

首先，我们创建一个名为hrdb的新数据库，它由两个表组成：employees 和 departments。

创建数据库 -

CREATE DATABASE IF NOT EXISTS hrdb;

创建表 -

USE hrdb;CREATE TABLE departments ( department_id INT AUTO_INCREMENT PRIMARY KEY, dept_name VARCHAR(100));CREATE TABLE employees ( id int AUTO_INCREMENT primary key, first_name varchar(50) not null, last_name varchar(50) not null, department_id int not null, FOREIGN KEY (department_id) REFERENCES departments (department_id));

其次，将样本数据插入到 employees 和 departments 表中：

-- 插入数据到 departments 表中

INSERT INTO departments(dept_name)

VALUES('Sales'),('Markting'),('Finance'),('Accounting'),('Warehouses'),('Production');

-- 插入数据到 employees 表中

INSERT INTO employees(first_name,last_name,department_id)

VALUES('John','Doe',1),

('Bush','Lily',2),

('David','Dave',3),

('Mary','Jane',4),

('Jonatha','Josh',5),

('Mateo','More',1);

第三，查询在 employees 和 departments 表中的数据：

mysql> SELECT

department_id, dept_name

FROM

departments;

+---------------+------------+

| department_id | dept_name |

+---------------+------------+

| 1 | Sales |

| 2 | Markting |

| 3 | Finance |

| 4 | Accounting |

| 5 | Warehouses |

| 6 | Production |

+---------------+------------+

6 rows in set

mysql> SELECT

id, first_name, last_name, department_id

FROM

employees;

+----+------------+-----------+---------------+

| id | first_name | last_name | department_id |

+----+------------+-----------+---------------+

| 1 | John | Doe | 1 |

| 2 | Bush | Lily | 2 |

| 3 | David | Dave | 3 |

| 4 | Mary | Jane | 4 |

| 5 | Jonatha | Josh | 5 |

| 6 | Mateo | More | 1 |

+----+------------+-----------+---------------+

6 rows in set

重命名视图引用的表

如果重命名一个被视图引用的表，在重命名表后，视图就无效了，并且必须手动调整视图。

例如，我们基于employees和departments表创建一个名为v_employee_info的视图，如下所示：

CREATE VIEW v_employee_info as SELECT  id, first_name, last_name, dept_name from employees inner join departments USING (department_id);

视图使用内连接子句来连接employees和departments表。

以下SELECT语句返回v_employee_info视图中的所有数据。

mysql> SELECT  *FROM v_employee_info;+----+------------+-----------+------------+| id | first_name | last_name | dept_name |+----+------------+-----------+------------+| 1 | John | Doe | Sales || 2 | Bush | Lily | Markting || 3 | David | Dave | Finance || 4 | Mary | Jane | Accounting || 5 | Jonatha | Josh | Warehouses || 6 | Mateo | More | Sales |+----+------------+-----------+------------+6 rows in set

现在，将v_employee_info视图中的employees表重命名为people，并查询视图的数据。

RENAME TABLE employees TO people;

-- 查询数据

SELECT

*

FROM

v_employee_info;

MySQL返回以下错误消息：

1356 - View 'hrdb.v_employee_info' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

我们可以使用CHECK TABLE语句来检查v_employee_info视图的状态如下：

CHECK TABLE v_employee_info;

mysql> CHECK TABLE v_employee_info;

+----------------------+-------+----------+----------------------------------------------------------------------------------------------------------------------------------------+

| Table | Op | Msg_type | Msg_text |

+----------------------+-------+----------+----------------------------------------------------------------------------------------------------------------------------------------+

| hrdb.v_employee_info | check | Error | Table 'hrdb.employees' doesn't exist |

| hrdb.v_employee_info | check | Error | View 'hrdb.v_employee_info' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |

| hrdb.v_employee_info | check | error | Corrupt |

+----------------------+-------+----------+----------------------------------------------------------------------------------------------------------------------------------------+

3 rows in set

需要手动更改v_employee_info视图，以便它引用people表而不是employees表。

重命名由存储过程引用的表

如果要重命名由存储过程引用的表，则必须像对视图一样进行手动调整。

首先，将people表重命名为employees表。

RENAME TABLE people TO employees;

然后，创建一个名为get_employee的新存储过程，该过程引用employees表。

DELIMITER $$CREATE PROCEDURE get_employee(IN p_id INT)BEGIN SELECT first_name ,last_name ,dept_name FROM employees INNER JOIN departments using (department_id) WHERE id = p_id;END $$DELIMITER;

接下来，执行get_employee存储过程从employees表来获取id为1的员工的数据，如下所示：

CALL get_employee(1);

执行上面查询语句，得到以下结果 -

mysql> CALL get_employee(1);+------------+-----------+-----------+| first_name | last_name | dept_name |+------------+-----------+-----------+| John | Doe | Sales |+------------+-----------+-----------+1 row in setQuery OK, 0 rows affected

之后，我们再次将employees表重新命名为people表。

RENAME TABLE employees TO people;

最后，调用get_employee存储过程来获取id为2的员工信息：

CALL get_employee(2);

MySQL返回以下错误消息：

1146 - Table 'hrdb.employees' doesn't exist

要解决这个问题，我们必须手动将存储过程中的employees表更改为people表。

重命名引用外键的表

departments表使用department_id列链接到employees表。 employees表中的department_id列是引用departments表的department_id列作为外键。

如果重命名departments表，那么指向departments表的所有外键都不会被自动更新。 在这种情况下，我们必须手动删除并重新创建外键。

RENAME TABLE departments TO depts;

我们删除ID为1的部门，由于外键约束，people表中的所有行也应删除。 但是，我们将department表重命名为depts表，而不会手动更新外键，MySQL会返回错误，如下所示：

DELETE FROM depts

WHERE

department_id = 1;

执行上面语句，得到以下以下错误提示 -

1451 - Cannot delete or update a parent row: a foreign key constraint fails (`hrdb`.`people`, CONSTRAINT `people_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `depts` (`department_id`))

重命名多个表

也可以使用RENAME TABLE语句来一次重命名多个表。 见下列声明：

RENAME TABLE old_table_name_1 TO new_table_name_2, old_table_name_2 TO new_table_name_2,...

以下语句将 people 和 depts 重命名为 employees 和 departments 表：

RENAME TABLE depts TO departments,

people TO employees;

注意RENAME TABLE语句不是原子的。所以如果在任何时候发生错误，MySQL会将所有重新命名的表都回滚到旧名称。

使用ALTER TABLE语句重命名表

我们可以使用ALTER TABLE语句重命名一个表，如下所示：

ALTER TABLE old_table_nameRENAME TO new_table_name;

RENAME TABLE语句不能用于重命名临时表，这时就可以使用ALTER TABLE语句来重命名一个临时表。

重命名临时表示例

首先，我们创建一个临时表，其中包含来自employees表的last_name列的所有唯一的姓氏：

CREATE TEMPORARY TABLE lastnames

SELECT DISTINCT last_name from employees;

第二步，使用RENAME TABLE重命名姓氏表：

RENAME TABLE lastnames TO unique_lastnames;

MySQL返回以下错误消息：

Error Code: 1017. Can't find file: '.\hrdb\lastnames.frm' (errno: 2 - No such file or directory)

第三，使用ALTER TABLE语句来重命名姓氏表。

ALTER TABLE lastnamesRENAME TO unique_lastnames;

第四，从unique_lastnames临时表查询数据：

SELECT  last_nameFROM unique_lastnames;+-----------+| last_name |+-----------+| Doe || Lily || Dave || Jane || Josh || More |+-----------+6 rows in set

在本教程中，我们向您展示了如何使用MySQL RENAME TABLE和ALTER TABLE语句重命名表。