Mysql User-defined function instances and solutions to some problems _ MySQL

Mysql User-defined function instances and solutions to some problems Mysql User-defined function instances and solutions to some problems _ MySQL

1. check whether the function is enabled:

mysql> show variables like '%func%';+-----------------------------------------+-------+| Variable_name | Value |+-----------------------------------------+-------+| log_bin_trust_function_creators | ON |+-----------------------------------------+-------+1 row in set (0.02 sec)

2. if the Value of Value is OFF, enable it.

mysql> set global log_bin_trust_function_creators=1;use app02delimiter //mysql>DROP FUNCTION IF EXISTS `testhanshu`;CREATE FUNCTION `testhanshu`(`tustate` int)RETURNS varchar(2000)BEGINdeclare oneAddr varchar(200) default '';declare allAddr varchar(2000) default '';DECLARE done INT DEFAULT FALSE;declare curl CURSOR for select utruename from tsys_user where ustate = tustate;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;open curl;  REPEATFETCH curl INTO oneAddr;IF NOT done THENset oneAddr = CONCAT(oneAddr, ';');set allAddr = CONCAT(allAddr, oneAddr);END IF;UNTIL done END REPEAT;close curl;RETURN allAddr;END;mysql>select testhanshu(1);

Java. SQL. SQLException: This function has none of DETERMINISTIC, no SQL, or reads SQL DATA in its declaration and binary logging is enabled (you * might * want to use the less safe log_bin_trust_function_creators variable)

There are three solutions:

1. log on to the MySql client and run: set global log_bin_trust_function_creators = 1;
2. when logging on to the MySQL server, add the "-- log-bin-trust-function-creators = 1" parameter when starting the service and set it to 1.
3. in the [mysqld] section of my. ini (my. cnf), add log-bin-trust-function-creators = 1.

The above is the mysql User-defined function instance and some problem solutions _ MySQL content. For more information, please follow the PHP Chinese network (www.php1.cn )!