将MYSQL 数据库表明批量改成大写

原理：查询数据库表信息，生成修改的sql 然后执行动态sql

1、首先创建存储过程

DELIMITER //  
    
DROP PROCEDURE IF EXISTS uppercase //  
    
CREATE PROCEDURE uppercase(IN dbname VARCHAR(200))  
    
BEGIN 
    
DECLARE done INT DEFAULT 0;  
    
DECLARE oldname VARCHAR(200);  
    
DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema = dbname;  
    
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
    
OPEN cur;  
    
REPEAT  
    
FETCH cur INTO oldname;  
    
SET @newname = UPPER(oldname);  
    
#IF newname equals to oldname, do nothing;  
#select 'a' <> 'A'; -> 0  
#select 'a' <> BINARY 'A'; -> 1  
SET @isNotSame = @newname <> BINARY oldname;  
    
IF NOT done && @isNotSame THEN 
    
SET @SQL = CONCAT('rename table ',oldname,' to ',@newname);  
    
PREPARE tmpstmt FROM @SQL;  
    
EXECUTE tmpstmt;  
    
DEALLOCATE PREPARE tmpstmt;  
    
END IF;  
    
UNTIL done END REPEAT;  
    
CLOSE cur;  
    
END //  
    
DELIMITER ;

2、执行

#调用存储过程 
call uppercase('TEST');
#TEST为你想要修改的数据库的名称