Mysql通过Adjacency List(邻接表)存储树形结构

以下内容给大家介绍了MYSQL通过Adjacency List (邻接表)来存储树形结构的过程介绍和解决办法,并把存储后的图例做了分析。

今天来看看一个比较头疼的问题,如何在数据库中存储树形结构呢?

像mysql这样的关系型数据库,比较适合存储一些类似表格的扁平化数据,但是遇到像树形结构这样有深度的人,就很难驾驭了。

举个栗子:现在有一个要存储一下公司的人员结构,大致层次结构如下:

(画个图真不容易。。)

那么怎么存储这个结构?并且要获取以下信息:

1.查询小天的直接上司。

2.查询老宋管理下的直属员工。

3.查询小天的所有上司。

4.查询老王管理的所有员工。

方案一、(Adjacency List)只存储当前节点的父节点信息。

  CREATE TABLE Employees(
  eid int,
  ename VARCHAR(100),
        position VARCHAR(100),
  parent_id int
  )

记录信息简单粗暴,那么现在存储一下这个结构信息:

好的,现在开始进入回答环节:

1.查询小天的直接上司:

SELECT e2.eid,e2.ename FROM employees e1,employees e2 WHERE e1.parent_id=e2.eid AND e1.ename='小天';

2.查询老宋管理下的直属员工:

SELECT e1.eid,e1.ename FROM employees e1,employees e2 WHERE e1.parent_id=e2.eid AND e2.ename='老宋';

3.查询小天的所有上司。

这里肯定没法直接查,只能用循环进行循环查询,先查直接上司,再查直接上司的直接上司,依次循环,这样麻烦的事情,还是得先建立一个存储过程:

睁大眼睛看仔细了,接下来是骚操作环节:

CREATE DEFINER=`root`@`localhost` FUNCTION `getSuperiors`(`uid` int) RETURNS varchar(1000) CHARSET gb2312
BEGIN
  DECLARE superiors VARCHAR(1000) DEFAULT '';
  DECLARE sTemp INTEGER DEFAULT uid;
  DECLARE tmpName VARCHAR(20);
  WHILE (sTemp>0) DO
    SELECT parent_id into sTemp FROM employees where eid = sTemp;
    SELECT ename into tmpName FROM employees where eid = sTemp;
    IF(sTemp>0)THEN
      SET superiors = concat(tmpName,',',superiors);
    END IF;
  END WHILE;
    SET superiors = LEFT(superiors,CHARACTER_LENGTH(superiors)-1);
  RETURN superiors;
END

这一段存储过程可以查询子节点的所有父节点,来试验一下 

好的,骚操作完成。

显然,这样。获取子节点的全部父节点的时候很麻烦。。

4.查询老王管理的所有员工。

思路如下:先获取所有父节点为老王id的员工id,然后将员工姓名加入结果列表里,在调用一个神奇的查找函数,即可进行神奇的查找:

CREATE DEFINER=`root`@`localhost` FUNCTION `getSubordinate`(`uid` int) RETURNS varchar(2000) CHARSET gb2312BEGIN   DECLARE str varchar(1000);  DECLARE cid varchar(100);DECLARE result VARCHAR(1000);DECLARE tmpName VARCHAR(100);SET str = '$';   SET cid = CAST(uid as char(10));   WHILE cid is not null DO     SET str = concat(str, ',', cid);   SELECT group_concat(eid) INTO cid FROM employees where FIND_IN_SET(parent_id,cid);         END WHILE;  SELECT GROUP_CONCAT(ename) INTO result FROM employees WHERE FIND_IN_SET(parent_id,str);RETURN result;   END

看神奇的结果:

虽然搞出来了,但说实话,真是不容易。。。

这种方法的优点是存储的信息少,查直接上司和直接下属的时候很方便,缺点是多级查询的时候很费劲。所以当只需要用到直接上下级关系的时候,用这种方法还是不错的,可以节省很多空间。后续还会介绍其它存储方案,并没有绝对的优劣之分,适用场合不同而已。

时间: 2017-12-08

MySQL存储毫秒数据的方法

有相当一部分刚接触到 MySQL 的朋友都遇到这样一个相同的问题,就是关于毫秒的存储与显示.由于MySQL数据类型中只提供了DATETIME, TIMESTAMP, TIME, DATE, YEAR这几种时间类型,而且DATETIME 以及 TIMESTAMP 的最小单位是秒,没有存储毫秒级别的函数. 不过MySQL却能识别时间中的毫秒部分.而且我们有多种方式可以获得毫秒的部分,比如函数:microsecond 等. 我这里举一个简单的例子,来存储秒之前和之后的部分. 对于把时间字段作为主键的应

mysql 存储过程中变量的定义与赋值操作

一.变量的定义 mysql中变量定义用declare来定义一局部变量,该变量的使用范围只能在begin...end 块中使用,变量必须定义在复合语句的开头,并且是在其它语句之前,也可以同时申明多个变量,如果需要,可以使用default赋默认值. 定义一个变量语法如下: declare var_name[,...] type[default value]看一个变量定义实例 declare last date;二.mysql存储过程变量赋值 变量的赋值可直接赋值与查询赋值来操作,直接赋值可以用set

