我有一个mysql表,如下所示:
id | name | parent_id
19 | category1 | 0
20 | category2 | 19
21 | category3 | 20
22 | category4 | 21
......
现在,我想有一个mysql查询,我只需提供id[例如说'id=19'],然后我应该得到它的所有子id[即,result应该有id'20,21,22']。。。。而且,孩子们的等级制度还不清楚,它可能会有所不同。。。。
另外,我已经有了使用for循环的解决方案。。。。。如果可能的话,让我知道如何使用一个mysql查询实现同样的功能。
13条答案
按热度按时间f8rj6qna1#
在mysql中管理分层数据的blog
表结构
查询:
输出
大多数用户曾经处理过sql数据库中的层次数据,毫无疑问,他们了解到层次数据的管理并不是关系数据库的目的。关系数据库的表不是分层的(如xml),而是简单的平面列表。层次数据具有一种父子关系,这种关系不是在关系数据库表中自然表示的。阅读更多
更多详情请参阅博客。
编辑:
输出:
参考:如何在mysql中进行递归选择查询?
t98cgbkg2#
通过递归查询(ymmv-on-performance),在其他数据库中可以很容易地做到这一点。
另一种方法是存储两个额外的数据位,一个左值和一个右值。left和right值是从所表示的树结构的预先顺序遍历中派生出来的。
这就是所谓的修改前序树遍历,它允许您运行一个简单的查询来一次获取所有父值。它也被称为“嵌套集”。
q5lcpyga3#
对于mysql 8+:使用递归
with
语法。对于mysql 5.x:使用内联变量、路径id或自连接。
mysql 8版+
中指定的值
parent_id = 19
应设置为id
要选择其所有子代的父级的。mysql 5.x版
对于不支持公共表表达式的mysql版本(版本5.7之前),您可以通过以下查询实现这一点:
这是一把小提琴。
这里,中指定的值
@pv := '19'
应设置为id
要选择其所有子代的父级的。如果一个父母有多个孩子,这也会起作用。但是,要求每个记录满足条件
parent_id < id
,否则结果将不完整。查询中的变量赋值
这个查询使用特定的mysql语法:在执行过程中分配和修改变量。对执行顺序进行了一些假设:
这个
from
首先计算子句。所以这就是@pv
初始化。这个
where
子句按照从from
别名。因此,这里的条件是只包含父级已被标识为在子代树中的记录(主父级的所有子级将逐步添加到子代树中)@pv
).这里的条件
where
从句按顺序求值,一旦总结果确定,求值就中断。因此,第二个条件必须位于第二位,因为它添加了id
只有在id
通过第一个条件。这个length
函数的调用只是为了确保此条件始终为真,即使pv
字符串会因为某些原因产生错误的值。总而言之,人们可能会发现这些假设太过冒险而无法依赖。文档警告:
您可能会得到预期的结果,但这并不能保证[…]涉及用户变量的表达式的求值顺序未定义。
因此,即使它与上面的查询一致,求值顺序仍然可能更改,例如,当您添加条件或将此查询用作较大查询中的视图或子查询时。这是一个“特性”,将在以后的mysql版本中删除:
mysql的早期版本允许在语句中为用户变量赋值,而不是
SET
. 为了向后兼容,MySQL8.0支持此功能,但在将来的mysql版本中可能会删除此功能。如上所述,从mysql 8.0开始,您应该使用递归
with
语法。效率
对于非常大的数据集,此解决方案可能会变慢,因为
find_in_set
操作不是在列表中查找数字的最理想方法,当然也不是在与返回的记录数达到相同数量级的列表中。备选方案1:使用递归,连接方式
越来越多的数据库实现了sql:1999 iso 标准
WITH [RECURSIVE]
递归查询的语法(例如postgres 8.4+、sql server 2005+、db2、oracle 11gr2+、sqlite 3.8.4+、firebird 2.1+、h2、hypersql 2.1.0+、teradata、mariadb 10.2.2+)。从8.0版开始,mysql也支持它。有关要使用的语法,请参见此答案的顶部。有些数据库有一种替代的、非标准的语法用于分层查找,例如
CONNECT BY
子句在oracle、db2、informix、cubrid和其他数据库上可用。MySQL5.7版本没有提供这样的功能。如果您的数据库引擎提供了这种语法,或者您可以迁移到提供这种语法的数据库引擎,那么这无疑是最好的选择。如果没有,那么也考虑以下替代方案。
备选方案2:路径样式标识符
如果你愿意,事情会变得容易得多
id
包含层次结构信息的值:路径。例如,在您的案例中,这可能如下所示:ID名称19类别119/1类别219/1/1类别319/1/1/1类别4
然后你的
select
看起来像这样:备选方案3:重复自联接
如果知道层次结构树的深度上限,可以使用标准
sql
这样查询:看到这把小提琴了吗
这个
where
条件指定要检索其后代的父级。您可以根据需要使用更多级别扩展此查询。nvbavucw4#
对另一个问题也做了同样的事
mysql select recursive get all multiple level子级
查询将是:
jm2pwxwz5#
这对我有用,希望这对你也有用。它是w
beq87vna6#
列出第一个递归子级的简单查询:
结果:
... 使用左连接:
@tincot的解决方案是列出所有孩子的:
使用sqlfiddle在线测试它并查看所有结果。
http://sqlfiddle.com/#!9/a318e3/4/0型
kx1ctssn7#
只需使用bluem/tree php类在mysql中生成一个自关系表的树。
树 和 树\节点 是用于处理使用父id引用分层结构的数据的php类。一个典型的例子是关系数据库中的一个表,其中每个记录的“父”字段引用另一个记录的主键。当然,tree不能只使用来自数据库的数据,而是使用任何数据:您提供数据,tree使用它,而不管数据来自何处以及如何处理。阅读更多
下面是一个使用bluem/tree的示例:
v7pvogib8#
这是一个分类表。
输出:
uqzxnwby9#
如果需要快速读取,最好的选择是使用闭包表。闭包表包含每个祖先/后代对的一行。在您的示例中,闭包表如下所示
一旦有了这个表,分层查询就变得非常简单和快速。要获取类别20的所有后代:
当然,无论何时使用这样的非规范化数据,都有很大的缺点。您需要在categories表旁边维护闭包表。最好的方法可能是使用触发器,但是正确跟踪闭包表的插入/更新/删除有点复杂。与其他方法一样,您需要查看您的需求,并决定什么方法最适合您。
编辑:请参阅以下问题:在关系数据库中存储分层数据的选项是什么?更多选择。对于不同的情况有不同的最优解。
z6psavjg10#
这里没有提到的东西,虽然有点类似于接受答案的第二个备选方案,但对于大层次结构查询和easy(insert update delete)项来说是不同的和低成本的,将为每个项添加一个持久路径列。
有些人喜欢:
例子:
优化路径长度和
ORDER BY path
使用base36编码代替实数路径idhttps://en.wikipedia.org/wiki/base36
通过对编码的id使用固定长度和填充来抑制斜杠“/”分隔符
详细优化说明如下:https://bojanz.wordpress.com/2014/04/25/storing-hierarchical-data-materialized-path/
待办事项
构建一个函数或过程来分割一个项目的检索祖先的路径
z8dt9xmd11#
这是一个有点棘手的,检查这是否是为你工作
sql小提琴链接http://www.sqlfiddle.com/#!2/e3cdf/2号
适当地替换为字段和表名。
flmtquvp12#
我想出的最好办法是
使用沿袭存储\排序\跟踪树。这就足够了,阅读速度比任何其他方法都快上千倍。它还允许保持该模式,即使db将更改(因为任何db都允许使用该模式)
使用确定特定id沿袭的函数。
可以随意使用(在selects中,或在cud操作中,甚至按作业使用)。
沿袭方法描述。可以在任何地方找到,例如这里或这里。作为功能-这是什么使我着迷。
最终得到了或多或少简单、相对快速、简单的解决方案。
函数体
然后你就
希望它能帮助别人:)
os8fio9y13#
试试这些:
表定义:
实验行:
递归存储过程:
存储过程的 Package 函数:
选择示例:
输出:
筛选具有特定路径的行:
输出: