MySQL重命名表(RENAME TABLE语句)

<p>在本教程中,您将学习如何使用MySQL RENAME TABLE语句和ALTER TABLE语句来重命名表。</p><p>MySQL RENAME TABLE语句简介</p><p>由于业务需求的变化,我们需要将当前表重命名为一个新的名称,以更好地反映新的情况。MySQL提供了用于修改一个或多个表的名称的非常有用的语句。</p><p>要修改一个或多个表,我们使用 RENAME TABLE 语句如下:</p><p>RENAME TABLE old_table_name TO new_table_name;</p><p>TABLE old_table_name TO new_table_name;</p><p>旧表( old_table_name)必须存在,而新表( new_table_name)一定不存在。如果新表 new_table_name 确实存在,该语句将失败。</p><p>除了表,我们可以使用 RENAME TABLE 语句重新命名视图。</p><p>我们执行RENAME TABLE语句之前,必须确保不存在活跃事务或锁定表。请注意,不能使用 RENAME TABLE 语句重命名一个临时表,但可以使用 ALTER TABLE 语句来重命名一个临时表。从安全性角度,我们给予旧表的任何权限必须手动迁移到新表上。</p><p>重命名表之前,应彻底评估的影响。例如,应该调查哪些应用程序正在使用这个旧表。如果确实需要进行更改表的名称,那么也要更改对应引用表名的名称,以及在应用程序代码中修改表的名称。此外,还要手动调整其他数据库对象,如:视图,存储过程,触发器,外键约束等,即参照表。我们将在下面的实例中进行更详细讨论。</p><p>MySQL RENAME TABLE示例</p><p>首先,我们创建一个新的数据库名为:hr_db,在这个数据库里边创建两个表:employee 和 department ,其关系及表结构如下 ER 图所示 -</p><p>CREATE DATABASE IF NOT EXISTS hr_db;</p><p>DATABASE IF NOT EXISTS hr_db;</p><p>CREATE TABLE hr_db.department(</p><p> department_id INT AUTO_INCREMENT PRIMARY KEY,</p><p> dept_name VARCHAR(100)</p><p>);</p><p></p><p>CREATE TABLE hr_db.employee(</p><p> id int AUTO_INCREMENT primary key,</p><p> first_name varchar(50)not null,</p><p> last_name varchar(50)not null,</p><p> department_id int not null,</p><p> FOREIGN KEY(department_id)</p><p> REFERENCES department(department_id)</p><p>);</p><p>TABLE hr_db.department(</p><p> department_id INT AUTO_INCREMENT PRIMARY KEY,</p><p> dept_name VARCHAR(100)</p><p>);</p><p></p><p>CREATE TABLE hr_db.employee(</p><p> id int AUTO_INCREMENT primary key,</p><p> first_name varchar(50)not null,</p><p> last_name varchar(50)not null,</p><p> department_id int not null,</p><p> FOREIGN KEY(department_id)</p><p> REFERENCES department(department_id)</p><p>);</p><p>第二步,插入一些样本数据到 employee 和 department 表:</p><p>INSERT INTO hr_db.department(dept_name)</p><p>VALUES(&#39;销售部&#39;),(&#39;市场部&#39;),(&#39;技术部&#39;),(&#39;会计部&#39;),(&#39;仓库部&#39;),(&#39;生产部&#39;);</p><p>.department(dept_name)</p><p>VALUES(&#39;销售部&#39;),(&#39;市场部&#39;),(&#39;技术部&#39;),(&#39;会计部&#39;),(&#39;仓库部&#39;),(&#39;生产部&#39;);</p><p>INSERT INTO hr_db.employee(first_name,last_name,department_id)</p><p>VALUES(&#39;学友&#39;,&#39;张&#39;,1),</p><p>(&#39;家辉&#39;,&#39;张&#39;,2),</p><p>(&#39;星星&#39;,&#39;周&#39;,3),</p><p>(&#39;超&#39;,&#39;邓&#39;,4),</p><p>(&#39;志祥&#39;,&#39;罗&#39;,5),</p><p>(&#39;亦凡&#39;,&#39;吴&#39;,1);</p><p>.employee(first_name,last_name,department_id)</p><p>VALUES(&#39;学友&#39;,&#39;张&#39;,1),</p><p>(&#39;家辉&#39;,&#39;张&#39;,2),</p><p>(&#39;星星&#39;,&#39;周&#39;,3),</p><p>(&#39;超&#39;,&#39;邓&#39;,4),</p><p>(&#39;志祥&#39;,&#39;罗&#39;,5),</p><p>(&#39;亦凡&#39;,&#39;吴&#39;,1);</p><p>第三,重新查询 employee 和 department 表中的数据,如下是查询所示 -</p><p>SELECT</p><p> department_id,dept_name</p><p>FROM</p><p> hr_db.department;</p><p> department_id,dept_name</p><p>FROM</p><p> hr_db.department;</p><p>执行上面SQL查询,得到如下结果 -</p><p>SELECT</p><p> id,first_name,last_name,department_id</p><p>FROM</p><p> hr_db.employee;</p><p> id,first_name,last_name,department_id</p><p>FROM</p><p> hr_db.employee;</p><p>执行上面SQL查询,得到如下结果 -</p><p></p><p>重命名视图所引用的表</p><p>如果要重命名视图所引用的表,重命名表成功后,视图引用的旧表将变得无效,所以这个时候必须手动调整视图。</p><p>例如,我们基于 employee 和 department 表创建一个视图名称为 v_employee_info ,如下:</p><p>CREATE VIEW v_employee_info as</p><p> SELECT</p><p> id,first_name,last_name,dept_name</p><p> from</p><p> hr_db.employee</p><p> inner join</p><p> hr_db.department USING (department_id);</p><p>VIEW v_employee_info as</p><p> SELECT</p><p> id,first_name,last_name,dept_name</p><p> from</p><p> hr_db.employee</p><p> inner join</p><p> hr_db.department USING (department_id);</p><p>该视图使用内部联接(inner join)子句连接 employee 和 department 表。</p><p>下面的 SELECT 语句是从 v_employee_info 视图查询并返回所有数据。</p><p>SELECT</p><p> *</p><p>FROM</p><p> v_employee_info;</p><p> *</p><p>FROM</p><p> v_employee_info;</p><p>执行上面SQL查询,得到如下结果 -</p><p>现在,我们再次重命名表employees 为 people 并从 v_employee_info 视图中查询数据。如下查询 -</p><p>RENAME TABLE employee TO people;</p><p>TABLE employee TO people;</p><p>SELECT</p><p> *</p><p>FROM</p><p> v_employee_info;</p><p> *</p><p>FROM</p><p> v_employee_info;</p><p>MySQL将返回以下错误信息:</p><p>Error Code: 1356. View &#39;hr_db.v_employee_info&#39; references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them</p><p>1356. View &#39;hr_db.v_employee_info&#39; references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them</p><p>我们可以使用CHECK TABLE语句来检查 v_employee_info 视图的状态如下:</p><p>CHECK TABLE v_employee_info;</p><p>TABLE v_employee_info;</p><p>执行上面SQL查询,得到如下结果 -</p><p>我们需要手动改变 v_employee_info 视图,以便它引用到 people 表,而不是 employee 表。</p><p></p><p>重命名由存储过程所引用的表</p><p>如果要重命名一个表,但是这个表它由存储过程引用,需要手动作一些类似于视图引用中所作的调整。</p><p>首先,重命名表 people 为 employee 。</p><p>RENAME TABLE hr_db.people TO hr_db.employee;</p><p>TABLE hr_db.people TO hr_db.employee;</p><p>然后,创建一个新的存储过程名为 get_employee 并引用 employee表。</p><p>DELIMITER $$</p><p></p><p>CREATE PROCEDURE get_employee(IN p_id INT)</p><p></p><p>BEGIN</p><p>SELECT first_name , last_name , dept_name</p><p>FROM hr_db.employee</p><p>INNER JOIN hr_db.department using(department_id)</p><p>WHERE id=p_id;</p><p>END $$</p><p></p><p>DELIMITER;</p><p>CREATE PROCEDURE get_employee(IN p_id INT)</p><p></p><p>BEGIN</p><p>SELECT first_name , last_name , dept_name</p><p>FROM hr_db.employee</p><p>INNER JOIN hr_db.department using(department_id)</p><p>WHERE id=p_id;</p><p>END $$</p><p></p><p>DELIMITER;</p><p>接着,我们执行 get_employee 存储过程并指定参数:1,查询获得员工的数据如下:</p><p>CALL get_employee(1);</p><p>get_employee(1);</p><p>执行上面SQL查询,得到如下结果 -</p><p>在这之后,我们再次重命名表 employee 为 people 。如下查询 -</p><p>RENAME TABLE hr_db.employee TO hr_db.people;</p><p>TABLE hr_db.employee TO hr_db.people;</p><p>最后,我们调用 get_employee 存储过程并指定参数:1,来获得员工的信息,如下查询 -</p><p>CALL get_employee(2);</p><p>get_employee(2);</p><p>MySQL返回以下错误信息:</p><p>ErrorCode:1146.Table &#39;hr_db.employees&#39; doesn&#39;t exist</p><p>1146.Table &#39;hr_db.employees&#39; doesn&#39;t exist</p><p>为了解决这个问题,我们必须手动在存储过程更改 employee 表为 people 。</p><p></p><p>重命名有引用外键的表</p><p>department表使用 department_id 列连接到 employee 表。 在 employee 表中的 department_id 列是外键,它引用 department 表。</p><p>如果重命名 department 表,所有的指向 department 表的外键不会自动更新。在这种情况下,我们必须手动删除并重新创建外键。</p><p>RENAME TABLE hr_db.department TO hr_db.dept;</p><p>TABLE hr_db.department TO hr_db.dept;</p><p>我们删除ID为1的部门,因为外键约束,在 people 表中所有行也相应地删除了。但是,我们重命名 department 表为 depts 表而不手动更新外键,MySQL将返回一个错误,如下图所示:</p><p>DELETE FROM hr_db.dept</p><p>WHERE</p><p> department_id=1;</p><p>FROM hr_db.dept</p><p>WHERE</p><p> department_id=1;</p><p>Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`hr_db`.`people`, CONSTRAINT `people_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `dept` (`department_id`))</p><p>delete or update a parent row: a foreign key constraint fails (`hr_db`.`people`, CONSTRAINT `people_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `dept` (`department_id`))</p><p></p><p>重命名多个表</p><p>也可以使用RENAME TABLE语句,一次重命名多个表。参见下面的语句:</p><p>RENAME TABLE old_table_name_1 TO new_table_name_2,</p><p> old_table_name_2 TO new_table_name_2,...</p><p> TABLE old_table_name_1 TO new_table_name_2,</p><p> old_table_name_2 TO new_table_name_2,...</p><p>下面的语句重命名 people 和 depts 表为 employee 和 department 表:</p><p>RENAME TABLE hr_db.dept TO hr_db.department,</p><p> hr_db.people TO hr_db.employee;</p><p>TABLE hr_db.dept TO hr_db.department,</p><p> hr_db.people TO hr_db.employee;</p><p>注意:RENAME TABLE 语句不是原子操作。如果发生了任何错误,MySQL的执行将回退所有重命名表到原来的名称。</p><p></p><p>使用ALTER TABLE语句重命名表</p><p>可以使用ALTER TABLE语句重命名表,如下:</p><p>ALTER TABLE old_table_name</p><p>RENAME TO new_table_name;</p><p>TABLE old_table_name</p><p>RENAME TO new_table_name;</p><p>ALTER TABLE 语句可以重命名一个临时表,而 RENAME TABLE 语句不能。</p><p></p><p>重命名临时表的例子</p><p>首先,我们创建一个包含来自 employee 表中的姓氏列唯一的临时表:</p><p>CREATE TEMPORARY TABLE lastnames SELECT DISTINCT last_name from employee;</p><p>TEMPORARY TABLE lastnames SELECT DISTINCT last_name from employee;</p><p>其次,我们用 RENAME TABLE 语句重命名 lastnames 表:</p><p>RENAME TABLE lastnames TO unique_lastnames;</p><p> TABLE lastnames TO unique_lastnames;</p><p>MySQL返回以下错误信息:</p><p>Error Code: 1017. Can&#39;t find file: &#39;.\hr_db\lastnames.frm&#39; (errno: 2 - No such file or directory)</p><p>Code: 1017. Can&#39;t find file: &#39;.\hr_db\lastnames.frm&#39; (errno: 2 - No such file or directory)</p><p>第三,使用 ALTER TABLE 语句重新命名 lastnames 表。</p><pre class="brush:bash;toolbar:false">ALTERTABLElastnames RENAMETOunique_lastnames; TABLElastnames RENAMETOunique_lastnames;</pre><p>第四,查询 unique_lastnames 临时表中的数据:</p><pre class="brush:bash;toolbar:false">SELECT last_name FROM unique_lastnames; last_name FROM unique_lastnames;</pre>
RangeTime:0.006310s
RangeMem:215.58 KB
返回顶部 留言