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）

 1 WITH RECURSIVE FindAllChildCategories AS (
 2   SELECT id, parent_id
 3   FROM categories
 4   WHERE id = 6167
 5 
 6   UNION ALL
 7 
 8   SELECT c.id, c.parent_id
 9   FROM categories c
10   JOIN FindAllChildCategories fc ON c.parent_id = fc.id
11 )
12 SELECT id FROM FindAllChildCategories;