求知 文章 文库 Lib 视频 iPerson 课程 认证 咨询 工具 讲座 Model Center   Code  
会员   
要资料
 
 

mysql教程
MySQL快速学习入门
MySQL是什么
MySQL安装
MySQL示例数据库
MySQL导入示例数据库
MySQL基础教程
MySQL查询数据
MySQL WHERE语句
MySQL插入数据
MySQL更新表数据
MySQL删除表数据
MySQL创建与删除数据库
MySQL创建表
MySQL修改表结构
MySQL重命名表
MySQL数据类型
高级部分
MySQL技巧
MySQL存储过程
MySQL视图
MySQL触发器
MySQL管理
MySQL全文搜索
MySQL函数
应用程序连接
MySQL+Node.js连接和操作
Python+MySQL连接和操作
 
 

MySQL重命名表
1271 次浏览
32次  

在本教程中,您将学习如何使用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 set
Query 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_name
RENAME 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 lastnames
RENAME TO unique_lastnames

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

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

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


您可以捐助,支持我们的公益事业。

1元 10元 50元





认证码: 验证码,看不清楚?请点击刷新验证码 必填



1271 次浏览
32次
 捐助