mysql 查询树状结构数据

Question

有表字段为id,parent_id,name。
想要根据name模糊查询出相关的层级

比如有以下树状数据：

----食物
   ---- 水果
     ---- 香蕉
     ---- 苹果
   ---- 肉类
     ---- 鸡肉
     ---- 肥牛

查询 name like '%肉%'希望只出现以下结构

----食物
   ---- 肉类
     ---- 鸡肉

Answer 1

建议使用"闭包表"(可以百度一下)

CREATE TABLE tree(self INT,parent INT,distance INT);

+------+---------+----------+
| self | parent  | distance |
+------+---------+----------+
| 食物 |      食物 |       0 |
| 水果 |      水果 |       0 |
| 水果 |      食物 |       1 |
| 香蕉 |      香蕉 |       0 |
| 香蕉 |      水果 |       1 |
| 香蕉 |      食物 |       2 |
| 苹果 |      苹果 |       0 |
| 苹果 |      水果 |       1 |
| 苹果 |      食物 |       2 |
| 肉类 |      肉类 |       0 |
| 肉类 |      食物 |       1 |
| 鸡肉 |      鸡肉 |       0 |
| 鸡肉 |      肉类 |       1 |
| 鸡肉 |      食物 |       2 |
| 肥牛 |      肥牛 |       0 |
| 肥牛 |      肉类 |       1 |
| 肥牛 |      食物 |       2 |
+------+--------+----------+

查询带肉的字段

>> select * from table where self like '%肉%
+------+---------+----------+
| self | parent  | distance |
+------+---------+----------+
| 肉类 |      肉类 |       0 |
| 肉类 |      食物 |       1 |
| 鸡肉 |      鸡肉 |       0 |
| 鸡肉 |      肉类 |       1 |
| 鸡肉 |      食物 |       2 |
+------+--------+----------+

在代码中按self分类,合并树

from_datebase = [
    ["鸡肉", "鸡肉", 0],
    ["鸡肉", "肉类", 1],
    ["鸡肉", "食物", 2],

    ["肉类", "肉类", 0],
    ["肉类", "食物", 1],
]
from itertools import groupby

root = {}

for _, path in groupby(from_datebase, key=lambda x: x[0]):
    path = sorted(list(path), key=lambda x: -x[2])
    node = root
    for _, nodeName, _ in path:
        node = node.setdefault(nodeName, {})
print(root)  # {'食物': {'肉类': {'鸡肉': {}}}}

其他:

1. 在实际使用中,不要之间映射中文, 应该给每个分类搞个id .
2. 表的空间会比你现在大层级的层数倍, 但大多数情况下是没关系的 .
3. 插入, 更新, 删除等操作都需要重写,工作量不小,但是这是一种比较正规的做法 .

Answer 2

要利用递归查询，存储过程

Answer 3

最好分步查询，建议表上添加path字段（就是整个parent_id路径，比如鸡肉的path就是食物、肉类）。然后首先按name查询，然后做path切割后取唯一id，再把所有id的数据都查出来，丢给前端让前端自己拼树结构。