一种树形菜单表设计

menu-tree.sql

CREATE TABLE mptt(
  id       int(11) NOT NULL AUTO_INCREMENT,
  name     varchar(20) NOT NULL UNIQUE,
  left_    int(11) NOT NULL,
  right_   int(11) NOT NULL,
  PRIMARY KEY(id));
  
-- 初始根节点
INSERT INTO
  mptt(
    name,
    left_,
    right_)
VALUES
  ('Root',1, 2);

-- 向任意节点插入子节点,若中断则必须全部回退
SELECT @right := right_ FROM mptt WHERE name = 'Root';
UPDATE mptt SET left_ = left_ + 2 WHERE left_ > @right;
UPDATE mptt SET right_ = right_+ 2 WHERE right_ > @right;
INSERT INTO mptt (name, left_, right_) VALUES ('subRootA', @right, @right+1);
UPDATE mptt SET right_ = @right + 2 WHERE name = 'Root';

-- 向某节点之后插入一个节点,若中断则必须全部回退
SELECT @right := right_ FROM mptt WHERE name = 'subRootA';
UPDATE mptt SET left_ = left_ + 2 WHERE left_ > @right;
UPDATE mptt SET right_ = right_ + 2 WHERE right_ > @right;
INSERT INTO mptt (name, left_, right_) VALUES ('subRootAB', @right + 1, @right + 2);

-- 删除节点
SELECT @left := left_, @right := right_, @width := right_ - left_ + 1 FROM mptt WHERE name = 'SubAB-1';
delete FROM mptt WHERE left_ between @left and @right;
UPDATE mptt SET left_ = left_ - @width WHERE left_ > @right;
UPDATE mptt SET right_ = right_ - @width WHERE right_ > @right;

-- 查询所有节点
SELECT   node.name, node.left_, node.right_, count(node.id) AS level
FROM     mptt node, mptt parent
WHERE    node.left_ BETWEEN parent.left_ AND parent.right_
GROUP BY node.id
ORDER BY node.left_