Mysql的遞歸查詢

創建表：

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50726
 Source Host           : localhost:3306
 Source Schema         : rightsmysql

 Target Server Type    : MySQL
 Target Server Version : 50726
 File Encoding         : 65001

 Date: 08/11/2019 19:14:39
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sys_menu
-- ----------------------------
DROP TABLE IF EXISTS `sys_menu`;
CREATE TABLE `sys_menu`  (
  `MENU_NO` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '菜單編號',
  `MENU_NAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '菜單名稱',
  `MENU_PNO` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '菜單上級編號',
  `MENU_URL` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '菜單連接網址',
  `MENU_CLASS` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '菜單對應CSS樣式',
  `MENU_ORDER` int(20) NULL DEFAULT NULL COMMENT '排序',
  PRIMARY KEY (`MENU_NO`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

創建mysql方法：

DROP FUNCTION IF EXISTS queryChildren;
CREATE FUNCTION queryChildren(areaId VARCHAR(4000))
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';
SET sTempChd = areaId;

WHILE sTempChd IS NOT NULL DO
SET sTemp= CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(MENU_NO) INTO sTempChd FROM sys_menu WHERE FIND_IN_SET(MENU_PNO,sTempChd)!='TreeRoot';
END WHILE;
RETURN sTemp;
END;

 

查詢以上方法的sql：

SELECT * FROM sys_menu WHERE FIND_IN_SET(MENU_NO,queryChildren('TreeRoot'));

 

查詢以上的樹：

SELECT DISTINCT A.MENU_NO ID,
                A.MENU_NAME NAME,
                A.MENU_PNO PID,
                IFNULL( REPLACE ( B.MENU_NO, B.MENU_NO, 'true' ), NULL ) CHECKED,
                IFNULL( REPLACE ( A.MENU_URL, A.MENU_URL, '' ) , 'true' ) OPEN,
                A.MENU_URL MENUURL,
                A.MENU_CLASS ICON
  FROM SYS_MENU A
  LEFT JOIN SYS_ROLE_MENU B
    ON A.MENU_NO = B.MENU_NO
   AND B.ROLE_NO = 'PRV-QH'
   AND  FIND_IN_SET(a.MENU_NO,queryChildren('TreeRoot'));

 

呈現的結果如下：