MySQL父子级查询

1. 根据父类id，查找所有子类id：

• 设计初期：记录 每条记录的顶级ID
• MySQL7.5：临时表

DELIMITER //

CREATE PROCEDURE FindAllChildCategories(IN parentCategoryId INT)
BEGIN
  DROP TABLE IF EXISTS temp_categories;
  CREATE TABLE temp_categories (id INT);
  
  INSERT INTO temp_categories (id)
  SELECT id FROM cms_category WHERE id = parentCategoryId;
  
  WHILE ROW_COUNT() > 0 DO
    INSERT INTO temp_categories (id)
    SELECT c.id
    FROM cms_category c
    INNER JOIN temp_categories tc ON c.parent_id = tc.id
    WHERE NOT EXISTS (SELECT 1 FROM temp_categories t WHERE t.id = c.id);
  END WHILE;

  SELECT id FROM temp_categories;
END //

DELIMITER ;

CALL FindAllChildCategories(6167);

• MySQL8.0：通用表表达式(CTE）

WITH RECURSIVE FindAllChildCategories AS (
  SELECT id, parent_id
  FROM categories
  WHERE id = 6167

  UNION ALL

  SELECT c.id, c.parent_id
  FROM categories c
  JOIN FindAllChildCategories fc ON c.parent_id = fc.id
)
SELECT id FROM FindAllChildCategories;