时间：2021-07-01 10:21:17 帮助过：12人阅读
2. 函数的例子
2.1. 最简单的函数
2.2. 返回查询结果的例子
3. 函数与存储过程的区别
3.1. 参数和返回值的区别
3.2. 函数不允许提交事务
3.3. 函数的特征限制
函数过程的语法结构如下：
CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
各个部分的介绍如下：
DEFINER：函数的创建者所属用户名。
函数的参数：
proc_parameter:
param_name type
函数的参数只能是IN类型的参数。
参数类型：
type:
Any valid MySQL data type
返回值：
RETURNS type
特征：
characteristic:
COMMENT ‘string‘
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
函数体：
routine_body:
Valid SQL routine statement
其中，
(1)COMMENT定义注释；
(2)LANGUAGE SQL
(3)DETERMINISTIC
DETERMINISTIC：确定的。
NOT DETERMINISTIC：不确定的。
(4) CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA:
(a)CONTAINS SQL:函数包含SQL语句；
(b)NO SQL： 函数中不包含SQL语句。
(c)READS SQL DATA：函数中包含读取数据的SQL语句。
(d)MOFIFIES SQL DATA：函数中包含修改数据的SQL语句。
(5)SQL SECURITY { DEFINER | INVOKER }：
假定函数定义者为a,调用者为b，则执行函数时:
DEFINER：先检查b是否有执行函数权限，再检查a是否有访问相关数据表的权限。
INVOKER：先检查b是否有执行函数权限，再检查b是否有访问相关数据表的权限。
一个最简单的函数的例子如下：
drop function if exists sf_p1;
delimiter $$
create function sf_p1()
returns integer
deterministic
return 100;
$$
delimiter ;
使用DETERMINISTIC特征限制，是因为MySQL启用了Binary Log；在启用了Binary Log时，如果不使用DETERMINISTRIC等特征限制，则该函数无法创建成功。
调用该函数：
mysql> select sf_p1();
+---------+
| sf_p1() |
+---------+
| 100 |
+---------+
1 row in set (0.00 sec)
drop function if exists sf_p1;
delimiter $$
create function sf_p1()
returns integer
deterministic
begin
declare x integer;
select count(*) into x from t1;
return x;
end;
$$
delimiter ;
调用函数：
mysql> select sf_p1();
+---------+
| sf_p1() |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
(1)函数的参数在语法上不允许使用IN和OUT以及INOUT修饰符；在语义上只支持IN类型参数。存储过程则在语法和语义上均支持IN,OUT和INOUT三种修饰符。
(2)函数必须有返回值定义，即RETURNS语句；函数还必须有至少一条RETURN语句来返回一个值;函数还必须有且仅有一个返回值。存储过程没有返回值的概念；存储过程需要通过OUT或者INOUT类型的参数来返回数据；存储过程可以定义N个OUT或INOUT类型的参数，N>=0。
函数不允许显式的提交事务(start transaction和commit等语句)，函数也不允许隐式提交事务(truncate table等语句)；而存储过程则没有这个限制。
以下的函数中存在隐式提交事务的truncate table语句，因此这个函数将无法创建成功。
drop function if exists sf_p1;
delimiter $$
create function sf_p1()
returns varchar(100)
NOT DETERMINISTIC
begin
truncate table t1;
insert into t1 values ( @@hostname);
insert into t1 values ( uuid());
insert into t1 values ( cast(rand() as char));
return ‘1‘;
end;
$$
delimiter ;
ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger.
解决办法：
移除truncate语句。
在启用了Binary Log的情况下，函数必须使用某些特征限制，否则该函数将无法创建成功。而存储过程则不存在这样的限制。
在启用了Binary Log的情况下，有可能会存在主从复制，这时必须保证函数的返回值在master和slave中是完全相同的，否则会导致主从数据不一致的问题。
因此，在启用了Binary Log的情况下，MySQL要求函数必须使用一些特征限制，否则不允许创建该函数。
这些限制可以是以下三种之一：
DETERMINISTIC：确定的值。
NO SQL：函数不执行任何SQL。
READS SQL DATA：函数仅仅读取一些数据。
如果当前用户有SUPER权限，则没有上述限制。
如果全局变量log_bin_trust_function_creators设置为ON，则也没有上述限制。这个全局变量默认为OFF。
mysql> show variables like ‘%log_bin_trust%‘;
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.02 sec)
drop function if exists sf_p1;
delimiter $$
create function sf_p1()
returns varchar(100)
begin
declare x varchar(100);
set x = ‘001‘;
return x;
end;
$$
delimiter ;
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)
解决办法：
增加DETERMINSTIC或NO SQL等特征。
drop function if exists sf_p1;
delimiter $$
create function sf_p1()
returns varchar(100)
deterministic
begin
declare x varchar(100);
set x = ‘001‘;
return x;
end;
$$
delimiter ;
或者：
drop function if exists sf_p1;
delimiter $$
create function sf_p1()
returns varchar(100)
no sql
begin
declare x varchar(100);
set x = cast( rand() as char);
return x;
end;
$$
delimiter ;
MySQL基础知识08函数
标签：values contain mys character hostname 提交 safe fine enable