获取 MySQL 数据库中的链表

2021-11-20 00:00:00 linked-list data-structures hierarchical-data mysql

我有一个具有这种结构的 MySQL 数据库表:

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

我需要按 id=1, 2, 4, 3, 9 的顺序获取行.如何使用数据库查询执行此操作?(我可以在客户端做到.我很好奇这是否可以在数据库端做到.因此，说不可能是可以的(有足够的证据)).

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

也有一个终止点会很好(例如，在 10 次提取后停止，或者当行上的某些条件变为真时)，但这不是必需的(可以在客户端完成).我(希望我)不需要检查循环引用.

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.

推荐答案

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

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.

您描述的问题与在 SQL 数据库中表示树结构相同.你只是有一棵又长又瘦的树.

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

有多种解决方案可用于从 RDBMS 存储和获取此类数据结构.请参阅以下一些问题:

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

  • "将平面表解析为树的最有效/最优雅的方法是什么?"
  • "是否可以进行递归 SQL 查询?"

既然您提到要限制查询返回的深度"，您可以在以这种方式查询列表时实现此目的:

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.

相关文章