Рекурсивные хранимые функции в MySQL

Question

Я пытаюсь сделать функцию, которая рекурсивно строит путь для определенной категории

CREATE FUNCTION getPath(inId INT)
RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE return_path TEXT;
    DECLARE return_parent_id INT;
    SELECT CONCAT('/', name) INTO return_path FROM article_categories WHERE id = inId;
    SELECT parent_id INTO return_parent_id FROM article_categories WHERE id = inId;

    IF return_parent_id > 0 THEN
        SELECT CONCAT(getPath(return_parent_id), return_path) INTO return_path;
    END IF;

    RETURN return_path;
END

Когда я пытаюсь запустить эту функцию с категорией, у которой нет родителей (parent_id = 0), она работает нормально, но когда я пытаюсь категории с родительским идентификатором> 0, я получаю 1424 рекурсивных хранимых функции, и триггеры не допускаются.

Как мне обойти это? Я собираюсь разместить этот код на обычном веб-хостинге, который должен иметь как минимум MySQL-сервер версии 5.1.

---

После некоторой помощи от Айка Уолкера я сделал предварительное условие, которое отлично работает

DROP PROCEDURE IF EXISTS getPath;
DELIMITER //
CREATE PROCEDURE getPath(IN category_id INT UNSIGNED, OUT return_path TEXT)
BEGIN
    DECLARE parent_id INT UNSIGNED;
    DECLARE path_result TEXT;

    SET max_sp_recursion_depth=50;

    SELECT CONCAT('/', ac.name), ac.parent_id INTO return_path, parent_id FROM article_categories AS ac WHERE ac.id = category_id;

    IF parent_id > 0 THEN
        CALL getPath(parent_id, path_result);
        SELECT CONCAT(path_result, return_path) INTO return_path;
    END IF;
END //
DELIMITER ;

Затем я использую что-то вроде этого, чтобы назвать это

CALL getPath(72, @temp); SELECT @temp;

Answer 1

MySQL не допускает рекурсивные ФУНКЦИИ, даже если вы установили max_sp_recursion_depth.

Это позволяет до 255 рекурсии в ПРОЦЕДУРЕ, если вы установите max_sp_recursion_depth.

Поэтому я рекомендую заменить вашу функцию процедурой, используя переменную INOUT для return_path.

Answer 2

Из хранимой процедуры в вашем вопросе * с помощью @Ike Walker,

DROP PROCEDURE IF EXISTS getPath;
DELIMITER $$
CREATE PROCEDURE getPath(IN category_id INT UNSIGNED, OUT return_path TEXT)
BEGIN
    DECLARE parent_id INT UNSIGNED;
    DECLARE path_result TEXT;
    SET max_sp_recursion_depth=50;

    SELECT CONCAT('/', ac.name), ac.parent_id INTO return_path, parent_id FROM article_categories AS ac WHERE ac.id = category_id;
    IF parent_id > 0 THEN
        CALL getPath(parent_id, path_result);
        SELECT CONCAT(path_result, return_path) INTO return_path;
    END IF;
END $$
DELIMITER ;

Создать функцию:

DROP FUNCTION IF EXISTS getPath;
CREATE FUNCTION getPath(category_id INT) RETURNS TEXT DETERMINISTIC
BEGIN
    DECLARE res TEXT;
    CALL getPath(category_id, res);
    RETURN res;
END$$

Далее вы можете выбрать:

SELECT category_id, name, getPath(category_id) AS path FROM article_categories ;

Answer 3

В MySQL 8.0 появился еще один возможный подход. Вы можете включить рекурсивный CTE в свою функцию:

      
DELIMITER $$
DROP FUNCTION IF EXISTS getPath $$
CREATE FUNCTION getPath(inId INT)
RETURNS TEXT
DETERMINISTIC
BEGIN
DECLARE result TEXT;
WITH RECURSIVE cte_cat(id, parent_id, output_name) AS (
            SELECT ac0.id, ac0.parent_id, CONCAT('/',ac0.`name`) 
                FROM article_categories ac0
                WHERE ac0.id = inId
        UNION ALL
            SELECT ac.id, ac.parent_id, CONCAT('/',ac.`name`,cte.output_name) 
                FROM cte_cat cte
                    INNER JOIN article_categories ac ON ac.id = cte.parent_id
    )
SELECT output_name INTO result FROM cte_cat WHERE parent_id IS NULL;
RETURN result;
END$$
DELIMITER ;

Вот моя тестовая таблица:

      CREATE TABLE `article_categories` (
  `id` INT NOT NULL,
  `parent_id` INT NULL,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));
  
  INSERT INTO `article_categories`(`id`,`parent_id`,`name`) VALUES(1,NULL,'A');
  INSERT INTO `article_categories`(`id`,`parent_id`,`name`) VALUES(2,1,'A1');
  INSERT INTO `article_categories`(`id`,`parent_id`,`name`) VALUES(3,2,'A1a');
  INSERT INTO `article_categories`(`id`,`parent_id`,`name`) VALUES(4,1,'A2');
  INSERT INTO `article_categories`(`id`,`parent_id`,`name`) VALUES(5,NULL,'B');