MySQL重命名表（RENAME TABLE语句）

2021-12-07 14:35 MySQL

在本教程中，您将学习如何使用MySQL RENAME TABLE语句和ALTER TABLE语句来重命名表。MySQL RENAME TABLE语句简介由于业务需求的变化，我们需要将当前表重命名为一个新的名称，以更好地反映新的情况。MySQL提供了用于修改一个或多个表的名称的非常有用的语句。要修改一个或多个表，我们使用 RENAME TABLE 语句如下：RENAME TABLE old_table_name TO new_table_name;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示例首先，我们创建一个新的数据库名为：hr_db，在这个数据库里边创建两个表：employee 和 department ，其关系及表结构如下 ER 图所示 -CREATE DATABASE IF NOT EXISTS hr_db;DATABASE IF NOT EXISTS hr_db;CREATE TABLE hr_db.department( department_id INT AUTO_INCREMENT PRIMARY KEY, dept_name VARCHAR(100));CREATE TABLE hr_db.employee( 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 department(department_id));TABLE hr_db.department( department_id INT AUTO_INCREMENT PRIMARY KEY, dept_name VARCHAR(100));CREATE TABLE hr_db.employee( 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 department(department_id));第二步，插入一些样本数据到 employee 和 department 表：INSERT INTO hr_db.department(dept_name)VALUES('销售部'),('市场部'),('技术部'),('会计部'),('仓库部'),('生产部');.department(dept_name)VALUES('销售部'),('市场部'),('技术部'),('会计部'),('仓库部'),('生产部');INSERT INTO hr_db.employee(first_name,last_name,department_id)VALUES('学友','张',1),('家辉','张',2),('星星','周',3),('超','邓',4),('志祥','罗',5),('亦凡','吴',1);.employee(first_name,last_name,department_id)VALUES('学友','张',1),('家辉','张',2),('星星','周',3),('超','邓',4),('志祥','罗',5),('亦凡','吴',1);第三，重新查询 employee 和 department 表中的数据，如下是查询所示 -SELECT department_id,dept_nameFROM hr_db.department; department_id,dept_nameFROM hr_db.department;执行上面SQL查询，得到如下结果 -SELECT id,first_name,last_name,department_idFROM hr_db.employee; id,first_name,last_name,department_idFROM hr_db.employee;执行上面SQL查询，得到如下结果 -重命名视图所引用的表如果要重命名视图所引用的表，重命名表成功后，视图引用的旧表将变得无效，所以这个时候必须手动调整视图。例如，我们基于 employee 和 department 表创建一个视图名称为 v_employee_info ，如下：CREATE VIEW v_employee_info as SELECT id,first_name,last_name,dept_name from hr_db.employee inner join hr_db.department USING (department_id);VIEW v_employee_info as SELECT id,first_name,last_name,dept_name from hr_db.employee inner join hr_db.department USING (department_id);该视图使用内部联接(inner join)子句连接 employee 和 department 表。下面的 SELECT 语句是从 v_employee_info 视图查询并返回所有数据。SELECT *FROM v_employee_info; *FROM v_employee_info;执行上面SQL查询，得到如下结果 -现在，我们再次重命名表employees 为 people 并从 v_employee_info 视图中查询数据。如下查询 -RENAME TABLE employee TO people;TABLE employee TO people;SELECT *FROM v_employee_info; *FROM v_employee_info;MySQL将返回以下错误信息：Error Code: 1356. View 'hr_db.v_employee_info' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them1356. View 'hr_db.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;TABLE v_employee_info;执行上面SQL查询，得到如下结果 -我们需要手动改变 v_employee_info 视图，以便它引用到 people 表，而不是 employee 表。重命名由存储过程所引用的表如果要重命名一个表，但是这个表它由存储过程引用，需要手动作一些类似于视图引用中所作的调整。首先，重命名表 people 为 employee 。RENAME TABLE hr_db.people TO hr_db.employee;TABLE hr_db.people TO hr_db.employee;然后，创建一个新的存储过程名为 get_employee 并引用 employee表。DELIMITER $$CREATE PROCEDURE get_employee(IN p_id INT)BEGINSELECT first_name , last_name , dept_nameFROM hr_db.employeeINNER JOIN hr_db.department using(department_id)WHERE id=p_id;END $$DELIMITER;CREATE PROCEDURE get_employee(IN p_id INT)BEGINSELECT first_name , last_name , dept_nameFROM hr_db.employeeINNER JOIN hr_db.department using(department_id)WHERE id=p_id;END $$DELIMITER;接着，我们执行 get_employee 存储过程并指定参数：1，查询获得员工的数据如下：CALL get_employee(1);get_employee(1);执行上面SQL查询，得到如下结果 -在这之后，我们再次重命名表 employee 为 people 。如下查询 -RENAME TABLE hr_db.employee TO hr_db.people;TABLE hr_db.employee TO hr_db.people;最后，我们调用 get_employee 存储过程并指定参数：1，来获得员工的信息，如下查询 -CALL get_employee(2);get_employee(2);MySQL返回以下错误信息：ErrorCode:1146.Table 'hr_db.employees' doesn't exist1146.Table 'hr_db.employees' doesn't exist为了解决这个问题，我们必须手动在存储过程更改 employee 表为 people 。重命名有引用外键的表department表使用 department_id 列连接到 employee 表。在 employee 表中的 department_id 列是外键，它引用 department 表。如果重命名 department 表，所有的指向 department 表的外键不会自动更新。在这种情况下，我们必须手动删除并重新创建外键。RENAME TABLE hr_db.department TO hr_db.dept;TABLE hr_db.department TO hr_db.dept;我们删除ID为1的部门，因为外键约束，在 people 表中所有行也相应地删除了。但是，我们重命名 department 表为 depts 表而不手动更新外键，MySQL将返回一个错误，如下图所示：DELETE FROM hr_db.deptWHERE department_id=1;FROM hr_db.deptWHERE department_id=1;Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`hr_db`.`people`, CONSTRAINT `people_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `dept` (`department_id`))delete or update a parent row: a foreign key constraint fails (`hr_db`.`people`, CONSTRAINT `people_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `dept` (`department_id`))重命名多个表也可以使用RENAME TABLE语句，一次重命名多个表。参见下面的语句：RENAME TABLE old_table_name_1 TO new_table_name_2, old_table_name_2 TO new_table_name_2,... TABLE old_table_name_1 TO new_table_name_2, old_table_name_2 TO new_table_name_2,...下面的语句重命名 people 和 depts 表为 employee 和 department 表：RENAME TABLE hr_db.dept TO hr_db.department, hr_db.people TO hr_db.employee;TABLE hr_db.dept TO hr_db.department, hr_db.people TO hr_db.employee;注意：RENAME TABLE 语句不是原子操作。如果发生了任何错误，MySQL的执行将回退所有重命名表到原来的名称。使用ALTER TABLE语句重命名表可以使用ALTER TABLE语句重命名表，如下：ALTER TABLE old_table_nameRENAME TO new_table_name;TABLE old_table_nameRENAME TO new_table_name;ALTER TABLE 语句可以重命名一个临时表，而 RENAME TABLE 语句不能。重命名临时表的例子首先，我们创建一个包含来自 employee 表中的姓氏列唯一的临时表：CREATE TEMPORARY TABLE lastnames SELECT DISTINCT last_name from employee;TEMPORARY TABLE lastnames SELECT DISTINCT last_name from employee;其次，我们用 RENAME TABLE 语句重命名 lastnames 表：RENAME TABLE lastnames TO unique_lastnames; TABLE lastnames TO unique_lastnames;MySQL返回以下错误信息：Error Code: 1017. Can't find file: '.\hr_db\lastnames.frm' (errno: 2 - No such file or directory)Code: 1017. Can't find file: '.\hr_db\lastnames.frm' (errno: 2 - No such file or directory)第三，使用 ALTER TABLE 语句重新命名 lastnames 表。<pre class="brush:bash;toolbar:false">ALTERTABLElastnames RENAMETOunique_lastnames; TABLElastnames RENAMETOunique_lastnames;</pre>第四，查询 unique_lastnames 临时表中的数据：<pre class="brush:bash;toolbar:false">SELECT last_name FROM unique_lastnames; last_name FROM unique_lastnames;</pre>