遍历所有表并重命名列 [英] Loop through all tables and rename a column
问题描述
一个客户端给了我一个遗留数据库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.
这篇关于遍历所有表并重命名列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!