mysql 如何设置一个函数_MySQL学习笔记之MySQL自定义函数

最新推荐文章于 2023-03-22 21:17:08 发布

马屿人

最新推荐文章于 2023-03-22 21:17:08 发布

阅读量564

点赞数

文章标签： mysql 如何设置一个函数

本文链接：https://blog.csdn.net/weixin_42389113/article/details/113225872

版权

一、什么是函数

函数存储着一系列sql语句，调用函数就是一次性执行这些语句。所以函数可以降低语句重复。【但注意的是函数注重返回值，不注重执行过程，所以一些语句无法执行。所以函数并不是单纯的sql语句集合。】

二、函数和存储过程的区别

关于存储过程，可以查看MySQL学习笔记之MySQL存储过程。

1)存储过程可以没有返回值，也可以有多个返回值，比较适合做批量插入、批量更新等操作

2)函数有且仅有一个返回值，比较适合做处理数据后返回一个结果

三、创建函数

语法：

create function 函数名([参数列表]) returns 返回类型

begin

函数体

end

参数格式：参数名参数类型

四、调用函数

语法：select 函数名(参数列表)

五、系统变量log_bin_trust_function_creators

首先查看一个系统变量：log_bin_trust_function_creators

select @@log_bin_trust_function_creators;

mysql> select @@log_bin_trust_function_creators;

+-----------------------------------+

| @@log_bin_trust_function_creators |

+-----------------------------------+

| 0 |

+-----------------------------------+

1 row in set (0.00 sec)

变量说明：

当二进制日志启用后，这个变量就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。如果设置为0(默认值)，用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。如果变量设置为1，MySQL不会对创建存储函数实施这些限制。此变量也适用于触发器的创建。

当log_bin_trust_function_creators的值为0时创建存储函数将会报错：“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)”，如下：

mysql> delimiter $$

mysql> create function get_students_total() returns int

-> begin

-> declare total int default 0; # 定义total变量，默认值为0

-> select count(*) into total from students;

-> return total;

-> end $$

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)

mysql> set @@global.log_bin_trust_function_creators = 1;

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter $$

mysql> create function get_students_total() returns int

-> begin

-> declare total int default 0;

-> select count(*) into total from students;

-> return total;

-> end $$

Query OK, 0 rows affected (0.02 sec)

mysql> select get_students_total();

+----------------------+

| get_students_total() |

+----------------------+

| 7 |

+----------------------+

1 row in set (0.06 sec)

那么为什么MySQL有这样的限制呢？因为二进制日志的一个重要功能是用于主从复制，而存储函数有可能导致主从的数据不一致。

所以当开启二进制日志后，参数log_bin_trust_function_creators就会生效，限制存储函数的创建、修改、调用。

如何解决这个问题呢？

1)如果数据库没有使用主从复制，那么就可以将参数log_bin_trust_function_creators设置为1。

mysql> set @@global.log_bin_trust_function_creators = 1;

这个动态设置的方式会在服务重启后失效，所以我们还必须在my.cnf中设置，加上log_bin_trust_function_creators=1，这样就会永久生效。

2)明确指明函数的类型，如果我们开启了二进制日志，那么我们就必须为我们的function指定一个参数。

其中下面几种参数类型里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。

这样一来相当于明确的告知MySQL服务器这个函数不会修改数据。

① DETERMINISTIC 不确定的

② NO SQL 没有SQl语句，当然也不会修改数据

③ READS SQL DATA 只是读取数据，当然也不会修改数据

④ MODIFIES SQL DATA 要修改数据

⑤ CONTAINS SQL 包含了SQL语句

mysql> delimiter $$

mysql> create function get_total() returns int reads sql data

-> begin

-> declare total int default 0;

-> select count(*) into total from students;

-> return total;

-> end $$

Query OK, 0 rows affected (0.08 sec)

mysql> delimiter ;

mysql> select get_total();

+-------------+

| get_total() |

+-------------+

| 7 |

+-------------+

1 row in set (0.09 sec)

六、存储函数使用示例

例1：创建存储函数，获取所有学生的总成绩

mysql> delimiter $$

mysql> create function get_students_totalScore() returns int reads sql data

-> begin

-> declare totalScore int default 0;

-> select sum(ifnull(t.总成绩,0)) into totalScore from students st left join (select s.student_id,sum(s.score) 总成绩 from subject su left join score s on su.id=s.subject_id group by s.student_id order by 总成绩 desc) t on st.id=t.student_id;

-> return totalScore;

-> end $$

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> select get_students_totalScore();

+---------------------------+

| get_students_totalScore() |

+---------------------------+

| 576 |

+---------------------------+

1 row in set (0.04 sec)

例2：创建存储函数根据传入的学生姓名，获取该学生的总成绩

mysql> delimiter $$

mysql> create function get_totalScore(name varchar(20)) returns int reads sql data

-> begin

-> declare totalScore int default 0;

-> select ifnull(t.总成绩,0) into totalScore from students st left join (select s.student_id,sum(s.score) 总成绩 from subject su left join score s on su.id=s.subject_id group by s.student_id order by 总成绩 desc) t on st.id=t.student_id where st.name=name;

-> return totalScore;

-> end $$

Query OK, 0 rows affected (0.06 sec)

mysql> delimiter ;

mysql> select get_totalScore('李四');

+--------------------------+

| get_totalScore('李四') |

+--------------------------+

| 268 |

+--------------------------+

1 row in set (0.06 sec)

七、查看存储函数

语法：show create function 函数名

mysql> show create function get_totalScore\G;

*************************** 1. row ***************************

Function: get_totalScore

sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `get_totalScore`(name varchar(20)) RETURNS int

READS SQL DATA

begin

declare totalScore int default 0;

select ifnull(t.总成绩,0) into totalScore from students st left join (select s.student_id,sum(s.score) 总成绩 from subject su left join score s on su.id=s.subject_id group by s.student_id order by 总成绩 desc) t on st.id=t.student_id where st.name=name;

return totalScore;

end

character_set_client: utf8mb4

collation_connection: utf8mb4_0900_ai_ci

Database Collation: utf8mb4_unicode_ci

1 row in set (0.00 sec)

八、删除存储函数

语法：drop function 函数名

马屿人

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql 如何设置一个函数_MySQL学习笔记之MySQL自定义函数

一、什么是函数函数存储着一系列sql语句，调用函数就是一次性执行这些语句。所以函数可以降低语句重复。【但注意的是函数注重返回值，不注重执行过程，所以一些语句无法执行。所以函数并不是单纯的sql语句集合。】二、函数和存储过程的区别关于存储过程，可以查看MySQL学习笔记之MySQL存储过程。1)存储过程可以没有返回值，也可以有多个返回值，比较适合做批量插入、批量更新等操作2)函数有且仅有一个返回值，...
复制链接

扫一扫