How Can I Efficiently Convert All MyISAM Tables to InnoDB in MySQL?

How to Efficiently Convert All MyISAM Tables to InnoDB

Many MySQL users encounter the need to convert their MyISAM tables to InnoDB. While the individual 'ALTER TABLE' command can be used for this purpose, the process can be tedious for large databases. This article provides a more efficient solution for bulk conversion of MyISAM tables to InnoDB.

To retrieve a list of all MyISAM tables in the current database:

SET @DATABASE_NAME = 'name_of_your_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;

This query will generate a series of 'ALTER TABLE' statements for each MyISAM table. Once the output is copied, it can be executed as a new SQL query to perform the bulk conversion.

By following these steps, users can quickly and efficiently convert all MyISAM tables in their database to InnoDB, enhancing the performance and durability of their data storage.

The above is the detailed content of How Can I Efficiently Convert All MyISAM Tables to InnoDB in MySQL?. For more information, please follow other related articles on the PHP Chinese website!