BatSchool · Хранимые процедуры, функции и триггеры

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

Кнопка, функция и датчик

На кофемашине есть кнопка «капучино». Ты нажимаешь одну кнопку, а машина выполняет десяток шагов: мелет зёрна, греет воду, взбивает молоко. Это хранимая процедура: последовательность команд в базе, которую запускают по имени.

Рядом весы в магазине: кладёшь яблоки, получаешь цену. Это хранимая функция: принимает значения, возвращает одно значение, и её можно вызвать прямо внутри SELECT.

А в подъезде датчик движения: никто его не включает, свет загорается сам, когда кто-то вошёл. Это триггер: код, который сервер запускает сам при изменении строк таблицы.

DELIMITER: чем кончается команда

Тело процедуры состоит из нескольких команд, и каждая заканчивается точкой с запятой. А клиент mysql отправляет текст серверу, как только встречает ;. Он отрежет процедуру на первой же внутренней команде.

Поэтому перед созданием процедуры клиенту говорят: «пока команды кончаются на //». Это клиентская команда DELIMITER, сервер её никогда не видит. После процедуры возвращаем привычную ;.

DELIMITER //
CREATE PROCEDURE film_schedule(IN p_film_id INT)
BEGIN
  SELECT s.id, s.starts_at, h.name AS hall, s.price
  FROM screenings s JOIN halls h ON h.id = s.hall_id
  WHERE s.film_id = p_film_id
  ORDER BY s.starts_at;
END //
DELIMITER ;

Query OK, 0 rows affected (0.00 sec)

BEGIN и END обрамляют тело, IN p_film_id INT это входной параметр. Приставка p_ помогает не перепутать параметр с колонкой таблицы.

CALL и параметры

Процедуру запускают командой CALL:

CALL film_schedule(1);

+----+---------------------+----------------+--------+
| id | starts_at           | hall           | price  |
+----+---------------------+----------------+--------+
|  1 | 2026-10-23 19:00:00 | Большой        | 450.00 |
|  2 | 2026-10-24 21:30:00 | Малый          | 350.00 |
+----+---------------------+----------------+--------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Два ответа: таблица от SELECT внутри и Query OK от самой процедуры. Число аргументов сервер проверяет строго:

CALL film_schedule();

ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE kino.film_schedule; expected 1, got 0

Теперь процедура посерьёзнее: продажа билета с проверкой мест. Она принимает сеанс, зрителя и место, а номер нового билета отдаёт через параметр OUT:

DELIMITER //
CREATE PROCEDURE sell_ticket(IN p_screening INT, IN p_viewer INT, IN p_seat VARCHAR(4), OUT p_ticket INT)
BEGIN
  DECLARE v_sold INT;
  DECLARE v_seats INT;
  SELECT COUNT(*) INTO v_sold FROM tickets WHERE screening_id = p_screening AND status <> 'refunded';
  SELECT h.seats INTO v_seats FROM screenings s JOIN halls h ON h.id = s.hall_id WHERE s.id = p_screening;
  IF v_sold >= v_seats THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Зал заполнен';
  END IF;
  INSERT INTO tickets (screening_id, viewer_id, seat, sold_at) VALUES (p_screening, p_viewer, p_seat, NOW());
  SET p_ticket = LAST_INSERT_ID();
END //
DELIMITER ;

Разберём по шагам:

DECLARE заводит две локальные переменные, они живут только внутри вызова.
SELECT ... INTO кладёт результат запроса в переменную: сколько билетов продано и сколько мест в зале.
IF сравнивает. Если зал полон, SIGNAL прерывает процедуру ошибкой с нашим текстом.
INSERT продаёт билет, LAST_INSERT_ID() отдаёт его номер в параметр p_ticket.

Номер нужно куда-то принять. Для этого в вызов передают пользовательскую переменную с собачкой:

CALL sell_ticket(5, 6, 'D1', @ticket);
SELECT @ticket;

Query OK, 1 row affected (0.00 sec)

