阅读 693

数据库递归查询:MySQL VS Sequelize

一、前言

最近在做团队的排期系统改版时涉及到数据库的递归查询问题,有一个需求数据表,表中的需求数据以parentId为外键定义数据的继承关系,需求之间的关系呈现树状关系。需求数据表如下:

mysql> desc needs;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(45) | YES  |     | NULL    |                |
| parentId | int(11)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
复制代码

目前有这样的需求需要根据某个根需求,查找出全部的层级的子需求。

例如A需求的树状结构如下:

数据如下:

mysql> select * from needs;
+----+------+----------+
| id | name | parentId |
+----+------+----------+
|  1 | A    |     NULL |
|  2 | B    |        1 |
|  3 | C    |        1 |
|  4 | D    |        2 |
|  5 | E    |        2 |
|  6 | F    |        3 |
|  7 | G    |        3 |
|  8 | H    |        5 |
|  9 | I    |        5 |
| 10 | J    |        8 |
+----+------+----------+
10 rows in set (0.00 sec)
复制代码

二、MySQL实现

1.自定义函数实现

实现思路:首先根据子级parenId等于父级id的关系循环找出所有的层级关系数据的id,再拉出所有这些id的数据。

(1)函数声明

DELIMITER // 
CREATE FUNCTION `getParentList`(rootId INT)
    RETURNS char(400)
    BEGIN
      DECLARE fid int default 1;
      DECLARE str char(44) default rootId;
      WHILE rootId > 0 DO
      SET fid=(SELECT parentId FROM needs WHERE id=rootId);
     IF fid > 0 THEN
     SET str=CONCAT(str , ',' , fid);
     SET rootId=fid;
     ELSE SET rootId=fid;
     END IF;
     END WHILE;
  return  str;
  END //
复制代码

语法解释:

DELIMITER:定义MySQL的分隔符为//,默认分隔符是;,为了防止函数内使用;中断函数

CREATE FUNCTION 函数名(参数) RETURNS 返回值类型:自定义函数

DECLARE:声明变量

WHILE 条件 DO 循环体:while循环

IF 条件 THEN 内容体 ELSE 内容体:if判断

SET 变量=值:存储值

CONCAT(str1,str2,...):函数,用于将多个字符串连接成一个字符串

(2)函数调用

mysql> DELIMITER;
    -> SELECT getParentList(1);
+-----------------------+
| getParentList(1)       |
+-----------------------+
| ,1,2,3,4,5,6,7,8,9,10 |
+-----------------------+
1 row in set (0.01 sec)
复制代码

语法解释:

DELIMITER;:由于之前执行了DELIMITER //修改了分隔符,因此需要重新调用修改分隔符为;

SELECT 函数():调用函数并搜索出结果

(3)结合FIND_IN_SET,拉取出所有的子需求

mysql> SELECT * FROM needs WHERE FIND_IN_SET(ID , getParentList(1));
+----+------+----------+
| id | name | parentId |
+----+------+----------+
|  1 | A    |     NULL |
|  2 | B    |        1 |
|  3 | C    |        1 |
|  4 | D    |        2 |
|  5 | E    |        2 |
|  6 | F    |        3 |
|  7 | G    |        3 |
|  8 | H    |        5 |
|  9 | I    |        5 |
| 10 | J    |        8 |
+----+------+----------+
10 rows in set (0.03 sec)
复制代码

FIND_IN_SET(str,strlist):函数,查询字段strlist中包含str的结果,strlist中以,分割各项

2.递归CTE实现

(1)递归CTE介绍

CTE(common table expression)为公共表表达式,可以对定义的表达式进行自引用查询。在MySQL 8.0版以上才支持。

递归CTE由三个部分组成:初始查询部分、递归查询部分、终止递归条件。

语法如下:

WITH RECURSIVE cte_name AS(
    initial_query       -- 初始查询部分
    UNION ALL           -- 递归查询与初始查询部分连接查询
    recursive_query     -- 递归查询部分
)
SELECT * FROM cte_name
复制代码

更多CTE介绍可以查看文档:A Definitive Guide To MySQL Recursive CTE

(2)递归CTE实现

WITH RECURSIVE needsTree AS
( SELECT id,
         name,
         parentId,
         1 lvl
    FROM needs
    WHERE id = 1 
  UNION ALL
  SELECT nd.id,
         nd.name,
         nd.parentId,
         lvl+1
    FROM needs AS nd
    JOIN needsTree AS nt ON nt.id = nd.parentId 
)
SELECT * FROM needsTree ORDER BY lvl;
复制代码

实现解释:

初始查询部分:找出一级需求

递归查询部分:找出子级需求

终止递归条件:子级的parentId等于父级的id

查询结果:

+------+------+----------+------+
| id   | name | parentId | lvl  |
+------+------+----------+------+
|    1 | A    | NULL     |    1 |
|    2 | B    | 1        |    2 |
|    3 | C    | 1        |    2 |
|    6 | F    | 3        |    3 |
|    7 | G    | 3        |    3 |
|    4 | D    | 2        |    3 |
|    5 | E    | 2        |    3 |
|    8 | H    | 5        |    4 |
|    9 | I    | 5        |    4 |
|   10 | J    | 8        |    5 |
+------+------+----------+------+
10 rows in set (0.00 sec)
复制代码

