Can MySQL Retrieve Linked List Data Without Client-Side Processing?

Retrieving Linked List Data from MySQL Database

Question:

Given a MySQL database table with the structure:

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

Can you retrieve the data in the linked list order using a database query, without resorting to client-side processing?

Answer:

Unfortunately, MySQL does not support recursive queries, which are required to retrieve linked list data efficiently. This problem is analogous to representing a tree structure in a relational database.

Alternative Approaches:

Refer to the following questions for alternative methods of storing and retrieving tree-like data from a relational database system:

• "What is the most efficient/elegant way to parse a flat table into a tree?"
• "Is it possible to make a recursive SQL query ?"

Query with Limited Depth:

If you wish to limit the depth of the query result, you can employ the following approach, albeit with poor performance:

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);

The result will be returned in a single row for each linked list, but the query performance will be slow.

The above is the detailed content of Can MySQL Retrieve Linked List Data Without Client-Side Processing?. For more information, please follow other related articles on the PHP Chinese website!