原来想批量修改的，但是报错如下：
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 start1 !';
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 语句提出
来，然后再命令行里面一个个手动执行。
|
