树形菜单数据库

SELECT * FROM menu WHERE parent_id = ?;

SELECT * FROM menu WHERE path LIKE ?;

SELECT * FROM menu WHERE lft > ? AND rgt < ?;

SELECT m.* FROM menu m JOIN menu_closure mc ON m.id = mc.descendant_id WHERE mc.ancestor_id = ?;

INSERT INTO menu (id, name, parent_id) VALUES (?, ?, ?);

INSERT INTO menu (id, name, path) VALUES (?, ?, CONCAT(path, '/', id));

-- 更新父节点的lft和rgt值
UPDATE menu SET lft = lft + 2, rgt = rgt + 2 WHERE lft > ? AND rgt < ?;
UPDATE menu SET lft = lft + 1, rgt = rgt + 1 WHERE id = ?;

INSERT INTO menu (id, name, lft, rgt) VALUES (?, ?, ?, ?);

INSERT INTO menu (id, name) VALUES (?, ?);
INSERT INTO menu_closure (ancestor_id, descendant_id, depth) VALUES (?, ?, ?), (?, ?, ?);

DELETE FROM menu WHERE id = ? OR parent_id = ?;

DELETE FROM menu WHERE path LIKE ?;

-- 更新受影响的节点的lft和rgt值
UPDATE menu SET lft = lft - (SELECT rgt - lft + 1 FROM menu WHERE id = ?), rgt = rgt - (SELECT rgt - lft + 1 FROM menu WHERE id = ?) WHERE lft > ? OR rgt < ?;

DELETE FROM menu WHERE id = ?;

DELETE FROM menu_closure WHERE descendant_id = ?;
DELETE FROM menu WHERE id = ?;