How Can I Fetch a Linked List in MySQL Without Recursive Queries?

Fetching a Linked List in MySQL Database

Database storage of linked lists poses unique challenges. Unlike other RDBMS solutions such as Oracle or Microsoft SQL Server, MySQL does not support recursive queries necessary for directly fetching linked list data.

The solution to this problem, akin to storing tree structures in an RDBMS, revolves around extracting the linked list from the database and processing it on the client end. However, this raises the question of whether it's possible to terminate the query at a certain depth or based on row conditions.

While MySQL lacks built-in recursive query capabilities, a somewhat cumbersome workaround exists:

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

This query limitations are its slow performance and the return of all results on a single row. However, it provides a way to fetch linked list data in depth-limited increments.

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