mysql用戶自定義函數實例與部分問題解決方法

一、查看創建函數的功能是否開啟：
mysql> show variables like '%func%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| log_bin_trust_function_creators | ON |
+-----------------------------------------+-------+
1 row in set (0.02 sec)

二、如果Value處值為OFF，則需將其開啟。
mysql> set global log_bin_trust_function_creators=1;
use app02
delimiter //
mysql>DROP FUNCTION IF EXISTS `testhanshu`;

CREATE FUNCTION `testhanshu`(`tustate` int)
RETURNS varchar(2000)
BEGIN

declare 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;

REPEAT
FETCH curl INTO oneAddr;
IF NOT done THEN
set oneAddr = CONCAT(oneAddr, ';');
set allAddr = CONCAT(allAddr, oneAddr);
END IF;
UNTIL done END REPEAT;
close curl;
RETURN allAddr;
END;

mysql>select testhanshu(1);

在使用MySql自己定義的函數時，出現錯誤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)

解決辦法有三種：

1. 登錄MySql客戶端，執行： SET GLOBAL log_bin_trust_function_creators = 1;
2.在登錄MySQL服務器是，在服務啟動時加上 “--log-bin-trust-function-creators=1 ”參數並設置為1。
3.在my.ini（my.cnf）中的[mysqld]區段中加上 log-bin-trust-function-creators=1。