BEGIN
DECLARE done INT DEFAULT 0;
DECLARE cTbl varchar(64);
DECLARE cur1 CURSOR FOR select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='LYBLOG';
set @sqlDrop='';
OPEN cur1;
FETCH cur1 INTO cTbl;/*得到表名*/
WHILE done = 0 DO #set @sqlDrop=concat('alter table `',cTbl'` rename to `',upper('(cTbl),'`;');
set @sqlDrop=concat('alter table ',cTbl, ' rename to upper(',cTbl,');'); #select @sqlDrop; PREPARE stmt1 FROM @sqlDrop;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
set done = 0;
FETCH cur1 INTO cTbl;
END WHILE;
CLOSE cur1;
END这是我写的。但不对。需求是把指定库中所有的表名全部统一改为大写。但我这里的upper方法不对。并且循环也不对。
求大家帮看看,并且最好自己高度一下。
UPPER->UCASE
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DECLARE done INT DEFAULT 0;
DECLARE cTbl varchar(64);
DECLARE cur1 CURSOR FOR select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='LYBLOG';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
set @sqlDrop='';
OPEN cur1;
FETCH cur1 INTO cTbl;/*得到表名*/
WHILE done = 0 DO set @sqlDrop=concat('alter table `',cTbl,'` rename to `',upper('(cTbl),'`;'); PREPARE stmt1 FROM @sqlDrop;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END WHILE;
CLOSE cur1;
END
你发了,又遇到你了。代码不能执行。。
@sqlDrop内容是什么
DECLARE done INT DEFAULT 0;
DECLARE cTbl varchar(64);
DECLARE cur1 CURSOR FOR select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='LYBLOG';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
set @sqlDrop='';
OPEN cur1;
FETCH cur1 INTO cTbl;/*得到表名*/
WHILE done = 0 DO
set @sqlDrop=concat('alter table ',cTbl, ' rename to upper(',cTbl,');');
PREPARE stmt1 FROM @sqlDrop;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1; FETCH cur1 INTO cTbl;
END WHILE;
CLOSE cur1;
END运行报错。。大家可以调试一下。不行
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE cTbl VARCHAR(64);
DECLARE cur1 CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='ww';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET @sqlDrop='';
OPEN cur1;
FETCH cur1 INTO cTbl;/*得到表名*/
WHILE done = 0 DO
SET @sqlDrop=CONCAT('alter table ',cTbl, ' rename to a',UCASE(cTbl));
SELECT @sqlDrop;
PREPARE stmt1 FROM @sqlDrop;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
FETCH cur1 INTO cTbl;
END WHILE;
CLOSE cur1;
END$$DELIMITER ;
set @sqlDrop=concat('alter table ',cTbl, ' rename to upper(',cTbl,');');
select @sqlDrop;
打印出来没错:alter table abc rename to upper(abc);但如果这样去执行则报错:PREPARE stmt1 FROM @sqlDrop;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
the right syntax to use near '(abc)' at line 1
mysql>
mysql> USE `ww`$$
Database changed
mysql>
mysql> DROP PROCEDURE IF EXISTS `uu`$$
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `uu`()
-> BEGIN
-> DECLARE done INT DEFAULT 0;
-> DECLARE cTbl VARCHAR(64);
-> DECLARE cur1 CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABL
ES WHERE TABLE_SCHEMA='ww';
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-> SET @sqlDrop='';
-> OPEN cur1;
-> FETCH cur1 INTO cTbl;/*得到表名*/
-> WHILE done = 0 DO
-> SET @sqlDrop=CONCAT('alter table ',cTbl, ' rename to a',UCASE(cTbl));
-> SELECT @sqlDrop;
-> PREPARE stmt1 FROM @sqlDrop;
-> EXECUTE stmt1;
-> DEALLOCATE PREPARE stmt1;
-> FETCH cur1 INTO cTbl;
-> END WHILE;
-> CLOSE cur1;
-> END$$
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> DELIMITER ;
mysql> CALL UU;
+--------------------------------------+
| @sqlDrop |
+--------------------------------------+
| alter table aaaatt rename to aAAAATT |
+--------------------------------------+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.03 sec)mysql>
MYSQL命令行下
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE cTbl VARCHAR(64);
DECLARE cur1 CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='lyblog';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET @sqlDrop='';
OPEN cur1;
FETCH cur1 INTO cTbl;/*得到表名*/
WHILE done = 0 DO
SET @sqlDrop=CONCAT('alter table ',cTbl, ' rename to ',UCASE(cTbl)); PREPARE stmt1 FROM @sqlDrop;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
FETCH cur1 INTO cTbl;
END WHILE;
CLOSE cur1;
END
我用这一段执行。但所有表名还是小写的。没有生效。。
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE cTbl VARCHAR(64);
DECLARE cur1 CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='lyblog';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET @sqlDrop='';
OPEN cur1;
FETCH cur1 INTO cTbl;/*得到表名*/
WHILE done = 0 DO
SET @sqlDrop=CONCAT('alter table ',cTbl, ' rename to ',upper(cTbl));
PREPARE stmt1 FROM @sqlDrop;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
FETCH cur1 INTO cTbl;
END WHILE;
CLOSE cur1;
END
再一次改进代码。。还是不行