mysql创建函数1227_mysql 创建函数 error Code: 1227. Access denied;

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:02CREATE 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; ENDError Code: 1227. Access denied; you need (at least one of) the SUPER privilege(s) for this operation0.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)