假设有这么一个分类:
一、邻接表模型
邻接表模型中,数据表中的每项包含了指向其父项的指示器,最上层项的父项为0或者空值(NULL)。
建立表结构:
CREATE TABLE `category`(
`cid` int not null auto_increment primary key comment '类目ID',
`name` varchar(30) not null comment '类目名',
`pid` int not null default 0 comment '父ID'
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='分类表';
insert into category values
(1,'ELECTRONICS',0),
(2,'TELEVISIONS',1),
(3,'TUBE',2),
(4,'LCD',2),
(5,'PLASMA',2),
(6,'PORTABLE ELECTRONICS',1),
(7,'MP3 PLAYERS',6),
(8,'FLASH',7),
(9,'CD PLAYERS',6),
(10,'2 WAY RADIOS',6);
select * from category order by cid;
通过这种数据库设计出的无限级,读取的时候相当麻烦。
所以大部分的程序最多3到4级分类,这就足以满足需求,从而一次性读出所有的数据,再对得到数组或者对象进行递归。
本身负荷还是没太大问题。但是如果分类到更多级,那是不可取的办法。这样看来这种方法就是增删改的时候轻松了。然而就二级分类而言,采用这种算法就应该算最优先了。
1.检索整树
1)原生SQL
使用自连接检索整数:
select t1.name as lev1,t2.name as lev2,t3.name as lev3,t4.name as lev4
from category as t1
left join category as t2 on t2.pid = t1.cid
left join category as t3 on t3.pid = t2.cid
left join category as t4 on t4.pid = t3.cid
where t1.name = 'ELECTRONICS';
2)MySQL函数
获取所有子节点:
delimiter $$
create function tree_child_lists(root_id int)
returns varchar(1000)
begin
declare temp varchar(1000);
declare temp_char varchar(1000);
set temp = '$';
set temp_char = cast(root_id as char);
while temp_char is not null do
set temp = concat(temp,',',temp_char);
select group_concat(cid) into temp_char from category where find_in_set(pid,temp_char) > 0;
end while;
return temp;
end
$$
delimiter ;
select tree_child_lists(1);
select * from category where find_in_set(cid,tree_child_lists(2));
获取所有父节点:
delimiter $$
create function tree_parent_lists(cid int)
returns varchar(1000)
begin
declare parent_list varchar(1000);
declare parent_temp varchar(1000);
set parent_temp = cast(cid as char);
while parent_temp is not null do
if (parent_list is not null) then
set parent_list = concat(parent_temp,',',parent_list);
else
set parent_list = concat(parent_temp);
end if;
select group_concat(pid) into parent_temp from category where find_in_set(cid,parent_temp) > 0;
end while;
return parent_list;
end
$$
delimiter ;
select tree_parent_lists(3) as id;
select * from category where find_in_set(cid,tree_parent_lists(3));
3)临时表和存储过程递归
入口存储过程:
-- 入口过程
delimiter $$
create procedure tree_child_list_proc(in root_id int)
begin
create temporary table if not exists temp_list(
sno int primary key auto_increment,
cid int,
depth int
);
delete from temp_list;
call tree_child_list_create_proc(root_id,0);
select temp_list.*,category.* from temp_list,category where temp_list.cid = category.cid order by temp_list.sno;
end
$$
delimiter ;
递归存储过程:
-- 递归过程
delimiter $$
create procedure tree_child_list_create_proc(in root_id int,in depth int)
begin
declare done int default 0;
declare b int;
declare cur cursor for select cid from category where pid = root_id;
declare continue handler for not found set done = 1;
insert into temp_list values(null,root_id,depth);
open cur;
fetch cur into b;
while done = 0 do
call tree_child_list_create_proc(b,depth + 1);
fetch cur into b;
end while;
close cur;
end
$$
delimiter ;
调用:
set SQL_SAFE_UPDATES = 0;
set max_sp_recursion_depth = 12;
call tree_child_list_proc(2);
4)PHP逻辑处理
递归处理:
/**
* 数据集组合分类树(一维数组)
* @param cate 分类查询结果集
* @param html 格式串
* @return array
* @author 张朝坤
*/
function list_to_tree($cate,$html = '--',$pid = 0,$level = 0){
$arr = array();
foreach($cate as $v){
if($pid == $v['pid']){
$v['level'] = $level + 1;
$v['html'] = str_repeat($html,$level);
$arr[] = $v;
$arr = array_merge($arr,list_to_tree($cate,$html,$v['id'],$level+1));
}
}
return $arr;
}
/**
* 数据集组合分类树(多维数组)
* @param cate 分类结果集
* @param child 子树
* @return array
* @author 张朝坤
*/
function list_to_tree2($cate,$child = 'child',$pid = 0){
$arr = array();
foreach($cate as $v){
if($v['pid'] == $pid){
$v[$child] = list_to_tree2($cate,$child,$v['id']);
$arr[] = $v;
}
}
return $arr;
}
/**
* 通过子级ID返回父级
* @param cate 分类结果集
* @param
* @return
* @author 张朝坤
*/
function get_parents_by_id($cate,$id){
$arr = array();
foreach($cate as $v){
if($v['id'] == $id){
$arr[] = $v;
$arr = array_merge(get_parents_by_id($cate,$v['pid']),$arr);
}
}
return $arr;
}
/**
* 通过父级ID返回子级
* @param cate 分类结果集
* @param
* @return
* @author 张朝坤
*/
function get_childs_by_pid($cate,$pid){
$arr = array();
foreach($cate as $v){
if($v['pid'] == $pid){
$arr[] = $v;
$arr = array_merge($arr,get_childs_by_pid($cate,$v['id']));
}
}
return $arr;
}
栈处理:
if(!function_exists('list_2_tree'))
{
function list_2_tree($list, $pid = 0)
{
$tree = [];
if(!is_array($list) || empty($list)){
return false;
}
//先将数组反转,因为出栈时会优先出最上面的
$list = array_reverse($list);
//先取出顶级的分类元素压入数组$stack中,并删除在$list中的
$stack = [];
foreach ($list as $key => $value) {
if ($value['pid'] == $pid) {
array_push($stack,$value);
unset($list[$key]);
}
}
while (count($stack)) {
//先从栈中取出第一项
$info = array_pop($stack);
//查找子节点
foreach ($list as $key => $child) {
//如果有子节点则入栈,用以后续继续查找子节点的下级
if ($child['pid'] == $info['id']) {
array_push($stack, $child);
unset($list[$key]);
}
}
//组装成下拉菜单格式
$tree[$info['id']] = $info['name'];
}
return $tree;
}
}
引用处理:
function getTree($list, $pid = 0)
{
$tree = [];
if (!empty($list)) {
//先修改为以id为下标的列表
$newList = [];
foreach ($list as $k => $v) {
$newList[$v['id']] = $v;
}
//然后开始组装成特殊格式
foreach ($newList as $value) {
if ($pid == $value['pid']) {
//先取出顶级
$tree[] = &$newList[$value['id']];
} elseif (isset($newList[$value['pid']])) {
//再判定非顶级的pid是否存在,如果存在,则再pid所在的数组下面加入一个字段items,来将本身存进去
$newList[$value['pid']]['items'][] = &$newList[$value['id']];
}
}
}
return $tree;
}
function formatTree($tree)
{
$options = [];
if (!empty($tree)) {
foreach ($tree as $key => $value) {
$options[$value['id']] = $value['name'];
if (isset($value['items'])) {
//查询是否有子节点
$optionsTmp = formatTree($value['items']);
if (!empty($optionsTmp)) {
$options = array_merge($options, $optionsTmp);
}
}
}
}
return $options;
}
先调用getTree,再调用formatTree,formatTree虽然是递归,但是通过了getTree的特殊处理,效率有很大提高。
2.检索所有叶子节点
用左连接(LEFT JOIN)来检索出树中所有叶子节点。
SELECT t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.cid = t2.pid
WHERE t2.cid IS NULL;
3.检索单一路径
通过自连接检索出单一路径。
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.pid = t1.cid
LEFT JOIN category AS t3 ON t3.pid = t2.cid
LEFT JOIN category AS t4 ON t4.pid = t3.cid
WHERE t1.name = 'ELECTRONICS' AND t4.name = 'FLASH';
4.局限性
用纯 SQL 编码实现邻接表模型有一定的难度。
在我们检索某分类的路径之前,我们需要知道MYSQL 中分层数据的管理该分类所在的层次。另外,我们在删除节点的时候要特别小心,因为潜在的可能会孤立一棵子树(当删除 portable electronics 分类时,所有他的子分类都成了孤儿)。
部分局限性可以通过使用客户端代码或者存储过程来解决,我们可以从树的底部开始向上迭代来获得一颗树或者单一路径,我们也可以在删除节点的时候使其子节点指向一个新的父节点,来防止孤立子树的产生。
二、PATH路径模型
1.简易设计
设置父项字段类型为varchar,将父项id都集中存储在这个字段里,用约定符号隔开,比如:1,5,6或者1|5|6。
这样可以比较容易得到各上级分类的ID,而且在查询分类下的信息的时候, 可以使用:SELECT * FROM category WHERE pid LIKE '1,3%'。
相比于邻接表模型的递归算法,在读取数据方面优势非常大,但是若查找该分类的所有 父分类 或者 子分类 查询的效率也不是很高,至少也要二次query。
倘若递增到无限级,还需考虑字段是否达到要求,而且在修改分类和转移分类的时候操作将非常麻烦。
2.路径法
路径法的一些操作可以用存储过程或者函数来实现。
三、嵌套集合模型
在嵌套集合模型中,将以一种新的方式来看待分层数据,不再是线与点了,而是嵌套容器。
依旧保持了数据的层次,父分类包围了其子分类。在数据表中,通过使用表示节点的嵌套关系的左值(left value)和右值(right value)来表现嵌套集合模型中数据的分层特性。
从外层节点的最左侧开始,从左到右编号:
为树状的结构编号时,从左到右,一次一层,为节点赋右值前先从左到右遍历其子节点给其子节点赋左右值。这种方法被称作改进的先序遍历算法或者左右值。
- 每一个 后代节点 的 左值 > 父节点 的 左值
- 每一个 后代节点 的 右值 < 父节点的 右值
- 每一个节点的 右值 > 左值
改进的先序遍历算法便于输出和查询,但是在移动分类和常规理解上有些复杂。
建立表结构:
CREATE TABLE `nested_category`(
`category_id` int not null auto_increment primary key comment '分类ID',
`name` varchar(30) not null comment '类目名',
`lft` int not null comment '左值',
`rgt` int not null comment '右值'
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='分类表';
insert into `nested_category` values
(1,'ELECTRONICS',1,20),
(2,'TELEVISIONS',2,9),
(3,'TUBE',3,4),
(4,'LCD',5,6),
(5,'PLASMA',7,8),
(6,'PORTABLE ELECTRONICS',10,19),
(7,'MP3 PLAYERS',11,14),
(8,'FLASH',12,13),
(9,'CD PLAYERS',15,16),
(10,'2 WAY RADIOS',17,18);
select * from nested_category order by category_id;
1.检索整树
通过自连接把父节点连接到子节点上来检索整树,因为子节点的 lft 值总是在其父节点的 lft 值和 rgt 值之间。
SELECT node.category_id,node.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.name = 'ELECTRONICS'
ORDER BY node.lft;
2.检索所有叶子节点
叶子节点的左右值是连续的,要检索出叶子节点,我们只要查找满足 rgt = lft+1 的节点。
SELECT category_id,name FROM nested_category WHERE rgt = lft + 1;
3.检索单一路径
可以不用多个自连接就可以检索出单一路径。
SELECT parent.name
FROM nested_category AS node,nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.name = 'FLASH'
ORDER BY node.lft;
4.检索节点的深度
在查询语句上增加 COUNT 函数和 GROUP BY 子句来更好的标识出节点在树中所处层次。
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
根据 depth 值来缩进分类名字,使用 CONCAT 和 REPEAT 字符串函数。
SELECT CONCAT( REPEAT('----', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
5.检索子树的深度
当需要子树的深度信息时,不能限制自连接中的 node 或 parent,因为这么做会打乱数据集的顺序。
因此,添加了第三个自连接作为子查询,来得出子树新起点的深度值。
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name
ORDER BY node.lft
) AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name
ORDER BY node.lft;
6.检索节点的直接子节点
例如在零售网站上呈现电子产品的分类。当用户点击分类后,将要呈现该分类下的产品,同时也需列出该分类下的直接子分类,而不是该分类下的全部分类。
为此,我们只呈现该节点及其直接子节点,不再呈现更深层次的节点。
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name
ORDER BY node.lft
) AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;
7.集合函数的应用
添加一个产品表,用它来示例集合函数的应用。
CREATE TABLE product(
`product_id` int not null auto_increment primary key comment '产品ID',
`name` varchar(30) not null comment '产品名',
`category_id` int not null comment '所属分类'
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='产品表';
INSERT INTO product(name, category_id) VALUES
('20" TV',3),
('36" TV',3),
('SuperLCD 42"',4),
('UltraPlasma 62"',5),
('Value Plasma 38"',5),
('PowerMP3 5gb',7),
('SuperPlayer 1gb',8),
('Porta CD',9),
('CD To go!',9),
('Family Talk 360',10);
写一个查询语句,在检索分类树的同时,计算出各分类下的产品数量:
SELECT parent.name, COUNT(product.name)
FROM nested_category AS node ,nested_category AS parent,product
WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.category_id = product.category_id
GROUP BY parent.name
ORDER BY node.lft;
8.新增节点
新增顶级分类:
- 获取该树中 最大右值
- 左值 = 最大右值 + 1
- 右值 = 最大右值 + 2
新增子节点:
- 首先获取父节点右值
UPDATE `catagory` SET `lft` = `lft`+2 WHERE `lft`>`父节点右值` UPDATE `catagory` SET `rgt` = `rgt` + 2 WHERE `rgt`>= `父节点右值`
-
新增节点左值 = 父节点右值
-
新增节点右值 = 新增节点左值 + 1
9.删除节点
1)获取删除节点的左右值 $lft, $rgt
2)删除该节点以及所有后代节点
DELETE FROM `catagory` WHERE `lft`>=$lft AND `rgt`<=$Rgt;
3)更新左右值
$Value=$rgt-$lft+1;
UPDATE `catagory` SET `lft`=`lft`- $Value WHERE `lft`>$lft;
UPDATE `catagory` SET `rgt`=`rgt`- $Value WHERE `rgt`>$rgt;
10.PHP逻辑处理
1)整树、子树
- 查询根节点(或子树节点)的左右值。
- 查询左右值在自根节点(或子树节点)左右值范围内的记录,根据左值排序。
- 本次右值大于前次的为子分类。
如:子树节点lft = 2、rgt = 11,那么所有lft>2 && rgt < 11的为该子树的子集。
2)新增
3)删除
简单实现:
<?php
require_once './medoo/Medoo.php';
final class Category
{
/**
* [$connection description]
* @var null
*/
private $connection = null;
/**
* [$instance description]
* @var null
*/
private static $instance = null;
/**
* [__construct description]
*/
private function __construct()
{
if(is_null($this->connection)){
$this->connection = new Medoo([
'database_type' => 'mysql',
'database_name' => 'study',
'server' => 'localhost',
'username' => 'root',
'password' => '123456',
'charset' => 'utf8',
'port' => 3306,
'option' => [
PDO::ATTR_CASE => PDO::CASE_NATURAL
]
]);
}
return $this->connection;
}
/**
* [__clone description]
* @return [type] [description]
*/
public function __clone()
{
trigger_error('Clone is not allow!', E_USER_ERROR);
}
/**
* [getInstance 单例]
* @return [type] [description]
*/
public static function getInstance()
{
if(!self::$instance instanceof self){
self::$instance = new self();
}
return self::$instance;
}
/**
* [tree 显示整树结构]
* @return [type] [description]
*/
public function tree()
{
$data = $this->connection->query("SELECT * FROM nested_category ORDER BY lft ASC")->fetchAll(PDO::FETCH_ASSOC);
$parent = [];
$list = [];
foreach($data as $item){
if(count($parent)){
while((count($parent) -1) > 0 && $parent[count($parent) - 1]['rgt'] < $item['rgt']){
array_pop($parent);
}
}
$item['depth'] = count($parent);
$parent[] = $item;
$list[] = $item;
}
foreach($list as $k => $v){
if($v['category_id'] != 1){
if($v['depth'] != 1){
$list[$k]['tree'] = str_repeat('-----',$v['depth']);
}
}else{
unset($list[$k]);
}
}
return $list;
}
/**
* 显示树形结构2
* @return [type] [description]
*/
public function tree2()
{
}
/**
* 显示属性结构3
* [tree3 description]
* @return [type] [description]
*/
public function tree3()
{
}
/**
* [get 获取一个节点]
* @param [type] $id [description]
* @return [type] [description]
*/
public function get($id)
{
return $this->connection->get('nested_category',[
'category_id','name','lft','rgt'
],[
'category_id' => $id
]);
}
/**
* [add 新增节点]
* 1.首先获取父节点右值,把lft大于该右值的节点的lft加2且把rgt大于等于该右值的节点的rgt加2
* 2.新增节点左值 = 父节点右值
* 3.新增节点右值 = 新增节点左值 + 1
*/
public function add($parent_id,$data)
{
$parent_id = $parent_id ?? 1;
$parent_category = $this->get($parent_id);
if(isset($parent_category['rgt']) && !empty($parent_category['rgt'])){
//把lft大于该右值的节点的lft加2
$this->connection->update('nested_category',[
'lft[+]' => 2
],[
'lft[>]' => $parent_category['rgt']
]);
//把rgt大于等于该右值的节点的rgt加2
$this->connection->update('nested_category',[
'rgt[+]' => 2
],[
'rgt[>]' => $parent_category['rgt']
]);
//新增节点左值 = 父节点右值 新增节点右值 = 新增节点左值 + 1
$this->connection->insert('nested_category',[
'name' => $data['name'],
'lft' => $parent_category['rgt'],
'rgt' => $parent_category['rgt'] + 1
]);
return $this->connection->id();
}
return false;
}
/**
* [add2 新增节点]
* 找到其父节点,之后把左值和右值大于父节点左值的节点的左右值加上2,之后再插入本节点,左右值分别为父节点左值加一和加二
* @param [type] $parent_id [description]
* @param [type] $data [description]
* @return [type] [description]
*/
public function add2($parent_id,$data)
{
$parent_id = $parent_id ?? 1;
//获取到父级分类的lft rgt
$parent_category = $this->get($parent_id);
//左值和右值大于父节点左值的节点的左右值加上2
if(!empty($parent_category)){
$this->connection->update('nested_category',[
'lft[+]' => 2
],[
'lft[>]' => $parent_category['lft']
]);
$this->connection->update('nested_category',[
'rgt[+]' => 2
],[
'rgt[>]' => $parent_category['lft']
]);
}
$this->connection->insert('nested_category',[
'name' => $data['name'],
'lft' => $parent_category['lft'] + 1,
'rgt' => $parent_category['lft'] + 2
]);
return $this->connection->id();
}
/**
* 删除节点
* [del description]
* @param [type] $id [description]
* @return [type] [description]
*/
public function del($id)
{
//获取删除节点的左右值
$category = $this->get($id);
//删除该节点以及所有后代节点
$this->connection->delete('nested_category',[
"AND" => [
"lft[>=]" => $category['lft'],
"rgt[<=]" => $category['rgt']
]
]);
//更新其他节点左右值
$width = $category['rgt'] - $category['lft'] + 1;
$this->connection->update('nested_category',[
'lft[-]' => $width
],[
'lft[>]' => $category['lft']
]);
$this->connection->update('nested_category',[
'rgt[-]' => $width
],[
'rgt[>]' => $category['rgt']
]);
}
/**
* [edit 编辑节点]
* @param [type] $id [description]
* @param [type] $data [description]
* @return [type] [description]
*/
public function edit($id,$data)
{
return $this->connection->update('nested_category',[
$data
],[
'category_id' => $id
]);
}
/**
* [getSons 获取子孙]
* @param [type] $id [description]
* @return [type] [description]
*/
public function sons($id)
{
$category = $this->get($id);
if($category)
{
$sql = 'SELECT * FROM nested_category WHERE `lft` >= '.$category['lft'].' AND `rgt` <= '.$category['rgt'];
return $this->connection->query($sql)->fetchAll(PDO::FETCH_ASSOC);
}
return false;
}
/**
* [parent 获取直接父类]
* @param [type] $son_id [description]
* @return [type] [description]
*/
public function parent($son_id)
{
}
/**
* [parents 获取所有父类]
* @param [type] $son_id [description]
* @return [type] [description]
*/
public function parents($son_id)
{
}
/**
* [depth 分类树的深度]
* @param integer $depth [description]
* @return [type] [description]
*/
public function depth($depth = 1)
{
$data = $this->connection->query("SELECT * FROM nested_category ORDER BY lft ASC")->fetchAll(PDO::FETCH_ASSOC);
$parent = [];
$lists = [];
foreach($data as $k => $item)
{
if(count($parent))
{
while (count($parent) -1 > 0 && $parent[count($parent) -1]['rgt'] < $item['rgt']){
array_pop($parent);
}
}
$item['depth'] = count($parent);
$parent[] = $item;
if($item['category_id'] != 1)
{
if($item['depth'] == $depth){
$lists[] = $item;
/*
//按照sort排序
foreach ($lists as $key => $row)
{
$vals[$key] = $row['sort'];
}
array_multisort($vals, SORT_ASC, $lists);
*/
}
}
else
{
unset($data[$k]);
}
}
return $lists;
}
}
$cate = Category::getInstance();
//echo '<pre>';var_dump($cate->sons(2));
//echo '<pre>';var_dump($cate->depth(2));
/*for($i = 11;$i <= 46;$i++){
$cate->del($i);
}*/
//$cate->add(10,['name' => 'FRS']);
//echo '<pre>';var_dump($cate);die;
//echo '<pre>';var_dump($cate->add2(10,['name' => 'FRS']));