MySQL 函数学习总结

1 概述

我们都知道Mysql 内置了很多常用函数，比如日期函数 DATE_FORMAT(date,format)、 SUBSTR(str FROM pos FOR len) 等，为了就是方便的进行数据转换处理，但是有些业务场景系统内置函数不能满足，此时我们可以通过自定义函数的方式进行。通过select 的方式进行函数使用。

2 使用函数的好处

函数可以简化操作，提供sql 复用性。举个例子，比如我们想统计某个分类下有多少个子类，一般通过接口代码的方式通过传参进行递归调用实现，也可以通过SQL 函数传参的方式调用实现。

3 函数定义

 函数 由关键字 FUNCTION  声明，需要定义函数名、参数、返回值类型，以及函数体，函数体通过BEGIN 开始，END 结束，可以使用循环，判断等操作。

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型

  BEGIN

      函数体

  END

无参数：

CREATE FUNCTION getCount() RETURNS INT

  BEGIN

      DECLARE num INT DEFAULT 0;  #定义局部变量

      SELECT COUNT(*) INTO num FROM test;    #赋值与c

      RETURN num;   # 返回值

   END                 
              
有参数 ：   

CREATE FUNCTION getCount(id int) RETURNS INT

  BEGIN

      DECLARE num INT DEFAULT 0;  #定义局部变量

      SELECT COUNT(*) INTO num FROM test where id = id;    #赋值与c

      RETURN num;   # 返回值

   END                 
                                      

4 函数调用

 通过select 加函数名的方式调用，注意一定要带（），如果有参数需要在括号中添加参数。

select  functionname()

5 常用树查询函数 

• 根据节点查询下属节点个数

CREATE FUNCTION `getCatagroyLevelCount`(`nodeId` int) RETURNS int(8)
BEGIN
    DECLARE num int(8); 
	
	select count(*) into num from aim_cmdb_dm_dt where dm_def_id in (SELECT t3.cate_def_id FROM
	( SELECT *, IF (find_in_set(t1.cate_def_p_id, @p) >0,@p:= concat(@p,',',cate_def_id),0) AS childId FROM
			(SELECT cate_def_id,cate_def_p_id FROM 	aim_cmdb_cate_def t 	ORDER BY cate_def_id) t1,(SELECT @p:= nodeId ) t2 ) t3 WHERE	childId != 0) ;
			RETURN num;
END

• 根据叶子节点查询上级节点

CREATE  FUNCTION getParentNodeList(`nodeId` int) RETURNS varchar(1000) CHARSET utf8
BEGIN
    DECLARE parentList VARCHAR(1000);      # 返回父节点结果集
    DECLARE tempParent VARCHAR(1000);      # 临时存放父节点

    SET parentList = '';
    SET tempParent = CAST(nodeId as CHAR); # 将int类型转换为String

    WHILE tempParent <> -1 DO        # 循环，用于查询节点上所有的父节点
        SET parentList = CONCAT(parentList, ',', tempParent);   # 存入到返回结果中
        SELECT cate_def_p_id INTO tempParent FROM aim_cmdb_cate_def where cate_def_id = tempParent;   # 查询节点上所有父节点
    END WHILE;
    RETURN SUBSTRING(parentList, 2);      
END

• 根据节点查询子节点及叶子节点

create FUNCTION getLeafList(nodeId int) RETURNS VARCHAR(1000) 
BEGIN
  declare leaf VARCHAR(1000);
	
	select GROUP_CONCAT(cate_def_id) into leaf from (
	SELECT
		t3.cate_def_id 
	FROM
		(
	SELECT
		*,
	IF
		(
		find_in_set( t1.cate_def_p_id, @p ) > 0,
		@p := concat( @p, ',', cate_def_id ),
		0 
		) AS childId 
	FROM
		( SELECT cate_def_id, cate_def_p_id FROM aim_cmdb_cate_def t ORDER BY cate_def_id ) t1,
		( SELECT @p := nodeId ) t2 
		) t3 
	WHERE
		childId != 0 ) d;
	return leaf;
 END