MySQL函数-递归函数

1. 前置学习 @符号

• @后接变量名，用以定义一个变量，该变量的有效期为语句级，即再一次执行中始终有效，基本示例如下：

-- 由于通常情况下=被认为是等于比较运算符，因此赋值运算符一般使用:= SELECT @lt:=1, @lt:=@lt+1, @lt:=@lt+1;

1 2 3

-- 由于tmp只有一行，当这两表进行笛卡尔积链接时，结果集实际上等同于增加了一列，而由于r变量的特性，每行都在原值的基础上在进行增加操作 SELECT a.BATCHNO, a.YEAR, @r:=@r+1 FROM m1 a, (SELECT @r:=0) tmp;

例子：连续出现的数字

输入：
Logs 表：
+----+-----+
| Id | Num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
输出：
Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
解释：1 是唯一连续出现至少三次的数字。

#首先遍历一遍整张表，找出每个数字的连续重复次数
#具体方法为：
    #初始化两个变量，一个为pre，记录上一个数字；一个为count，记录上一个数字已经连续出现的次数。
    #然后调用if()函数，如果pre和当前行数字相同，count加1极为连续出现的次数；如果不同，意味着重新开始一个数字，count重新从1开始。
    #最后，将当前的Num数字赋值给pre，开始下一行扫描。
    select 
        Num,    #当前的Num 数字
        if(@pre=Num,@count := @count+1,@count := 1) as nums, #判断 和 计数
        @pre:=Num   #将当前Num赋值给pre
    from Logs as l ,
        (select @pre:= null,@count:=1) as pc #这里需要别名
    #上面这段代码执行结果就是一张三列为Num,count as nums,pre的表。

#②将上面表的结果中，重复次数大于等于3的数字选出，再去重即为连续至少出现三次的数字。
    select 
        distinct Num as ConsecutiveNums 
    from  
        (select Num,
                if(@pre=Num,@count := @count+1,@count := 1) as nums,
                @pre:=Num
            from Logs as l ,
                (select @pre:= null,@count:=1) as pc
        ) as n
    where nums >=3;

#注意：pre初始值最好不要赋值为一个数字，因为不确定赋值的数字是否会出现在测试表中。
# 表的别名一个不能少

2. find_in_set()介紹

find_in_set函数使用

FIND_IN_SET(str,strlist)

str 要查询的字符串
strlist 字段名 参数以”,”分隔 如 (1,2,6,8)

精确匹配 ,分隔的数据

mysql中find_in_set()函数的使用 - 平凡希 - 博客园

3. MySQL function函数 - 递归函数使用

Mysql 根据id查所有父级或子级 - 锐洋智能 - 博客园

mysql递归查询，mysql中从子类ID查询所有父类（做无限分类经常用到）
 
 
由于mysql 不支持类似 oracle with ...connect的 递归查询语法
之前一直以为类似的查询要么用存储过程要么只能用程序写递归查询.
 
 
现在发现原来一条sql语句也是可以搞定的
 
 
先来看数据表的结构如下：
 
 
id  name    parent_id
---------------------------
1   Home        0
2   About       1
3   Contact     1
4   Legal       2
5   Privacy     4
6   Products    1
7   Support     1
我要的要求是根据一个分类ID（这个分类ID可能是一个子分类），得到所有的父分类，下面是相应的SQL：
 
 
SELECT T2.id, T2.name
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id, #@r是id,下一个递归的id=当前的parent_id,下一个递归的id作为新的parent_id
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 5, @l := 0) vars, #定义变量@r,@l
        table1 h
    WHERE @r <> 0) T1  #递归终止条件是 id==0
JOIN table1 T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC
 
 
 
 
代码@r := 5标示查询id为5的所有父类。结果如下
1, ‘Home’
2, ‘About’
4, ‘Legal’
5, ‘Privacy’

单个递归

### 声明头文件  单个
CREATE DEFINER=`user_name` FUNCTION `getFromTree`(
	selectIds VARCHAR (1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,      ### 入参是  123 的形式，递归找出所有路径上的父节点
	workId VARCHAR (100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci       ### 单个其他条件
) RETURNS varchar(1000) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci


BEGIN
  DECLARE selectId VARCHAR (50);  ### 单个selectId
  DECLARE result VARCHAR (1000);     ### 单个selectId找到的父节点

SELECT
	GROUP_CONCAT(T2.result_id) into result  ### 将查询的结果放入临时变量,GROUP_CONCAT是 追加,
FROM
	(
		SELECT
			@r AS _id,
			(
				SELECT
					@r := parent_id
				FROM
					work_tree
				WHERE
					id = _id
			) AS parent_id
		FROM
			(SELECT @r := selectId) vars,    ### 定义变量@r
			work_tree
		WHERE
			@r <> 0       ###  父节点不为0
	) T1
JOIN work_tree T2 ON T1._id = T2.id
WHERE
	T2.workId = workId and (T2.type !=2 or T2.parent_id = 0);

RETURN result;
END