mysql使用存储过程遍历表_mysql存储过程遍历表的实例代码

本节内容:

mysql存储过程，遍历表。

sql代码：

复制代码 代码示例:

DELIMITER $$

/*更改discuz数据库中每个表的字符集为utf8，校对规则为utf8_bin */

DROP PROCEDURE IF EXISTS `discuz`.`qifei` $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `qifei`()

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE a VARCHAR(20);

DECLARE rs CURSOR FOR

SELECT `TABLE_NAME` FROM information_schema.`TABLES` where `TABLE_SCHEMA`='discuz';

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

open rs;

REPEAT

FETCH rs INTO a;

SET @s = CONCAT('

ALTER TABLE ',a,'

DEFAULT CHARACTER SET utf8

DEFAULT COLLATE utf8_bin');

PREPARE stmt FROM @s;

EXECUTE stmt;

UNTIL done END REPEAT;

CLOSE rs;

END $$

DELIMITER ;

知识点：变量定义、预处理语句、系统表、游标、循环语句、字符串函数。