+---------+
| @ticket |
+---------+
|      12 |
+---------+
1 row in set (0.00 sec)

Егор Ёлкин купил первый билет, номер 12. Повторный вызов с тем же местом упрётся в ключ uq_ticket_seat, и ошибка изнутри процедуры дойдёт до клиента как есть:

CALL sell_ticket(5, 6, 'D1', @ticket);

ERROR 1062 (23000): Duplicate entry '5-D1' for key 'tickets.uq_ticket_seat'

Функция

Функция считает цену со скидкой. Скидка больше 50 процентов считается ошибкой кассира, и функция её не даёт:

DELIMITER //
CREATE FUNCTION price_with_discount(p_price DECIMAL(7, 2), p_percent INT)
RETURNS DECIMAL(7, 2)
DETERMINISTIC
BEGIN
  IF p_percent < 0 OR p_percent > 50 THEN
    RETURN p_price;
  END IF;
  RETURN ROUND(p_price * (100 - p_percent) / 100, 2);
END //
DELIMITER ;

SELECT id, price, price_with_discount(price, 15) AS student, price_with_discount(price, 90) AS too_much FROM screenings WHERE id <= 3;

+----+--------+---------+----------+
| id | price  | student | too_much |
+----+--------+---------+----------+
|  1 | 450.00 |  382.50 |   450.00 |
|  2 | 350.00 |  297.50 |   350.00 |
|  3 | 400.00 |  340.00 |   400.00 |
+----+--------+---------+----------+
3 rows in set (0.00 sec)

RETURNS задаёт тип результата, RETURN возвращает значение. Слово DETERMINISTIC обещает: одинаковые аргументы, одинаковый результат. Без такой пометки функция не создастся:

DELIMITER //
CREATE FUNCTION film_age(p_year SMALLINT) RETURNS INT
BEGIN
  RETURN YEAR(CURDATE()) - p_year;
END //
DELIMITER ;

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Причина в бинарном журнале, куда сервер записывает все изменения для реплик. Про него будет урок о репликации. Серверу надо знать, даст ли функция на реплике тот же результат. Но не ставь DETERMINISTIC на film_age ради тишины: она зависит от сегодняшней даты, и обещание было бы враньём. Честная пометка здесь NO SQL: функция не читает и не меняет таблицы.

Триггер AFTER: журнал изменений

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

CREATE TABLE ticket_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT NOT NULL,
  old_status VARCHAR(10),
  new_status VARCHAR(10),
  changed_at DATETIME NOT NULL
);

DELIMITER //
CREATE TRIGGER tickets_status_log
AFTER UPDATE ON tickets
FOR EACH ROW
BEGIN
  IF OLD.status <> NEW.status THEN
    INSERT INTO ticket_log (ticket_id, old_status, new_status, changed_at)
    VALUES (NEW.id, OLD.status, NEW.status, NOW());
  END IF;
END //
DELIMITER ;

AFTER UPDATE ON tickets значит «после изменения строки в tickets», FOR EACH ROW значит «для каждой строки отдельно». Внутри доступны две версии строки: OLD до изменения и NEW после.

Отметим, что сеанс 3 прошёл и билеты использованы, а потом повторим то же для одного билета:

UPDATE tickets SET status = 'used' WHERE screening_id = 3;
UPDATE tickets SET status = 'used' WHERE id = 5;
SELECT * FROM ticket_log;

Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

+----+-----------+------------+------------+---------------------+
| id | ticket_id | old_status | new_status | changed_at          |
+----+-----------+------------+------------+---------------------+
|  1 |         5 | paid       | used       | 2026-10-24 12:00:00 |
|  2 |         6 | paid       | used       | 2026-10-24 12:00:00 |
|  3 |         7 | refunded   | used       | 2026-10-24 12:00:00 |
+----+-----------+------------+------------+---------------------+
3 rows in set (0.00 sec)

