获取 MySQL 中分层类别的所有父级
我有一个如下所示的表格,
cat_id | name | parent_id | Level
-------------------------------------------------
1 cat1 0 1
2 subcat1 1 2
3 subcat1-subcat 2 3
我想知道获取 cat_id 3
的 parent 类别的最有效方法是什么,所以我的结果集将如下所示
cat_id | name | parent_id | Level
--------------------------------------------------
1 cat1 0 1
2 subcat1 1 2
I have a table that looks like the following
cat_id | name | parent_id | Level
-------------------------------------------------
1 cat1 0 1
2 subcat1 1 2
3 subcat1-subcat 2 3
I am wondering what is the most efficient way to get the parent categories of cat_id 3
, so my result set will look like this
cat_id | name | parent_id | Level
--------------------------------------------------
1 cat1 0 1
2 subcat1 1 2
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您必须进行多次查询。您想要升级的每个级别都有一个(或将其编写为存储过程)。
最有效的方法不是使用所谓的“邻接列表模型”(您正在使用的模型),而是切换到“嵌套集”。
谷歌搜索“嵌套集”会给你很多信息。不过,您需要一些时间来习惯它,并编写处理它的代码。
You have to do multiple queries. One for each level up you want to go (or write it as a stored procedure).
The most efficient way is not to use what's called the "adjacency list model" (what you are using) and instead switch to "nested sets".
Googling for "nested set" will give you lots of info. It will take you some time to get used to it though, and to write the code for handling it.
我遇到了同样的问题,但不幸的是,无法在单个查询中执行此操作。所以你必须编写一个函数或一个存储过程来获取所有父类别。
算法将如下
返回将包含给定类别的所有父级(平面)
1)函数:如果是函数,您将返回一个字符串值,因为函数不能返回超过1个值,因此您的结果将是类似“1,2”的东西。但这并不能完全满足我们的目的,因为我们必须在查询中使用这个结果
,所以我们将以正则表达式的形式返回结果;)那就是“^(1|2)$”
2) 存储过程:对于存储过程,我们可以动态准备一条语句,并且在执行该查询时我们将获得所需的结果。
有关存储过程的更多详细信息,请查看以下教程。
获取类别的所有嵌套父级 - MySQL
I was having the same issue, but unfortunately there is no way to do this in single query. so you have to either write a function or a stored procedure which can get all parent categories.
algo will be as follows
return will contain all parents(flat) of given category
1) FUNCTION : in case of function you will return a string value as function can not return more than 1 values so your result will be some thing like "1,2". but again this will not full fill our purpose as we have to use this result in a query
so instead we will return our result as a regular expression ;) and that is "^(1|2)$"
2) STORED PROCEDURE: in case of stored procedure we can prepare a statement dynamically and upon executing that query we will have our required result.
for more detail on stored procedure pleas have a look on following tutorial.
Get all nested parents of a category – MySQL