轻松上手,快乐学习!

MySQL 教程

MySQL 首页MySQL 介绍MySQL 安装MySQL 实例库下载MySQL 实例库导入MySQL SELECTMySQL DISTINCTMySQL ORDER BYMySQL WHEREMySQL ANDMySQL ORMySQL INMySQL BETWEENMySQL LIKEMySQL LIMITMySQL IS NULLMySQL 别名MySQL JOINSMySQL INNER JOINMySQL LEFT JOINMySQL RIGHT JOINMySQL CROSS JOINMySQL SELF JOINMySQL GROUP BYMySQL HAVINGMySQL ROLLUPMySQL 子查询MySQL 派生表MySQL EXISTSMySQL CTEMySQL 递归 CTEMySQL UNIONMySQL INTERSECTMySQL MINUSMySQL INSERTMySQL INSERT INTO SELECTMySQL INSERT IGNOREMySQL UPDATEMySQL UPDATE JOINMySQL DELETEMySQL ON DELETE CASCADEMySQL DELETE JOINMySQL REPLACEMySQL PREPAREMySQL 事务MySQL 表锁定MySQL USEMySQL 数据库管理MySQL CREATE DATABASEMySQL DROP DATABASEMySQL 存储引擎MySQL CREATE TABLEMySQL 序列MySQL ALTER TABLEMySQL RENAME TABLEMySQL DROP COLUMNMySQL ADD COLUMNMySQL DROP TABLEMySQL 临时表MySQL TRUNCATE TABLEMySQL 数据类型MySQL NOT NULLMySQL Primary KeyMySQL Foreign KeyMySQL UNIQUEMySQL CHECKMySQL 字符集MySQL 排序规则MySQL 导入 CSVMySQL 导出 CSVMySQL 自然排序MySQL 基础

MySQL 存储过程

MySQL 存储过程介绍MySQL 存储过程实例MySQL 存储过程变量MySQL 存储过程参数MySQL 存储过程返回多值MySQL IF 语句MySQL CASE 语句MySQL IF CASE 选择MySQL 存储过程循环MySQL 存储过程游标MySQL 存储过程列表MySQL 存储过程异常处理MySQL SIGNAL 和 RESIGNALMySQL 存储函数

MySQL 视图

MySQL 视图教程SQL 视图介绍MySQL 视图介绍MySQL 创建视图MySQL 可更新视图WITH CHECK OPTIONLOCAL&CASCADEDMySQL 视图管理

MySQL 触发器

MySQL 触发器SQL 触发器MySQL 触发器介绍MySQL 触发器的创建MySQL 创建多个触发器MySQL 触发器管理MySQL 计划事件MySQL事件修改

MySQL 索引

MySQL 索引MySQL 索引创建MySQL 索引删除MySQL 显示索引MySQL 唯一索引MySQL 前缀索引MySQL 隐形索引MySQL 降序索引MySQL 复合索引MySQL 聚集索引MySQL 索引基数MySQL USE INDEXMySQL 强制索引

MySQL 管理

MySQL 管理MySQL访问控制系统MySQL 用户创建MySQL 用户密码MySQL 权限授予MySQL 权限撤销MySQL 角色MySQL 删除用户MySQL 表维护mysqldump 备份工具MySQL 数据库列表MySQL 表列表MySQL 表字段列表MySQL 用户列表MySQL 进程列表MySQL 列生成比较MySQL中同一表中的连续行

MySQL 全文搜索

MySQL 全文搜索MySQL 全文搜索介绍FULLTEXT索引MySQL 自然语言全文搜索MySQL 布尔全文搜索MySQL查询扩展MySQL ngram

MySQL 高级

MySQL 函数MySQL 窗口函数

MySQL 技巧

MySQL 查找重复数据MySQL 删除重复数据MySQL UUIDMySQL 表的复制MySQL 复制库MySQL 变量MySQL SELECT INTO 变量MySQL 表的存储引擎MySQL 使用正则查询MySQL 添加序号MySQL 随机查询MySQL 查询第 N 高记录MySQL 重置自增值MySQL VS MariaDBMySQL 间隔值MySQL 获取当天日期MySQL NULL 映射MySQL 注释理解MySQL EXPLAINMySQL 技巧介绍MySQL COUNTMySQL 数据分层MySQL 两表比较Mysql Like 优化

MySQL 窗口函数

MySQL CUME_DIST() 函数MySQL DENSE_RANK() 函数MySQL FIRST_VALUE 函数MySQL LAG() 函数MySQL LAST_VALUE() 函数MySQL LEAD 函数MySQL NTH_VALUE 函数MySQL NTILE 函数MySQL PERCENT_RANK 函数MySQL RANK 函数MySQL ROW_NUMBER 函数

MySQL 应用

MySQL应用程序编程接口PHP MySQL教程PHP 数据库连接

MySQL 优化

mysqld 内存持续变高


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,它包含两个表:employeesdepartments用于演示。

HR样本数据库

-- 创建库名
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)
);

其次,我们将样本数据插入到表employeesdepartments表中:

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

第三,我们在departmentsemployees表中查看我们的数据:

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基于employeesdepartments表命名的视图,如下所示:

CREATE VIEW v_employee_info as
    SELECT 
        id, first_name, last_name, dept_name
    from
        employees
            inner join
        departments USING (department_id);

视图使用  INNER JOIN  子句来连接departmentsemployees表。

以下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,...

以下语句将peopledepts表重命名为employeesdepartments表:

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语句不能。

重命名临时表示例

首先,我们创建一个临时表,其中包含来自employeeslast_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语句重命名表。