Как найти нужные таблицы и отобразить всю информацию в одном запросе SQL

Где-то месяц назад ко мне обратился коллега с просьбой помочь составить комплексный запрос (если это можно так назвать), который можно было бы скопировать и вставить в phpMyAdmin, для выборки всех данных из таблиц плагина, оставшихся после удаления сайта/сайтов из сети WordPress.

Что? Объясняю. Допустим, у нас есть сеть на WordPress, в которой имеется N число сайтов. Установлен сетевой плагин, который для каждого сайта создает собственную таблицу в базе данных. Таблицы имеют следующие имена: wp_table для первого сайта и wp_N_table для N-го сайта (например, wp_10_table для десятого сайта в сети). В нашем случае, сайт X, Y и Z были удалены, но таблицы остались. Задача: одним запросом получить данные из этих таблиц для дальнейшего анализа.

Я не считаю себя огромным специалистом SQL и мне данная задача изначально показалась если не невыполнимой, то уж точно не той, которую я мог бы решить за пару минут. Пришлось покопаться в сети и найти как такое решают другие. Ниже представлено мое видение потенциального решения данной проблемы. Не беру на себя ответственность утверждать, что это самое оптимальное решение, но оно позволило мне поближе познакомиться с процедурным расширением SQL.

Итак, приступим. Изначально я разбил задачу на несколько этапов:

1. Получить список всех таблиц от плагина
2. Найти «таблицы-сироты»
3. Получить данные из найденных таблиц

Чтобы всего этого добиться, мы будем использовать хранимые процедуры в SQL.

Хранимая процедура — объект базы данных, представляющий собой набор SQL-инструкций, который компилируется один раз и хранится на сервере. Хранимые процедуры очень похожи на обыкновенные процедуры языков высокого уровня, у них могут быть входные и выходные параметры и локальные переменные, в них могут производиться числовые вычисления и операции над символьными данными, результаты которых могут присваиваться переменным и параметрам. В хранимых процедурах могут выполняться стандартные операции с базами данных (как DDL, так и DML). Кроме того, в хранимых процедурах возможны циклы и ветвления, то есть в них могут использоваться инструкции управления процессом исполнения.

Базовый синтаксис для них следующий:

DELIMITER $$

CREATE PROCEDURE GetAllTables()
BEGIN
  ...
END

$$

CALL GetAllTables();

С помощью DELIMITER $$ мы задаем последовательность символов, которая будет завершать хранимую процедуру, без этого при наборе первой же строчки SQL будет исполнять набранный код. Данная последовательность может быть произвольной. В конце $$ укажет на завершение процедуры.

Создаем процедуру GetAllTables() с помощью CREATE PROCEDURE, она будет иметь начало BEGIN и конец END.

CALL GetAllTables(); — это выполнение всей процедуры.

В теле GetAllTables() мы объявляем переменные с помощью инструкции DECLARE, задаем тип и значение по умолчанию:

DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_table VARCHAR(100) DEFAULT "";

В переменной v_finished мы будем хранить статус обработки, в v_table — текущую таблицу.

Далее мы будем использовать курсор, чтобы осуществить построчную обработку нашего запроса. Задаем курсор table_cursor, который будет получать название таблиц из базы данных, соответствующих определенной маске.

DECLARE table_cursor CURSOR FOR
  SELECT table_name FROM information_schema.tables
  WHERE table_schema = 'local' AND table_name LIKE '%mp_product_attributes';

Указываем что мы будем делать, когда не найдем больше результатов. Обработчик ошибок объявляется следующим образом:

DECLARE action HANDLER FOR condition_value statement;

Здесь action может принимать значение CONTINUE или EXIT, которые указывают на то что нужно продолжить или прекратить исполнение кода при достижении определенных условий. В нашем случае условием является NOT FOUND (результатов больше нет), при достижении которого мы задаем значение переменной v_finished равной 1.

DECLARE CONTINUE HANDLER 
  FOR NOT FOUND SET v_finished = 1;

