Skip to main content

使用MySQL RENAME Table语句重命名表

MySQL重命名表语句简介

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

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

RENAME TABLE old_table_name TO new_table_name;

旧表(旧表名称)必须存在,而新表(新表名称)必须不存在。如果新表new_table_name确实存在,则语句将失败。

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

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

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

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

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

MySQL重命名表示例

首先,我们创建一个名为hr的新数据库,该数据库由两个表组成:用于演示的员工和部门。

HR Sample Database

CREATE DATABASE IF NOT EXISTS hr;
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)
);

其次,我们在Employee和departments表中插入样本数据:

INSERT INTO departments(dept_name)
VALUES('Sales'),('Markting'),('Finance'),('Accounting'),('Warehouses'),('Production');
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);

第三,我们查看部门和员工表中的数据:

SELECT 
department_id, dept_name
FROM
departments;

MySQL RENAME TABLE departments Table

SELECT 
id, first_name, last_name, department_id
FROM
employees;

MySQL RENAME TABLE employees table

重命名视图引用的表

如果要重命名的表被视图引用,则如果重命名该表,该视图将无效,并且必须手动调整视图。

例如,我们根据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);

视图使用内部join子句连接部门和员工表。

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

SELECT 
*
FROM
v_employee_info;

MySQL RENAME TABLE with View example

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

RENAME TABLE employees TO people;
SELECT 
*
FROM
v_employee_info;

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 them

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

CHECK TABLE v_employee_info;

MySQL CHECK TABLE

我们需要手动更改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表来获取id为1的员工的数据,如下所示:

CALL get_employee(1);

MySQL RENAME TABLE with Stored Procedure

之后,我们再次将员工重命名为people表。

RENAME TABLE employees TO people;

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

CALL get_employee(2);

MySQL返回以下错误消息:

Error Code: 1146. Table 'hr.employees' doesn't exist

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

重命名引用了外键的表

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

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

RENAME TABLE departments TO depts;

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

DELETE FROM depts 
WHERE
department_id = 1;
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`))

重命名多个表

我们还可以使用RENAME TABLE语句一次重命名多个表。见以下声明:

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

以下语句将“人员”和“部门”表重命名为“员工”和“部门”表:

RENAME TABLE depts TO departments,
people TO employees;

注意RENAME TABLE语句不是原子的。这意味着,如果出现任何错误,MySQL会将所有重命名的表回滚到它们的旧名称。

使用ALTER TABLE语句重命名表

我们可以使用ALTER table语句重命名一个表,如下所示:

ALTER TABLE old_table_name
RENAME TO new_table_name;

ALTER TABLE语句可以重命名临时表,而rename TABLE语句不能。

重命名临时表示例

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

CREATE TEMPORARY TABLE lastnames
SELECT DISTINCT last_name from employees;

其次,我们使用RENAME表重命名lastnames表:

RENAME TABLE lastnames TO unique_lastnames;

MySQL返回以下错误消息:

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

第三,我们使用ALTERTABLE语句重命名LASTNAME表。

ALTER TABLE lastnames
RENAME TO unique_lastnames;

第四,我们从unique_lastnames临时表中查询数据:

SELECT 
last_name
FROM
unique_lastnames;

MySQL ALTER TABLE renames temporary table

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