MySQL 递归查询教程

最近有网友问题我 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 拼接长度问题，建议还是从其他业务方面优化你的实现。

： » MySQL 递归查询教程