你的位置:首页 > 数据库

[数据库]MYSQL将表名称修改成大写的存储过程


1. 条件:
 1.1 Mysql设置对大小写敏感
2. 执行下述存储过程:

 1 #call uppercase('库名') 2 DROP PROCEDURE IF EXISTS uppercase;  3 CREATE PROCEDURE uppercase(IN dbname VARCHAR(200))   4 BEGIN  5   6 DECLARE done INT DEFAULT 0;   7   8 DECLARE oldname VARCHAR(200);   9  10 DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema = dbname;  11  12 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  13  14 OPEN cur;  15  16 REPEAT  17  18 FETCH cur INTO oldname;  19  20 SET @newname = UPPER(oldname);  21 22 SET @isNotSame = @newname <> BINARY oldname;  23  24 IF NOT done && @isNotSame THEN 25  26 SET @SQL = CONCAT('rename table `',oldname,'` to `', LOWER(@newname), '_tmp` ');  27  28 PREPARE tmpstmt FROM @SQL;  29  30 EXECUTE tmpstmt;  31 32 33 SET @SQL = CONCAT('rename table `',LOWER(@newname),'_tmp` to `',@newname, '`');  34  35 PREPARE tmpstmt FROM @SQL;  36  37 EXECUTE tmpstmt; 38  39 DEALLOCATE PREPARE tmpstmt;  40  41 END IF;  42  43 UNTIL done END REPEAT;  44  45 CLOSE cur;  46  47 END  

3. 执行一下语句

call uppercase('库名');