mysql存储过程 游标 循环使用介绍

Mysql的存储过程是从版本5才开始支持的,所以目前一般使用的都可以用到存储过程.今天分享下自己对于Mysql存储过程的认识与了解. 一些简单的调用以及语法规则这里就不在赘述,网上有许多例子.这里主要说说大家常用的游标加循环的嵌套使用. 首先先介绍循环的分类: (1)WHILE ... END WHILE (2)LOOP ... END LOOP (3)REPEAT ... END REPEAT (4)GOTO 这里有三种标准的循环方式:WHILE循环,LOOP循环以及REPEAT循环.还有一种

MySQL存储引擎总结

前言 在数据库中存的就是一张张有着千丝万缕关系的表,所以表设计的好坏,将直接影响着整个数据库.而在设计表的时候,我们都会关注一个问题,使用什么存储引擎.等一下,存储引擎?什么是存储引擎? 什么是存储引擎? 关系数据库表是用于存储和组织信息的数据结构,可以将表理解为由行和列组成的表格,类似于Excel的电子表格的形式.有的表简单,有的表复杂,有的表根本不用来存储任何长期的数据,有的表读取时非常快,但是插入数据时去很差:而我们在实际开发过程中,就可能需要各种各样的表,不同的表,就意味着存储不同类型的

MySql存储过程与函数详解

存储过程和函数是在数据库中定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句.存储过程和函数可以避免开发人员重复的编写相同的SQL语句.而且,存储过程和函数是在MySQL服务器中存储和执行的,可以减少客户端和服务器端的数据传输. 一.存储过程 1.1.基本语法 CREATE PROCEDURE sp_name ([proc_parameter[,...]])    [characteristic ...] routine_body Sp_name:存储过程的名称

mysql存储过程详解

1.存储过程简介 我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它. 一个存储过程是一个可编程的函数,它在数据库中创建并保存.它可以有SQL语句和一些特殊的控制结构组成.当希望在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的.数据库中的存储过程可以看做是对编程中面

MySQL两种表存储结构MyISAM和InnoDB的性能比较测试

MySQL支持的两种主要表存储格式MyISAM,InnoDB,上个月做个项目时,先使用了InnoDB,结果速度特别慢,1秒钟只能插入10几条.后来换成MyISAM格式,一秒钟插入上万条.当时决定这两个表的性能也差别太大了吧.后来自己推测,不应该差别这么慢,估计是写的插入语句有问题,决定做个测试:测试环境:Redhat Linux9,4CPU,内存2G,MySQL版本为4.1.6-gamma-standard测试程序:Python+Python-MySQL模块.测试方案:1.MyISAM格式分别测

mysql 海量数据的存储和访问解决方案

第1章  引言 随着互联网应用的广泛普及,海量数据的存储和访问成为了系统设计的瓶颈问题.对于一个大型的互联网应用,每天几十亿的PV无疑对数据库造成了相当高的负载.对于系统的稳定性和扩展性造成了极大的问题.通过数据切分来提高网站性能,横向扩展数据层已经成为架构研发人员首选的方式.水平切分数据库,可以降低单台机器的负载,同时最大限度的降低了了宕机造成的损失.通过负载均衡策略,有效的降低了单台机器的访问负载,降低了宕机的可能性:通过集群方案,解决了数据库宕机带来的单点数据库不能访问的问题:通过读写分离

MySQL存储结构用法案例分析

本文实例讲述了MySQL存储结构用法.分享给大家供大家参考,具体如下: 前言 今天公司老大让我做一个MySQL的调研工作,是关于MySQL的存储结构的使用.这里我会通过3个例子来介绍一下MySQL中存储结构的使用过程,以及一些需要注意的点. 笔者环境 系统:Windows 7 MySQL:MySQL 5.0.96 准备工作 1.新建两张数据表:student1, student2 新建student1 DROP TABLE IF EXISTS student1; CREATE TABLE stu

更改Mysql数据库存储位置的具体步骤

一.首先把mysql的服务先停掉. 二.更改MySQL配置文件My.ini中的数据库存储主路径 打开MySQL默认的安装文件夹C:\Program Files\MySQL\MySQL Server 5.1中的my.ini文件,点击记事本顶部的"编辑","查找",在查找内容中输入datadir后并点击"查找下一个"转到"Path to the database root数据库存储主路径"参数设置,找到datadir="

CentOS Linux更改MySQL数据库目录位置具体操作

引言: 由于MySQL的数据库太大,默认安装的/var盘已经再也无法容纳新增加的数据,没有办法,只能想办法转移数据的目录. 下面我整理一下把MySQL从/var/lib/mysql目录下面转移到/home/mysql_data/mysql目录的具体操作: 1.首先我们需要关闭MySQL,命令如下: service mysqld stop 2.然后是转移数据,为了安全期间,我们采用复制命令cp,先找到mysql的原目录 cd /var/lib ls 运行这个命令之后就会看到mysql的目录了,然后

