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);
$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,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