Shulou(Shulou.com)06/02 Report--

In order to solve the problem of how to use recursive query to solve the endless loop in MYSQL, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Here is an example of a recursive dead loop

Let's explain CTE recursion first.

1 Recursive query contains at least two subqueries. The purpose of the first query is to set the initial value of the recursion.

2 the second query becomes a recursive query, and the second query invokes the result of the first query, and then starts the loop

They are connected by union all.

In a recursive query, it stops when the results of the query do not match or exceed the number of recursions. Or the query will be terminated after the maximum cte_max_recursion_depth is set when the execution is found to be a dead loop.

There are 3636 problems in recursive queries, which are divided into two types.

1 data problems (this is a common cause of recursion problems)

2 there is a problem with the writing of SQL recursion

According to 1, the probability of problems is high, and it is difficult to troubleshoot, so we need to add some statements when writing SQL to avoid recursive problems.

Method 1, using distinct, remove the duplicated data by adding distinct after the union. Most of the endless loops are because there is duplicate data, so the data can be found. But the problem is that it is possible in WORKBENCH, but the statement is reported as an error in MYSQL program, which I can't explain.

2 the LIMIT statement is introduced into MYSQL 8.109 to limit the amount of output data through LIMIT, which opportunely avoids some 3636 errors.

This method is OK under both workbench and MYSQL commands.

In practice, it is possible to use another way to automatically set the end of the endless loop.

WITH RECURSIVE cte_all AS

(

SELECT dname AS Child

FROM cte_test

WHERE rname='Tim'

UNION all

SELECT r.dname

FROM cte_test r, cte_all d

WHERE r.rname=d.Child

)

SELECT / * + MAX_EXECUTION_TIME (1000) * / * FROM cte_all

This way of writing is OK in workbench, but it is still not allowed on the MYSQL command line.

Of course, the most important thing to go around is to repair the data that leads to an endless loop.

After repairing the data, execute the query here, and the problem is solved.

The answer on how to use recursive query to solve the endless loop problem in MYSQL is shared here. I hope the above content can be of some help to everyone. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.