Хранимые процедуры MySQL, локальные и пользовательские переменные

Не всегда удобно умещать необходимые действия над СУБД в сложные sql запросы. Иногда в одном запросе действия могут конфликтовать друг с другом, да и по синтаксису легче разбить логику на более простые запросы. Тут могут помочь хранимые процедуры mysql, а также функции. Рассмотрим примеры хранимых процедур.

Есть две таблицы — ответов и голосов за них. Необходимо удалить отдельные голоса у ответов по определенным условиям. Ситуация выполнить это одним запросом осложнена во-первых тем, что поля в таблице ответов обновляются триггерами, при удалении записей из таблицы голосов. Во-вторых в таблице голосов десятки миллионов записей. Напишем такую хранимую процедуру:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `dorepeat_delete_votes`(IN `p1` INT)
    NO SQL
BEGIN
    SET @x = 0;
    SET @id = 0;
    SET @c = 0;
    REPEAT
        SELECT app_vote.id FROM app_vote WHERE app_vote.answer_id = (SELECT answers.id FROM answers WHERE answers.id = app_vote.answer_id AND answers.quality >= 2) AND app_vote.useless = 1 LIMIT 1 INTO @id;
        SET @id = IFNULL(@id, 0);
        DELETE FROM app_vote WHERE id = @id;
        SET @c = (SELECT ROW_COUNT() + @c);
        SET @x = IF(@id > 0, @x + 1, p1);
    UNTIL @x >= p1 END REPEAT;
    SELECT @c AS deleted;
END$$
DELIMITER ;

Здесь мы используем пользовательские переменные и оператор REPEAT — это аналог конструкции do…while в других языках. Процедура принимает входящий параметр — максимальное число итераций цикла. Также итерации будут остановлены по условию отсутствия удаленных записей. Таким образом при вызове процедуры мы можем контролировать процесс.

CALL `dorepeat_delete_votes`(1000);

Простейший пример процедуры с циклом из документации:

mysql> delimiter //

mysql> CREATE PROCEDURE dorepeat(p1 INT)
       BEGIN
         SET @x = 0;
         REPEAT
           SET @x = @x + 1;
         UNTIL @x > p1 END REPEAT;
       END
       //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL dorepeat(1000)//
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

Другой вариант аналогичной по функционалу процедуры:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `del_votes`()
    NO SQL
BEGIN
    DECLARE i INTEGER DEFAULT 0;
    DECLARE n INTEGER DEFAULT 0;
    SELECT app_vote.id FROM app_vote WHERE app_vote.answer_id = (SELECT answers.id FROM answers WHERE answers.id = app_vote.answer_id AND answers.quality >= 2) AND app_vote.useless = 1 LIMIT 1 INTO i;
    WHILE i > 0 DO
    	DELETE FROM app_vote WHERE id = i;
        SELECT app_vote.id FROM app_vote WHERE app_vote.answer_id = (SELECT answers.id FROM answers WHERE answers.id = app_vote.answer_id AND answers.quality >= 2) AND app_vote.useless = 1 LIMIT 1 INTO i;
        SET n = n + 1;
    END WHILE;
    SELECT n;
END$$
DELIMITER ;

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