Теперь мы отобразим все таблицы, которые были найдены в базе данных по заданной маске. Для этого лишь надо выполнить SQL запрос:

SELECT table_name FROM information_schema.tables
  WHERE table_schema = 'local' AND table_name LIKE '%mp_product_attributes';

Наверное, повторение одного запроса здесь и выше, когда мы задавали курсор, является не самым оптимальным решением, но зато оно понятно для начинающих и не требует каких-то углубленных знаний SQL.

Далее мы откроем установленный раннее курсор и задаем цикл get_data, где будем последовательно присваивать переменной v_table результаты нашего запроса.

OPEN table_cursor;
  get_data: LOOP
    FETCH table_cursor INTO v_table;
    ...
  END LOOP get_data;
CLOSE table_cursor;

Проверяем закончилась ли выборка и есть ли еще результаты. Напоминаю, что если результатов нет, то переменная v_finished будет равна 1. Это мы задавали выше. Если больше результатов нет, то мы выходим из цикла.

IF v_finished = 1 THEN 
  LEAVE get_data;
END IF;

Здесь у меня возникла небольшая проблема: таблицы-то я нахожу, но как определить что именно данная таблица — это оставшаяся таблица от удаленного сайта. Как я писал выше, все таблицы в базе данных имеют вид wp_N_table. Нам лишь нужно получить значение N, присвоить его переменной v_id и посмотреть есть ли сайт с данным индексом в таблице wp_blogs. Но есть одно условие — у первого сайта в сети не будет индекса N, таблица будет иметь вид wp_table. Но, в то же время, сайт с индексом 1 все равно будет присутствовать в таблице wp_blogs. Чтобы избежать ошибок, мы сделаем небольшую проверку и установим v_id = 1, если N не будет задана в названии таблицы:

SET @v_id = substr(v_table, 4, length(v_table) - 25);

IF @v_id = '' THEN
  SET @v_id = 1;
END IF;

Теперь осталось самое простое — для всех таблиц, индекс которых мы не нашли в wp_blogs, нужно выполнить запрос выборки данных и отобразить все это пользователю:

IF (SELECT COUNT(*) FROM wp_blogs WHERE blog_id = @v_id) = 0 THEN
  SET @sql_query = concat("SELECT * FROM ", v_table);
  PREPARE stmt FROM @sql_query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END IF;

Чтобы подставить значение v_table в запрос, необходимо использовать команду concat, результат которой мы присваиваем переменной sql_query. Кстати, переменные в коде задаются с символом @ перед именем.

Подготавливаем запрос к исполнению PREPARE и исполняем EXECUTE.

Вот итоговый результат:

DELIMITER $$

DROP PROCEDURE IF EXISTS GetAllTables$$

CREATE PROCEDURE GetAllTables()
BEGIN
  DECLARE v_finished INTEGER DEFAULT 0;
  DECLARE v_table VARCHAR(100) DEFAULT "";

  DECLARE table_cursor CURSOR FOR
    SELECT table_name FROM information_schema.tables
    WHERE table_schema = 'local' AND table_name LIKE '%mp_product_attributes';

  DECLARE CONTINUE HANDLER 
    FOR NOT FOUND SET v_finished = 1;

  SELECT table_name FROM information_schema.tables
  WHERE table_schema = 'local' AND table_name LIKE '%mp_product_attributes';

  OPEN table_cursor;

  get_data: LOOP
    FETCH table_cursor INTO v_table;

    IF v_finished = 1 THEN 
      LEAVE get_data;
    END IF;

    SET @v_id = substr(v_table, 4, length(v_table) - 25);

    IF @v_id = '' THEN
      SET @v_id = 1;
    END IF;

    IF (SELECT COUNT(*) FROM wp_blogs WHERE blog_id = @v_id) = 0 THEN
      SET @sql_query = concat("SELECT * FROM ", v_table);
      PREPARE stmt FROM @sql_query;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
    END IF;

  END LOOP get_data;

  CLOSE table_cursor;

END$$

CALL GetAllTables();