遍历所有表并重命名列

本文介绍了遍历所有表并重命名列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

一个客户端给了我一个遗留数据库old_data，其中包含一堆表，我将在新系统中逐步激活这些表.不幸的是，有两个表包含一列名为hash的列，该列与我用来处理该项目的Ruby on Rails不太兼容.

A client gave me a legacy database old_data which contains a bunch of tables which I'm going to activate in a new system step by step. Unfortunately a couple of the tables contain a column with the name hash which doesn't float well with Ruby on Rails which I use to tackle this project.

是否有一种方法可以告诉MySQL控制台中的MySQL(版本8.0.12)遍历给定数据库中的所有表，并将列hash重命名为old_hash(如果该列存在)?

Is there a way to tell MySQL (version 8.0.12) in the MySQL console to loop through all tables in the given database and rename the column hash to old_hash if that column exists?

推荐答案

幸运的是，MySQL 8带有ALTER TABLE RENAME COLUMN a TO b语法，因此您可以编写一个相当简单的存储过程来做到这一点.

Luckily MySQL 8 comes with an ALTER TABLE RENAME COLUMN a TO b syntax so you can write a reasonably straightforward stored procedure to do this.

DELIMITER // CREATE PROCEDURE rename_columns(IN name_of_database CHAR(64), IN old_name_of_column CHAR(64), IN new_name_of_column CHAR(64)) BEGIN DECLARE done INT DEFAULT FALSE; DECLARE name_of_table CHAR(64); DECLARE table_cursor CURSOR FOR SELECT TABLE_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = name_of_database AND COLUMN_NAME = old_name_of_column; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN table_cursor; table_loop: LOOP FETCH table_cursor INTO name_of_table; IF done THEN LEAVE table_loop; END IF; SET @alter_sql = CONCAT( 'ALTER TABLE ', name_of_database, '.', name_of_table, ' RENAME COLUMN ', old_name_of_column, ' TO ', new_name_of_column); PREPARE alter_statement FROM @alter_sql; EXECUTE alter_statement; DEALLOCATE PREPARE alter_statement; END LOOP; CLOSE table_cursor; END// DELIMITER ; CALL rename_columns('old_data', 'hash', 'old_hash');

在5.7及更早版本中，它更为复杂，因为您需要生成一个包含完整列定义的ALTER TABLE CHANGE a b ...语句.

It's more complicated in 5.7 and earlier because you'll need to generate an ALTER TABLE CHANGE a b ... statement which includes the full column definition.