MySQL-递归查询

MySQL 8.0版本中才被正式引入WITH recursive，用于递归查询

语法

WITH recursive 临时表名 AS (
    初始语句（非递归部分）
    UNION [ ALL | DISTINCT ]
    递归部分语句
)
[ SELECT| INSERT | UPDATE | DELETE ]

案例
表结构

create table ccms_category(
 `category_id` varchar(150) NOT NULL COMMENT '商品类目ID',
 `category_name` varchar(200) COMMENT '商品类目名称',
 `parent_category_id` varchar(150) NOT NULL COMMENT '商品类目父ID'
)

根据父节点递归查询所有子节点

with recursive tb (category_id, category_name, parent_category_id) as
(
    select category_id, category_name, parent_category_id from ccms_category where category_name like '%HAHA%'
    union DISTINCT
    select
        c.category_id, c.category_name, c.parent_category_id
    from
        ccms_category c
    join tb t on
        t.category_id = c.parent_category_id -- 从c表中找子节点
)
select * from tb;

根据子节点递归查找所有父节点

with recursive tb (category_id, category_name, parent_category_id) as
(
    select category_id, category_name, parent_category_id from ccms_category where category_name like '%HAHA%'
    union DISTINCT
    select
        c.category_id, c.category_name, c.parent_category_id
    from
        ccms_category c
    join tb t on
        t.parent_category_id = c.category_id -- 从c表中找父节点
)
select * from tb;