How to convert all tables from MyISAM into InnoDB?

Scripting the conversion: Working smart, not hard!

The beauty of the above SQL script lies in its capacity to automate the conversion process. It constructs ALTER TABLE commands for each MyISAM table in your specific database, saving you the time and potential errors associated with manual conversion.

Command line magic for MySQL enthusiasts

Command-line lovers can also use the power of the MySQL CLI to rapidly convert all MyISAM tables:

mysql -u username -ppassword -D databasename -e "SHOW TABLE STATUS WHERE Engine = 'MyISAM'" | 
awk '{ if(NR>1) print "ALTER TABLE `" $1 "` ENGINE=InnoDB;"}' | 
mysql -u username -ppassword -D databasename


Replace username, password, and databasename with your MySQL credentials and desired database name. This one-liner performs three tasks in quick succession:

1. Lists MyISAM tables.
2. Appends the ALTER TABLE command to each table with awk (or as I like to call it - table sorcery 🔮).
3. Executes the ALTER TABLE commands within your target database (feel the power 💪💫).

Dealing with views and special characters: Keeping it tidy!

Remember, information_schema also includes views. No, not the kind you get from a high-rise! These are not actual tables, so they mustn't appear in your ALTER TABLE commands. Luckily, our script filters these out using the 'BASE TABLE' distinction.

Make sure to handle special characters in table names – treat them like VIPs and enclose them with backticks `

SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` ENGINE=InnoDB;') 
FROM information_schema.tables 
WHERE engine = 'MyISAM' AND table_schema = 'input_your_database_here' AND `TABLE_TYPE` = 'BASE TABLE';


In a PHP environment? We got you covered!

For the PHP developers out there, you can handle batch conversions directly without leaving the comfort of your favorite IDE:

$pdo = new PDO('mysql:host=hostname;dbname=databasename', 'username', 'password');
$stmt = $pdo->query("SELECT table_name FROM information_schema.tables WHERE engine = 'MyISAM' AND table_schema = 'databasename'");
while ($row = $stmt->fetch()) {
    $pdo->exec("ALTER TABLE `{$row['table_name']}` ENGINE=InnoDB");  // Here we go again 🎢
}


Ensure you update hostname, databasename, username, and password with your credentials. Also, remember to handle exceptions—unlike my ex, they won't just ignore you!

Hitchhiker's guide to possible pitfalls

All aboard the Foreign key express 🚂

InnoDB's foreign key constraints are stricter compared to MyISAM. Ensure your foreign keys are compatible before they embark on the InnoDB express.

Full-text search, lost and found 🕵️

InnoDB handles full-text search indexes differently from MyISAM, requiring you to rebuild these indexes after the conversion – no one likes a broken search function!

One size does not fit all ❗

Some MyISAM tables may have specific storage settings which do not apply to InnoDB – customize them post-conversion to keep your tables from feeling homesick.

Size matters (for databases) 💽

If your database size would make a data Blu-ray blush, expect the conversion to take time and put significant load on the server. No one wants a server meltdown, so consider off-peak hours or incremental conversion steps.