一、前言
最近在做團隊的排期系統改版時涉及到資料庫的遞迴查詢問題，有一個需求資料表，表中的需求資料以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 getNeedChild(1);
+-----------------------+
| getNeedChild(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 , getNeedChild(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