Conditional change of database engine

Question

I would like to change the table format of my database to InnoDB. But I only want the action to run if the table format is not InnoDB yet.

If you execute the following command, a time-intensive action is started again and again (similar to a repair), if the table format is already InnoDB:

ALTER TABLE `MyTable` ENGINE InnoDB;

Is there a condition, which you can insert for this case, so that the operation runs faster, if the target format is already InnoDB?

I'm thinking about something like:

ALTER TABLE `MyTable` ENGINE InnoDB WHERE ENGINE != 'InnoDB';
ALTER TABLE IGNORE `MyTable` ENGINE InnoDB;

May be this will help you [link](https://stackoverflow.com/questions/41631724/change-storage-engine-from-myisam-to-innodb-all-tables-at-once-using-phpmyad) — ArunKumar M N, Apr 02 '19 at 06:18
It's a beginning, but I can only ship an sql-File for the conversion (no script). So I need a way to directly execute the result. — SuperNova, Apr 02 '19 at 07:41

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

You can use information_schema.TABLES, to generate the script:

set @db_schema = 'test';
set @alter_query = 'alter table `{table}` engine=innodb;';

select group_concat(replace(
    @alter_query,
    '{table}',
    replace(t.TABLE_NAME, '`', '``')
) separator '\n') as script
from information_schema.TABLES t
where t.TABLE_SCHEMA = @db_schema
  and t.ENGINE = 'MyISAM';

Then you need to copy the result and execute it.

Demo

Update

If you need to execute it in one run, you can define a stored procedure with a cursor on information_schema.TABLES and execute it:

drop procedure if exists tmp_alter_myisam_to_innodb;
delimiter //
create procedure tmp_alter_myisam_to_innodb(in db_name text)
begin
    declare done int default 0;
    declare tbl text;
    declare cur cursor for 
        select t.TABLE_NAME
        from information_schema.TABLES t
        where t.TABLE_SCHEMA = db_name
          and t.ENGINE = 'MyISAM';
    declare continue handler for not found set done = 1;
    open cur;
    fetch_loop: loop
        fetch cur into tbl;
        if done then
            leave fetch_loop;
        end if;
        set @stmt = 'alter table `{table}` engine=innodb;';        
        set tbl = replace(tbl, '`', '``');
        set @stmt = replace(@stmt, '{table}', tbl);
        prepare stmt from @stmt;
        execute stmt;
        deallocate prepare stmt;
    end loop;
    close cur;
end //
delimiter ;

call tmp_alter_myisam_to_innodb('my_db');
drop procedure tmp_alter_myisam_to_innodb;

"Then you need to copy the result and execute it." is not possible. I've only one sql-file and cannot execute anything manually. SELECT [..] INTO FILE works, but SOURCE command is not available. — SuperNova, Apr 03 '19 at 10:27

Conditional change of database engine

1 Answers1

Update