带有关系 innoDB 的递归 MySQL 查询 [英] Recursive MySQL Query with relational innoDB

问题描述

我有一个类似(简化)的表结构:

I've got a table structure like (simplified):

内容

 - id_content
 - my_string1
 - ...

content_has_content

content_has_content

  - id_content
  - id_subcontent

topic_has_content

topic_has_content

  - id_topic
  - id_content

任何主题都可以有多个内容"，任何内容"都可以有多个子内容"(内容实例).使用给定的 id_topic，我想从链接的内容、子内容、子内容的子内容等中接收所有 my_string1 的列表.

Any topic can have multiple 'content', any 'content' can have multiple 'subcontent' (instance of content). With a given id_topic I'd like to recieve a list of all my_string1 from the linked contents, subcontents, subcontents-of-subcontents, and so on.

我知道WITH"不适用于 mysql，但找不到合适的递归替代方案.

I understand "WITH" is not working for mysql but cannot find a nice recursive alternative.

谢谢丹尼尔

推荐答案

MySQL 中没有递归，而且你得到的结果是扁平的(没有结构).最好的方法仍然是在 PHP、Java 或您使用的任何编程语言中使用 while 循环.

There is no recursion in MySQL and also the result you would get would be flat (no structure). The best way is still a while loop in PHP, Java or whatever programming language you use.

查询可能如下所示:

SELECT C.*, CHC.ID_SUBCONTENT
FROM CONTENT C
LEFT OUTER JOIN CONTENT_HAS_CONTENT CHC ON CHC.ID_CONTENT = C.ID_CONTENT
WHERE C.ID = ?
... // you get the idea

并且在 PHP 中，您可以使用下一个 sub_content_id 重复查询，直到 ID_SUBCONTENT 为 null

and in PHP you could repeat the query with the next sub_content_id, until ID_SUBCONTENT is null

这篇关于带有关系 innoDB 的递归 MySQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！