mysql rename表已经存在 mysql rename column

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语句重命名表。