MySQL创建函数失败提示1418解决:
show variables like '%func%'; -- 如果Value处值为OFF,则需将其开启
set global log_bin_trust_function_creators=1;
函数
CREATE DEFINER=`root`@`%` FUNCTION `fun_demo1`(
`p_str` varchar(225),
`p_delimiter` varchar(1)
) RETURNS int(11)
BEGIN
return 1 + ( length(p_str) - length( replace(p_str, p_delimiter, '') ) );
END;
CREATE DEFINER=`root`@`%` FUNCTION `fun_demo2`(
p_str varchar(1000),
p_delimiter varchar(5),
p_index int
) RETURNS varchar(255) CHARSET utf8
BEGIN
declare result varchar(255) default '';
set result = reverse(substring_index(reverse(substring_index(f_str,f_delimiter,f_index)),f_delimiter,1));
return result;
END;
存储过程
DROP PROCEDURE IF EXISTS pro_demo1;
CREATE DEFINER=`erp`@`%` PROCEDURE `pro_demo1`(
IN field_1 int(11),
IN field_2 decimal(10,3)
)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE counter LONG DEFAULT 10000;
DECLARE userKey varchar(64);
DECLARE userName varchar(32);
DECLARE demo1_sursor CURSOR FOR select user_key,user_name from demo1 order by id asc;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
OPEN demo1_sursor;
REPEAT
FETCH demo1_sursor INTO userkey,userName;
IF done != 1 THEN
set counter = counter + 1;
INSERT INTO table1(`counter`, `userKey`, `userName`, `field_1`, `field_2`)
VALUES (counter, userKey, userName, field_1, field_2);
END IF;
UNTIL DONE END REPEAT;
CLOSE demo1_sursor ;
END;
DROP PROCEDURE IF EXISTS pro_demo2;
CREATE DEFINER=`erp`@`%` PROCEDURE `pro_demo2`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE field_1 int(6);
DECLARE field_2 decimal(6);
DECLARE demo2_sursor CURSOR FOR select field_1,field_2 from demo2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
OPEN demo2_sursor;
REPEAT
FETCH demo2_sursor INTO field_1,field_2;
IF done != 1 THEN
call pro_demo1(field_1,field_2);
END IF;
UNTIL DONE END REPEAT;
CLOSE demo2_sursor ;
END;
DROP PROCEDURE IF EXISTS pro_demo3;
CREATE DEFINER=`root`@`%` PROCEDURE `pro_demo3`()
BEGIN
DECLARE arrayLen INT DEFAULT 0;
DECLARE arrayIndex INT DEFAULT 0;
SET arrayLen = fun_demo1( '1,2,3,4', ',');
WHILE arrayIndex < arrayLen
DO
SET arrayIndex = arrayIndex + 1;
SELECT arrayIndex;
END WHILE;
END;