三、Sequqlize实现

1.Sequelize介绍

Sequelize是Node.js的ORM框架,能够把关系数据库的表结构映射到对象上,支持数据库Postgres、MySQL、 MariaDB、 SQLite and Microsoft SQL Server。在这次的排期系统后台开发中,我选择了该框架来操作数据库,可以更方便地处理数据。

更多Sequelize介绍可以查看官方文档:Sequelize官方文档

2.递归实现

1.连接mysql数据库

var Sequelize = require('sequelize');

const sequelize = new Sequelize('schedule' , 'root' , '12345678' , {
    host : '127.0.0.1',
    dialect : 'mysql',
    port : '3306',
})

module.exports = {
    sequelize
}
复制代码

语法解释:

new Sequelize(databse , username , password , options):实例化Sequelize,连接数据库

options = {
    host,   //数据库主机
    dialect,    //数据库
    port        //数据库端口号,默认为3306
}
复制代码

2.定义数据表的schema模型表

module.exports = function(sequelize, DataTypes) {
  return sequelize.define('needs', {
    id: {
      type: DataTypes.INTEGER(11),
      allowNull: false,
      primaryKey: true,
      autoIncrement: true
    },
    name: {
      type: DataTypes.STRING(45),
      allowNull: false
    },
    parentId: {
      type: DataTypes.INTEGER(11),
      allowNull: true,
    },
  }, {
    tableName: 'needs',
    timestamps: false
  });
};
复制代码

语法解释:

sequelize.define(modelName , attribute , options):定义数据表的模型,相当于定义数据表。

attribute:一个对象,为数据表对应的列项,key值为对应的列项名,value为对应列项的定义,比如数据类型、是否主键、是否必需等

options:数据表的一些配置。比如对应的数据表名tableName、是否需要时间戳timestamp

3.导入数据表模型

const { sequelize } = require('../config/db');

// 导入数据表模型
const Needs = sequelize.import('./needs.js');
复制代码

语法解释:

sequelize.import(path):导入数据表模型

4.递归查询

实现思路:跟CTE实现思路相似,先找出找出一级需求,再递归找出子需求。

class NeedModule{
    constructor(id){
        this.id = id;
    }
    async getNeedsTree(){
        let rootNeeds = await Needs.findAll({
            where : { 
                id : this.id 
            }
        })
        rootNeeds = await this.getChildNeeds(rootNeeds);
        return rootNeeds;
    }
    async getChildNeeds(rootNeeds){
        let expendPromise = [];
        rootNeeds.forEach(item => {
            expendPromise.push(Needs.findAll({
                where : {
                    parentId : item.id
                }
            }))
        })
        let child = await Promise.all(expendPromise);
        for(let [idx , item] of child.entries()){
            if(item.length > 0){
                item = await getChildNeeds(item);
            }
            rootNeeds[idx].child = item;
        }
        return rootNeeds;
    }
}
复制代码

语法解释:

findALL(options):查询多条数据

options:查询配置

  • options.where:查询条件

查询结果如下:

从搜索结果可以看出,使用Sequelize查询可以更好的给层级数据划分层级存储。

3.nested属性实现

Sequelize的findAll方法中的nested属性可以根据连接关系找出继承关系的数据。

1.定义表关系

由于需要需求表进行自连接查询,因此需要先定义表关系。需求表自身关系以父需求为主查询是一对多关系,因此使用hasMany定义关系。

Needs.hasMany(
    Needs, 
    {
        as: 'child', 
        foreignKey: 'parentId'
    }
);
复制代码

语法解释:

sourceModel.hasMany(targetModel, options):定义源模型和目标模型的表是一对多关系,外键会添加到目标模型中

options:定义表关系的一些属性。如as定义连接查询时,目标模型的别名。foreignKey为外键名。

2.自连接查询

async getNeedTree(id){
    return await Needs.findAll({
        where : {
            id 
        },
        include : {
            model: Needs,
            as:'child', 
            required : false,
            include : {
                all : true,
                nested : true,
            }
        }
    })
}
复制代码

语法解释:

include:连接查询列表

  • include.model:连接查询的模型

  • include.as:连接查询模型的别名

  • include.requeired:如果为true,连接查询为内连接。false为左连接。如果有where默认为true,其他情况默认为false

  • include.all:嵌套查询所有的模型

  • include.nested:嵌套查询

使用此方法,查询最深的子级结果为三层。如果能保证数据继承关系最深为三层,可以使用此方法。

四、总结

在MySQL 8+可以使用CTE实现,相对于自定义函数实现可以使用更少的代码量实现,且使用WITH...AS可以优化递归查询。Sequelize目前支持CTE,但仅支持PostgreSQL、SQLite、MSSQL数据库,如果有更好的实现方式,可以分享下哦(≧▽≦)

参考文档

1.mysql 递归查询 http://www.cnblog…

2.Managing Hierarchical Data in MySQL Using the Adjacency List Model

3.A Definitive Guide To MySQL Recursive CTE

4.http://docs.seque…

关注下面的标签,发现更多相似文章
评论