MySQL自定义函数
一、MySQL err1418的解决方案
Err] [Dtf] 1418 - 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)
[Err] [Dtf] Finished - Unsuccessfully
大概意思就是在Create Function的时候出了问题。首先我们登录MySQL查看一下MySQL的系统变量;
mysql>show VARIABLES like ‘%func%’; 关于show VARIABLES的用法参见:https://dev.mysql.com/doc/refman/5.0/en/show-variables.html
mysql> show variables like '%func%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)
解决方案很简单:
1.root登录MySQL,以下命令必须具有root权限。
2.执行
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
然后我们在再来查看VARIABLES
mysql> set GLOBAL log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show VARIABLES like '%func%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON |
+---------------------------------+-------+
1 row in set (0.00 sec)mysql>
二、自定义函数
1、NVL函数
CREATE DEFINER=`root`@`%` FUNCTION `NVL`(eExpression1 VARCHAR(36),eExpression2 VARCHAR(36)) RETURNS varchar(36) CHARSET utf8mb4
COMMENT '实现oracle中nvl函数'
BEGIN
#Routine body goes here...
SET @rst = 0;
SELECT IFNULL(eExpression1, eExpression2) INTO @rst;
RETURN @rst;
END
2、NVL2函数
CREATE DEFINER=`root`@`%` FUNCTION `NVL2`(eExpression1 VARCHAR(36),eExpression2 VARCHAR(36),eExpression3 VARCHAR(36)) RETURNS varchar(36) CHARSET utf8mb4
COMMENT '实现oracle中nvl2函数'
BEGIN
#Routine body goes here...
SET @rst = 0;
SELECT IF ( ISNULL(eExpression1) , eExpression2 , eExpression3 ) INTO @rst;
RETURN @rst;
END
3、DECODE函数
CREATE DEFINER=`root`@`%` FUNCTION `DECODE`(eExpression1 VARCHAR(36),eExpression2 VARCHAR(36),eExpression3 VARCHAR(36),eExpression4 VARCHAR(36)) RETURNS varchar(36) CHARSET utf8mb4
COMMENT '实现oracle中decode函数,但是,仅支持4个参数!!!'
BEGIN
#Routine body goes here...
DECLARE returnstring VARCHAR(36);
IF(eExpression1=eExpression2) THEN
SET returnstring=eExpression3;
ELSE
SET returnstring=eExpression4;
END IF;
RETURN returnstring;
END