Створення рекурсивної процедури у MySQL

Розглянемо задачу, пов’язану з деревом категорій товарів.

Ми хочемо показати відвідувачу сайту, скільки товарів є в кожній категорії з вкладеними категоріями. Щоб уникнути необхідності обчислювати щоразу, коли завантажується сторінка сайту, ми створимо поле «qty» у таблиці «categories» та оновлюватимемо його щоразу, коли події змінюють кількість, наприклад, коли експортуються нові товари.

Якщо ми оновимо це поле за допомогою PHP, у нас буде багато обміну даними з базою даних, і існує ризик недотримання обмеження часу процесу PHP (зазвичай 30 секунд). Я хочу поговорити про можливість перенесення всієї логіки цього оновлення в MySQL.

Перш за все, увімкніть рекурсію для routines:

SET @@SESSION.max_sp_recursion_depth = 15

У цьому прикладі 15 — бажана максимальна глибина рекурсії. Звичайно, ви можете змінити її для свого дерева.

Також ви можете встановити цю змінну постійно у файлі конфігурації (my.cnf or my.ini):

[mysqld]
max_sp_recursion_depth = 15

Тепер ми можемо написати процедуру:

CREATE PROCEDURE update_category_qty(IN parent_category_id BIGINT, OUT parent_quantity INT)
BEGIN
DECLARE children_qty INT DEFAULT 0;
DECLARE one_child_qty INT DEFAULT 0;
DECLARE products_qty INT DEFAULT 0;
DECLARE child_category_id BIGINT;
DECLARE v_done BOOLEAN DEFAULT FALSE;
DECLARE child_cursor CURSOR FOR
   SELECT id
   FROM categories
   WHERE ifnull(parent_id,0) = ifnull(parent_category_id,0); /* null !== null */

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

OPEN child_cursor;

child_loop: LOOP 

   FETCH child_cursor INTO child_category_id;

   IF v_done THEN 
      LEAVE child_loop; 
   END IF;

   /* Рекурсивний виклик функції: */
   CALL update_category_qty(child_category_id, one_child_qty); 

   SET children_qty = children_qty+one_child_qty; 

END LOOP;

CLOSE child_cursor;

IF (children_qty = 0) THEN /* Без вкладених категорій */ 
   SELECT COUNT(*) INTO products_qty 
   FROM products 
   WHERE category_id = parent_category_id; 
END IF;

SET parent_quantity = children_qty + products_qty;

UPDATE categories 
SET qty = parent_quantity WHERE id = parent_category_id; 
END

Старшим людям буде приємно згадати стародавній синтаксис з мітками.

Одна або декілька кореневих категорій зазвичай мають parent_id, що дорівнює нулю.

Тепер ми можемо викликати цю процедуру з консолі MySQL:

call update_category_qty(null,@n)

Якщо ви хочете отримати загальну кількість товарів у категоріях без запиту до таблиць, виконайте команду

select @n

Також ви можете запустити цю процедуру з PHP. Приклад для Laravel:

DB::statement("call update_category_qty(null,@n)");

Щоб отримати загальну кількість:

$total = DB::select("SELECT @n total")[0]->total;

Тепер у нас є вибір: PHP або збережена процедура.

Тільки уявіть, скільки обмінів даними між PHP та MySQL вдається уникнути в цьому кейсі!

Підписуйтеся на Telegram-канал «DOU #tech», щоб не пропустити нові технічні статті