mysql批量修改表存储引擎_批量修改MyISAM存储引擎为Innodb存储引擎！

原来想批量修改的，但是报错如下：

mysql> use information_schema;

Database changed

mysql> update tables set engine='InnoDB' where table_schema in('bak','dms','DBTEST','access','csf','test');

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

后来上网找资料，别人告诉我alter table tbname engine='innodb'; 也是要drop table tb，create table tb select * from tb;

看来没有办法，只好写存储过程，一个个来修改了，存储过程如下：

delimiter $$

use `test` $$

drop procedure if exists proc_alter_engine_all $$

use `test` $$

create procedure proc_alter_engine_all()

SQL SECURITY INVOKER

begin

declare v_tbname varchar(500) default '';

declare v_schema varchar(500) default '';

declare v_flag int default 0;

declare _done int default 0;

declare v_sqlcounts varchar(500) default '';

declare cur_tbnames cursor for

select table_schema,table_name

from information_schema.tables

where table_schema not in('information_schema','mysql','test') and engine='MyISAM' order by table_schema, table_name limit 3;

declare continue handler for not found

begin

set v_flag=1;

set _done=1;

select 'continue handler not found';

commit;

end;

-- handler exception

declare exit handler for sqlexception

begin

set v_flag=0;

set _done=1;

select 'exit handler sqlexception';

end;

open cur_tbnames;

select ' cur start  1 !';

allcode:loop

fetch cur_tbnames into v_schema, v_tbname;

if not _done then

set v_sqlcounts = '';

-- 修改表的存储引擎

set v_sqlcounts = concat('alter table ',v_schema,'.',v_tbname, ' engine=\'innodb\'');

set @sqlcounts := v_sqlcounts;

prepare stmt from @sqlcounts;

execute stmt ;

deallocate prepare stmt;

-- select v_sqlcounts;

else

leave allcode;

end if;

select v_schema, v_tbname;

end loop allcode;

close cur_tbnames;

select ' cur end !';

end

这里面有一个小瑕疵，就是如果表上千了的话，用游标遍历会很慢的，特别是有的表有几千万数据记录的情况下。

这个时候不能在存储过程里面遍历游标的时候，进行alter table tb engine=‘innodb’；可以将alter 语句提出

来，然后再命令行里面一个个手动执行。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25267658/viewspace-689597/，如需转载，请注明出处，否则将追究法律责任。