mysql rename table_MySQL RENAME TABLE

MySQL RENAME TABLE

简介：在本教程中，您将学习如何使用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示例

首先，我们创建一个的新数据库hr，它包含两个表：employees并departments用于演示。

-- 创建库名

CREATE DATABASE IF NOT EXISTS hr;

-- 选择刚创建的库名

USE 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)

);

其次，我们将样本数据插入到表employees和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);

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

SELECT

department_id, dept_name

FROM

departments;

+---------------+------------+

| department_id | dept_name |

+---------------+------------+

| 1 | Sales |

| 2 | Markting |

| 3 | Finance |

| 4 | Accounting |

| 5 | Warehouses |

| 6 | Production |

| 7 | Sales |

| 8 | Markting |

| 9 | Finance |

| 10 | Accounting |

| 11 | Warehouses |

| 12 | Production |

+---------------+------------+

12 rows in set (0.00 sec)

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 (0.00 sec)

重命名视图引用的表

如果要重命名的表由视图引用，则在重命名表时视图将变为无效，并且您必须手动调整视图。

例如，我们创建一个v_employee_info基于employees和departments表命名的视图，如下所示：

CREATE VIEW v_employee_info as

SELECT

id, first_name, last_name, dept_name

from

employees

inner join

departments USING (department_id);

视图使用  INNER JOIN  子句来连接departments和employees表。

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

SELECT

*

FROM

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 (0.03 sec)

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

RENAME TABLE employees TO people;

SELECT

*

FROM

v_employee_info;

MySQL返回以下错误消息：

ERROR 1356 (HY000): 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;

+--------------------+-------+----------+--------------------------------------------------------------------------------------------------------------------------------------+

| Table | Op | Msg_type | Msg_text |

+--------------------+-------+----------+--------------------------------------------------------------------------------------------------------------------------------------+

| hr.v_employee_info | check | Error | Table 'hr.employees' doesn't exist |

| hr.v_employee_info | check | Error | 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 |

| hr.v_employee_info | check | error | Corrupt |

+--------------------+-------+----------+--------------------------------------------------------------------------------------------------------------------------------------+

3 rows in set (0.00 sec)

我们需要手动更改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);

+------------+-----------+-----------+

| first_name | last_name | dept_name |

+------------+-----------+-----------+

| John | Doe | Sales |

+------------+-----------+-----------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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

RENAME TABLE employees TO people;

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

CALL get_employee(2);

MySQL返回以下错误消息：

mysql> CALL get_employee(2);

ERROR 1146 (42S02): 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 1451 (23000): 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,...

以下语句将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;

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

重命名临时表示例

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

CREATE TEMPORARY TABLE lastnames

SELECT DISTINCT last_name from employees;

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

RENAME TABLE lastnames TO unique_lastnames;

MySQL返回以下错误消息：

ERROR 1017 (HY000): Can't find file: './hr/lastnames.frm' (errno: 2 - No such file or directory)

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

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 (0.00 sec)

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