（六）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)
