在MySQL数据库中获取链表 [英] Fetching linked list in MySQL database

问题描述

我有一个这样的结构的MySQL数据库表：

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

我需要按照链表。例如，给出以下数据：

  id | next_id 
 ---- + --------- 
 1 | 2 
 2 | 4 
 3 | 9 
 4 | 3 
 9 | NULL 
  

我需要获取id = 1，2，4，3，9，那个订单。如何使用数据库查询？ （我可以在客户端这样做，我好奇，如果这可以在数据库端完成，所以说这是不可能的（给足够的证明））。

还有一个终止点也是很好的（例如10次提取后停止，或当行上的某些条件变为真）时，这不是一个要求（可以在客户端进行）。我（希望我）不需要检查循环引用。

解决方案

某些品牌的数据库（例如Oracle，Microsoft SQL Server ）支持额外的SQL语法来运行递归查询，但MySQL不支持任何此类解决方案。

您要描述的问题与表示一个树结构相同SQL数据库。你只有一个长而瘦的树。

从RDBMS存储和获取这种数据结构有几种解决方案。请参阅以下一些问题：

• 什么是最有效/优雅的方式将平面表解析成树？


• 是可以进行递归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）; 
  

它将像糖蜜一样执行，结果将返回一行（每个链表） ），但您会得到结果。

I have a MySQL database table with this structure:

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

I need to fetch the data in order of the linked list. For example, given this data:

 id | next_id
----+---------
  1 |       2
  2 |       4
  3 |       9
  4 |       3
  9 |    NULL

I need to fetch the rows for id=1, 2, 4, 3, 9, in that order. How can I do this with a database query? (I can do it on the client end. I am curious if this can be done on the database side. Thus, saying it's impossible is okay (given enough proof)).

It would be nice to have a termination point as well (e.g. stop after 10 fetches, or when some condition on the row turns true) but this is not a requirement (can be done on client side). I (hope I) do not need to check for circular references.

解决方案

Some brands of database (e.g. Oracle, Microsoft SQL Server) support extra SQL syntax to run "recursive queries" but MySQL does not support any such solution.

The problem you are describing is the same as representing a tree structure in a SQL database. You just have a long, skinny tree.

There are several solutions for storing and fetching this kind of data structure from an RDBMS. See some of the following questions:

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

---

Since you mention that you'd like to limit the "depth" returned by the query, you can achieve this while querying the list this way:

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

It'll perform like molasses, and the result will come back all on one row (per linked list), but you'll get the result.

这篇关于在MySQL数据库中获取链表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！