前言最近在做的业务场景涉及到数据库的递归查询。我们公司用的Oracle，众所周知，Oracle自带递归查询功能，所以实现起来非常简单。不过我记得mysql是没有递归查询功能的，那么在mysql中应该怎么实现呢？于是，就有了这篇文章。文章主要知识点：Oracle递归查询，startwithconnectbypriorusagefind_in_set函数concat,concat_ws,group_concat函数MySQL自定义函数手动实现MySQL递归查询Oracle递归查询在Oracle中通过startwithconnectby的语法实现之前的。根据先验关键字是在子节点上还是在父节点上，以及是否包含当前查询的节点，分为四种情况。prior在子节点端（递归向下）第一种情况：以子节点id='querynode'开始connectbyprior子节点id=父节点idselect*fromdeptstartwithid='1001'connetbypriorid=pid;这里根据条件id='1001'递归查询当前节点及其子节点。查询结果包括自身和所有子节点。第二种情况：从父节点id='querynode'开始connectbyprior子节点id=parentnodeidselect*fromdeptstartwithpid='1001'connectbypriorid=pid;这里根据条件pid='1001'对当前节点Node的所有子节点进行递归查询。查询结果只包括它的所有子节点，不包括它本身。其实想想也对，因为起始条件是以父节点为根节点，向下递归，自然不包括当前节点。prior在父节点端（递归向上）第三种情况：startwithchildnodeid='querynode'connectbypriorparentnodeid=childnodeidselect*fromdeptstartwithid='1001'connectbypriorpid=id;这里根据条件id='1001'，递归查询当前节点及其父节点。查询结果包括自身及其所有父节点。第四种情况：从父节点id='querynode'开始connectbypriorparentnodeid=childnodeidselect*fromdeptstartwithpid='1001'connectbypriorpid=id;这里根据条件pid='1001'，当前节点的第一个节点代表子节点及其父节点递归查询。查询结果包括自己的第一代子节点和所有父节点。（包括你自己）其实这种情况很好理解，因为查询起始条件是以父节点为根节点，向上递归，自然要包括当前父节点。以上四种情况乍看之下可能会比较混乱，容易记住，其实不然。我们只需要记住prior的位置是在子节点的末尾，然后向下递归，在父节点的末尾向上递归。如果起始条件是子节点，自然包含自己的节点。如果起始条件是父节点，向下递归自然不包括当前节点。对于向上递归，需要包含当前节点及其第一代子节点。MySQL递归查询可以看到，Oracle实现递归查询非常方便。但是MySQL并没有帮我们处理，所以需要我们自己手动实现递归查询。为了方便，我们创建一张部门表，插入几条可以形成递归关系的数据。DROPTABLEIFEXISTS`dept`;CREATETABLE`dept`(`id`varchar(10)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNOTNULL,`name`varchar(255)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNULLDEFAULTNULL,`pid`varchar(10)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNULLDEFAULTNULL,PRIMARYKEY(`id`)USINGBTREE)ENGINE=InnoDBCHARACTERSET=utf8mb4COLLATE=utf8mb4_general_ciROW_FORMAT=动态；INSERTINTO`dept`(`id`,`name`,`pid`)VALUES('1000','HeadOffice',NULL);INSERTINTO`dept`(`id`,`name`,`pid`)VALUES('1001','北京分公司','1000');INSERTINTO`dept`(`id`,`name`,`pid`)VALUES('1002','上海分公司','1000');INSERTINTO`dept`(`id`,`name`,`pid`)VALUES('1003','北京研发部','1001');INSERTINTO`dept`(`id`,`name`,`pid`)VALUES('1004','北京财务部','1001');INSERTINTO`dept`(`id`,`name`,`pid`)VALUES('1005','北京市场部','1001');INSERTINTO`dept`(`id`,`name`,`pid`)VALUES('1006','北京研发1部','1003');INSERTINTO`dept`(`id`,`name`,`pid`)VALUES('1007','北京研发二部','1003');INSERTINTO`dept`(`id`,`name`,`pid`)VALUES('1008','北京研发部及某集团','1006');插入TINTO`dept`(`id`,`name`,`pid`)VALUES('1009','北京研发一、二组','1006');INSERTINTO`dept`(`id`,`name`,`pid`)VALUES('1010','北京研发2部1组','1007');INSERTINTO`dept`(`id`,`name`,`pid`)VALUES('1011','北京研发二部二组','1007');INSERTINTO`dept`(`id`,`name`,`pid`)VALUES('1012','北京市场一','1005');INSERTINTO`dept`(`id`,`name`,`pid`)VALUES('1013','上海研发部','1002');INSERTINTO`dept`(`id`,`name`,`pid`)VALUES('1014','上海研发1部','1013');INSERTINTO`dept`(`id`,`name`,`pid`)VALUES('1015','上海研发部2','1013');没错，刚才Oracle递归用到了这张表图1另外，在这之前，我们需要复习一下MYSQL中的几个函数，后面会用到。find_in_set函数函数语法：find_in_set(str,strlist)str表示要查询的字符串，strlist是逗号分隔的字符串，如('a,b,c')。该函数用于查找str字符串在字符串strlist中的位置，返回结果为1~n。如果找不到则返回0。例如：selectFIND_IN_SET('b','a,b,c,d');结果返回2。因为b的位置是第二个子串位置。另外，在查询表数据时，它还有一个用法，如下：select*fromdeptwhereFIND_IN_SET(id,'1000,1001,1002');结果返回id在strlist中的所有记录，即id='1000'、id='1001'、id='1002'三个记录。看到这里，不知道大家对我们要解决的递归查询有没有什么启发。以递归查询到所有子节点为例。我想，是不是可以找到一个包含当前节点和所有子节点的逗号拼接的字符串strlist，传入find_in_set函数。您可以查询所有需要的递归数据。那么，现在的问题就转化为如何构造这样一个字符串strlist。这需要使用以下字符串连接函数。concat、concat_ws、group_concat函数1、字符串拼接函数中，最基本的就是concat。它用于连接N个字符串，例如selectCONCAT('M','Y','S','Q','L')fromdual；结果是“MYSQL”字符串。2.concat使用逗号作为默认分隔符，而concat_ws可以指定分隔符，第一个参数传入分隔符，比如下划线分隔。3、group_concat功能更强大。它可以在分组时将字段组合成具有特定分隔符的字符串。Usage:group_concat([distinct]需要拼接的字段[orderbysortingfieldasc/desc][separator'separator'])可以看到有可选参数，可以对需要拼接的字段的值进行去重，你也可以对它们进行排序。指定分隔符。如果不指定，默认以逗号分隔。对于dept表，我们可以用逗号连接表中的所有id。（这里没有用到groupbyfield，可以认为只有一组）MySQL自定义函数，实现递归查询，可以发现上面的字符串拼接问题也解决了。那么，问题就变成了如何构造具有递归关系的字符串。我们可以自定义一个函数，通过传入根节点id来查找它所有的子节点。以向下递归为例。（一边讲解自定义函数写法一边讲解递归逻辑）delimiter$$dropfunctionifexistsget_child_list$$createfunctionget_child_list(in_idvarchar(10))returnsvarchar(1000)begindeclareidsvarchar(1000)default'';declaretempidsnotchar(1000);settempids=in_id;whiletempidosetisis(',',ids,tempids);selectGROUP_CONCAT(id)intotempidsfromdeptwhereFIND_IN_SET(pid,tempids)>0;endwhile;returnids;end$$delimiter;(1)定界符$$用于定义终止符。我们知道MySQL默认的结束符是分号，表示命令结束，执行完毕。但是在函数体中，有时候我们希望分号不要结束，所以我们需要临时将结束符改成一个随机的其他值。我这里设置为$$，意思是遇到$$就结束，执行当前语句。(2)如果存在get_child_list$$则删除函数。如果函数get_child_list已经存在，先删除它。请注意，需要使用当前自定义终止符$$来结束和执行该语句。因为，这里，数字和下面的函数体是需要分开执行的。(3)createfunctionget_child_list创建函数。并将该参数传递给根节点的子节点id。需要注意参数的类型和长度，比如这里的varchar(10)。returnsvarchar(1000)用于定义返回值参数类型。(4)函数体由begin和end包围。用来写具体的逻辑。(5)declare用于声明变量，default可用于设置默认值。这里定义的ids是整个函数的返回值，用来拼接成我们需要的最终逗号分隔的递归字符串。而tempids就是用逗号记录while循环中临时产生的所有子节点的字符串。(6)set用于给变量赋值。这里将传入的根节点分配给tempids。(7)whiledo...endwhile;循环语句，包括循环逻辑。请注意，在endwhile的末尾需要一个分号。在循环体中，使用CONCAT_WS函数将最终结果id和临时生成的tempids用逗号拼接在一起。然后以FIND_IN_SET(pid,tempids)>0为条件，遍历tempids中的所有pid，找到所有以this为父节点的子节点id，通过GROUP_CONCAT(id)将这些子节点id用逗号拼接成tempidsup，并覆盖更新时间。下一个循环进来的时候，ids会重新拼接起来，所有子节点的所有子节点都会重新查找。循环往复，逐层递归遍历子节点。直到判断tempids为空，说明遍历完所有子节点，整个循环结束。这里以'1000'为例，即：（参考图1中的表数据关系）第一次循环：tempids=1000ids=1000tempids=1001,1002（1000的所有子节点）第二次循环：tempids=1001,1002ids=1000,1001,1002tempids=1003,1004,1005,1013（1001和1002的所有子节点）第三次循环：tempids=1003,1004,1005,1013ids=1000,1001,1002,1003,1004,1005,1013tempids=1003and1004and1005and1013的所有子节点...最后一个循环，因为找不到子节点，tempids=null，循环结束。(8)返回ids；用于返回ids作为函数的返回值。(9)函数体结束后，记得用终止符$$来结束整个逻辑并执行。(10)最后，不要忘记将终止符重新设置为默认的终止符分号。自定义函数完成后，我们就可以使用它来递归查询我们需要的数据了。比如我查询北京研发部的所有子节点。上面是递归向下查询所有子节点，包括当前节点，或者修改逻辑不包括当前节点，就不演示了。手动实现递归查询（向上递归）比向下递归更简单。因为向下递归时，每一层递归的父节点对应多个子节点。向上递归时，每一层递归的子节点只对应一个父节点，关系比较简单。同样，我们可以定义一个函数get_parent_list来获取根节点的所有父节点。分隔符$$dropfunctionifexistsget_parent_list$$createfunctionget_parent_list(in_idvarchar(10))returnsvarchar(1000)begindeclareidsvarchar(1000);declaretempidvarchar(10);settempid=in_id;whiletempidisnotnulldosetids=CONCAT_WS(',',ids,tempid);selectpidintotempidfromdeptwhereid=tempid;endwhile;returnids；结束$$定界符；找到北京研发部2和组1，及其递归父节点，如下：注意我们使用group_concat函数来拼接字符串。不过需要注意的是它有长度限制，默认是1024字节。可以通过showvariableslike"group_concat_max_len"来查看；注意单位是字节，不是字符。在MySQL中单个字母占1个字节，而在我们平时使用的utf-8下，一个汉字占3个字节。这对于递归查询来说还是很致命的。因为在一般的递归中，关系层级比较深，很可能会超过最大长度。（虽然一般拼接的都是数字串，也就是单字节）那么，我们有两种方法来解决这个问题：修改MySQL配置文件my.cnf，增加group_concat_max_len=102400#你想要的最大长度。执行以下任何语句。设置全局group_concat_max_len=102400；或SETSESSIONgroup_concat_max_len=102400；它们的区别在于global是全局的，任何开启一个新的session都会生效，但是注意已经开启的当前session不会生效。该会话只会在当前会话中生效，不会在其他会话中生效。它们的共同点是都在MySQL重启后失败，以配置文件中的配置为准。所以建议直接修改配置文件。102400的长度通常就足够了。假设一个id的长度是10个字节，可以拼出10000个id。另外，使用group_concat函数还有一个限制，就是limit不能同时使用。比如我只想查5条数据拼接，现在没有生效。但是，如果需要，可以通过子查询来实现。本文转载自微信公众号“烟雨星空”，可通过以下二维码关注。转载本文请联系烟雨星空公众号。
