1. 查询父节点:
SELECT m.*FROM region m, (SELECT@r AS _id,(SELECT @r := parent_idFROM regionWHERE region_index_code = _id) AS parent_idFROM(SELECT @r := '000e487e13dd4867888e232ba57d59e7', @s := 0) temp,region) tWHERE m.region_index_code = t._id
2. 查询子节点:
SELECTT3.region_index_codeFROM(SELECT@region_index_code AS _ids,( SELECT @region_index_code := GROUP_CONCAT( region_index_code ) FROM region WHERE FIND_IN_SET( parent_id, @region_index_code ) ) AS T1,@l := @l + 1 AS level_ FROMregion,( SELECT @region_index_code := '15b0bfad3263415ba63c1e39da8737ce', @l := - 1 ) T4 WHERE@region_index_code IS NOT NULL ) T2,region T3 WHEREFIND_IN_SET( T3.region_index_code, T2._ids ) AND region_index_code != '15b0bfad3263415ba63c1e39da8737ce' AND `status` = 1
3.注意事项
MySql中group_concat函数很好用,可以将多列的值拼合成逗号分开的行。
但是此函数默认长度为1024个字符,经常会出现溢出的现象,溢出后拼合字符串长度就会从第1024个字符截断。
解决此问题的方法是修改函数长度
3.1 方法一:修改后需重新建立连接生效,但是mysql服务重启后又会失效。
SET GLOBAL group_concat_max_len=102400;SET SESSION group_concat_max_len=102400;
3.2 方法二:修改my.ini配置文件,重启mysql服务后生效
group_concat_max_len = 102400
参考链接:
/xubenxismile/article/details/107662209?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-1.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-1.control