mysql - 修改嵌套父过程代码以使用名称而不是 ID

Question

我找到了一个我寻找了几个月的宝藏，一个列出所有父类别到子类别的 SQL 过程，以生成面包屑或提供类别搜索建议。但它需要类别 ID 才能找到它的父级，我想将其修改为使用类别名称，因为我正在制作一个提供搜索建议以显示类别及其所有父级的搜索框。

代码来自 this link .

CREATE PROCEDURE `getAllParentCategories`( IN idCat int, 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 cur1 CURSOR FOR SELECT parent_id FROM sb_categories WHERE website_id IN (@param);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

SET chrFullReturn = '';
SET @param = idCat;
set chrProcessed = concat('|',idCat, '|');
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;

SET @strQuery = concat('SELECT website_id, name FROM sb_categories WHERE website_id IN (',chrFullReturn,')'); PREPARE stmt1 FROM @strQuery;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END

我的表结构就这么简单:

+------------+-------------+-----------+
| website_id | name        | parent_id |
+------------+-------------+-----------+
| 1          | Electronics |         0 |
+------------+-------------+-----------+
| 2          | Computers   |         1 |
+------------+-------------+-----------+
| 3          | Asus        |         2 |
+------------+-------------+-----------+
| 4          | Food        |         0 |
+------------+-------------+-----------+
| 5          | Chicken     |         4 |
+------------+-------------+-----------+

我希望当用户搜索“asus”时，我会得到一个显示“3-Asus、2-Computers、1-Electronics”的表格结果，以便在下拉列表中显示为“Electronics -> Computers -> Asus”。

现在，如果我使用:call getAllParentCategories(3, 10)，我希望它能像 call getAllParentCategories('asus', 10)，但我的 SQL 知识对我没有帮助。

谢谢你的帮助。

Answer 1

您想更改该过程，使其接受类别名称而不是类别 ID 作为第一个参数。您的过程的输出应保持不变。

一个解决方案是在查询中添加一个额外的步骤，从 nameCat 参数初始化 idCat 变量:

CREATE PROCEDURE `getAllParentCategories`( IN nameCat VARCHAR(255), IN intMaxDepth int)
BEGIN
    ...
    SET chrFullReturn = '';
    SELECT @param := website_id FROM sb_categories WHERE name = nameCat;
    set chrProcessed = concat('|',@param, '|');
    ...

您的其余代码应保持不变。

请注意，只有类别名称是唯一的，这才能正常工作......您可能需要在此列上创建唯一约束:

ALTER TABLE sb_categories ADD CONSTRAINT UC_name UNIQUE (name);