ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 批量修改MyISAM存储引擎为Innodb存储引擎!

批量修改MyISAM存储引擎为Innodb存储引擎!

原创 Linux操作系统 作者:mysqldbd 时间:2011-03-16 13:35:18 0 删除 编辑
原来想批量修改的,但是报错如下:
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-01-19

  • 博文量
    35
  • 访问量
    79168