【MySQL】MySQL层级数据的递归遍历

层级的业务数据在系统中很常见，如组织机构、商品品类等。

如果要获取层级数据的全路径，除了缓存起来，就是递归访问的方式了：

1. 将层级数据缓存在redis中，用redis递归获取层级结构。此方法效率高。
2. 在MySQL中做递归遍历，（Oracle有递归的语法支持，而MySQL并没有），需要自己写函数去递归。此方法效率低。

程序运行基于效率要求，一般使用Redis去递归。本次，因为初始化数据需要，写了一份MySQL函数去递归：

/* 根据ID获取级联编码路径 */
CREATE FUNCTION `f_org_recursion`(
v_id INTEGER
) RETURNS VARCHAR(1024)
BEGIN
/* 编码 */
DECLARE v_code VARCHAR(1024);
/* 编码路径 */
DECLARE v_cascade_code VARCHAR(1024);
/* 父节点ID */
DECLARE v_parent_id INT;
/* 递归操作的层次 */
DECLARE v_recursion_level INT; SET v_cascade_code = '';
SET v_recursion_level = 0; /* 查询本节点代码、和父节点ID */
SELECT t.code, t.parent_id INTO v_code, v_parent_id FROM t_organ t WHERE t.id = v_id; /* 是否为空 */
IF v_code IS NULL THEN
RETURN CONCAT('no record for id ', v_id);
ELSE
SET v_cascade_code = CONCAT('', v_code);
END IF; /* 递归拼接代码 */
WHILE v_parent_id IS NOT NULL DO
/* 递归层次限制，避免死循环 */
SET v_recursion_level = v_recursion_level + 1;
IF v_recursion_level > 20 THEN
RETURN 'Recursion out of 20 times';
END IF;/* 以父ID为ID查询 */
SELECT t.code, t.parent_id INTO v_code, v_parent_id FROM t_organ t WHERE t.id = v_parent_id;/* 是否为空 */
IF v_code IS NULL THEN
RETURN CONCAT('no record for parent id ', v_parent_id);
ELSE
SET v_cascade_code = CONCAT(v_code, '-', v_cascade_code);
END IF; END WHILE; RETURN v_cascade_code;
END