Converting table engine in MySQL

Sometimes you need to convert the table engine MySQL in an existing database.

For example, I needed to create relationships between tables. To create them, the tables must be InnoDB (to support foreign keys).

Old Wordpress (and other) projects use MyISAM because until version MySQL 5.5.4 it was the default engine. But it doesn't support many useful features, such as foreign keys, transactions, etc. All these features are supported by the more modern engine InnoDB, which is now used by default.

But this is not a big problem, as tables can be converted. To convert a single table, it is enough to use the SQL query (which can be executed, for example, through phpmyadmin or the mysql command line):

ALTER TABLE table_name ENGINE=InnoDB;

If you need to convert the engine for all tables in the database, you can get a list of queries as follows:

SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') as ExecuteTheseSQLCommands
FROM information_schema.tables WHERE table_schema = 'db_name'
AND ENGINE != 'InnoDB' 
ORDER BY table_name DESC;

And then execute them (via phpmyadmin or the command line).

Or do it in one command using bash and the MySQL interpreter (using the console):

echo "SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') as ExecuteTheseSQLCommands FROM information_schema.tables WHERE table_schema = 'database_name' AND ENGINE != 'InnDB' ORDER BY table_name DESC;" | mysql -uroot -p1111| tail -n +2 | mysql -uroot -p1111 -Ddatabase_name

The example is a bit confusing, so let's break it down.

The first part:

echo "SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') as ExecuteTheseSQLCommands FROM information_schema.tables WHERE table_schema = 'database_name' AND ENGINE != 'InnDB' ORDER BY table_name DESC;" | mysql -uroot -p1111

sends the query to the mysql interpreter through a pipe, note the -u and -p options - these are the user and password for the database.

The result will be, for example, the following:

ExecuteTheseSQLCommands
ALTER TABLE users ENGINE=InnoDB;
ALTER TABLE templates ENGINE=InnoDB;
ALTER TABLE source_lists ENGINE=InnoDB;
ALTER TABLE roles ENGINE=InnoDB;
ALTER TABLE column_properties ENGINE=InnoDB;
ALTER TABLE cached_sources ENGINE=InnoDB;

The first line is the field name. To pass this result to the mysql interpreter, we remove the first line:

 | tail -n +2

Then we pass it to mysql again:

| mysql -uroot -p1111 -Ddatabase_name

Here you need to specify the name of the database, as it is not present in the generated queries.

In the examples, the conversion was to InnoDB, of course, you can replace it with the type you need.