数据库擅长操作的数据是无层次的数据，根据不同的sql语句提取特定的行运算。对于树型结构的存储第一次应用是做省市县三级列表。当时仅仅使用了三个字段id，pid，name。pid表示name的所属的父id。然后可以在服务器端组织好数据结构给客户端，也可以让客户端自行处理。但是现在需要做一个背单词的联想功能，层数是不确定的。根据一个单词需要把它的所有的子节点全部查出来，并且以树的形式展示出来，这就不能够用上面的存储方式，首先是因为上面的存储会导致数据库递归查找，效率不高。第二就是想要获取关于某个节点的子树的任何信息，不进行遍历是不能达到效果的，也是查询次数过多，效率问题。
https://www.sitepoint.com/hierarchical-data-database/，这篇文章详细介绍了数据库存储树形结构的两种方式，第一种就是上面介绍的哪种，另一种是一种新的存储方式。这种新的存储方式极大的简化了数据库的查询次数。
树.png
数据库数据.png
查询food的所有的节点
SELECT * FROM tree WHERE lft BETWEEN 1 AND 18;
更改Food的名字，直接更改就行
UPDATE tree set name = 'Foods' where name = 'Food'
在Red下加入新的子节点
UPDATE tree SET rgt=rgt+2 WHERE rgt>5;
UPDATE tree SET lft=lft+2 WHERE lft>5;
INSERT INTO tree SET lft=6, rgt=7, name='Strawberry';
删除草莓
DELETE FROM tree where name = 'Strawberry'
UPDATE tree SET rgt=rgt-2 WHERE rgt>7;
UPDATE tree SET lft=lft-2 WHERE lft>7;
image.png
操作很简单了，接下来是上面前端的展示，最简单的就是按照层次打印，如图
image.png
但是这样显示效果不好，所以网上搜索了下树的展示。参考这里https://jsfiddle.net/Limitlessisa/5Lhb0ron/
发现这里是根据css设置的，而且每一个节点都是独立的。所以可以参考这里把树按照这样的方式展示出来。
单词树编辑.gif
实现代码基于TP
public function treeEdit(Request $request)
{
$action_type = $request->param('action_type', '');
$root_name = $request->param('root_name', '');
if (empty($root_name) && $action_type != 'add_root_node'){
return json(['status' => -1,'msg' => '没有根节点名称']);
}
if ($action_type == 'add_root_node') { //添加根节点
$new_name = $request->param('root_name');
Db::table('h_dict_tree')->insert(['name' => $new_name, 'root' => $new_name, 'parent' => '','lft' => 1,'rgt' => 2]);
return json(['status' => 0,'msg' => '添加根节点成功']);
} elseif ($action_type == 'add_child_node' || $action_type == 'edit_node_name' || $action_type == 'add_brother_node') { //添加子节点
$origin_name = $request->param('origin_name', '');
if (empty($origin_name)) {
return json(['status' => -1,'msg' => '请选择节点']);
}
$new_name = $request->param('new_name','');
if (empty($new_name)) {
return json(['status' => -1, '请输入新名称']);
}
$exist_nodes = Db::table('h_dict_tree')->where('root', $root_name)->column('name');
if (in_array($new_name, $exist_nodes)) {
return json(['status' => -1,'msg' => '节点已存在']);
}
if ($action_type == 'add_child_node'){
$parent_node = Db::table('h_dict_tree')->where(['root' => $root_name, 'name' => $origin_name])->find();
Db::table('h_dict_tree')->where(['rgt' => ['gt', $parent_node['rgt']-1],'root' => $root_name])->update(['rgt' => array('exp', 'rgt+2')]);
Db::table('h_dict_tree')->where(['lft' => ['gt', $parent_node['rgt']-1],'root' => $root_name])->update(['lft' => array('exp', 'lft+2')]);
Db::table('h_dict_tree')->insert(['name' => $new_name, 'lft' => $parent_node['rgt'],'rgt' => $parent_node['rgt']+1, 'root' => $root_name, 'parent' => $origin_name]);
return json(['status' => 0,'msg' => '添加子节点成功']);
}else if($action_type == 'edit_node_name'){
Db::table('h_dict_tree')->where(['name' => $origin_name, 'root' => $root_name])->update(['name' => $new_name]);
Db::table('h_dict_tree')->where(['parent' => $origin_name, 'root' => $root_name])->update(['parent' => $new_name]);
if ($root_name == $origin_name){
Db::table('h_dict_tree')->where(['root' => $root_name])->update(['root' => $new_name]);
}
return json(['status' => 0,'msg' => '更改节点名成功']);
}else {
if ($origin_name == $root_name) {
return json(['status' =>-1,'msg' => '根节点不能添加兄弟节点']);
}
$add_pos = $request->param('add_pos','left');
$brother_node = Db::table('h_dict_tree')->where(['root' => $root_name, 'name' => $origin_name])->find();
if ($add_pos == 'right') {
Db::table('h_dict_tree')->where(['lft' => ['gt', $brother_node['rgt']],'root' => $root_name])->update(['lft' => array('exp', 'lft+2')]);
Db::table('h_dict_tree')->where(['rgt' => ['gt', $brother_node['rgt']],'root' => $root_name])->update(['rgt' => array('exp', 'rgt+2')]);
Db::table('h_dict_tree')->insert(['name' => $new_name, 'lft' => $brother_node['rgt']+1,'rgt' => $brother_node['rgt']+2, 'root' => $root_name, 'parent' => $brother_node['parent']]);
}elseif ($add_pos == 'left') {
Db::table('h_dict_tree')->where(['lft' => ['gt', $brother_node['lft']-1],'root' => $root_name])->update(['lft' => array('exp', 'lft+2')]);
Db::table('h_dict_tree')->where(['rgt' => ['gt', $brother_node['lft']-1],'root' => $root_name])->update(['rgt' => array('exp', 'rgt+2')]);
Db::table('h_dict_tree')->insert(['name' => $new_name, 'lft' => $brother_node['lft'],'rgt' => $brother_node['lft']+1, 'root' => $root_name, 'parent' => $brother_node['parent']]);
}
return json(['status' => 0, '加入成功']);
}
} elseif ($action_type == 'delete_node') { //删除节点
$del_name = $request->param('origin_name','');
$count = Db::table('h_dict_tree')->where(['parent' => $del_name, 'root' => $root_name])->count('*');
if ($count == 0){
$data = Db::table('h_dict_tree')->where(['name'=> $del_name,'root' => $root_name])->find();
Db::table('h_dict_tree')->where(['lft' => ['gt',$data['rgt']],'root' => $root_name])->update(['lft' => array('exp', 'lft-2')]);
Db::table('h_dict_tree')->where(['rgt' => ['gt',$data['rgt']],'root' => $root_name])->update(['rgt' => array('exp', 'rgt-2')]);
Db::table('h_dict_tree')->where(['name' => $data['name'], 'root' => $root_name])->delete();
return json(['status' => 0,'msg' => '删除节点成功']);
}else{
return json(['status' => -1,'msg' => '不能删除非叶子节点']);
}
}
}
{extend name='include/head'/}
{block name='title'}单词树{/block}
{block name="menu"}<a href="{:url('dict/wordEdit')}">添加单词</a>{/block}
{block name='content'}
<head>
<style>
.tree ul {
padding-top: 20px; position: relative;
transition: all 0.5s;
-webkit-transition: all 0.5s;
-moz-transition: all 0.5s;
}
.tree li {
float: left; text-align: center;
list-style-type: none;
position: relative;
padding: 20px 5px 0 5px;
transition: all 0.5s;
-webkit-transition: all 0.5s;
-moz-transition: all 0.5s;
}
.tree li::before, .tree li::after{
content: '';
position: absolute; top: 0; right: 50%;
border-top: 1px solid #ccc;
width: 50%; height: 20px;
}
.tree li::after{
right: auto; left: 50%;
border-left: 1px solid #ccc;
}
.tree li:only-child::after, .tree li:only-child::before {
display: none;
}
.tree li:only-child{ padding-top: 0;}
.tree li:first-child::before, .tree li:last-child::after{
border: 0 none;
}
.tree li:last-child::before{
border-right: 1px solid #ccc;
border-radius: 0 5px 0 0;
-webkit-border-radius: 0 5px 0 0;
-moz-border-radius: 0 5px 0 0;
}
.tree li:first-child::after{
border-radius: 5px 0 0 0;
-webkit-border-radius: 5px 0 0 0;
-moz-border-radius: 5px 0 0 0;
}
.tree ul ul::before{
content: '';
position: absolute; top: 0; left: 50%;
border-left: 1px solid #ccc;
width: 0; height: 20px;
}
.tree li div{
border: 1px solid #ccc;
padding: 5px 10px;
text-decoration: none;
color: #666;
font-family: arial, verdana, tahoma;
font-size: 11px;
display: inline-block;
border-radius: 5px;
-webkit-border-radius: 5px;
-moz-border-radius: 5px;
transition: all 0.5s;
-webkit-transition: all 0.5s;
-moz-transition: all 0.5s;
}
.tree li div:hover, .tree li div:hover+ul li div {
background: #c8e4f8; color: #000; border: 1px solid #94a0b4;
}
.tree li div:hover+ul li::after,
.tree li div:hover+ul li::before,
.tree li div:hover+ul::before,
.tree li div:hover+ul ul::before{
border-color: #94a0b4;
}
</style>
</head>
<div class="tree" align="center">
{$tree_div}
</div>
{if $has_root == 1}
<div style="clear: both;margin-top:50px;padding-top: 50px">
<table width="300" cellpadding="3" cellspacing="1" class="table_row copy_table" style="margin-left: 50px">
<tr>
<td width="100" nowrap="nowrap"><div align="center">已选择</div></td>
<td width="100" nowrap="nowrap"><div align="center">节点内容</div></td>
<td width="100" nowrap="nowrap"><div align="center">操作</div></td>
</tr>
<tr>
<td nowrap="nowrap">
<div style="text-align: center">
<input type="text" name="origin_name" value="" readonly required style="width: 95%">
</div>
<div style="text-align: center"><button class="sure" name="delete_node">确定删除</button></div>
<td>
<div><input type="text" name="new_name" value="" required style="width: 95%"></div>
<div style="text-align: center"><button class="sure" name="edit_node_name">更新节点</button></div>
</td>
<td>
<div style="text-align: center"><button class="sure" name="add_brother_left">添加左节点</button></div>
<div style="text-align: center"><button class="sure" name="add_brother_right">添加右节点</button></div>
<div style="text-align: center"><button class="sure" name="add_child_node">添加子节点</button></div>
</td>
</tr>
</table>
</div>
{else/}
<div>
<span>根节点名</span>
<input type="text" name="root_name" value="{$root_name}">
<button class="sure" name="add_root_node">确定添加</button>
</div>
{/if}
<script>
$(function () {
$('input:radio[name="action_type"]').click(function () {
var check_operation = $(this).val();
$('.operation').hide();
$('#' + check_operation).show();
});
//点击提交
$(".sure").click(function () {
var check_operation = $('input:radio[name="action_type"]:checked').val();
var d = {};
var action_type = $(this).prop('name');
if (action_type == 'add_brother_left') {
d['add_pos'] = 'left';
d['action_type'] = 'add_brother_node';
}else if(action_type == 'add_brother_right'){
d['add_pos'] = 'right';
d['action_type'] = 'add_brother_node';
}else {
d['action_type'] = action_type;
}
d['origin_name'] = $('input[name="origin_name"]').val();
d['new_name'] = $('input[name="new_name"]').val();
d['root_name'] = "{$root_name}";
$.post('treeEdit', d, function (data) {
if (data['status']<0){
alert(data['msg']);
}
window.location.reload();
}, 'json');
});
$(".node").click(function () {
var select_text = $(this).text();
var check_operation = $('input:radio[name="action_type"]:checked').val();
if (check_operation != 'add_root_node') {
$('input[name="origin_name"]').val(select_text);
}
})
})
</script>
{/block}
网友评论