学习如何使用MySQL RENAME TABLE
语句和ALTER TABLE
语句重命名表。
MySQL RENAME TABLE语句简介
由于业务需求变化,我们需要将当前表重新命名为新表,以更好地反映或表示新情况。 MySQL提供了一个非常有用的语句来更改一个或多个表的名称。
要更改一个或多个表,我们使用RENAME TABLE
语句如下:
RENAME TABLE old_table_name TO new_table_name;
SQL
旧表(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;
SQL
创建表 -
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)
);
SQL
其次,将样本数据插入到 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);
SQL
第三,查询在 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
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);
SQL
视图使用内连接子句来连接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
SQL
现在,将v_employee_info
视图中的employees
表重命名为people
,并查询视图的数据。
RENAME TABLE employees TO people;
-- 查询数据
SELECT
*
FROM
v_employee_info;
SQL
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
SQL
我们可以使用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
SQL
需要手动更改v_employee_info
视图,以便它引用people
表而不是employees
表。
重命名由存储过程引用的表
如果要重命名由存储过程引用的表,则必须像对视图一样进行手动调整。
首先,将people
表重命名为employees
表。
RENAME TABLE people TO employees;
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;
SQL
接下来,执行get_employee
存储过程从employees
表来获取id
为1
的员工的数据,如下所示:
CALL get_employee(1);
SQL
执行上面查询语句,得到以下结果 -
mysql> CALL get_employee(1);
+------------+-----------+-----------+
| first_name | last_name | dept_name |
+------------+-----------+-----------+
| John | Doe | Sales |
+------------+-----------+-----------+
1 row in set
Query OK, 0 rows affected
SQL
之后,我们再次将employees
表重新命名为people
表。
RENAME TABLE employees TO people;
SQL
最后,调用get_employee
存储过程来获取id
为2
的员工信息:
CALL get_employee(2);
SQL
MySQL返回以下错误消息:
1146 - Table 'hrdb.employees' doesn't exist
SQL
要解决这个问题,我们必须手动将存储过程中的employees
表更改为people
表。
重命名引用外键的表
departments
表使用department_id
列链接到employees
表。 employees
表中的department_id
列是引用departments
表的department_id
列作为外键。
如果重命名departments
表,那么指向departments
表的所有外键都不会被自动更新。 在这种情况下,我们必须手动删除并重新创建外键。
RENAME TABLE departments TO depts;
SQL
我们删除ID
为1
的部门,由于外键约束,people
表中的所有行也应删除。 但是,我们将department
表重命名为depts
表,而不会手动更新外键,MySQL会返回错误,如下所示:
DELETE FROM depts
WHERE
department_id = 1;
SQL
执行上面语句,得到以下以下错误提示 -
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`))
SQL
重命名多个表
也可以使用RENAME TABLE
语句来一次重命名多个表。 见下列声明:
RENAME TABLE old_table_name_1 TO new_table_name_2,
old_table_name_2 TO new_table_name_2,...
SQL
以下语句将 people
和 depts
重命名为 employees
和 departments
表:
RENAME TABLE depts TO departments,
people TO employees;
SQL
注意
RENAME TABLE
语句不是原子的。所以如果在任何时候发生错误,MySQL会将所有重新命名的表都回滚到旧名称。
使用ALTER TABLE语句重命名表
我们可以使用ALTER TABLE
语句重命名一个表,如下所示:
ALTER TABLE old_table_name
RENAME TO new_table_name;
SQL
RENAME TABLE
语句不能用于重命名临时表,这时就可以使用ALTER TABLE
语句来重命名一个临时表。
重命名临时表示例
首先,我们创建一个临时表,其中包含来自employees
表的last_name
列的所有唯一的姓氏:
CREATE TEMPORARY TABLE lastnames
SELECT DISTINCT last_name from employees;
SQL
第二步,使用RENAME TABLE
重命名姓氏表:
RENAME TABLE lastnames TO unique_lastnames;
SQL
MySQL返回以下错误消息:
Error Code: 1017. Can't find file: '.hrdblastnames.frm' (errno: 2 - No such file or directory)
SQL
第三,使用ALTER TABLE
语句来重命名姓氏表。
ALTER TABLE lastnames
RENAME TO unique_lastnames;
SQL
第四,从unique_lastnames
临时表查询数据:
SELECT
last_name
FROM
unique_lastnames;
+-----------+
| last_name |
+-----------+
| Doe |
| Lily |
| Dave |
| Jane |
| Josh |
| More |
+-----------+
6 rows in set
SQL
在本教程中,我们向您展示了如何使用MySQL RENAME TABLE
和ALTER TABLE
语句重命名表。