mysql> show function status;
+------+------------------+----------+------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+------------------+----------+------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| zjzc | loadTreeByParent | FUNCTION | zjzc_app@% | 2016-06-02 21:51:04 | 2016-06-02 21:51:04 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
+------+------------------+----------+------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
重新创建函数;
delimiter $$
CREATE FUNCTION `loadTreeByParent`(begin_sn INT) RETURNS varchar(600) CHARSET utf8
BEGIN
DECLARE rest VARCHAR(600);
DECLARE temp VARCHAR(60);
SET rest='$';
SET temp=CAST(begin_sn AS CHAR);
WHILE temp IS NOT NULL DO
SET rest=CONCAT(rest,',',temp);
SELECT GROUP_CONCAT(sn) INTO temp FROM ClientManager WHERE FIND_IN_SET(parent,temp)>0;
END WHILE;
RETURN rest;
END$$
mysql> show function status;
+------+------------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+------------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| zjzc | loadTreeByParent | FUNCTION | root@% | 2016-06-03 19:04:41 | 2016-06-03 19:04:41 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
+------+------------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
CREATE DEFINER=`root`@`%` FUNCTION `loadTreeByParent`(begin_sn INT) RETURNS varchar(600) CHARSET utf8
BEGIN
DECLARE rest VARCHAR(600);
DECLARE temp VARCHAR(60);
SET rest='$';
SET temp=CAST(begin_sn AS CHAR);
WHILE temp IS NOT NULL DO
SET rest=CONCAT(rest,',',temp);
SELECT GROUP_CONCAT(sn) INTO temp FROM ClientManager WHERE FIND_IN_SET(parent,temp)>0;
END WHILE;
RETURN rest;
END
19:08:02 CREATE DEFINER=`zjzc_app`@`%` FUNCTION `loadTreeByParent`(begin_sn INT) RETURNS varchar(600) CHARSET utf8 BEGIN
DECLARE rest VARCHAR(600); DECLARE temp VARCHAR(60); SET rest='$'; SET temp=CAST(begin_sn AS CHAR);
WHILE temp IS NOT NULL DO SET rest=CONCAT(rest,',',temp);
SELECT GROUP_CONCAT(sn) INTO temp FROM ClientManager WHERE FIND_IN_SET(parent,temp)>0; END WHILE;
RETURN rest; END Error Code: 1227. Access denied; you need (at least one of) the SUPER privilege(s) for this operation 0.016 sec
直接进入终端,root登陆执行;
mysql> delimiter $$
mysql> CREATE DEFINER=`zjzc_app`@`%` FUNCTION `loadTreeByParent`(begin_sn INT) RETURNS varchar(600) CHARSET utf8
-> BEGIN
-> DECLARE rest VARCHAR(600);
-> DECLARE temp VARCHAR(60);
-> SET rest='$';
-> SET temp=CAST(begin_sn AS CHAR);
-> WHILE temp IS NOT NULL DO
-> SET rest=CONCAT(rest,',',temp);
-> SELECT GROUP_CONCAT(sn) INTO temp FROM ClientManager WHERE FIND_IN_SET(parent,temp)>0;
-> END WHILE;
-> RETURN rest;
-> END$$
Query OK, 0 rows affected (0.00 sec)
转载于:https://www.cnblogs.com/zhaoyangjian724/p/6199907.html