Ich habe eine MySQL-Tabelle wie unten gezeigt:
id | Name | parent_id |
---|---|---|
19 | Kategorie 1 | 0 |
20 | Kategorie 2 | 19 |
21 | Kategorie 3 | 20 |
22 | Kategorie 4 | 21 |
... | ... | ... |
Jetzt möchte ich eine MySQL-Abfrage, bei der ich nur die ID [z. B. id=19
] bereitstelle und dann alle Unter-IDs erhalten sollte [d. h. das Ergebnis sollte die ID „20,21,22“ haben]....
Die Hierarchie der Kinder ist unbekannt; sie kann variieren...
Ich weiß, wie man das mit einer for
-Schleife macht ... aber wie erreiche ich dasselbe mit einer einzelnen MySQL-Abfrage?
对于MySQL 8+:使用递归
使用
语法。对于 MySQL 5.x: 使用内联变量、路径 ID 或自连接。
MySQL 8+
parent_id = 19
中指定的值应设置为您要选择其所有后代的父级的id
。MySQL 5.x
对于不支持公共表表达式的 MySQL 版本(最高版本 5.7),您可以使用以下查询来实现此目的:
这是一个小提琴。
此处,
@pv := '19'
中指定的值应设置为您要选择其所有后代的父级的id
。 p>如果父母有多个孩子,这也将起作用。但要求每条记录都满足
parent_id 条件,否则结果不完整。
查询内的变量赋值
该查询使用特定的 MySQL 语法:在执行期间分配和修改变量。对执行顺序做了一些假设:
from
子句。这就是@pv
初始化的地方。from
别名检索的顺序对每条记录评估where
子句。因此,这里设置的条件仅包括父级已被识别为位于后代树中的记录(主要父级的所有后代都将逐步添加到@pv
)。where
子句中的条件按顺序求值,一旦总结果确定,求值就会中断。因此,第二个条件必须位于第二位,因为它将 id 添加到父列表中,并且只有在 id 通过第一个条件时才会发生这种情况。调用length
函数只是为了确保此条件始终为真,即使pv
字符串由于某种原因会产生虚假值。总而言之,人们可能会发现这些假设风险太大,无法依赖。 文档警告:
因此,即使它与上述查询一致,评估顺序仍然可能会发生变化,例如,当您添加条件或将此查询用作较大查询中的视图或子查询时。这是一个将在未来的 MySQL 版本中删除的“功能” 一>:
如上所述,从 MySQL 8.0 开始,您应该使用递归
with
语法。效率
对于非常大的数据集,此解决方案可能会变慢,因为
find_in_set
操作不是在列表中查找数字的最理想方式,当然也不是在达到与返回的记录数。替代方案 1:
使用递归
,连接
越来越多的数据库实现 SQL:1999 ISO 标准
WITH [RECURSIVE]递归查询的
语法(例如 Postgres 8.4+ 、SQL Server 2005+ a>、DB2、Oracle 11gR2+,SQLite 3.8.4+,Firebird 2.1+、H2、HyperSQL 2.1.0+,Teradata,MariaDB 10.2.2+)。从 版本 8.0 开始,MySQL 也支持它。请参阅此答案的顶部以了解要使用的语法。某些数据库具有用于分层查找的替代非标准语法,例如 Oracle,DB2,Informix、CUBRID a> 和其他数据库。
MySQL 5.7 版不提供这样的功能。当您的数据库引擎提供此语法或者您可以迁移到提供此语法的数据库引擎时,那么这无疑是最佳选择。如果没有,请考虑以下替代方案。
替代方案 2:路径样式标识符
如果您分配包含分层信息(路径)的 id 值,事情就会变得容易得多。例如,在您的情况下,这可能如下所示:
然后您的
选择
将如下所示:替代方案 3:重复自连接
如果您知道层次结构树的深度上限,则可以使用标准
sql
查询,如下所示:请参阅此小提琴
where
条件指定您要检索哪个父代的后代。您可以根据需要扩展此查询更多级别。