Хранимая процедура, которая принимает запрос в качестве параметра

Question

Я пытаюсь написать хранимую процедуру MySQL, которая принимает SELECT запрос и выполняет его по списку баз данных. Это возможно даже с MySQL?

Каждый клиент в нашем приложении имеет свою собственную базу данных в одном экземпляре. Все базы данных клиентов идентичны по своей структуре схемы. Иногда мне нужно выполнить простой запрос SELECT, но по всем базам данных клиентов (например, SELECT COUNT(*) FROM users).

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

Мое текущее решение состоит в том, что у меня есть node скрипт, который я запускаю локально, чтобы генерировать запросы SELECT для каждой базы данных, а затем объединять их все, используя UNION генерировать гигантский запрос, который я затем выполняю на экземпляре базы данных.

SELECT 'customerdb1' AS customer,
       COUNT(*) AS user_count
FROM customerdb1.users
UNION
SELECT 'customerdb2' AS customer,
       COUNT(*) AS user_count
FROM customerdb2.users
UNION
SELECT 'customerdb3' AS customer,
       COUNT(*) AS user_count
FROM customerdb3.users

Answer 1

Вы можете сделать это в хранимой процедуре с помощью PREPARE и EXECUTE, но это считается уязвимостью безопасности для запуска произвольного SQL таким способом. Вы сказали, что процедура будет использоваться только специальным аккаунтом, но просто позволить существовать процедуре - это риск. Что если привилегии изменены и позволяют кому-либо запускать процедуру?

Также будет довольно медленно выполнять этот запрос по многим схемам, потому что MySQL выполняет только один поток на запрос. Он должен будет выполнять запросы последовательно, собирая результат во временную таблицу.

Также у вас есть ограничение на длину любого отдельного запроса SQL, который max_allowed_packet, Это должно быть довольно большим, но вы все равно можете превысить длину, если у вас достаточно терминов UNION.

Я работал над сайтом, подобным описанному вами, где было много схем, по одной на клиента, с одинаковыми таблицами. Когда мы хотели выполнить запрос по всем схемам, я запускал простой запрос (нет UNION) во многих параллельных потоках и собирать результаты в коде приложения.

---

См. https://thedailywtf.com/articles/For-the-Ease-of-Maintenance для интересного рассказа о хранимых процедурах, которые позволяют вводить произвольный SQL.