'ALTER table' для всех таблиц в базе данных

Содержание

Вопрос или проблема
Ответ или решение
Как выполнить команду ALTER для всех таблиц в базе данных MySQL
Шаги для выполнения
Дополнительные советы
Заключение

Вопрос или проблема

Как мне выполнить следующее для каждой таблицы в базе данных:

ALTER table [table_name] type=innodb;

Я не хочу запускать это вручную для каждой таблицы, а хочу выполнить это для всех таблиц в базе данных.
К слову: если вам интересно, почему я это выполняю: http://bugs.mysql.com/bug.php?id=1341 & http://bugs.mysql.com/bug.php?id=1287

Предполагая, что у вас настроены учетные данные и информация для подключения в my.cnf, команда вроде этой может сделать то, что вам нужно.

mysql --batch --skip-column-names --execute= \
'select concat("alter table ",TABLE_SCHEMA,".",TABLE_NAME," type=innodb;") 
 from information_schema.TABLES where TABLE_SCHEMA="database_name"' | mysql

Замените database_name на имя вашей базы данных.

Что касается вашей главной проблемы. Похоже, вы хотите восстановить место после того, как ваши таблицы InnoDB стали большими. Как упоминалось в ошибке, единственный безопасный способ справиться с этим — использовать mysqldump для экспорта всего. Удалите файлы, а затем восстановите.

Как отмечает эта статья. Вы можете включить опцию innodb_file_per_table в my.cnf вашего сервера, чтобы каждый файл существовал как отдельный файл. Таким образом, восстановление места можно осуществить, удалив/восстановив проблемную таблицу, а не все сразу.

Что касается ответа Zoredache (который был очень полезен для меня, спасибо): –skip-column-names следует использовать для удаления заголовка.

mysql --batch --skip-column-names -e \ 
'select concat("alter table ",TABLE_SCHEMA,".",TABLE_NAME," type=innodb;")  
 from information_schema.TABLES where TABLE_SCHEMA="database_name"' | mysql

Я не знаю, на каком языке вы работаете, но, вероятно, вы просто можете выполнить:

show table status from `DATABASE`;

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

Ответ или решение

Как выполнить команду ALTER для всех таблиц в базе данных MySQL

Если вам необходимо изменить тип всех таблиц в вашей базе данных на InnoDB, то для этого не требуется вручную вводить команду для каждой таблицы. Вместо этого вы можете использовать SQL-запрос, который автоматически сгенерирует необходимые команды ALTER TABLE для всех таблиц в вашей базе данных. В этом ответе будет рассмотрен эффективный подход к решению данной задачи.

Шаги для выполнения

Подключение к базе данных: Убедитесь, что у вас есть необходимые параметры подключения к вашей базе данных. Это может быть сделано через конфигурационный файл my.cnf или напрямую через командную строку.
Использование SQL-команды: Вы можете использовать следующий SQL-запрос для генерации команд ALTER TABLE для всех таблиц вашей базы данных. Замените database_name на имя вашей базы данных:
```
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ENGINE=InnoDB;')
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'database_name';
```
Этот запрос обращается к information_schema.tables, что позволяет получить информацию о всех таблицах в указанной базе данных.
Исполнение сгенерированных команд: Для выполнения сгенерированных команд можно воспользоваться следующими командами в командной строке MySQL:
```
mysql --batch --skip-column-names -e \
'SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, ".", TABLE_NAME, " ENGINE=InnoDB;") 
 FROM information_schema.tables 
 WHERE TABLE_SCHEMA="database_name";' | mysql -u your_username -p database_name
```
Здесь your_username — это ваше имя пользователя MySQL, а -p запросит ввод пароля.
Важно учитывать: Использование опции ENGINE вместо type в командах ALTER TABLE является стандартом в MySQL, который вы должны придерживаться для изменения типа таблицы на InnoDB. В некоторых версиях MySQL возможны старые варианты написания, но для современного использования рекомендуется использовать именно ENGINE.

Дополнительные советы

Проверка состояния таблиц: После выполнения команд ALTER, рекомендуется проверить состояние таблиц, чтобы убедиться, что все изменения прошли успешно. Для этого выполните команду:
```
SHOW TABLE STATUS FROM database_name;
```
Резервное копирование данных: Прежде чем выполнять операции, связанные с изменением структуры таблиц, всегда рекомендуется создать резервную копию ваших данных. Копирование можно осуществить с помощью mysqldump.
Обновление конфигурации: Если у вас возникают проблемы с размерами таблиц в будущем, может быть полезно активировать опцию innodb_file_per_table, которая позволяет создавать отдельный файлы для каждой таблицы. Это облегчит процесс управления размерами и восстановление данных.

Заключение

Используя описанный подход, вы сможете автоматизировать процесс изменения типа всех таблиц в вашей базе данных на InnoDB без необходимости вводить команды для каждой таблицы вручную. Это обеспечит более быструю и эффективную работу с данными и снизит риск ошибок.

‘ALTER table’ для всех таблиц в базе данных