Mysql_8.0.18-16：MySQL流程控制2-自定义函数
Mysql_8.0.18-16：MySQL流程控制2-自定义函数
MySQL数据库提供了一些内置的函数（数学函数、字符串函数）。
在开发过程中，有些业务场景需要自定义函数。
1.函数的基本语法
DELIMITER $$
CREATE FUNCTION example_function(input_param INT)
RETURNS VARCHAR(255)
BEGIN
DECLARE output_message VARCHAR(255);
IF input_param > 10 THEN
SET output_message = 'Input is greater than 10';
ELSE
SET output_message = 'Input is 10 or less';
END IF;
RETURN output_message;
END$$
DELIMITER ;
DELIMITER $$ -- 自定义结束符，避免函数体中的分号引起解析错误
CREATE FUNCTION 函数名(参数名 参数类型)
RETURNS 返回类型
READS SQL DATA -- 或 MODIFIES SQL DATA 等，根据函数行为选择
DETERMINISTIC -- 可选：如果输入相同输出一定相同
BEGIN
-- 声明局部变量（推荐使用 DECLARE）
DECLARE 变量名 数据类型 DEFAULT 初始值;
-- 函数体逻辑
SET 变量名 = 表达式;
RETURN 返回值;
END$$
DELIMITER ; -- 恢复默认结束符
关键字delimiter的使用：
delimiter是mysql分隔符。
在mysqlclient中分隔符默认是分号;
假设一次输入的语句较多，而且语句中间有分号，这时须要新指定一个特殊的分隔符比如 $$ \\。
完成函数定义后，再恢复默认_分号;
2.创建自定义函数
use shop;
SET GLOBAL log_bin_trust_function_creators = TRUE;
DELIMITER $$
CREATE FUNCTION fun(a1 varchar(255)) RETURNS VARCHAR(255)
BEGIN
DECLARE x VARCHAR(255) DEFAULT '';
SET x = CONCAT(a1,'word');
RETURN x;
END $$
DELIMITER ;
（2）查看自定义函数
- SHOW FUNCTION STATUS LIKE 'fun'; 检查的是函数 fun 的状态和元信息。
- SHOW CREATE FUNCTION fun; 检查的是函数 fun 的完整创建定义（代码）。
（3）调用自定义函数
- SELECT function_name(parameter_value,...)
MySQL [shop]> SELECT FUN('hello');
+--------------+
| FUN('hello') |
+--------------+
| helloword |
+--------------+
1 row in set (0.001 sec)
（4）修改自定义函数
ALTER FUNCTION function_name [characteristic ...]
characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
ALTER FUNCTION fun
READS SQL DATA
COMMENT '字符串连接';
SHOW CREATE FUNCTION fun;
（5）删除自定义函数
- DROP FUNCTION function_name
- DROP FUNCTION IF EXISTS function_name
DROP FUNCTION IF EXISTS fun;
DROP FUNCTION fun;
2.创建不带参数的自定义函数
注释方法：
- 方法一：使用 -- (双连字符 + 空格) 或 # (井号) 进行单行注释
- 方法二：使用 /* ... */ 进行多行注释
删除旧的函数：
DROP FUNCTION IF EXISTS getFormatDatetime;
USE shop;
DELIMITER $$
DROP FUNCTION IF EXISTS getFormatDatetime; -- 先删除旧函数
CREATE FUNCTION getFormatDatetime()
RETURNS VARCHAR(255) CHARACTER SET utf8mb4 -- 明确指定返回字符集
READS SQL DATA
BEGIN
RETURN CONVERT(DATE_FORMAT(NOW(), '%Y年%m月%d日%h时%i分%s秒') USING utf8mb4);
-- 或者更直接地：
-- RETURN _utf8mb4'%Y年%m月%d日%h时%i分%s秒' 格式化为时间; 但 DATE_FORMAT 不支持直接前缀
-- 所以用 CONVERT 是可靠方法
END$$
DELIMITER ;
/*
创建日期函数 getFormatDatetime
*/
/*
修改结束符为默认值分号;
*/
调用日期时间函数
MySQL [shop]> SELECT getFormatDatetime();
+----------------------------------+
| getFormatDatetime() |
+----------------------------------+
| 2023年08月27日09时43分46秒 |
+----------------------------------+
1 row in set (0.002 sec)
MySQL [shop]> show function status like 'getFormatDatetime';
+------+-------------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+-------------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| shop | getFormatDatetime | FUNCTION | root@% | 2023-08-27 21:43:31 | 2023-08-27 21:43:31 | DEFINER | | utf8 | utf8_general_ci | utf8mb4_0900_ai_ci |
+------+-------------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.003 sec)
MySQL [shop]> show create function getFormatDatetime;
+-------------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Function | sql_mode | Create Function | character_set_client | collation_connection | Database Collation |
+-------------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| getFormatDatetime | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`%` FUNCTION `getFormatDatetime`() RETURNS varchar(255) CHARSET utf8mb4
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日%h时%i分%s秒') | utf8 | utf8_general_ci | utf8mb4_0900_ai_ci |
+-------------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.001 sec)
3.创建带参数的自定义函数
use shop;
SET GLOBAL log_bin_trust_function_creators = TRUE;
创建自定义加法函数addFun
DELIMITER $$ -- 修改结束符
CREATE FUNCTION addFun(x int UNSIGNED,y int UNSIGNED)
RETURNS int
BEGIN
DECLARE sum int UNSIGNED DEFAULT 0;
set sum = x + y;
RETURN sum;
END $$
DELIMITER ; -- 结束符恢复默认
调用自定义加法函数
MySQL [shop]> SELECT addFun(10,26);
+---------------+
| addFun(10,26) |
+---------------+
| 36 |
+---------------+
1 row in set (0.002 sec)
修改自定义函数
ALTER FUNCTION addFun
READS SQL DATA
COMMENT '加法自定义函数';
mysql> show function status like 'addFun';
+------+--------+----------+---------+---------------------+---------------------+---------------+-----------------------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+--------+----------+---------+---------------------+---------------------+---------------+-----------------------+----------------------+----------------------+--------------------+
| shop | addFun | FUNCTION | root@% | 2023-08-27 22:53:42 | 2023-08-27 22:50:01 | DEFINER | 加法自定义函数 | utf8 | utf8_general_ci | utf8mb4_0900_ai_ci |
+------+--------+----------+---------+---------------------+---------------------+---------------+-----------------------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)
删除自定义函数
直接删除
mysql> DROP FUNCTION addFun;
Query OK, 0 rows affected (0.00 sec)
如果函数不存在，没有判断直接删除，会报错，会引起程序中断。
mysql> DROP FUNCTION addFun;
ERROR 1305 (42000): FUNCTION shop.addFun does not exist
先判断函数是否存在，再删除
mysql> DROP FUNCTION IF EXISTS addFun;
浙公网安备 33010602011771号