Simple usage example of MySQL 8.0 recursive query

Preface

This article uses the new features of MySQL 8.0 to implement recursive queries. Detailed example code is given in the article. Let's take a look at the detailed introduction.

Mysql8.0 recursive query usage

The table data is as follows

+--------+----------+------------+
| cat_id | name | parent_cid |
+--------+----------+------------+
| 12 | Beauty | 0 |
| 4 | Clothing | 0 |
| 5 | Women's Clothing | 4 |
| 6 | Menswear | 4 |
| 7 | Children's Clothing | 4 |
| 19 | Beauty and Body | 12 |
| 18 | Makeup | 12 |
| 13 | Skin Care | 12 |
| 15 | Skin Care Set | 13 |
| 40 | Sun protection | 13 |
| 39 | Makeup removal | 13 |
| 38 | Lip Balm | 13 |
| 17 | Lotion and Cream | 13 |
| 16 | Facial Mask | 13 |
| 14 | Toner | 13 |
+--------+----------+------------+

1. We need to find all subcategories under the "clothing" category

with recursive type_cte as (
    select * from t_category where cat_id = 4
    union all
    select t.* from t_category t
                        inner join type_cte type_cte2 on t.parent_cid = type_cte2.cat_id
)
select
    cat_id, name, parent_cid
from type_cte

+--------+------+------------+
| cat_id | name | parent_cid |
+--------+------+------------+
| 4 | Clothing | 0 |
| 5 | Women's Clothing | 4 |
| 6 | Menswear | 4 |
| 7 | Children's Clothing | 4 |
+--------+------+------------+

2. Query all subcategories under the "Beauty" category, and the category name includes the name of the parent category

with recursive type_cte as (
    select cat_id,name,parent_cid from t_category where cat_id = 12
    union all
    select t.cat_id,concat(type_cte2.name,'>',t.name),t.parent_cid 
    from t_category t
        inner join type_cte type_cte2 on t.parent_cid = type_cte2.cat_id
)
select
    cat_id, name, parent_cid
from type_cte;

+--------+------------------------+------------+
| cat_id | name | parent_cid |
+--------+------------------------+------------+
| 12 | Beauty | 0 |
| 13 | Beauty>Skin Care | 12 |
| 18 | Beauty>Makeup | 12 |
| 19 | Beauty>Beauty and Body | 12 |
| 14 | Beauty>Skin Care>Toner | 13 |
| 15 | Beauty>Skin Care>Skin Care Sets | 13 |
| 16 | Beauty>Skin Care>Facial Mask | 13 |
| 17 | Beauty>Skin Care>Lotions and Creams | 13 |
| 35 | Beauty>Skin Care>Cleansing | 13 |
| 36 | Beauty>Skin Care>Essence | 13 |
| 37 | Beauty>Skin Care>Eye Cream | 13 |
| 38 | Beauty>Skin Care>Lip Balm | 13 |
| 39 | Beauty > Skin Care > Makeup Removal | 13 |
| 40 | Beauty>Skin Care>Sunscreen | 13 |
+--------+------------------------+------------+

3. Query all parent categories of a category

Just adjust the SQL according to the second question.

with recursive type_cte as (
    select cat_id,name,parent_cid from t_category where cat_id = 40
    union all
    select t.cat_id,concat(type_cte2.name,'>',t.name),t.parent_cid
    from t_category t
             inner join type_cte type_cte2 on t.cat_id = type_cte2.parent_cid
)
select
    cat_id, name, parent_cid
from type_cte;

+--------+----------------+------------+
| cat_id | name | parent_cid |
+--------+----------------+------------+
| 40 | Sun protection | 13 |
| 13 | Sun Protection > Skin Care | 12 |
| 12 | Sun Protection > Skin Care > Makeup | 0 |
+--------+----------------+------------+

Summarize

This is the end of this article about MySQL 8.0 recursive query. For more relevant MySQL 8.0 recursive query content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!