MySQL 使用函数级联查询上级和下级

：）项目中遇到了类似的处理，觉得比直接写sql好用，小记一下

创建表：

-- ----------------------------
-- Table structure for level
-- ----------------------------
DROP TABLE IF EXISTS `level`;
CREATE TABLE `level`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `parent_id` int(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of level
-- ----------------------------
INSERT INTO `level` VALUES (1, '一级', 0);
INSERT INTO `level` VALUES (2, '二级', 1);
INSERT INTO `level` VALUES (3, '三级', 2);
INSERT INTO `level` VALUES (4, '三级', 2);

SET FOREIGN_KEY_CHECKS = 1;

根据ID级联查询 上级

创建函数

DELIMITER //

CREATE DEFINER="root"@"localhost" FUNCTION findP(cid INT) RETURNS VARCHAR(250) CHARSET utf8
BEGIN
    DECLARE bstr VARCHAR(250) DEFAULT '';
    DECLARE cstr INT DEFAULT cid;

    WHILE cstr IS NOT NULL AND cstr != 0 DO
        SET bstr = CONCAT(bstr, ',', cstr);
        SELECT parent_id INTO cstr FROM `level` WHERE id = cstr;
    END WHILE;

    RETURN TRIM(LEADING ',' FROM bstr);
END //

DELIMITER ;

根据ID级联查询 下级

创建函数

DELIMITER //
CREATE DEFINER="root"@"localhost" FUNCTION findC(cid INT) RETURNS VARCHAR(250) CHARSET utf8
BEGIN
    DECLARE bstr VARCHAR(250) DEFAULT '';
    DECLARE cstr VARCHAR(250) DEFAULT cid;

    WHILE cstr IS NOT NULL DO
        SET bstr = CONCAT(bstr, ',', cstr);
        SELECT GROUP_CONCAT(id) INTO cstr FROM `level` WHERE FIND_IN_SET(parent_id, cstr);
    END WHILE;

    RETURN TRIM(LEADING ',' FROM bstr);
END //
DELIMITER ;

使用

函数创建注意

• 函数入参不要使用字段名，例如：id，否则函数返回错误结果
• 参数类型定义时如果为 varchar，需要指定长度
• 注意循环中的判空
• 使用 GROUP_CONCAT 函数，是因为查询不到会返回 N/A，函数包装之后会返回 NULL，并为了拼接多个下级id