MySQL|MySQL 列出相关依赖
【MySQL|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）
SET GLOBAL group_concat_max_len = 16777215;
# TINYTEXT 255 TEXT 65535 MEDIUMTEXT 16777215 LONGTEXT 4294967295
# 若存储过程存在则删除
DROP PROCEDURE IF EXISTS dep;
# 创建一个存储过程
CREATE PROCEDURE dep(jobId MEDIUMTEXT)
# 开始内容
BEGIN
# 定义一个变量存储合并后的字符串（逗号分隔)
DECLARE childs MEDIUMTEXT;
# 定义一个变量存储当前查到的字符串(逗号分隔)
DECLARE ids MEDIUMTEXT;
# 初始化字符串
SET childs = '';
SET jobId = replace(jobId, '\n', '');
SET jobId = replace(jobId, '\r', '');
IF instr(jobId, ',') = 0
THEN
# 逗号分隔拼接字符串，支持查到多个
# INTO 放入 JobId
# 拼接 % 使自带 like 模糊查找
SELECT DISTINCT group_concat(job_id)
INTO jobId
FROM job_depend
WHERE job_id LIKE concat('%', jobId, '%');
END IF;
SET ids = jobId;
# 当前查到的字符串不为空时
WHILE ids IS NOT NULL DO
# 拼接字符串
SET childs = concat(ids, ',', childs);
# 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;
SET ids = jobId;
# 反过来查找依赖 ids 的
WHILE ids IS NOT NULL DO
SELECT DISTINCT group_concat(job_id)
INTO ids
FROM job_depend
WHERE find_in_set(depend_job_id, ids) > 0
AND NOT find_in_set(job_id, childs);
IF ids IS NOT NULL THEN
SET childs = concat(childs, ',', ids);
END IF;
END WHILE;
SELECT
# DISTINCT
# 如果 job_id 是查询传入的 jobId
# SQL 下的 IF 条件写法
CASE find_in_set(job_id, jobId) > 0
# 为真 则标记 =>
WHEN TRUE THEN '=>'
# 否则不显示任何内容
ELSE '' END AS f,
job_id,
depend_job_id
FROM job_depend
WHERE find_in_set(job_id, childs)
# 按下面依赖上面排序(需查找位置的子字符串, 大字符串)
ORDER BY instr(job_id, jobId);
END;
# 使用例子
CALL dep('c');
# 查询本程序
SELECT
SPECIFIC_NAME,
ROUTINE_TYPE
FROM information_schema.ROUTINES
WHERE SPECIFIC_NAME = 'dep';
推荐阅读
- 列出所有自定义的function和view
- py连接mysql
- 2019-01-18Mysql中主机名的问题
- MySql数据库备份与恢复
- mysql|InnoDB数据页结构
- mysql中视图事务索引与权限管理
- 思维导图作业3—工作相关导图
- MYSQL主从同步的实现
- MySQL数据库的基本操作
- AnyProxy抓取http/https请求