MySQL實(shí)現(xiàn)樹形查詢

背景

通常我們會使用數(shù)據(jù)庫表保存菜單信息。
此類信息一般會使用樹形結(jié)構(gòu)存儲，即通過parent_id=resource_id方式實(shí)現(xiàn)數(shù)據(jù)庫表記錄的關(guān)聯(lián)。
在Oracle下我們可以使用start with connect by prior語句構(gòu)建查詢。
使用MySQL8.0以上時，我們也可以使用遞歸樹查詢實(shí)現(xiàn)相同功能。
這里記錄以下MySQL8的遞歸查詢語法寫法。

MySQL遞歸查詢

  1. 測試表信息準(zhǔn)備
CREATE TABLE `perm_resource` (
  `resource_id` varchar(50) NOT NULL COMMENT '資源ID',
  `resource_name` varchar(100) DEFAULT NULL COMMENT '資源名稱',
  `resource_parent_id` varchar(50) DEFAULT NULL COMMENT '父節(jié)點(diǎn)ID',
  `is_enable` varchar(1) DEFAULT NULL COMMENT '是否啟用',
  `order_id` varchar(20) DEFAULT NULL COMMENT '排序ID',
  `resource_path` varchar(200) DEFAULT NULL COMMENT '資源路徑',
  `menu_level` varchar(2) DEFAULT NULL COMMENT '菜單級別',
  `belong_systems` varchar(20) DEFAULT NULL COMMENT '所屬系統(tǒng)',
  `is_del` varchar(1) DEFAULT NULL COMMENT '是否可刪除',
  `resource_type` varchar(1) DEFAULT NULL COMMENT '資源類型(COMM_CODE_ZYLX)',
  `button_id` varchar(50) DEFAULT NULL COMMENT '功能菜單ID',
  `menu_path` varchar(200) DEFAULT NULL COMMENT '樹結(jié)構(gòu)路徑',
  `login_flag` varchar(1) DEFAULT NULL COMMENT '是否需要登錄標(biāo)志',
  `login_page` varchar(200) DEFAULT NULL COMMENT '登錄頁面',
  `color` varchar(20) DEFAULT NULL COMMENT '按鈕背景顏色',
  `icon` varchar(50) DEFAULT NULL COMMENT '圖標(biāo)資源',
  PRIMARY KEY (`resource_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='資源';
  1. 準(zhǔn)備測試數(shù)據(jù)
INSERT INTO `perm_resource` VALUES ('011', '社會保險（個人）', '1', '1', '1', null, '2', '1', '1', null, null, '.1.011.', '1', null, null, null);
INSERT INTO `perm_resource` VALUES ('01101', '個人中心', '011', '1', '10', null, '3', '1', '1', '1', null, '.1.011.01101.', '1', '1', null, null);
INSERT INTO `perm_resource` VALUES ('0110102', '待遇資格', '01101', '1', '2', '/aio/view/neuqsoft/handan/yl/facecompare.jsp', '4', '1', '1', '1', null, '.1.011.01101.0110102.', '1', '1', '#4cd964', '/aio/aio/comm/image/menuIcon10.png');
INSERT INTO `perm_resource` VALUES ('0110103', '繳費(fèi)證明打印', '01101', '1', '1', '/aio/view/neuqsoft/handan/yl/grjfzm.jsp', '4', '1', '1', '1', null, '.1.011.01101.0110103.', '1', '1', '#ff2d55', '/aio/aio/comm/image/menuIcon3.png');
INSERT INTO `perm_resource` VALUES ('01103', '醫(yī)療保險', '011', '1', '30', null, '3', '1', '1', '1', null, '.1.011.01103.', '1', '1', null, null);
INSERT INTO `perm_resource` VALUES ('0110307', '基本信息查詢', '01103', '1', '10', '/aio/view/neuqsoft/handan/yb/query_basicinfo.jsp', '4', '1', '1', '2', null, '.1.011.01103.0110307.', '1', '1', '#5ac8fa', '/aio/aio/comm/image/menuIcon14.png');
INSERT INTO `perm_resource` VALUES ('0110308', '參保信息查詢', '01103', '1', '20', '/aio/view/neuqsoft/handan/yb/grcbxxquery.jsp', '4', '1', '1', '2', null, '.1.011.01103.0110308.', '1', '1', null, '/aio/aio/comm/image/menuIcon1.png');
INSERT INTO `perm_resource` VALUES ('0110309', '繳費(fèi)明細(xì)查詢', '01103', '1', '30', '/aio/view/neuqsoft/handan/yb/query_jfmx.jsp', '4', '1', '1', '1', null, '.1.011.01103.0110309.', '1', '1', '#FF9900', '/aio/aio/comm/image/menuIcon12.png');
INSERT INTO `perm_resource` VALUES ('0110310', '醫(yī)保賬戶查詢', '01103', '1', '40', '/aio/view/neuqsoft/handan/yb/query_grzh.jsp', '4', '1', '1', '2', null, '.1.011.01103.0110310.', '1', '1', null, '/aio/aio/comm/image/menuIcon17.png');
INSERT INTO `perm_resource` VALUES ('0110311', '醫(yī)療消費(fèi)查詢', '01103', '1', '50', '/aio/view/neuqsoft/handan/yb/querydoctor_zhzc.jsp', '4', '1', '1', '2', null, '.1.011.01103.0110311.', '1', '1', null, '/aio/aio/comm/image/menuIcon16.png');
INSERT INTO `perm_resource` VALUES ('0110312', '藥品目錄', '01103', '1', '60', '/aio/view/neuqsoft/handan/yb/drugscatalog.jsp', '4', '1', '1', '2', null, '.1.011.01103.0110312.', '1', '1', null, '/aio/aio/comm/image/menuIcon4.png');
INSERT INTO `perm_resource` VALUES ('0110313', '診療目錄', '01103', '1', '70', '/aio/view/neuqsoft/handan/yb/zhenliaomulu.jsp', '4', '1', '1', '2', null, '.1.011.01103.0110313.', '1', '1', null, '/aio/aio/comm/image/menuIcon23.png');
INSERT INTO `perm_resource` VALUES ('0110314', '醫(yī)療機(jī)構(gòu)', '01103', '1', '80', '/aio/view/neuqsoft/handan/yb/query_yljg.jsp', '4', '1', '1', '2', null, '.1.011.01103.0110314.', '1', '1', null, '/aio/aio/comm/image/menuIcon21.png');
INSERT INTO `perm_resource` VALUES ('01104', '工傷保險', '011', '1', '40', null, '3', '1', '1', '1', null, '.1.011.01104.', '1', '1', null, null);
INSERT INTO `perm_resource` VALUES ('0110401', '參保信息查詢', '01104', '1', '20', '/aio/view/neuqsoft/handan/gs/grcbxxquery.jsp', '4', '1', '1', '2', null, '.1.011.01104.0110401.', '1', '1', '#ff9500', '/aio/aio/comm/image/menuIcon1.png');
INSERT INTO `perm_resource` VALUES ('0110402', '待遇發(fā)放查詢', '01104', '1', '30', '/aio/view/neuqsoft/handan/gs/query_dyff.jsp', '4', '1', '1', '2', null, '.1.011.01104.0110402.', '1', '1', '#4cd964', '/aio/aio/comm/image/menuIcon22.png');
INSERT INTO `perm_resource` VALUES ('0110403', '基本信息查詢', '01104', '1', '10', '/aio/view/neuqsoft/handan/gs/query_basicinfo.jsp', '4', '1', '1', '2', null, '.1.011.01104.0110403.', '1', '1', '#4cd964', '/aio/aio/comm/image/menuIcon14.png');
INSERT INTO `perm_resource` VALUES ('0110404', '繳費(fèi)明細(xì)查詢', '01104', '1', '40', '/aio/view/neuqsoft/handan/gs/query_jfmx.jsp', '4', '1', '1', '2', null, '.1.011.01104.0110404.', '1', '1', null, '/aio/aio/comm/image/menuIcon12.png');
INSERT INTO `perm_resource` VALUES ('01105', '企業(yè)養(yǎng)老保險', '011', '1', '20', null, '3', '1', '1', '1', null, '.1.011.01105.', '1', '1', null, null);
INSERT INTO `perm_resource` VALUES ('0110501', '靈活人員繳費(fèi)核定', '01105', '1', '80', '/aio/view/neuqsoft/handan/yl/hdsb.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110501.', '1', '1', null, '/aio/aio/comm/image/lhjfhd.png');
INSERT INTO `perm_resource` VALUES ('0110502', '個人權(quán)益單打印', '01105', '1', '85', '/aio/view/neuqsoft/handan/yl/grqyd.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110502.', '1', '1', null, '/aio/aio/comm/image/menuIcon16.png');
INSERT INTO `perm_resource` VALUES ('0110503', '待遇發(fā)放查詢', '01105', '1', '40', '/aio/view/neuqsoft/handan/yl/query_yljff.jsp', '4', '1', '1', '1', null, '.1.011.01105.0110503.', '1', '1', '#ff2d55', '/aio/aio/comm/image/menuIcon19.png');
INSERT INTO `perm_resource` VALUES ('0110504', '退休金證明信打印', '01105', '1', '90', '/aio/view/neuqsoft/handan/yl/txrygzzm.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110504.', '1', '1', null, '/aio/aio/comm/image/menuIcon17.png');
INSERT INTO `perm_resource` VALUES ('0110505', '基本信息查詢', '01105', '1', '10', '/aio/view/neuqsoft/handan/yl/query_basicinfo.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110505.', '1', '1', '#ffcc00', '/aio/aio/comm/image/menuIcon14.png');
INSERT INTO `perm_resource` VALUES ('0110506', '參保信息查詢', '01105', '1', '20', '/aio/view/neuqsoft/handan/yl/grcbxxquery.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110506.', '1', '1', '#ff9500', '/aio/aio/comm/image/menuIcon1.png');
INSERT INTO `perm_resource` VALUES ('0110510', '養(yǎng)老賬戶查詢', '01105', '1', '60', '/aio/view/neuqsoft/handan/yl/query_ylyzh.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110510.', '1', '1', null, '/aio/aio/comm/image/menuIcon17.png');
INSERT INTO `perm_resource` VALUES ('0110511', '繳費(fèi)明細(xì)查詢', '01105', '1', '30', '/aio/view/neuqsoft/handan/yl/query_jfmx.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110511.', '1', '1', null, '/aio/aio/comm/image/menuIcon12.png');
INSERT INTO `perm_resource` VALUES ('0110512', '繳費(fèi)證明打印', '01105', '1', '70', '/aio/view/neuqsoft/handan/yl/grjfzm.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110512.', '1', '1', null, '/aio/aio/comm/image/menuIcon3.png');
  1. 遞歸查詢SQL語法
with recursive tab1(resource_id,resource_name,menu_level,resource_path,rn,orderstr) AS (
    SELECT resource_id,resource_name,menu_level,resource_path,
           @rn := 1 rn,
           cast(@rn AS CHAR) orderstr
    FROM perm_resource t0
    WHERE t0.belong_systems = '1' AND t0.resource_id='011'

    union all 

    SELECT t1.resource_id,t1.resource_name,t1.menu_level,t2.resource_path,
           @rn := @rn + 1 rn,
           concat(t2.orderstr, '-', @rn)
    FROM perm_resource t1, tab1 t2
    WHERE t1.belong_systems = '1' AND t1.resource_parent_id = t2.resource_id
)
SELECT t3.* FROM tab1 t3 
WHERE t3.rn > '0'
ORDER BY t3.orderstr

遞歸查詢SQL解析

MySQL8的查詢語法分為以下幾部分：

  1. 申明一個遞歸查詢的結(jié)果表及表里的字段，如這里的tab1
    1.1 申明的遞歸查詢的數(shù)據(jù)組成為兩個SQL查詢結(jié)果的union all
    1.2. 第一個子句查詢出起始條件的查詢結(jié)果，相當(dāng)于Oracle樹形查詢中的start with定義的條件
    1.3. 第二個子句，通常from子句至少包含兩個表，其中一個表為申明的表tab1，在該子句的的where中定義關(guān)聯(lián)關(guān)系如：t1.resource_parent_id = t2.resource_id
    1.4. 由于union all查詢出來的數(shù)據(jù)排序并不是我們想要的深度優(yōu)先方式，可以使用變量定義rownum，通過數(shù)據(jù)連接形成排序字符串orderstr。
  2. 針對遞歸查詢生成的表tab1中的數(shù)據(jù)進(jìn)行檢查。在此查詢語句中可以有where和order by子句，order by子句可以使用生成的orderstr字段實(shí)現(xiàn)深度優(yōu)先效果。最終等效于ORACLE的樹形查詢。
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容