Mysql用模式重命名多个表 [英] Mysql Rename Multiple tables with a pattern

问题描述

我知道如何运行

RENAME TABLE onetable_test TO onetable;

但是有一种方法可以用模式来重命名许多表，并且不需要编写很多类似的代码

But is there a method to rename many tables with a pattern and don't write a lot of code like

RENAME TABLE onetable_test TO onetable;
RENAME TABLE twotable_test TO twitable;
RENAME TABLE threetable_test TO threetable;    
...

我正在使用mysql.

I am using mysql.

谢谢！

推荐答案

在以下存储过程中使用::

Use below stored procedure ::

DELIMITER $$

创建

PROCEDURE `Rename_Tables`()

BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE tableName VARCHAR(50);
DECLARE newTableName VARCHAR(70);
DECLARE t_query VARCHAR(500);
DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_name LIKE '%table_test' AND table_schema='test' ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur1;

read_loop: LOOP
    FETCH cur1 INTO tableName;
    IF done THEN
        LEAVE read_loop;
    END IF;
    SET newTableName = SUBSTRING(tableName, 1,LOCATE('_',tableName)-1);
    SET t_query = CONCAT('RENAME TABLE ', tableName, ' TO ', newTableName);

    SET @myQuery = t_query;
    PREPARE stmt FROM @myQuery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END LOOP;

END$$

DELIMITER;

DELIMITER ;

用table_schema名称替换table_schema.

Replace table_schema with your table_schema name.

这篇关于Mysql用模式重命名多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！