MySQL学习笔记之MySQL⾃定义函数
⼀、什么是函数
函数存储着⼀系列sql语句，调⽤函数就是⼀次性执⾏这些语句。所以函数可以降低语句重复。【但注意的是函数注重返回值，不注重执⾏过程，所以⼀些语句⽆法执⾏。所以函
数并不是单纯的sql语句集合。】
⼆、函数和存储过程的区别
关于存储过程，可以查看。
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;
这个动态设置的⽅式会在服务重启后失效，所以我们还必须在myf中设置，加上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.        -> return totalScore;
-> end $$
Query OK, 0 rows affected (0.06 sec)
mysql> delimiter ;
mysql group by order bymysql> 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                          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 函数名

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。