How to Retrieve Data from a Linked List in MySQL Without Recursive Queries?

Retrieving Linked List Data in MySQL

In a MySQL database, you encounter a table structure like the one provided below:

table
    id INT NOT NULL PRIMARY KEY
    data ...
    next_id INT NULL

The task is to retrieve data according to the linked list's order. Consider the following data as an example:

id	next_id
1	2
2	4
3	9
4	3
9	NULL

The result should be in this order: 1, 2, 4, 3, 9.

However, unlike certain databases such as Oracle and Microsoft SQL Server, MySQL does not support recursive queries. This problem resembles representing a tree in a SQL database, specifically a thin, elongated tree.

Various solutions exist for managing this type of data structure:

• [Representing Tree Structures in SQL Databases](https://stackoverflow.com/questions/2321851/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tr)
• [Recursive SQL Queries](https://stackoverflow.com/questions/71469/is-it-possible-to-make-a-recursive-sql-query)

To limit the query's "depth," utilize the following technique:

<code class="sql">SELECT * FROM mytable t1
 LEFT JOIN mytable t2 ON (t1.next_id = t2.id)
 LEFT JOIN mytable t3 ON (t2.next_id = t3.id)
 LEFT JOIN mytable t4 ON (t3.next_id = t4.id)
 LEFT JOIN mytable t5 ON (t4.next_id = t5.id)
 LEFT JOIN mytable t6 ON (t5.next_id = t6.id)
 LEFT JOIN mytable t7 ON (t6.next_id = t7.id)
 LEFT JOIN mytable t8 ON (t7.next_id = t8.id)
 LEFT JOIN mytable t9 ON (t8.next_id = t9.id)
 LEFT JOIN mytable t10 ON (t9.next_id = t10.id);</code>

Despite its sluggish performance, this query produces a single row for each linked list.

The above is the detailed content of How to Retrieve Data from a Linked List in MySQL Without Recursive Queries?. For more information, please follow other related articles on the PHP Chinese website!