How To Rename Table Using MySQL RENAME TABLE Statement

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

MySQL RENAME TABLE 语句简介

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

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

RENAME TABLE old_table_name TO new_table_name;Code language: SQL (Structured Query Language) (sql)

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

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

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

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

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

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

MySQL 重命名表示例

首先，我们创建一个名为hr的新数据库，其中包含两个表： employees和departments用于演示。

CREATE DATABASE IF NOT EXISTS hr;Code language: SQL (Structured Query Language) (sql)

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)
);Code language: SQL (Structured Query Language) (sql)

其次，我们将示例数据插入到employees表和departments表中：

INSERT INTO departments(dept_name)
VALUES('Sales'),('Markting'),('Finance'),('Accounting'),('Warehouses'),('Production');Code language: SQL (Structured Query Language) (sql)

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);Code language: SQL (Structured Query Language) (sql)

第三，我们检查departments和employees表中的数据：

SELECT 
    department_id, dept_name
FROM
    departments;Code language: SQL (Structured Query Language) (sql)

SELECT 
    id, first_name, last_name, department_id
FROM
    employees;Code language: SQL (Structured Query Language) (sql)

重命名视图引用的表

如果你要重命名的表被视图引用了，重命名后该视图就会失效，需要手动调整视图。

例如，我们根据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);Code language: SQL (Structured Query Language) (sql)

这些视图使用内部联接子句来联接departments和employees表。

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

SELECT 
    *
FROM
    v_employee_info;Code language: SQL (Structured Query Language) (sql)

现在我们将employees重命名为people表，并再次从v_employee_info视图中查询数据。

RENAME TABLE employees TO people;Code language: SQL (Structured Query Language) (sql)

SELECT 
    *
FROM
    v_employee_info;Code language: SQL (Structured Query Language) (sql)

MySQL 返回以下错误消息：

Error Code: 1356. View 'hr.v_employee_info' references invalid table(s) or 
column(s) or function(s) or definer/invoker of view lack rights to use themCode language: SQL (Structured Query Language) (sql)

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

CHECK TABLE v_employee_info;Code language: SQL (Structured Query Language) (sql)

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

重命名存储过程引用的表

如果您要重命名的表被存储过程引用，您必须像处理视图一样手动调整它。

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

RENAME TABLE people TO employees;Code language: SQL (Structured Query Language) (sql)

然后，创建一个名为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;Code language: SQL (Structured Query Language) (sql)

接下来我们执行get_employee表获取id为1的员工的数据，如下：

CALL get_employee(1);Code language: SQL (Structured Query Language) (sql)

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

RENAME TABLE employees TO people;Code language: SQL (Structured Query Language) (sql)

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

CALL get_employee(2);Code language: SQL (Structured Query Language) (sql)

MySQL 返回以下错误消息：

Error Code: 1146. Table 'hr.employees' doesn't existCode language: SQL (Structured Query Language) (sql)

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

重命名引用了外键的表

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

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

RENAME TABLE departments TO depts;Code language: SQL (Structured Query Language) (sql)

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

DELETE FROM depts 
WHERE
    department_id = 1;Code language: SQL (Structured Query Language) (sql)

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`hr`.`people`, CONSTRAINT `people_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `depts` (`department_id`))Code language: SQL (Structured Query Language) (sql)

重命名多个表

我们还可以使用RENAME TABLE语句一次重命名多个表。请参阅以下声明：

RENAME TABLE old_table_name_1 TO new_table_name_2,
             old_table_name_2 TO new_table_name_2,...
Code language: SQL (Structured Query Language) (sql)

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

RENAME TABLE depts TO departments,
             people TO employees;Code language: SQL (Structured Query Language) (sql)

请注意， RENAME TABLE语句不是原子的。这意味着如果发生任何错误，MySQL 会将所有重命名的表回滚为其旧名称。

使用 ALTER TABLE 语句重命名表

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

ALTER TABLE old_table_name
RENAME TO new_table_name;Code language: SQL (Structured Query Language) (sql)

ALTER TABLE语句可以重命名临时表，而RENAME TABLE语句则不能。

重命名临时表示例

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

CREATE TEMPORARY TABLE lastnames
SELECT DISTINCT last_name from employees;Code language: SQL (Structured Query Language) (sql)

其次，我们使用RENAME TABLE重命名lastnames表：

RENAME TABLE lastnames TO unique_lastnames;Code language: SQL (Structured Query Language) (sql)

MySQL 返回以下错误消息：

Error Code: 1017. Can't find file: '.\hr\lastnames.frm' (errno: 2 - No such file or directory)Code language: SQL (Structured Query Language) (sql)

第三，我们使用ALTER TABLE语句重命名lastnames表。

ALTER TABLE lastnames
RENAME TO unique_lastnames;Code language: SQL (Structured Query Language) (sql)

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

SELECT 
    last_name
FROM
    unique_lastnames;Code language: SQL (Structured Query Language) (sql)

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