MySQL 递归查询

MySQL 递归查询

CREATE TABLE T_TEST_AREA(
	ID INT,
	PID INT,
	NAME VARCHAR(20),
	DELETED INT,
	CHECKED INT
);

DELETE FROM T_TEST_AREA WHERE 1=1;
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(1, -1, '长沙市', 0, 1);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(2, -1, '深圳市', 0, 0);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(3, 1, '开福区', 0, 1);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(4, 1, '雨花区', 0, 1);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(5, 1, '岳麓区', 0, 0);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(6, 2, '宝安区', 0, 1);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(7, 2, '南山区', 0, 1);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(8, 2, '福田区', 0, 0);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(9, 4, '黎托街道', 0, 1);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(10, 4, '东山街道', 0, 1);

1、MySQL8.0以上版本可以用 with recursive

with recursive t as(
	-- 主干
	SELECT ID, PID, NAME, DELETED, CHECKED FROM T_TEST_AREA WHERE PID = 1 
	UNION all
	-- 分支
	select A.ID, A.PID, A.NAME, A.DELETED, A.CHECKED FROM T_TEST_AREA A where A.PID = T.ID
)
select * from t;

2、全适用方法–SQL语句实现

-- 查询子节点
select t4.* from
(
	select @ids _ids, (select @ids := group_concat(id) from T_TEST_AREA where find_in_set(pid, @ids)) 
	from T_TEST_AREA t1, (select @ids := '1') t2
	where @ids is not null
) t3, T_TEST_AREA t4
where find_in_set(t4.id, t3._ids);

-- result
ID|PID|NAME|DELETED|CHECKED|
--|---|----|-------|-------|
 1| -1|长沙市 |      0|      1|
 3|  1|开福区 |      0|      1|
 4|  1|雨花区 |      0|      1|
 5|  1|岳麓区 |      0|      0|
 9|  4|黎托街道|      0|      1|
10|  4|东山街道|      0|      1|


-- 查询父节点
select t3.* from  T_TEST_AREA t3,
(
	select @ids fids, (select @ids := pid from T_TEST_AREA where id=@ids)
	from T_TEST_AREA t1, (select @ids := 9) t2
	where @ids is not null and @ids != -1
) t4 where t4.fids = t3.id;

-- result
ID|PID|NAME|DELETED|CHECKED|
--|---|----|-------|-------|
 9|  4|黎托街道|      0|      1|
 4|  1|雨花区 |      0|      1|
 1| -1|长沙市 |      0|      1|

注意：group_concat结果的默认长度为1024，当数据量较大时，可能会出现数据缺失的情况，可以通过调整group_concat长度来解决。

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_group_concat_max_len

-- 设置group_concat结果长度
set global group_concat_max_len = 10240;
set session group_concat_max_len = 10240;
show variables like 'group_concat_max_len';

修改配置文件：my.ini

在[mysqld]下新增配置：group_concat_max_len = 102400

group_concat_max_len = -1则是采用最大字节数