数据库树型结构的存储和展示

数据库擅长操作的数据是无层次的数据，根据不同的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}