MySQL - 如何查询树？
假设我有下表：
TABLE: category
- category_id (PK)
- parent_id (FK)
- name
给定category_id的值，如何返回给定的category_id及其所有后代？
Say I have the following table:
TABLE: category
- category_id (PK)
- parent_id (FK)
- name
Given a value for category_id, how do I return the given category_id and all its descendants?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果你想要一个单级别，你可以在条件
category_id = id OR Parent_id = id上执行SELECT - 但是使用MySQL，你无法获得带有单个查询。您可以编写一个存储过程来遍历所有中间结果并选取子子结果，但这确实不是很简洁。
相反，您可以重新设计您的桌子。在 MySQL 开发人员网站上，有一篇好文章，介绍了如何可以在表中存储分层数据，并提供比简单使用parent_id更灵活的设计。
If you want a single level, you'd do a SELECT on the condition
category_id = id OR parent_id = id- but with MySQL, you cannot get a complete tree with a single query.You can write a stored procedure to go through all of the intermediate results and pick up sub-children, but that really isn't very neat.
Instead, you can redesign your table. On the MySQL developer site, there is a nice article about how you can store hierarchical data in a table, and provides a design which is much more flexible than simply using a parent_id.