Is it possible to convert several tables from Myisam to Innodb at the same time?

Asked

Viewed 941 times

2

Need to convert multiple tables created with Myisam engine to Innodb engine.

There are about 250 tables and I wonder if it is possible to convert all of them at once by mysql command line or by phpmyadmin. Converting I know is possible, but doing it one at a time would be very laborious. Does anyone know if it is possible? And if it is, how could it be done?

mysql

3 answers

1

It is possible with a single bash line:

for t in `mysql MEU_BD -Bse 'show tables;'`; do mysql MEU_BD -e "alter table $t engine=InnoDB;"; done

0

You can try this (1 table per line, not forgetting the ;)

ALTER TABLE t1 ENGINE = InnoDB;
ALTER TABLE t2 ENGINE = InnoDB;
ALTER TABLE t3 ENGINE = InnoDB;
[..]

0

I wrote the stored file below that does what you need, recommend testing WELL it before in development or certification environment! I am not responsible for any problems =)

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `convert_tables`(IN schema_name VARCHAR(255))
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE t VARCHAR(255);
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = schema_name AND engine = 'MyISAM' INTO n;
SET i=0;
WHILE i < n DO 
       SELECT table_name FROM information_schema.tables WHERE table_schema = schema_name AND engine = 'MyISAM' LIMIT i,1 INTO t;

        SET @alter_sql = concat('alter table ',t,' ENGINE = InnoDB;');

        PREPARE stmt FROM @alter_sql;

        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET i = i + 1;
END WHILE;
END

To run a stored trial:

CALL convert_tables('teste');

Where 'test' is the name of the schema where your tables are.

Explanation:

Procedure initially calculates the number of tables with engine = 'Myisam' and assigns this value to variable n.

Then we iterate in each row of the above search extracting the table name for the variable t.

Having the table name in t, just run the command ALTER TABLE t ENGINE = InnoDB, however, it is not possible to directly execute the above command because Mysql does not allow using variables as placeholdes for tables. As a workaround, we need to build the above command into a string and create a statement from it, after that just run it.

Sources:

https://stackoverflow.com/questions/2754423/use-a-variable-for-table-name-in-mysql-sproc

https://stackoverflow.com/questions/5817395/how-can-i-loop-through-all-rows-of-a-table-mysql

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.