0

In phpMyAdmin the code below just outputs a list of sql statements for each table:

SET @DATABASE_NAME = 'my_db';
SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

How would I actually execute this to change all the tables? I've seen people say "batch" but I'm not sure if that is what I'm looking for.

2 Answers 2

0

It is actually easiest to simply copy&paste the SQL statements. Don't bother with trying to find and "eval".

It is even easier in the mysql commandline tool.

Here are my tips on gotchas that sometimes occur in the conversion from MyISAM to InnoDB: http://mysql.rjweb.org/doc.php/myisam2innodb

0

You can get the SQL DDL statements created in a file, like this (with Windows 11, phpMyAdmin 5.2.1 connecting to MySQL 8):

SET @DATABASE_NAME = 'my_db';
SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC 
INTO OUTFILE 'x:/mysql_scripts/my_output.sql';

This can be run from SQL tab of the phpMyAdmin or from mysql command-line client.

It creates a DDL script file with all the ALTER TABLE... statements. Now, run the file from the mysql client (I could not get it to run from the phpMyAdmin):

source x:/mysql_scripts/my_output.sql

This runs all the DDL statements which alters all tables as expected.

You can also copy and paste all the statements from the DDL script file into the phpMyAdmin and execute them as SQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.