MySQL 递归查询教程

JAVA herman 384浏览
公告:“业余草”微信公众号提供免费CSDN下载服务(只下Java资源),关注业余草微信公众号,添加作者微信:codedq,发送下载链接帮助你免费下载!
本博客日IP超过2000,PV 3000 左右,急需赞助商。
极客时间所有课程通过我的二维码购买后返现24元微信红包,请加博主新的微信号:codedq,之前的微信号好友位已满,备注:返现
饿了么大量招人,我内推!Java 方向!薪资不设上限,工作年龄不限!工作地点限魔都,可电话面试!简历,发我微信:codedq
所有面试题(java、前端、数据库、springboot等)一网打尽,请关注文末小程序
视频教程免费领

最近有网友问题我 MySQL 如何实现递归查询?我随手发他了一个MySQL视频教程,谁知他开始吐槽我了。“不会就不会,教程我自己不会搜?还需要你给我搜?太打击人了”。

我真是太难了,发给他一个教程还有错了不成?

说归说,今天我们还是一起来实现一个 MySQL 递归查询。

众所周知,在 Oracle 中,有一个 start with connect by prior 语法。所以,递归查询就变得非常的简单。

select * from xttblog_dept start with id='1001' connet by prior id=pid;

而 MySQL 中并没有提供这样的语法,我们需要自己实现一个递归查询函数。

下面我们以部门表为例,来说明我们如何自定义 MySQL 递归查询函数。

DROP TABLE IF EXISTS `xttblog_dept`;
CREATE TABLE `xttbblog_dept`  (
  `id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `pid` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1000', '业余草总公司', NULL);
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1001', '业余草北京分公司', '1000');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1002', '业余草上海分公司', '1000');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1003', '业余草北京研发部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1004', '业余草北京财务部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1005', '业余草北京市场部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1006', '业余草业余草北京研发一部', '1003');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1007', '业余草北京研发二部', '1003');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1008', '业余草北京研发一部一小组', '1006');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1009', '业余草北京研发一部二小组', '1006');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1010', '业余草北京研发二部一小组', '1007');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1011', '业余草北京研发二部二小组', '1007');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1012', '业余草北京市场一部', '1005');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1013', '业余草上海研发部', '1002');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1014', '业余草上海研发一部', '1013');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1015', '业余草上海研发二部', '1013');

部门递归函数实现:

delimiter $$ 
drop function if exists get_child_list$$ 
create function get_child_list(in_id varchar(10)) returns varchar(1000) 
begin 
    declare ids varchar(1000) default ''; 
    declare tempids varchar(1000); 
 
    set tempids = in_id; 
    while tempids is not null do 
        set ids = CONCAT_WS(',',ids,tempids); 
        select GROUP_CONCAT(id) into tempids from xttblog_dept where FIND_IN_SET(pid,tempids)>0;  
    end while; 
    return ids; 
end  
$$ 
delimiter ; 

从上面的变量中可以看出,虽然递归功能已经实现了,但还是有缺陷。用了不少系统函数不说,还有 1000 个字符长度限制。

上面是一个部门递归往下查的函数,如果我需要根据子部门递归查父部门该怎么办呢?不得已,我们还需要再实现一个递归函数。

delimiter $$ 
drop function if exists get_parent_list$$ 
create function get_parent_list(in_id varchar(10)) returns varchar(1000) 
begin 
    declare ids varchar(1000); 
    declare tempid varchar(10); 
     
    set tempid = in_id; 
    while tempid is not null do 
        set ids = CONCAT_WS(',',ids,tempid); 
        select pid into tempid from xttblog_dept where id=tempid; 
    end while; 
    return ids; 
end 
$$ 
delimiter ; 

以上两个函数建议大家收藏,平时遇到的可能性还是比较大的。

另外需要注意的是,用 group_concat 函数来拼接字符串,它是有长度限制的,默认为 1024 字节。可以通过 show variables like “group_concat_max_len”; 来查看。如果不够用,我们可以使用下面的 SQL 语句进行修改。

SET GLOBAL group_concat_max_len=102400; 
-- 或者 
SET SESSION group_concat_max_len=102400;
-- 或者修改 MySQL 配置文件 my.cnf ,增加 
# group_concat_max_len = 102400 
#你要的最大长度 。

我不建议大家修改这个值。如果遇到 group_concat 拼接长度问题,建议还是从其他业务方面优化你的实现。

业余草公众号

最后,欢迎关注我的个人微信公众号:业余草(yyucao)!可加作者微信号1:xmtxtt(5000人已满),微信号2:xttblog(5000人已满),微信号3:codedq(超3800)。备注:“1”,添加博主微信拉你进微信群。备注错误不会同意好友申请。再次感谢您的关注!后续有精彩内容会第一时间发给您!原创文章投稿请发送至532009913@qq.com邮箱。商务合作也可添加作者微信进行联系!

本文原文出处:业余草: » MySQL 递归查询教程