当前位置:首页 > MySQL > 正文内容

MySQL5.7 用存储过程实现递归查询的方法

管理员2年前 (2018-08-27)MySQL409

本文用存储过程实现一个递归查询,也就是常见的树形目录的查询,指定一个节点,查询出该节点及所有下级的节点信息,在Oracle或 SQL Server中实现这个很容易,在MySQL 中要麻烦一些,常见的方法是拼接id的方式,最后用find_in_set函数去过滤,为了提高效率,一些数据库的结构会增加一个 path 列,存储该行数据的 id 路径集合。
测试数据准备如下:

create table test(id int primary key, parent_id int, name varchar(10));

insert into test values(1, 0, 'aaa'),(2,0,'bbb'),(3,0,'ccc');
insert into test values(11, 1, 'aaaa'),(21,2,'bbbb'),(31,3,'cccc');
insert into test values(12, 1, 'aaaaa'),(22,2,'bbbbb'),(32,3,'ccccc');
insert into test values(121, 12, 'aaaaa'),(221,22,'bbbbb'),(321,32,'ccccc');
insert into test values(13, 1, 'aaaaa'),(23,2,'bbbbb'),(33,3,'ccccc');
insert into test values(131, 13, 'aaaaa'),(231,23,'bbbbb'),(331,33,'ccccc');
insert into test values(132, 13, 'aaaaa'),(232,23,'bbbbb'),(332,33,'ccccc');
insert into test values(1321, 131, 'aaab'),(2321,232,'bbbc'),(3321,332,'cccd');
insert into test values(1211, 121, 'aaap'),(1212,121,'aaaq'),(1213,121,'aaaw');

select * from test;
mysql> select * from test;
+------+-----------+-------+
| id   | parent_id | name  |
+------+-----------+-------+
|    1 |         0 | aaa   |
|    2 |         0 | bbb   |
|    3 |         0 | ccc   |
|   11 |         1 | aaaa  |
|   12 |         1 | aaaaa |
|   13 |         1 | aaaaa |
|   21 |         2 | bbbb  |
|   22 |         2 | bbbbb |
|   23 |         2 | bbbbb |
|   31 |         3 | cccc  |
|   32 |         3 | ccccc |
|   33 |         3 | ccccc |
|  121 |        12 | aaaaa |
|  131 |        13 | aaaaa |
|  132 |        13 | aaaaa |
|  221 |        22 | bbbbb |
|  231 |        23 | bbbbb |
|  232 |        23 | bbbbb |
|  321 |        32 | ccccc |
|  331 |        33 | ccccc |
|  332 |        33 | ccccc |
| 1211 |       121 | aaap  |
| 1212 |       121 | aaaq  |
| 1213 |       121 | aaaw  |
| 1321 |       131 | aaab  |
| 2321 |       232 | bbbc  |
| 3321 |       332 | cccd  |
+------+-----------+-------+
27 rows in set (0.00 sec)

存储过程的文本如下:

drop procedure if exists sp;

delimiter $$
create procedure sp(start_id int)
begin
declare last_count int;
declare last_level int;
set last_level = 0;
drop table if exists temp;
drop table if exists result;
create temporary table temp (id int primary key, parent_id int, name varchar(10), level int);
create temporary table result (id int primary key, parent_id int, name varchar(10), level int);
insert into temp(id, parent_id, name, level) select id, parent_id, name, last_level from test where id = start_id;
while exists (select * from temp) do
    insert into result select * from temp;
    delete from temp;
    insert into temp(id, parent_id, name, level) 
    select id, parent_id, name, last_level + 1
    from test 
    where parent_id in (select id from result where level = last_level);
    set last_level = last_level + 1;
end while;
select * from result;
end$$
delimiter ;

测试一下:

mysql> call sp(12);
+------+-----------+-------+-------+
| id   | parent_id | name  | level |
+------+-----------+-------+-------+
|   12 |         1 | aaaaa |     0 |
|  121 |        12 | aaaaa |     1 |
| 1211 |       121 | aaap  |     2 |
| 1212 |       121 | aaaq  |     2 |
| 1213 |       121 | aaaw  |     2 |
+------+-----------+-------+-------+
5 rows in set (0.04 sec)

Query OK, 0 rows affected (0.06 sec)

mysql>
打赏 支付宝打赏 微信打赏
    扫描二维码至手机访问

    扫描二维码推送至手机访问。

    版权声明:本文由卖水果的net发布,如需转载请注明出处。

    转载请注明出处:http://www.msgde.net/mysql/mysql_recursion_select.html

    标签: recursion
    分享给朋友:

    发表评论

    访客

    ◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。