MySQL自循环存储过程及递归查询分类后代关联数据函数创建需求

Alright, let's break this down into two clear, actionable parts: a recursive function to fetch all category descendants (including the parent itself), and a self-looping stored procedure that leverages this function to work with the listing_category table. I'll use MySQL 8.0+ features here since recursive CTEs make hierarchy queries way cleaner than old-school loops.

1. Recursive Function: Get All Category Descendants #

This function takes a category ID and returns a comma-separated string of all descendant IDs (including the input ID). We'll use a recursive CTE to traverse the full hierarchy from the parent down to all nested children.

DELIMITER //

CREATE FUNCTION get_category_descendants(p_category_id INT) 
RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN
    DECLARE descendant_ids VARCHAR(1000);
    
    -- Recursive CTE to pull the full category hierarchy
    WITH RECURSIVE category_hierarchy AS (
        SELECT id FROM category WHERE id = p_category_id
        UNION ALL
        SELECT c.id FROM category c
        JOIN category_hierarchy ch ON c.related = ch.id
    )
    SELECT GROUP_CONCAT(id SEPARATOR ',') INTO descendant_ids
    FROM category_hierarchy;
    
    RETURN descendant_ids;
END //

DELIMITER ;

Test with your sample data: #

If you run SELECT get_category_descendants(1);, it'll return 1,2,3,4,7,8,9,10 — exactly matching your expected hierarchy (parent 1, direct children 2/3/4/7/10, grandchildren 8/9).

2. Self-Looping Stored Procedure #

For the self-looping procedure, let's create one that takes a category ID, uses our function to get all descendants, then loops through each descendant to fetch and process corresponding listing_category records. We'll use a cursor to handle the loop, and a temporary table to store and output results.

DELIMITER //

CREATE PROCEDURE process_listings_by_category(p_category_id INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE current_category_id INT;
    -- Cursor to iterate through each descendant category ID
    DECLARE category_cursor CURSOR FOR
        SELECT id FROM category
        WHERE FIND_IN_SET(id, get_category_descendants(p_category_id));
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- Temporary table to store aggregated results
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_listing_results (
        result_id INT AUTO_INCREMENT PRIMARY KEY,
        category_id INT,
        listing_total INT
    );
    
    -- Start looping through descendant categories
    OPEN category_cursor;
    read_loop: LOOP
        FETCH category_cursor INTO current_category_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Count listings for the current category (adjust logic as needed)
        INSERT INTO temp_listing_results (category_id, listing_total)
        SELECT current_category_id, COUNT(*)
        FROM listing_category
        WHERE category = current_category_id;
    END LOOP;
    CLOSE category_cursor;
    
    -- Output the final results
    SELECT * FROM temp_listing_results;
    -- Clean up temporary table
    DROP TEMPORARY TABLE IF EXISTS temp_listing_results;
END //

DELIMITER ;

Test with your sample data: #

Calling CALL process_listings_by_category(1); will loop through each descendant ID and count matching records in listing_category. Using your sample listing_category data (1,1,2,3,3,5,6,9,7,7), the output will look like this:

Quick Alternative: Batch Query Without Explicit Loop #

If you just need to fetch all matching listing_category records (no need for a loop), you can use the function directly in a query:

SELECT * FROM listing_category
WHERE FIND_IN_SET(category, get_category_descendants(1));

This returns all 8 relevant records from your sample (excluding the entries with category 5 and 6, since they're not part of the category 1 hierarchy).

内容的提问来源于stack exchange，提问作者Maciek