Mysql 查询父菜单下所有的子菜单，Mysql迭代（递归）查询

 

================================

©Copyright 蕃薯耀 2021-11-27

https://www.cnblogs.com/fanshuyao/

 

一、查询父菜单下所有的子菜单

 
#查询某个父菜单下所有的子菜单，包含该父菜单
#注意：查询有变量，直接在sql查询窗口在第二次查询数据会变少
#参考：https://blog.csdn.net/qq_37493556/article/details/108296542
SELECT
    ID.LEVEL,
    #_ids,
    DATA.* 
FROM
    (
    SELECT
        @ids AS _ids,
        ( SELECT @ids := GROUP_CONCAT(id) FROM sys_menu WHERE FIND_IN_SET(parent_id, @ids ) ) AS cids,
        @l := @l + 1 AS LEVEL 
    FROM
        sys_menu,
        ( SELECT @ids := 3, @l := 0 ) b  #@ids为输入的参数
    WHERE
        @ids IS NOT NULL 
    ) ID,
    sys_menu DATA 
WHERE FIND_IN_SET( DATA.id, ID._ids) 
ORDER BY LEVEL,id;

 

 

二、查询某个子菜单所有的[父]菜单（包含该子菜单）

-- 查询某个子菜单所有的[父]菜单（包含该子菜单）
SELECT id,LEVEL,DATA.`title`,DATA.`parent_id` FROM(
    SELECT
    @id AS _id,
    ( SELECT @id := parent_id FROM sys_menu WHERE id = @id) AS _pid,
    @l := @l+1 AS LEVEL
    FROM sys_menu,
    (SELECT @id := (15), @l := 0 ) b -- @id为输入的参数
    WHERE @id > 0
) ID, sys_menu DATA -- 表关联
WHERE ID._id = DATA.id
ORDER BY LEVEL DESC

 

 

三、数据结构

 
-- 菜单
CREATE TABLE sys_menu(
 
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    parent_id BIGINT COMMENT '父菜单id',
    title VARCHAR(50) NOT NULL COMMENT '菜单标题',
    icon VARCHAR(100) NOT NULL COMMENT '菜单目录图标class',
    `type` SMALLINT NOT NULL DEFAULT 1 COMMENT '类型，0表示菜单目录，1表示链接菜单',
    open_type VARCHAR(50) COMMENT '打开类型，目录是空，菜单是：_iframe',
    href VARCHAR(2000) COMMENT '菜单点击链接',
    order_by DOUBLE NOT NULL DEFAULT 100 COMMENT '排序，越小越前，每个目录菜单最好相差为10',
    
    delete_flag SMALLINT NOT NULL DEFAULT 0 COMMENT '是否删除，默认为0表示未删除，1表示删除',
    create_time DATETIME NOT NULL DEFAULT NOW() COMMENT '创建时间',
    create_user BIGINT NOT NULL COMMENT '创建人',
    update_time DATETIME COMMENT '更新时间',
    update_user BIGINT COMMENT '更新人',
    remark VARCHAR(200) COMMENT '备注'
    
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
 
 
INSERT INTO sys_menu(title,icon,open_type,href,create_user)
VALUES ('菜单1','layui-icon layui-icon-set-fill', NULL, NULL, 1);
 
INSERT INTO sys_menu(title,icon,open_type,href,create_user)
VALUES ('菜单2','layui-icon layui-icon-set-fill', NULL, NULL, 1);
 
INSERT INTO sys_menu(title,icon,open_type,href,create_user)
VALUES ('菜单3','layui-icon layui-icon-set-fill', NULL, NULL, 1);
 
INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id)
VALUES ('菜单1.1','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,1);
INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id)
VALUES ('菜单1.2','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,1);
INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id)
VALUES ('菜单1.3','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,1);
INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id)
VALUES ('菜单1.4','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,1);
 
INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id)
VALUES ('菜单2.1','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,2);
INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id)
VALUES ('菜单2.2','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,2);
INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id)
VALUES ('菜单2.3','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,2);
 
INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id)
VALUES ('菜单3.1','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,3);
INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id)
VALUES ('菜单3.2','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,3);
INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id)
VALUES ('菜单3.3','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,3);
 
INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id)
VALUES ('菜单3.3.3','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,13);
INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id)
VALUES ('菜单3.3.4','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,13);
INSERT INTO sys_menu(title,icon,open_type,href,create_user,parent_id)
VALUES ('菜单3.3.5','layui-icon layui-icon-set-fill', '_iframe', 'https://www.baidu.com/', 1,13);
 
 
SELECT * FROM sys_menu l 
ORDER BY l.`order_by` ASC,l.`id` ASC;

 

 

（时间宝贵，分享不易，捐赠回馈，^_^）

 

 

 

================================

©Copyright 蕃薯耀 2021-11-27

https://www.cnblogs.com/fanshuyao/