只需创建一个查询函数,传入父节点ID,将所有子节点记录的ID拼成字符串返回。
CREATE FUNCTION getChild(parentId VARCHAR(1000))
RETURNS VARCHAR(5000)
BEGIN
DECLARE oTemp VARCHAR(5000);
DECLARE oTempChild VARCHAR(5000);
SET oTemp = '';
SET oTempChild = parentId;
WHILE oTempChild is not null DO
IF oTemp != '' THEN
SET oTemp = concat(oTemp, ',', oTempChild);
ELSE
SET oTemp = oTempChild;
END IF;
SELECT group_concat(ID) INTO oTempChild FROM sys_web_menu where ID<>parentId and FIND_IN_SET(parent_id, oTempChild)>0;
END WHILE;
RETURN oTemp;
END
调用函数,即可将该父ID下的所有子节点记录查询出。
SELECT * FROM sys_menu WHERE FIND_IN_SET(ID, getChild('123456'));
解释说明:
FIND_IN_SET(str, strlist)
str 要查询的字符串
strlist 字段名 参数以”,”分隔 如 (1,2,6,8,10,22)
查询字段(strlist)中包含(str)的结果,返回结果为null或记录
假如字符串str在由N个子链组成的字符串列表strlist 中,则返回值的范围在 1 到 N 之间。 一个字符串列表就是一个由一些被 ‘,' 符号分开的子链组成的字符串。如果第一个参数是一个常数字符串,而第二个是type SET列,则FIND_IN_SET() 函数被优化,使用比特计算。 如果str不在strlist 或strlist 为空字符串,则返回值为 0 。如任意一个参数为NULL,则返回值为 NULL。这个函数在第一个参数包含一个逗号(‘,')时将无法正常运行。
例:
select FIND_IN_SET('2', '1,2'); /* 返回2 */
select FIND_IN_SET('6', '1'); /* 返回0 strlist中不存在str,所以返回0。 */
group_concat()
将group by产生的同一个分组中的值连接起来,返回一个字符串结果。
group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
说明:通过使用distinct可以排除重复值;如果希望对结果中的值进行排序,可以使用order by子句;separator是一个字符串值,缺省为一个逗号。
可能出现的问题
当字段值内容过多时,可能会导致返回结果单字段值过长,而报如下错误,
Data truncated for column 'getChild('123456')' at row 1
mysql单个返回值内容过多导致默认长度(1024)不够,显示不全
查询单个返回值默认长度
show VARIABLES like 'group_concat_max_len';
临时设置:
SET SESSION group_concat_max_len = 10240;
#设置当前session的group_concat长度,其他session连接不受影响
#当mysql重启后失效
永久修改group_concat_max_len,需要修改my.cnf或my.ini文件,在文件中找到[mysqld] 在后面添加group_concat_max_len=10240,保存之后然后重启mysql即可。