Три записи, по одной на строку. Второй UPDATE ничего не поменял, и запись не появилась. А журнал сразу поймал нашу небрежность: возвращённый билет 7 стал «использованным». В условии UPDATE забыли AND status = 'paid', и без журнала мы бы этого не заметили.

Триггер BEFORE: поправить строку

Триггер BEFORE срабатывает до записи и может поменять NEW. Кассиры вводят место то строчными, то заглавными, приведём к одному виду:

DELIMITER //
CREATE TRIGGER tickets_seat_upper
BEFORE INSERT ON tickets
FOR EACH ROW
BEGIN
  SET NEW.seat = UPPER(NEW.seat);
END //
DELIMITER ;

INSERT INTO tickets (screening_id, viewer_id, seat, sold_at) VALUES (6, 2, 'e1', NOW());
SELECT id, seat FROM tickets WHERE screening_id = 6;

+----+------+
| id | seat |
+----+------+
| 14 | E1   |
| 11 | E7   |
+----+------+
2 rows in set (0.00 sec)

Вставили e1, в таблице E1. Номер 14, а не 13: тринадцатый достался неудачной продаже места D1, AUTO_INCREMENT номера не возвращает.

Триггер, который запрещает

SIGNAL в триггере BEFORE отменяет само изменение. Запретим уменьшать залы, чтобы не потерять купленные места:

DELIMITER //
CREATE TRIGGER halls_no_shrink
BEFORE UPDATE ON halls
FOR EACH ROW
BEGIN
  IF NEW.seats < OLD.seats THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Нельзя уменьшать зал';
  END IF;
END //
DELIMITER ;

UPDATE halls SET seats = 10 WHERE id = 3;

ERROR 1644 (45000): Нельзя уменьшать зал

Расширять можно: UPDATE halls SET seats = 14 WHERE id = 3 проходит, и в VIP теперь 14 мест.

Удаляем

DROP TRIGGER halls_no_shrink;
DROP PROCEDURE film_schedule;
CALL film_schedule(1);

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ERROR 1305 (42000): PROCEDURE kino.film_schedule does not exist

Функцию удаляют так же: DROP FUNCTION price_with_discount.

В базе или в приложении

Код в базе удобен тем, что его не обойти. Триггер-журнал сработает и для приложения, и для администратора, который правит строки руками. Процедура продажи делает проверку и вставку за один поход к серверу.

Но у кода в базе есть цена. Его труднее тестировать и отлаживать, он не лежит рядом с остальным кодом в Git, если не следить специально. Триггеры работают незаметно: новый разработчик удивится, откуда в таблице заглавные буквы. А сами процедуры пишут на маленьком собственном языке MySQL: в нём есть переменные, IF и циклы WHILE, но нет библиотек и привычной отладки. Всё, что длиннее пары десятков строк, обычно проще написать в приложении. Поэтому правило для начала такое:

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

Тот же разговор для PostgreSQL ведётся в уроке про функции и PL/pgSQL.

Попробуй сам

В песочнице база кинотеатра. Запрос создаёт функцию скидки, вызывает её в SELECT, ставит триггер-запрет на уменьшение зала и проверяет его.

Создай процедуру film_schedule из урока и вызови её для разных фильмов. Убери DETERMINISTIC из функции и посмотри на ошибку 1418. Добавь триггер BEFORE INSERT на tickets, который через SIGNAL отклоняет билет с пустым местом ''.

Проверь себя

Задание

MY-22-1★★

Создай таблицу price_log с колонками: номер сеанса, старая цена, новая цена, время изменения. Напиши триггер AFTER UPDATE на screenings, который пишет в неё строку, только если цена действительно поменялась. Проверь его двумя командами: подними цену сеанса 4 до 950 и потом поменяй у того же сеанса только зал. Покажи, что в журнале одна запись, и объясни, почему второй UPDATE её не добавил.

Дальше

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

домашка

Домашка

Написать триггер, который записывает каждое изменение цены сеанса в отдельную таблицу, и проверить его двумя UPDATEJ