mysql 存储过程 修改表属性_mysql存储过程——修改数据库的全部表的存储引擎

BEGIN

DECLARE bianliang varchar(128);

DECLARE done boolean default 0;

-- 定义游标

DECLARE Param_Cursor CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES AS t WHERETABLE_SCHEMA = 'testyinqing' AND TABLE_TYPE = 'BASE TABLE';

-- 解决mysql Bug:no data - zero rows fetched,selected,or processed

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; -- 设置游标的终止条件

OPEN Param_Cursor; -- 打开游标

-- 遍历所有的行

FETCH NEXT FROM Param_Cursor INTO bianliang;-- 获取第一条数据存入临时变量中

REPEAT

BEGIN

START TRANSACTION;-- 开始事务

SET @mytabl = CONCAT('ALTER TABLE ',bianliang,' ENGINE=InnoDB ');

PREPARE stmt from @mytabl;

EXECUTE stmt;

-- 获取下一条数据存入临时变量中

FETCH NEXT FROM Param_Cursor INTO bianliang;

COMMIT;-- 提交事务

END;

UNTIL done END REPEAT; -- 结束循环

CLOSE Param_Cursor; -- 关闭游标

END