MySQL 循环查询树 函数

注释齐全，可以用来学习存储函数的条件和循环、SQL条件

数据例子

DROP TABLE IF EXISTS test.job_depend;
# 创建测试表
CREATE TABLE `job_depend` (
  `sn_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '行号',
  `job_id` varchar(20) DEFAULT NULL COMMENT '作业ID',
  `depend_job_id` varchar(20) DEFAULT NULL COMMENT '依赖作业',
  PRIMARY KEY (`sn_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='作业依赖';

# 插入测试数据
INSERT INTO test.job_depend (job_id, depend_job_id) VALUES ('b', 'a');
INSERT INTO test.job_depend (job_id, depend_job_id) VALUES ('c', 'b');
INSERT INTO test.job_depend (job_id, depend_job_id) VALUES ('c', 's');
INSERT INTO test.job_depend (job_id, depend_job_id) VALUES ('s', 'c');

# 设置连接函数最大长度（默认1024）建议更改my.ini
SET GLOBAL group_concat_max_len = 16777215;

# 打开 MySQL8 默认关闭的创建存储函数开关
SET GLOBAL log_bin_trust_function_creators = 1;

# TINYTEXT 255 TEXT 65535 MEDIUMTEXT 16777215 LONGTEXT 4294967295
# 若函数存在则删除
DROP FUNCTION IF EXISTS getChild;
# 创建一个函数
CREATE FUNCTION getChild(jobId MEDIUMTEXT)
  RETURNS MEDIUMTEXT
  BEGIN
    # 定义一个变量存储合并后的字符串（逗号分隔)
    DECLARE childs MEDIUMTEXT;
    # 定义一个变量存储当前查到的字符串(逗号分隔)
    DECLARE ids MEDIUMTEXT;
    # 初始化字符串
    SET childs = '';
    SET ids = jobId;

    # 当前查到的字符串不为空时
    WHILE ids IS NOT NULL DO
      # 拼接字符串
      SET childs = concat(childs, ',', ids);

      # SELECT 不重复 拼接字段，默认逗号分隔
      # INTO 放入 当前查找的字符串
      # WHERE (查找值, 字符串集合) 且没有在合并字符串
      SELECT DISTINCT group_concat(depend_job_id)
      INTO ids
      FROM job_depend
      WHERE find_in_set(job_id, ids) > 0
            AND NOT find_in_set(depend_job_id, childs);
    END WHILE;

    # 返回合并后的字符串
    RETURN childs;
  END;

# 使用例子
SELECT *
FROM job_depend
WHERE find_in_set(job_id, getChild('c'));

# 查询本函数
SELECT
  SPECIFIC_NAME,
  ROUTINE_TYPE
FROM information_schema.ROUTINES
WHERE SPECIFIC_NAME = 'getChild';

拓展：
树的数据库建模方式有四种：
Adjacency List，即本文这种，子代易后代难，需要循环迭代查询。
Path Enumeration，存路径，非无限。
Nested Sets，左右标记，后代易子代难。
Closure Table，所有间距，查询易移动难。