有个树形的菜单, 当初设计表时将数据存储为 level0, level1,leve2,level3..., 表名menus_month

即0层级下子层级1,孙层级2

但是带来一个问题, 如何查询这个树形结构,变得非常复杂

以下是对数据表进行关系转换, 生成两张表, menus和 menus_relastionships

1.建菜单表及关系表,由于menus_month没有对每个菜单设id, 所以id设为自增型

CREATE TABLE menus (
  id INT PRIMARY KEY auto_increment,
  name VARCHAR(255)
);

CREATE TABLE menus_relationships (
  parent int,
  child int,
  FOREIGN KEY (parent) REFERENCES menus(id),
  FOREIGN KEY (child) REFERENCES menus(id)
);

2.插入菜单

INSERT INTO menus (name)
SELECT level0 FROM menus_month 
UNION
SELECT level1 FROM menus_month 
UNION
SELECT level2 FROM menus_month 
UNION
SELECT level3 FROM menus_month;

3.插入关系表

INSERT INTO menus_relationships (parent, child)
SELECT
  (SELECT id FROM menus WHERE name = menus_month.Level0),
  (SELECT id FROM menus WHERE name = menus_month.level1)
FROM menus_month WHERE level1 IS NOT NULL
UNION
SELECT
  (SELECT id FROM menus WHERE name = menus_month.Level1),
  (SELECT id FROM menus WHERE name = menus_month.level2)
FROM menus_month WHERE level2 IS NOT NULL
UNION
SELECT
  (SELECT id FROM menus WHERE name = menus_month.Level2),
  (SELECT id FROM menus WHERE name = menus_month.level3)
FROM menus_month WHERE level3 IS NOT NULL;