修改嵌套父类存储过程：支持按分类名称而非ID查询父类

Got it, let's adjust your stored procedure to support category name input and return the breadcrumb-style hierarchy you need. Here's a step-by-step breakdown and the revised code:

Key Changes Needed #

1. Switch Input Parameter: Replace the idCat integer parameter with a chrCatName string parameter to accept category names.
2. Fetch Initial Category ID: First, map the input name to its corresponding website_id (handle cases where the name doesn't exist).
3. Preserve Parent Collection Logic: Keep your existing loop/cursor logic to gather all parent category IDs.
4. Build Breadcrumb Hierarchy: Instead of returning a list of IDs and names, construct the "Root -> Parent -> Child" string by traversing from the target category up to the root.

Revised Stored Procedure (MySQL 8.0+ with CTE) #

This version uses recursive CTEs (available in MySQL 8.0+) for clean hierarchy building:

CREATE PROCEDURE `getAllParentCategories`( IN chrCatName VARCHAR(255), IN intMaxDepth int)
BEGIN
DECLARE chrProcessed TEXT;
DECLARE quit INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE Level INT DEFAULT 0;
DECLARE idFetchedCategory INT;
DECLARE chrSameLevelParents TEXT;
DECLARE chrFullReturn TEXT;
DECLARE initialCatId INT; -- Stores the ID of the input category name
DECLARE cur1 CURSOR FOR SELECT parent_id FROM sb_categories WHERE website_id IN (@param);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

-- Step 1: Get the category ID from the input name
SELECT website_id INTO initialCatId FROM sb_categories WHERE name = chrCatName LIMIT 1;

-- Handle case where category name doesn't exist
IF initialCatId IS NULL THEN
    SELECT 'Error: Category name not found' AS breadcrumb_result;
    LEAVE;
END IF;

-- Initialize variables for parent collection
SET chrFullReturn = '';
SET @param = initialCatId;
set chrProcessed = concat('|', initialCatId, '|');

-- Existing loop to collect all parent IDs (unchanged core logic)
myloop:LOOP
IF quit = 1 THEN leave myloop; END IF;
OPEN cur1;
SET chrSameLevelParents = '';
FETCH cur1 INTO idFetchedCategory;
while(not done) do
SET Level = Level + 1;
IF idFetchedCategory > 0 THEN
if NOT INSTR(chrProcessed,concat('|',idFetchedCategory, '|')) > 0 THEN
if CHAR_LENGTH(chrSameLevelParents) > 0 then
set chrSameLevelParents = concat( idFetchedCategory, ',', chrSameLevelParents );
else
set chrSameLevelParents = idFetchedCategory;
end if;
set chrProcessed = concat('|',idFetchedCategory, '|', chrProcessed );
end if;
END IF;
FETCH cur1 INTO idFetchedCategory;
end while;
CLOSE cur1;
IF Level > intMaxDepth THEN SET done =1; SET quit = 1; END IF;
if CHAR_LENGTH(chrSameLevelParents) > 0 THEN
if CHAR_LENGTH(chrFullReturn) > 0 THEN
set chrFullReturn = concat( chrFullReturn, ',', chrSameLevelParents );
ELSE
set chrFullReturn = chrSameLevelParents;
END IF;
SET @param = chrSameLevelParents;
SET chrSameLevelParents = '';
SET done = 0;
ELSE
SET quit = 1;
END IF;
END LOOP;

-- Step 2: Build breadcrumb using recursive CTE (MySQL 8.0+)
WITH RECURSIVE category_path AS (
    SELECT website_id, name, parent_id, 0 AS depth
    FROM sb_categories
    WHERE website_id = initialCatId
    UNION ALL
    SELECT c.website_id, c.name, c.parent_id, cp.depth + 1
    FROM sb_categories c
    JOIN category_path cp ON c.website_id = cp.parent_id
)
SELECT GROUP_CONCAT(name ORDER BY depth DESC SEPARATOR ' -> ') AS breadcrumb_result
FROM category_path;

END

Alternative for MySQL 5.x (No CTE Support) #

If you're on an older MySQL version that doesn't support CTEs, replace the CTE section with this loop-based breadcrumb builder:

-- Replace the CTE block with this code for MySQL 5.x
DECLARE currentId INT;
DECLARE currentName VARCHAR(255);
DECLARE breadcrumb TEXT DEFAULT '';

SET currentId = initialCatId;

breadcrumb_loop:LOOP
    SELECT name, parent_id INTO currentName, currentId FROM sb_categories WHERE website_id = currentId;
    
    -- Build breadcrumb from root to target category
    IF breadcrumb = '' THEN
        SET breadcrumb = currentName;
    ELSE
        SET breadcrumb = CONCAT(currentName, ' -> ', breadcrumb);
    END IF;
    
    -- Stop when we reach the root category (parent_id = 0)
    IF currentId = 0 THEN
        LEAVE breadcrumb_loop;
    END IF;
END LOOP;

SELECT breadcrumb AS breadcrumb_result;

How to Use It #

Call the procedure with the category name and max depth:

CALL getAllParentCategories('Asus', 10);

This will return:

Electronics -> Computers -> Asus

Notes #

• Duplicate Names: If multiple categories share the same name, LIMIT 1 will pick the first match. To avoid this, you could add an optional website_id parameter to narrow down the search.
• Depth Limit: The intMaxDepth parameter still controls how far up the hierarchy we go (e.g., if you set it to 1, you'll get Computers -> Asus).
• Concurrency: The original procedure uses a user variable @param; if you need better concurrency, you can refactor it to use local variables instead.

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