mysql函数使用场景_（六）MySQL自定义函数应用场景

先记应用示例：

自定义函数 (user-defined function UDF)就是用一个象ABS() 或 CONCAT()这样的固有(内建)函数一样作用的新函数去扩展MySQL。

所以UDF是对MySQL功能的一个扩展

个人实践中，高度抽象的查询逻辑，可以写成MySQL函数。之前在项目中，查询客户的推荐人，递归到顶级。查询客户的下级树，递归层数有30多层。在程序中写递归查询，效率很低，控制台飘着的全是SQL，借鉴他人的做法，把递归的逻辑写在了DB层，很好的提高了效率。

函数调试：

本人是建了张表，临时变量存进去，判断执行过程的，暂未掌握函数DEBUG方法

通用的树操作示例：

建表：

CREATE TABLE IF NOT EXISTS t_user(

id INT(11) AUTO_INCREMENT PRIMARY KEY,

`level` INT(11) DEFAULT 1,

parent_id INT(11) DEFAULT 0,

`name` VARCHAR(20),

`age` TINYINT(11) UNSIGNED,

`gender` TINYINT(11) UNSIGNED DEFAULT 0 COMMENT '0:男；1:女'

) ENGINE = InnoDB COMMENT = '用户信息表';

初始化数据：

INSERT INTO t_user(`level`, `parent_id`, name, age, gender) VALUES(1, 0, '张三', 14, 1);

INSERT INTO t_user(`level`, `parent_id`, name, age, gender) VALUES(1, 0, '李四', 12, 1);

INSERT INTO t_user(`level`, `parent_id`, name, age, gender) VALUES(2, 1, '王五', 13, 0);

INSERT INTO t_user(`level`, `parent_id`, name, age, gender) VALUES(2, 2, '赵六', 45, 0);

INSERT INTO t_user(`level`, `parent_id`, name, age, gender) VALUES(3, 3, '简柒', 34, 1);

INSERT INTO t_user(`level`, `parent_id`, name, age, gender) VALUES(3, 4, '王八', 23, 0);

INSERT INTO t_user(`level`, `parent_id`, name, age, gender) VALUES(3, 4, '何九', 12, 1);

INSERT INTO t_user(`level`, `parent_id`, name, age, gender) VALUES(4, 5, '石十', 98, 0);

INSERT INTO t_user(`level`, `parent_id`, name, age, gender) VALUES(4, 5, '范十一', 87, 0);

INSERT INTO t_user(`level`, `parent_id`, name, age, gender) VALUES(4, 6, '夏十二', 76, 1);

INSERT INTO t_user(`level`, `parent_id`, name, age, gender) VALUES(4, 7, '汤十三', 65, 0);

INSERT INTO t_user(`level`, `parent_id`, name, age, gender) VALUES(1, 7, '彭十四', 54, 1);

INSERT INTO t_user(`level`, `parent_id`, name, age, gender) VALUES(1, 5, '杨十五', 43, 1);

INSERT INTO t_user(`level`, `parent_id`, name, age, gender) VALUES(1, 6, '铁十六', 32, 0);

INSERT INTO t_user(`level`, `parent_id`, name, age, gender) VALUES(1, 7, '郭十七', 32, 1);

INSERT INTO t_user(`level`, `parent_id`, name, age, gender) VALUES(1, 8, '牛十八', 21, 0);

INSERT INTO t_user(`level`, `parent_id`, name, age, gender) VALUES(1, 9, '蔡十九', 79, 1);

INSERT INTO t_user(`level`, `parent_id`, name, age, gender) VALUES(1, 9, '白二十', 57, 0);

INSERT INTO t_user(`level`, `parent_id`, name, age, gender) VALUES(1, 8, '朱二一', 46, 1);

INSERT INTO t_user(`level`, `parent_id`, name, age, gender) VALUES(1, 10, '林二二', 35, 0);

递归到顶级的函数：

DROP FUNCTION IF EXISTS getParentsUser;

CREATE FUNCTION `getParentsUser`(rootId INT)

RETURNS varchar(1000)

BEGIN

DECLARE sTemp VARCHAR(1000);

DECLARE sTempPar VARCHAR(1000);

SET sTemp = '';

SET sTempPar =rootId;

-- 循环递归

WHILE sTempPar is not null DO

-- 判断是否是第一个，不加的话第一个会为空

IF sTemp != '' THEN

SET sTemp = concat(sTemp,',',sTempPar);

ELSE

SET sTemp = sTempPar;

END IF;

SET sTemp = concat(sTemp,',',sTempPar);

SELECT group_concat(pid) INTO sTempPar FROM treenodes where pid<>id and FIND_IN_SET(id,sTempPar)>0;

END WHILE;

RETURN sTemp;

END

递归子节点的函数：

DROP FUNCTION IF EXISTS findChildrenUser;

CREATE FUNCTION findChildrenUser(rootId INT)

RETURNS VARCHAR(4000)

BEGIN

DECLARE sTemp VARCHAR(4000);

DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';

SET sTempChd = CAST(rootId AS CHAR);

WHILE sTempChd IS NOT NULL DO

