How to Fetch Data from a Linked List Structure in MySQL?

Fetching Data from a Linked List in MySQL Database

In a MySQL database with a table containing a linked list structure, such as the one provided:

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

The challenge lies in retrieving data in the same order as the linked list. Unfortunately, MySQL does not directly support recursive queries, which are necessary for retrieving linked list structures.

However, there are approaches to work around this limitation. One method is to manually traverse the linked list using JOIN queries, as shown in the given response:

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

This query effectively performs a breadth-first traversal of the linked list, returning all rows in the order of their position in the list.

Alternatively, as suggested in the comments, you can store the linked list data in a NoSQL database, which typically provides better support for handling non-relational data structures.

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