MySQL 递归查询教程

公告：“业余草”微信公众号提供免费CSDN下载服务(只下Java资源)，关注业余草微信公众号，添加作者微信：xttblog2，发送下载链接帮助你免费下载！
本博客日IP超过2000，PV 3000 左右，急需赞助商。
极客时间所有课程通过我的二维码购买后返现24元微信红包，请加博主新的微信号：xttblog2，之前的微信号好友位已满，备注：返现
受密码保护的文章请关注“业余草”公众号，回复关键字“0”获得密码
所有面试题(java、前端、数据库、springboot等)一网打尽，请关注文末小程序

腾讯云】1核2G5M轻量应用服务器50元首年，高性价比，助您轻松上云

最近有网友问题我 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）！可加作者微信号：xttblog2。备注：“1”，添加博主微信拉你进微信群。备注错误不会同意好友申请。再次感谢您的关注！后续有精彩内容会第一时间发给您！原创文章投稿请发送至532009913@qq.com邮箱。商务合作也可添加作者微信进行联系！

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