你的位置:首页 > 软件开发 > 数据库 > MYSQL将表名称修改成大写的存储过程

MYSQL将表名称修改成大写的存储过程

发布时间:2016-08-12 17:00:03
1. 条件: 1.1 Mysql设置对大小写敏感2. 执行下述存储过程: 1 #call uppercase(库名) 2 DROP PROCEDURE IF EXISTS uppercase; 3 CREATE PROCEDURE uppercase(IN dbname V ...
1. 条件:
 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  

原标题:MYSQL将表名称修改成大写的存储过程

关键词:MYSQL

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。