I need to convert several tables created with MyISAM engine to InnoDB engine.
There are about 250 tables and I would like to know if you can convert them all at once through the mysql command line or phpmyadmin. Converting I know it's possible, but doing this one at a time would be very labor intensive. Does anyone know if it is possible? And if so, how could this be done?
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
You can try this (1 table per line, not forgetting ; )
ALTER TABLE t1 ENGINE = InnoDB;
ALTER TABLE t2 ENGINE = InnoDB;
ALTER TABLE t3 ENGINE = InnoDB;
[..]
I wrote the stored procedure below that does what you need, I recommend testing WELL it before in development environment or homologation! 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 the stored procedure:
CALL convert_tables('teste');
Where 'test' is the name of the schema where your tables are.
Explanation:
The procedure initially calculates the number of tables with engine = 'MyISAM' and assigns this value to the variable n.
Then we iterate through each line of the above search by extracting the table name for the variable t.
Having the table name in t, just run the ALTER TABLE t ENGINE = InnoDB command, however, you can not directly execute the above command because MySQL does not allow you to use variables as placeholdes for tables. As a workaround, we need to construct the above command in a string and create a statement from it, after that we just execute it.
Sources:
p>