1 - 前言:
在 MySL 使用递归查询是很不方便的, 不像 SQL Server 可以直接使用声明变量, 使用虚拟表等等. 如: DECLARE,BEGIN ... END ,WHILE ,IF 等等.
在 MySQL 可以通过创建函数, 来使用上面的流程控制语句, MySQL 对函数的语法检查也是很苛刻的, 可以说很烦人, 不熟悉的人估计会哭...
2 - 递归查询关键部分:
a - 我的表结构:
b - 我的递归脚本:
用于查询: 当前类目 ID 及所有的父级元素的 ID 使用逗号分割开的一个字符串:
下面脚本里使用了组合结果集的一个函数: GROUP_CONCAT, 使用该函数可以在查不到结果的时候继续给 pid 赋值, 从而跳出循环, 详细可参考文章下面的注意点.
SELECT ParentID INTO pid FROM product_leimu WHERE 1=2; -- 找不到数据的情况下, INTO 无法给 pid 赋值, pid 结果不变,
SELECT GROUP_CONCAT(ParentID) INTO pid FROM product_leimu WHERE 1=2; -- 找不到数据的情况下, 通过函数 GROUP_CONCAT 组合之后, 可以继续使用 INTO 给 pid 赋值, pid 结果为 NULL
- DROP FUNCTION IF EXISTS `fn_getLeimuPath`;
- CREATE DEFINER = `sa`@`%` FUNCTION `fn_getLeimuPath`(`subId` int)
- RETURNS varchar(1000)
- BEGINDECLARE pathID VARCHAR(4000) DEFAULT subId;
- DECLARE pid INT default subid;
- WHILE pid>0 DO
- SELECT GROUP_CONCAT(ParentID) INTO pid FROM product_leimu WHERE ID = pid;
- IF pid>0 THEN SET pathID = concat(pid, ',', pathID);
- END IF;
- END WHILE;
- RETURN pathID;
- END;
查询结果展示:
3 - 一些需要注意的点, 函数的一些特殊语法检查:
a - 脚本结束标记检查: 分号检查:
如: 每个独立的脚本语句; 流程控制语句结尾: END IF; END; END WHILE;
b - 流程控制语句组合:
如:
IF 条件 THEN
代码
ELSEIF
代码
END IF;
WHILE 条件 DO
代码
END WHILE;
c - 特殊函数的使用:
函数: GROUP_CONCAT: 将结果集链接在一起, 使用逗号分隔, group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
备注: 这个函数可以在找不到数据的情况下, 继续执行从而给 INTO 的变量赋值. 比较神奇:
SELECT ParentID INTO pid FROM product_leimu WHERE 1=2; -- 找不到数据的情况下, INTO 无法给 pid 的结果不变,
SELECT GROUP_CONCAT(ParentID) INTO pid FROM product_leimu WHERE 1=2; -- 找不到数据的情况下, 通过函数 GROUP_CONCAT 组合之后, 可以继续使用 INTO 给 pid 赋值, NULL
我们这里是想在查不到的结果的时候, 通过 WHILE 的判断结束循环, 如果不通过 GROUP_CONCAT 函数将结果传给 pid, 那么将会进入无线循环当中, 是很坑的!! 下面脚本的代码结果是: 2
- DECLARE pid INT;
- SELECT ParentID INTO pid FROM product_leimu WHERE 1=2;
- IF pid IS NULL THEN
- SET pid=1;
- END IF;
- SELECT GROUP_CONCAT(ParentID) INTO pid FROM product_leimu WHERE 1=2;
- IF pid IS NULL THEN
- SET pid=2;
- END IF;
来源: http://www.linuxidc.com/Linux/2018-10/154856.htm