SET sTemp= CONCAT(sTemp,',',sTempChd);

SELECT GROUP_CONCAT(id) INTO sTempChd FROM t_user WHERE FIND_IN_SET(parent_id,sTempChd)>0;

END WHILE;

RETURN sTemp;

END;

查询：

mysql> SELECT getParentsUser(15);

+-----------------------+

| getParentsUser(15) |

+-----------------------+

| 15,15,7,7,4,4,2,2,0,0 |

+-----------------------+

1 row in set (0.00 sec)

mysql> SELECT getChildrenUser(2);

+-----------------------------+

| getChildrenUser(2) |

+-----------------------------+

| $,2,4,6,7,10,11,12,14,15,20 |

+-----------------------------+

1 row in set (0.00 sec)

组合使用：

测试以下查询效率很低，暂不清楚原因，项目中选择了分部查。先查id集合，再用in查行记录

mysql> SELECT * FROM t_user WHERE FIND_IN_SET(id, getParentsUser(15));

+----+-------+-----------+-----------+------+--------+

| id | level | parent_id | name | age | gender |

+----+-------+-----------+-----------+------+--------+

| 2 | 1 | 0 | 李四 | 12 | 1 |

| 4 | 2 | 2 | 赵六 | 45 | 0 |

| 7 | 3 | 4 | 何九 | 12 | 1 |

| 15 | 4 | 7 | 郭十七 | 32 | 1 |

+----+-------+-----------+-----------+------+--------+

4 rows in set (0.02 sec)

mysql> SELECT * FROM t_user WHERE FIND_IN_SET(id, getChildrenUser(2));

+----+-------+-----------+-----------+------+--------+

| id | level | parent_id | name | age | gender |

+----+-------+-----------+-----------+------+--------+

| 2 | 1 | 0 | 李四 | 12 | 1 |

| 4 | 2 | 2 | 赵六 | 45 | 0 |

| 6 | 3 | 4 | 王八 | 23 | 0 |

| 7 | 3 | 4 | 何九 | 12 | 1 |

| 10 | 4 | 6 | 夏十二 | 76 | 1 |

| 11 | 4 | 7 | 汤十三 | 65 | 0 |

| 12 | 4 | 7 | 彭十四 | 54 | 1 |

| 14 | 4 | 6 | 铁十六 | 32 | 0 |

| 15 | 4 | 7 | 郭十七 | 32 | 1 |

| 20 | 5 | 10 | 林二二 | 35 | 0 |

+----+-------+-----------+-----------+------+--------+

10 rows in set (0.02 sec)

随机数

方便后续测试SQL效率，索引优化，写批量插入脚本

获取随机字符串：

DELIMITER //

CREATE FUNCTION RANDOM_STR(length int)

RETURNS VARCHAR(255)

BEGIN

DECLARE meta_str VARCHAR(62) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890';

DECLARE target_str VARCHAR(255) default '';

DECLARE i INT DEFAULT 0;

WHILE i < length DO

SET target_str = CONCAT(target_str, SUBSTRING(meta_str,FLOOR(1 + RAND() * 62), 1));

SET i = i + 1;

END WHILE;

RETURN target_str;

END //

测试：

mysql> SELECT RANDOM_STR(5);

+---------------+

| RANDOM_STR(5) |

+---------------+

| omuds |

+---------------+

1 row in set (0.00 sec)

mysql> SELECT RANDOM_STR(5);

+---------------+

| RANDOM_STR(5) |

+---------------+

| uTRrr |

+---------------+

1 row in set (0.00 sec)

mysql> SELECT RANDOM_STR(7);

+---------------+

| RANDOM_STR(7) |

+---------------+

| Li6kg9I |

+---------------+

1 row in set (0.00 sec)

获取随机整数值：

DELIMITER //

CREATE FUNCTION RANDOM_NUMBER(min INT, max INT)

RETURNS INT

BEGIN

DECLARE target_no INT;

SELECT FLOOR(RAMD() * (max - min) + min) INTO target_no;

RETURN target_no;

END //

测试：

mysql> SELECT RANDOM_NUMBER(3,5);

+--------------------+

| RANDOM_NUMBER(3,5) |

+--------------------+

| 3 |

+--------------------+

1 row in set (0.00 sec)

mysql> SELECT RANDOM_NUMBER(3,5);

+--------------------+

| RANDOM_NUMBER(3,5) |

+--------------------+

| 4 |

+--------------------+

1 row in set (0.00 sec)

mysql> SELECT RANDOM_NUMBER(3,5);

+--------------------+

| RANDOM_NUMBER(3,5) |

+--------------------+

| 3 |

+--------------------+

1 row in set (0.00 sec)

mysql> SELECT RANDOM_NUMBER(3,5);

+--------------------+

| RANDOM_NUMBER(3,5) |

+--------------------+

| 4 |

+--------------------+

1 row in set (0.00 sec)

mysql> SELECT RANDOM_NUMBER(3,5);

+--------------------+

| RANDOM_NUMBER(3,5) |

+--------------------+

| 4 |

+--------------------+

1 row in set (0.00 sec)