Массовое удаление (DROP) таблиц БД MySQL по префиксу

При работе с разными CMS часто встречается задача удалить в БД MySQL сразу большое количество таблиц с одним префиксом. Особенно в том случае, когда одна БД используется несколькими системами со своими префиксами.

Оставлю здесь для себя и посетителей несколько вариантов

Вариант 1

Этот способ находится на разных ресурсах в сети. Надо выполнить запрос, подставив свой prefix, который вернет текст уже нового запроса на удаление таблиц:

  SELECT GROUP_CONCAT('DROP TABLE ', table_name, ';' SEPARATOR '') AS statement
  FROM information_schema.tables
  WHERE table_name LIKE 'prefix\_%';

Вывод обязательно проверьте глазами. GROUP_CONCAT обрезает его по длине переменной group_concat_max_len (умолчание - 1024). Поэтому уже с 20-25 таблицами вы получите ошибочный запрос.

Учесть это можно вот таким образом:

  SET SESSION group_concat_max_len = 1000000;
  SELECT GROUP_CONCAT('DROP TABLE ', table_name, ';' SEPARATOR '') AS statement
  FROM information_schema.tables
  WHERE table_name LIKE 'prefix\_%';

Полученный вывод - это запрос на удаление искомых таблиц, который надо выполнить любым способом в MySQL. Удобнее всего этот способ для использующих веб-интерфейс (например phpMyAdmin) или оконные клиенты (например, MySQL Workbench).

Тем не менее, вывод запроса длиннее max_allowed_packet (умолчание 4Gb) получить и запустить не выйдет. Но, думаю, четырёх гигабайт хватит каждому. :)

Вариант 2

Просто подрихтовал исходный вариант по своему вкусу.

  SELECT CONCAT('DROP TABLE IF EXISTS `', GROUP_CONCAT(table_name, '`, `' SEPARATOR ''), '`;') AS statement
  FROM information_schema.tables
  WHERE table_name LIKE 'prefix\_%';

Достоинства этой версии понятны:

  • сервер получает один запрос, а не насилуется пачкой из сотни DROP-ов, потому отработает быстрее;
  • код раза в два короче, соответственно, в ограничение 1024 символа уместится раза в два больше таблиц;
  • код не выбросит ошибки когда длина строки превысит group_concat_max_len;
  • если ограничения превышены то после выполнения первого запроса, можно просто сгенерировать новый запрос, поскольку вместившиеся в него таблицы уже будут удалены, их место займут оставшиеся;
  • имена таблиц защищены кавычками `` на случай совпадения имен таблиц с ключевыми словами SQL;

Способ также подходит для использующих веб-интерфейс (например, phpMyAdmin) или оконные клиенты (например, MySQL Workbench).

Вариант для BASH

Тут никаких ограничений нет. В командную строку (CLI) интерпретатора bash выводится простой список таблиц. Запрос DROP TABLE формируется командами оболочки и снова выполняется клиентской утилитой mysql.

echo "show tables like 'PREFIX\_%'"   | \ # Составляем запрос для получения списка таблиц. Замените PREFIX\_ на требуемый вам.
mysql -N -u USER -pPASS DATABASE      | \ # Отправляем запрос в MySQL. Опция -N обрезает вывод заголовка столбца.
sed '/^.\+$/!d'                       | \ # Удаляем пустые строки на всякий случай.
sed 's/.\+/`&`/'                      | \ # Заключаем имена таблиц в защитные кавычки ``.
sed ':a;N;$!ba;s/\n/,/g'              | \ # Это sed-ово колдунство склеивает все строки в одну через запятую.
sed 's/^.\+/DROP TABLE IF EXISTS &;/' | \ # Дописываем SQL-запрос.
mysql -u USER -pPASS DATABASE             # Отправляем запрос в MySQL на исполнение.
# В 3-й и 7-й строках укажите свои параметры подключения к БД.
# USER - имя пользователя в MySQL.
# PASS - пароль пользователя (между -p и PASS пробел не нужен). Можно оставить просто пустую опцию -p и ввести пароль дважды.
# DATABASE - имя базы данных, с которой вы работаете.

Команда bash на удаление таблиц одной строкой.
echo "show tables like 'PREFIX\_%'" | mysql -N -u USER -pPASS DATABASE | sed '/^.\+$/!d' | sed 's/.\+/`&`/' | sed ':a;N;$!ba;s/\n/,/g' | sed 's/^.\+/DROP TABLE IF EXISTS &;/' | mysql -u USER -pPASS DATABASE

Примечание. Символ _ (подчеркивание) в операторе LIKE является специальным и означает один любой символ, поэтому экранируется \ (обратный слэш). В некоторых оболочках Linux обратный слэш тоже может интерпретироваться специальным образом и потребует второго \ перед ним. Учитывайте это и проверяйте, что передает echo в mysql.