Shulou(Shulou.com)05/31 Report--

This article is to share with you the content of an example analysis of MySQL's recursive glitch. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Mysql itself does not support recursive syntax, but can achieve some simple recursion through self-connection disguised. This paper mainly introduces the recursive minor problem of MySQL, which can be referred to by friends who need it. I hope it can help you.

-- Recursive small methods: different methods for temporary tables and regular tables-- this question uses the recursive drop table if exists test;create table test of the parent node of two temporary table queries (id varchar (100), name varchar (20), parentid varchar (100)) Insert test select'13ed38f1-3c24-dd81-492fmur673686dff0f3teacher, 'university teacher', '37e2ea0a-1c31-3412-455awhichle 5e60b8395f7d' union all select' 1ce203ac-ee34-b902-6c10muryc806f0f52876Med' primary school teacher', '37e2ea0a-1c31-3412-455amur5e60b8395f7d' union all select' 37e2ea0a-1c31-3412-455aMuve5e60b8395f7dteacher,' teacher', null union all select' c877b7ea-4ed3-f472-9527-53e1618cb1dccteacher 13ed38f1-3c24-dd81-492f union all select 673686dff0f3 'ce50a471-2955-00fa-2fb7-198f6b45b1bdbath,' middle school teacher', '37e2ea0a-1c31-3412-455a mur5e60b8395f7d' Delimiter $$create procedure usp_ser (in idd varchar (100)) begindeclare lev int;set lev=1;drop table if exists tmp1;drop table if exists tmp2;CREATE TEMPORARY TABLE tmp1 (id varchar, name varchar (20), parentid varchar (100), levv int); CREATE TEMPORARY TABLE tmp2 (pid varchar); insert tmp2 select parentid from test where id=idd;insert tmp1 select t.*, lev from test t join tmp2 an on t.idrooma.pid; while exists (select 1 from tmp2) dotruncate tmp2;set lev=lev+1 Insert tmp2 select t.id from test t join tmp1 an on t.id=a.parentid and a. Levv on. Levi Murray 1 [insert insert].] select id,name,parentid from tmp1;end;$$ delimiter; call usp_ser ('c877b7ea-4ed3-f472-9527-53e1618cb1dc') +-- +-- + | id | name | parentid | +- -- + | 13ed38f1-3c24-dd81-492f-673686dff0f3 | University teacher | 37e2ea0a-1c31-3412-455a-5e60b8395f7d | | 37e2ea0a-1c31-3412-455a-5e60b8395f7d | teacher | NULL | +-- +-- + call usp_ser ('13ed38f1-3c24-dd81-492fMel 673686dff0f3') +-- + | id | name | parentid | +-- + -+ | 37e2ea0a-1c31-3412-455a-5e60b8395f7d | teacher | NULL | +-+ call usp_ser ('37e2ea0a-1c31-3412-455amur5e60b8395f7d') Empty set (0.02 sec)

The above method uses two temporary tables because multiple references to temporary tables in the same statement are not allowed in MySQL

The following is a recursive query for querying child nodes that are completed with a regular table at one time

Core code

Drop table if exists test;create table test (id INT,parentid INT); insert test select1, 0 UNION ALL SELECT 2,1 UNION ALL SELECT 3,1 UNION ALL SELECT 4,0 UNION ALL SELECT 5,2 UNION ALL SELECT 6,5 UNION ALL SELECT 7,3; Go delimiter $create procedure usp_ser (in idd varchar (100)) begindeclare lev int;set lev=1;drop table if exists tmp1;CREATE TABLE tmp1 (id INT,parentid INT, levv INT,ppath VARCHAR (1000)); INSERT tmp1 SELECT *, lev,id FROM test WHERE parentid=idd; while row_count () > 0doset lev=lev+1 Insert tmp1 select * FROM tmp1; end;$$ delimiter; call usp_ser (0) from test t join tmp1 a concat (a.ppath on t.parentid=a.id AND levv=LEV-1; end while. ID) / * +-+ | id | parentid | ppath | +-+ | 1 | 0 | 1 | 4 | 0 | 1 | 4 | 2 | 1 | 2 | 12 | 3 | 1 | 2 | 13 | 5 | 2 | | 3 | 125 | | 7 | 3 | 3 | 137 | | 6 | 5 | 4 | 1256 | +-+ * / Thank you for your reading! This is the end of this article on "sample Analysis of Recursive minor problems of MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.