如何更改Linux(CentOS)系统下的MySQL数据库目录位置

如何更改CentOS系统下的MySQL数据库目录位置 1.首先我们需要关闭MySQL,命令如下: service mysqld stop 2.然后是转移数据,为了安全期间,我们采用复制的命令cp,先找到mysql的原目录 cd /var/lib ls 运行这个命令之后就会看到mysql的目录了,然后执行cp命令 cp -a mysql /home/mysql_data/ 这样就把数据库复制到/home/mysql_data下面去了 注意:(-a这个参数一定要带着,否则复制过去的权限就不对了.)

如何更改MySQL数据库的编码为utf8mb4

utf8mb4编码是utf8编码的超集,兼容utf8,并且能存储4字节的表情字符. 采用utf8mb4编码的好处是:存储与获取数据的时候,不用再考虑表情字符的编码与解码问题. 更改数据库的编码为utf8mb4: 1. MySQL的版本 utf8mb4的最低mysql版本支持版本为5.5.3+,若不是,请升级到较新版本. 2. MySQL驱动 5.1.34可用,最低不能低于5.1.13 SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%

Python3爬虫学习之MySQL数据库存储爬取的信息详解

本文实例讲述了Python3爬虫学习之MySQL数据库存储爬取的信息.分享给大家供大家参考,具体如下: 数据库存储爬取的信息(MySQL) 爬取到的数据为了更好地进行分析利用,而之前将爬取得数据存放在txt文件中后期处理起来会比较麻烦,很不方便,如果数据量比较大的情况下,查找更加麻烦,所以我们通常会把爬取的数据存储到数据库中便于后期分析利用. 这里,数据库选择MySQL,采用pymysql 这个第三方库来处理python和mysql数据库的存取,python连接mysql数据库的配置信息 db_

Ubuntu中更改MySQL数据库文件目录的方法

前言 公司的Ubuntu服务器对于各个系统的目录是放在不同的逻辑分区上的,比如存放mysql数据库文件的默认目录/var/lib/mysql所在的系统目录/var/单独在一个分区上,但是这个分区的大小是固定的,不足以存放整个数据库文件.但是另一个系统目录/data大小却达到T级,大大满足了需求,因此有了更改数据库文件目录的需求. 以下是我参考网络上一些文章进行尝试的过程: 1.停止数据库服务: 使用/etc/init.d/mysql stop 或者 stop mysql 2. 在目标位置(/da

MySQL数据库存储引擎和分支现状分析

MySQL随着相应的各主创和内部开发人员的离去,缔造了各个不同的引擎和分支,让MySQL有希望继续发扬光大起来. 在MySQL经历了2008年Sun的收购和2009年Oracle收购Sun的过程中,基本处于停滞发展的情况,在可以预见的未来,MySQL是肯定会被Oracle搁置并且逐步雪藏消灭掉的.MySQL随着相应的各主创和内部开发人员的离去,缔造了各个不同的引擎和分支,让MySQL有希望继续发扬光大起来. 本文大致讲解一下MySQL目前除了主要的 MyISAM.InnoDB.Heap(Memo

Linux下修改MySQL数据库数据文件路径的步骤

使用rpm安装方式安装完MySQL数据库后,数据文件的默认路径为/var/lib/mysql,然而根目录并不适合用于存储数据文件. 原路径:/var/lib/mysql 目标路径:/home/mysql_data/mysql Linux系统版本:centos7.6 MySQL数据库版本:5.7.25 步骤: 1.创建目标路径 mkdir -p /home/mysql_data 2.关闭MySQL systemctl stop mysqld 3.复制数据文件 cp -arp /var/lib/my

windows环境下mysql数据库的主从同步备份步骤(单向同步)

windows下mysql双向同步备份实现方法以下的文章主要讲述的是在windows环境下实现MySQL数据库的主从同步备份的正确操作方案,我在一些相关的网站看见关于windows环境下实现MySQL数据库的主从同步备份的操作步骤描述,但是很少有对其成功操作到底的,所以拿出此篇较为完整的方案与大家一起分享. 以下配置在本机上已经成功: 实现功能:A为主服务器,B为从服务器,初始状态时,A和B中的数据信息相同,当A中的数据发生变化时,B也跟着发生相应的变化,使得A和B的数据信息同步,达到备份的目的

thinkphp3.x连接mysql数据库的方法(具体操作步骤)

本文实例讲述了thinkphp3.x连接mysql数据库的方法.分享给大家供大家参考,具体如下: 惯例配置文件:ThinkPHP/conf/convention.php (1)在配置文件中填写配置信息(配置文件:"./xmall/conf/config.php"): 示例: <?php return array( //'配置项'=>'配置值' /* 数据库设置 */ 'DB_TYPE' => 'mysql', // 数据库类型 'DB_HOST' => 'loc