Конвертация движка таблицы в MySQL

Иногда нужно конвертировать движок таблицы MySQL в уже существующей базе данных.

Например, мне понадобилось создать связи между таблицами. Для их создания таблицы должны быть InnoDB (чтобы поддерживать foreign key).

Старые Wordpress (и не только) проекты используют MyISAM, потому что до версии MySQL 5.5.4 это был движок по умолчанию. Но он не поддерживает множество полезных функций, таких как внешние ключи, транзакции и т.д. Все эти функции поддерживает более современный движок InnoDB, который теперь используется по умолчанию.

Но это не есть большой проблемой, так как таблицы можно конвертировать. Для конвертирования одной таблицы достаточно SQL запроса (который можно выполнить, например, через phpmyadmin или командную строку mysql):

ALTER TABLE table_name ENGINE=InnoDB;
 

Если надо конвертировать движок всех таблицы в базе, тогда можно получить список запросов следующим образом:

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;

А потом уже их исполнить (Через тот же phpmyadmin или командную строку).

Или сделать одной командой с помощью bash и MySQL интерпретатора (используя консоль):

 

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

Пример слегка запутан, поэтому разберем его детально.

Первая часть

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

передает через пайп запрос mysql интерпретатору, обратите внимание на опции -u и -p - это пользователь и пароль до БД.

Результат будет, например, следующий:

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;

Первая строка - это название поля. Для того, чтобы этот результат передать mysql интерпретатору, удаляем первую строку:

 | tail -n +2

Дальше все это передаем опять mysql:

| mysql -uroot -p1111 -Ddatabase_name

Здесь уже нужно указать имя базы, так как его нету в сгенерированных запросах.

В примерах везде была конвертация в InnoDB, естественно, вы можете заменить на нужный вам тип.