§1 函数
信任自定义函数
show variables like '%log_bin_trust_function_creators%';
此变量为 OFF 时,创建修改函数会报错
ERROR 1418 (HY000): 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)
通过下面语句设置
set global log_bin_trust_function_creators=1;
创建自定义函数
创建格式如下
DELIMITER &&
CREATE FUNCTION func_name(var_name var_type) RETURNS return_type
BEGIN
// function
END
$$
示例(随机字符串)
DELIMITER &&
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE charstr VARCHAR(100) DEFAULT 'abcefghigklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE resulestr VARCHAR(100) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
set returnstr = CONCAT(returnstr, SUBSTRING(charstr, FLOOR(1 + RAND() * 62), 1));
set i = i + 1;
END WHILE:
RETURN returnstr;
END
$$
§2 存储过程
创建自定义函数
创建格式如下
DELIMITER &&
CREATE PROCEDURE name(var_name var_type)
BEGIN
// PROCEDURE
END
$$
示例
DELIMITER &&
CREATE PROCEDURE add_data (n INT)
BEGIN
DECLARE i INT DEFAULT 0;
set autocommit = 0;
REPEAT
set i = i + 1;
insert into .......
until i = n;
END REPEAT:
COMMIT;
END
$$
调用自定义函数
CALL PROCEDURE_NAME(params...);