mysql无限极分类

关注

原创最新推荐文章于 2026-03-08 00:23:27 发布 1k 阅读 AI 写同款· GEO 优化 ›

CREATE TABLE `tree` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`lft` INT(10) NOT NULL DEFAULT '0' COMMENT '左节点',
`rgt` INT(10) NOT NULL DEFAULT '0' COMMENT '右节点',
`status` INT(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除 1是 0否',
PRIMARY KEY (`id`),
KEY `lft` (`lft`),
KEY `rgt` (`rgt`),
KEY `status` (`status`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

第一个节点
INSERT INTO tree VALUES (NULL, 'A', 0, 1, 0);
SELECT * FROM tree

$a_l = 0; $a_r = 1;
第二个节点（A的子节点）
UPDATE tree SET lft = lft + 2 WHERE lft > 1;#lft > $a_r
UPDATE tree SET rgt = rgt + 2 WHERE rgt >= 1;#rgt >= $a_r
INSERT INTO tree VALUES (NULL, 'B', 1, 1+1, 0);#(NULL, 'B', $a_r, $a_r + 1, 0);

$a_l = 0; $a_r = 3;
第三个节点（A的子节点）
UPDATE tree SET lft = lft + 2 WHERE lft > 3;#lft > $a_r
UPDATE tree SET rgt = rgt + 2 WHERE rgt >= 3;#rgt >= $a_r
INSERT INTO tree VALUES (NULL, 'C', 3, 3+1, 0);#(NULL, 'C', $a_r, $a_r + 1, 0);

$a_l = 0; $a_r = 5;
第四个节点（A的子节点）
UPDATE tree SET lft = lft + 2 WHERE lft > 5;#lft > $a_r
UPDATE tree SET rgt = rgt + 2 WHERE rgt >= 5;#rgt >= $a_r
INSERT INTO tree VALUES (NULL, 'D', 5, 5+1, 0);#(NULL, 'D', $a_r, $a_r + 1, 0);

$b_l = 1; $b_r = 2;
第五个节点（B的子节点）
UPDATE tree SET lft = lft + 2 WHERE lft > 2;#lft > $b_r
UPDATE tree SET rgt = rgt + 2 WHERE rgt >= 2;#rgt >= $b_r
INSERT INTO tree VALUES (NULL, 'E', 2, 2+1, 0);#(NULL, 'E', $b_r, $b_r + 1, 0);

$b_l = 1; $b_r = 4;
第六个节点（B的子节点）
UPDATE tree SET lft = lft + 2 WHERE lft > 4;#lft > $b_r
UPDATE tree SET rgt = rgt + 2 WHERE rgt >= 4;#rgt >= $b_r
INSERT INTO tree VALUES (NULL, 'F', 4, 4+1, 0);#(NULL, 'F', $b_r, $b_r + 1, 0);

$e_l = 2; $e_r = 3;
第六个节点（E的子节点）
UPDATE tree SET lft = lft + 2 WHERE lft > 3;#lft > $e_r
UPDATE tree SET rgt = rgt + 2 WHERE rgt >= 3;#rgt >= $e_r
INSERT INTO tree VALUES (NULL, 'G', 3, 3+1, 0);#(NULL, 'G', $e_r, $e_r + 1, 0);

$e_l = 2; $e_r = 5;
删除节点（E节点）
$v = 5 - 2 + 1 = 4;#$v = $e_r - $e_l + 1;
DELETE FROM tree WHERE lft >= 2 AND rgt <= 5;#lft >= $e_l and rgt <= $e_r
UPDATE tree SET lft = lft - 4 WHERE lft > 2;#lft = lft - $v where lft > $e_l
UPDATE tree SET rgt = rgt - 4 WHERE rgt > 5;#rgt = rgt - $v WHERE rgt > $e_r

$b_l = 1; $b_r = 4;
删除节点（B节点）
$v = 4 - 1 + 1 = 4;#$v = $b_r - $b_l + 1;
DELETE FROM tree WHERE lft >= 1 AND rgt <= 4;#lft >= $b_l and rgt <= $b_r
UPDATE tree SET lft = lft - 4 WHERE lft > 1;#lft = lft - $v where lft > $b_l
UPDATE tree SET rgt = rgt - 4 WHERE rgt > 4;#rgt = rgt - $v WHERE rgt > $b_r

总结：
一、根节点
lft=0,rgt=1
二、添加子节点:
找到父节点的右值$p_r，所有节点左值 > $p_r 的左值 + 2；所有节点右值 >= $p_r的右值 +2;插入新节点lft=$p_r,rgt=$p_r + 1;
三、删除节点：
找到所要删除节点的左右值$del_l,$del_r,删除所有左值>=$del_l,右值<=$del_r的节点；计算$v = $del_r - $del_l + 1;修改节点左值 > $del_l的左值为lft = lft - $v;修改节点右值 > $del_r 的右值为rgt = rgt - $v
四、获取子节点
1、不包含自己lft > $current_l and rgt < $current_r
2、包含自己lft >= $current_l and rgt <= $current_r
3、不包含自己的所有父类 lft < $current_l and rgt > $current_r
4、包含自己的所有父类 lft <= $current_l and rgt >= $current_r
五、计算子节点的数量
($current_r - $current_l - 1)/2
六、叶子节点
rgt = lft + 1
七、树的深度
SELECT node.*, (COUNT(parent.name) - 1) AS deep FROM tree AS node,tree AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt GROUP BY node.name ORDER BY node.lft
八、检索单一路径
SELECT parent.* FROM tree AS node, tree AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.name = 'E' ORDER BY parent.lft;
九、获取直接子节点
SELECT * FROM (SELECT node.*, (COUNT(parent.name) - 1) AS deep FROM tree AS node,tree AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt GROUP BY node.name ORDER BY node.lft ) AS a WHERE a.deep<= 1;

<?php

/**
 * 基于左右值排序的无限分类算法 
 * 数据库结果为 
  CREATE TABLE om_catagory (
  CatagoryID int(10) unsigned NOT NULL auto_increment,
  Name varchar(50) default '',
  Lft int(10) unsigned NOT NULL default '0',
  Rgt int(10) unsigned NOT NULL default '0',
  PRIMARY KEY (id),
  KEY lft (lft),
  KEY rgt (rgt)
  )
 */
class sortclass {

    /**
     * Description 
     * @var        
     * @since     1.0 
     * @access    private 
     */
    var $db;

    /**
     * Description 
     * @var        
     * @since     1.0 
     * @access    private 
     */
    var $tablefix;

    /**
     * Short description.  
     * 构造函数,引入数据库操作类函数 
     * Detail description 
     * @param      none 
     * @global     none 
     * @since      1.0 
     * @access     private 
     * @return     void 
     * @update     date time 
     */
    function sortclass() {
        global $db;
        $this->db = $db;
        $this->tablefix = "om_";
    }

// end func  

    /**
     * Short description.  
     * 增加新的分类 
     * Detail description 
     * @param      none 
     * @global     none 
     * @since      1.0 
     * @access     private 
     * @return     void 
     * @update     date time 
     */
    function addsort($CatagoryID, $SortName) {
        if ($CatagoryID == 0) {
            $Lft = 0;
            $Rgt = 1;
        } else {
            $Result = $this->checkcatagory($CatagoryID);
            //取得父类的左值,右值  
            $Lft = $Result['Lft'];
            $Rgt = $Result['Rgt'];
            $this->db->query("UPDATE `" . $this->tablefix . "catagory` SET `Lft`=`Lft`+2 WHERE `Lft`>$Rgt");
            $this->db->query("UPDATE `" . $this->tablefix . "catagory` SET `Rgt`=`Rgt`+2 WHERE `Rgt`>=$Rgt");
        }

//插入  
        if ($this->db->query("INSERT INTO `" . $this->tablefix . "catagory` SET `Lft`='$Rgt',`Rgt`='$Rgt'+1,`Name`='$SortName'")) {
            //$this->referto("成功增加新的类别","JAVASCRIPT:HISTORY.BACK(1)",3);  
            return 1;
        } else {
            //$this->referto("增加新的类别失败了","JAVASCRIPT:HISTORY.BACK(1)",3);  
            return -1;
        }
    }

// end func  

    /**
     * Short description.  
     * 删除类别 
     * Detail description 
     * @param      none 
     * @global     none 
     * @since      1.0 
     * @access     private 
     * @return     void 
     * @update     date time 
     */
    function deletesort($CatagoryID) {
//取得被删除类别的左右值,检测是否有子类,如果有就一起删除  
        $Result = $this->checkcatagory($CatagoryID);
        $Lft = $Result['Lft'];
        $Rgt = $Result['Rgt'];
//执行删除  
        if ($this->db->query("DELETE FROM `" . $this->tablefix . "catagory` WHERE `Lft`>=$Lft AND `Rgt`<=$Rgt")) {
            $Value = $Rgt - $Lft + 1;
            //更新左右值  
            $this->db->query("UPDATE `" . $this->tablefix . "catagory` SET `Lft`=`Lft`-$Value WHERE `Lft`>$Lft");
            $this->db->query("UPDATE `" . $this->tablefix . "catagory` SET `Rgt`=`Rgt`-$Value WHERE `Rgt`>$Rgt");
            //$this->referto("成功删除类别","javascript:history.back(1)",3);  
            return 1;
        } else {
            //$this->referto("删除类别失败了","javascript:history.back(1)",3);  
            return -1;
        }
    }

// end func  

    /**
     * Short description.  
     * 1,所有子类,不包含自己;2包含自己的所有子类;3不包含自己所有父类4;包含自己所有父类 
     * Detail description 
     * @param      none 
     * @global     none 
     * @since      1.0 
     * @access     private 
     * @return     void 
     * @update     date time 
     */
    function getcatagory($CatagoryID, $type = 1) {
        $Result = $this->checkcatagory($CatagoryID);
        $Lft = $Result['Lft'];
        $Rgt = $Result['Rgt'];
        $SeekSQL = "SELECT * FROM `" . $this->tablefix . "catagory` WHERE ";
        switch ($type) {
            case "1":
                $condition = "`Lft`>$Lft AND `Rgt`<$Rgt";
                break;
            case "2":
                $condition = "`Lft`>=$Lft AND `Rgt`<=$Rgt";
                break;
            case "3":
                $condition = "`Lft`<$Lft AND `Rgt`>$Rgt";
                break;
            case "4":
                $condition = "`Lft`<=$Lft AND `Rgt`>=$Rgt";
                break;
            default :
                $condition = "`Lft`>$Lft AND `Rgt`<$Rgt";
                ;
        }
        $SeekSQL .= $condition . " ORDER BY `Lft` ASC";
        $Sorts = $this->db->getrows($SeekSQL);
        return $Sorts;
    }

// end func  

    /**
     * Short description.  
     * 取得直属父类 
     * Detail description 
     * @param      none 
     * @global     none 
     * @since      1.0 
     * @access     private 
     * @return     void 
     * @update     date time 
     */
    function getparent($CatagoryID) {
        $Parent = $this->getcatagory($CatagoryID, 3);
        return $Parent;
    }

// end func  
    /**
     * Short description.  
     * 移动类,如果类有子类也一并移动 
     * Detail description 
     * @param      none 
     * @global     none 
     * @since      1.0 
     * @access     private 
     * @return     void 
     * @update     date time 
     */

    function movecatagory($SelfCatagoryID, $ParentCatagoryID) {
        $SelfCatagory = $this->checkcatagory($SelfCatagoryID);
        $NewCatagory = $this->checkcatagory($ParentCatagoryID);

        $SelfLft = $SelfCatagory['Lft'];
        $SelfRgt = $SelfCatagory['Rgt'];
        $Value = $SelfRgt - $SelfLft;
//取得所有分类的ID方便更新左右值  
        $CatagoryIDS = $this->getcatagory($SelfCatagoryID, 2);
        foreach ($CatagoryIDS as $v) {
            $IDS[] = $v['CatagoryID'];
        }
        $InIDS = implode(",", $IDS);

        $ParentLft = $NewCatagory['Lft'];
        $ParentRgt = $NewCatagory['Rgt'];
//print_r($InIDS);  
//print_r($NewCatagory);  
//print_r($SelfCatagory);  
//exit;  
        if ($ParentRgt > $SelfRgt) {
            $UpdateLeftSQL = "UPDATE `" . $this->tablefix . "catagory` SET `Lft`=`Lft`-$Value-1 WHERE `Lft`>$SelfRgt AND `Rgt`<=$ParentRgt";
            $UpdateRightSQL = "UPDATE `" . $this->tablefix . "catagory` SET `Rgt`=`Rgt`-$Value-1 WHERE `Rgt`>$SelfRgt AND `Rgt`<$ParentRgt";
            $TmpValue = $ParentRgt - $SelfRgt - 1;
            $UpdateSelfSQL = "UPDATE `" . $this->tablefix . "catagory` SET `Lft`=`Lft`+$TmpValue,`Rgt`=`Rgt`+$TmpValue WHERE `CatagoryID` IN($InIDS)";
        } else {
            $UpdateLeftSQL = "UPDATE `" . $this->tablefix . "catagory` SET `Lft`=`Lft`+$Value+1 WHERE `Lft`>$ParentRgt AND `Lft`<$SelfLft";
            $UpdateRightSQL = "UPDATE `" . $this->tablefix . "catagory` SET `Rgt`=`Rgt`+$Value+1 WHERE `Rgt`>=$ParentRgt AND `Rgt`<$SelfLft";
            $TmpValue = $SelfLft - $ParentRgt;
            $UpdateSelfSQL = "UPDATE `" . $this->tablefix . "catagory` SET `Lft`=`Lft`-$TmpValue,`Rgt`=`Rgt`-$TmpValue WHERE `CatagoryID` IN($InIDS)";
        }
        $this->db->query($UpdateLeftSQL);
        $this->db->query($UpdateRightSQL);
        $this->db->query($UpdateSelfSQL);
//$this->referto("成功移动类别","javascript:history.back(1)",3);  
        return 1;
    }

// end func  

    /**
     * Short description.  
     * 
     * Detail description 
     * @param      none 
     * @global     none 
     * @since      1.0 
     * @access     private 
     * @return     void 
     * @update     date time 
     */
    function checkcatagory($CatagoryID) {
//检测父类ID是否存在  
        $SQL = "SELECT * FROM `" . $this->tablefix . "catagory` WHERE `CatagoryID`='$CatagoryID' LIMIT 1";
        $Result = $this->db->getrow($SQL);
        if (count($Result) < 1) {
            $this->referto("父类ID不存在,请检查", "javascript:history.back(1)", 3);
        }
        return $Result;
    }

// end func  

    /**
     * Short description.  
     * 
     * Detail description 
     * @param      none 
     * @global     none 
     * @since      1.0 
     * @access     private 
     * @return     array($Catagoryarray,$Deep) 
     * @update     date time 
     */
    function sort2array($CatagoryID = 0) {
        $Output = array();
        if ($CatagoryID == 0) {
            $CatagoryID = $this->getrootid();
        }
        if (empty($CatagoryID)) {
            return array();
            exit;
        }
        $Result = $this->db->query('SELECT Lft, Rgt FROM `' . $this->tablefix .
                'catagory` WHERE `CatagoryID`=' . $CatagoryID);
        if ($Row = $this->db->fetch_array($Result)) {
            $Right = array();
            $Query = 'SELECT * FROM `' . $this->tablefix .
                    'catagory` WHERE Lft BETWEEN ' . $Row['Lft'] . ' AND ' .
                    $Row['Rgt'] . ' ORDER BY Lft ASC';

            $Result = $this->db->query($Query);
            while ($Row = $this->db->fetch_array($Result)) {
                if (count($Right) > 0) {
                    while ($Right[count($Right) - 1] < $Row['Rgt']) {
                        array_pop($Right);
                    }
                }
                $Output[] = array('Sort' => $Row, 'Deep' => count($Right));
                $Right[] = $Row['Rgt'];
            }
        }
        return $Output;
    }

// end func  

    /**
     * Short description.  
     * 
     * Detail description 
     * @param      none 
     * @global     none 
     * @since      1.0 
     * @access     private 
     * @return     void 
     * @update     date time 
     */
    function getrootid() {
        $Query = "SELECT * FROM`" . $this->tablefix . "catagory` ORDER BY `Lft` ASC LIMIT 1";
        $RootID = $this->db->getrow($Query);
        if (count($RootID) > 0) {
            return $RootID['CatagoryID'];
        } else {
            return 0;
        }
    }

// end func  
}

// end class  
?>

优化mysql左右值无限极分类

CREATE TABLE `tree` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`pid` INT(10) NOT NULL DEFAULT '0' COMMENT '父ID',
`lft` INT(10) NOT NULL DEFAULT '0' COMMENT '左节点',
`rgt` INT(10) NOT NULL DEFAULT '0' COMMENT '右节点',
`level` INT(10) NOT NULL DEFAULT '0' COMMENT '深度',
`status` INT(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除 1是 0否',
PRIMARY KEY (`id`),
KEY `pid` (`pid`),
KEY `lft` (`lft`),
KEY `rgt` (`rgt`),
KEY `level` (`level`),
KEY `status` (`status`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

第一个节点
INSERT INTO tree VALUES (NULL, 'A', 0, 0, 1, 0, 0);
SELECT * FROM tree

父节点左值$a_l = 0; 父节点右值$a_r = 1; 父节点深度$p_h=0; 父节点ID $pid=1
第二个节点（A的子节点）
UPDATE tree SET lft = lft + 2 WHERE lft > 1;#lft > $a_r
UPDATE tree SET rgt = rgt + 2 WHERE rgt >= 1;#rgt >= $a_r
INSERT INTO tree VALUES (NULL, 'B', 1, 1, 1+1, 0+1, 0);#(NULL, 'B', $pid, $a_r, $a_r + 1, $p_h + 1, 0);

$a_l = 0; $a_r = 3; 父节点深度$p_h=0; 父节点ID $pid=1
第三个节点（A的子节点）
UPDATE tree SET lft = lft + 2 WHERE lft > 3;#lft > $a_r
UPDATE tree SET rgt = rgt + 2 WHERE rgt >= 3;#rgt >= $a_r
INSERT INTO tree VALUES (NULL, 'C', 1, 3, 3+1, 0+1, 0);#(NULL, 'C', $pid, $a_r, $a_r + 1, $p_h + 1, 0);

$a_l = 0; $a_r = 5;父节点深度$p_h=0; 父节点ID $pid=1
第四个节点（A的子节点）
UPDATE tree SET lft = lft + 2 WHERE lft > 5;#lft > $a_r
UPDATE tree SET rgt = rgt + 2 WHERE rgt >= 5;#rgt >= $a_r
INSERT INTO tree VALUES (NULL, 'D', 1, 5, 5+1, 0+1, 0);#(NULL, 'D', $pid, $a_r, $a_r + 1, $p_h + 1, 0);

$b_l = 1; $b_r = 2;父节点深度$p_h=1; 父节点ID $pid=2
第五个节点（B的子节点）
UPDATE tree SET lft = lft + 2 WHERE lft > 2;#lft > $b_r
UPDATE tree SET rgt = rgt + 2 WHERE rgt >= 2;#rgt >= $b_r
INSERT INTO tree VALUES (NULL, 'E', 2, 2, 2+1, 1+1, 0);#(NULL, 'E', $pid, $b_r, $b_r + 1, $p_h + 1, 0);

$b_l = 1; $b_r = 4;父节点深度$p_h=1; 父节点ID $pid=2
第六个节点（B的子节点）
UPDATE tree SET lft = lft + 2 WHERE lft > 4;#lft > $b_r
UPDATE tree SET rgt = rgt + 2 WHERE rgt >= 4;#rgt >= $b_r
INSERT INTO tree VALUES (NULL, 'F', 2, 4, 4+1, 1+1, 0);#(NULL, 'F', $pid, $b_r, $b_r + 1, $p_h + 1, 0);

$e_l = 2; $e_r = 3;父节点深度$p_h=2; 父节点ID $pid=5
第七个节点（E的子节点）
UPDATE tree SET lft = lft + 2 WHERE lft > 3;#lft > $e_r
UPDATE tree SET rgt = rgt + 2 WHERE rgt >= 3;#rgt >= $e_r
INSERT INTO tree VALUES (NULL, 'G', 5, 3, 3+1, 2+1, 0);#(NULL, 'G', $pid, $e_r, $e_r + 1, $p_h + 1, 0);

$c_l = 9; $c_r = 10;父节点深度$p_h=1; 父节点ID $pid=3
第八个节点(C的子节点)
UPDATE tree SET lft = lft + 2 WHERE lft > 10;#lft > $c_r
UPDATE tree SET rgt = rgt + 2 WHERE rgt >= 10;#rgt >= $c_r
INSERT INTO tree VALUES (NULL, 'H', 3, 10, 10+1, 1+1, 0);#(NULL, 'G', $pid, $c_r, $c_r + 1, $p_h + 1, 0);

总结：
一、根节点
lft=0,rgt=1,level=0
二、添加子节点:
找到父节点的右值$p_r，所有节点左值 > $p_r 的左值 + 2；所有节点右值 >= $p_r的右值 +2;插入新节点lft=$p_r,rgt=$p_r + 1,level=p_level+1;
三、删除节点：
找到所要删除节点的左右值$del_l,$del_r,删除所有左值>=$del_l,右值<=$del_r的节点；计算$v = $del_r - $del_l + 1;修改节点左值 > $del_l的左值为lft = lft - $v;修改节点右值 > $del_r 的右值为rgt = rgt - $v
四、获取子节点
1、不包含自己lft > $current_l and rgt < $current_r
2、包含自己lft >= $current_l and rgt <= $current_r
3、不包含自己的所有父类 lft < $current_l and rgt > $current_r
4、包含自己的所有父类 lft <= $current_l and rgt >= $current_r
五、计算子节点的数量
($current_r - $current_l - 1)/2
六、叶子节点
rgt = lft + 1
七、树的深度
就是level
八、检索单一路径
E的
select * from tree where lft <= 2 and rgt >= 5
九、获取直接子节点
SELECT * FROM tree WHERE lft > 1 AND rgt < 8 AND LEVEL = 2