MySQL 函数创建和管理

函数的基本概念

MySQL函数是一组预编译的SQL语句，用于执行特定的计算或操作，并返回一个值。函数可以分为：

• 内置函数：MySQL自带的函数，如数学函数、字符串函数、日期函数等
• 自定义函数：用户根据业务需求创建的函数，也称为存储函数

函数与存储过程的主要区别是：

• 函数必须返回一个值，而存储过程可以返回多个值或不返回值
• 函数可以在SQL语句中直接调用，而存储过程需要使用CALL语句调用

自定义函数的创建

基本语法

-- 创建自定义函数
DELIMITER //
CREATE FUNCTION function_name([parameter_list])
RETURNS return_type
[characteristic ...]
BEGIN
    -- 函数体
    RETURN return_value;
END //
DELIMITER ;

-- 参数列表格式
parameter_name data_type [, parameter_name data_type] ...

-- 特性选项
COMMENT 'comment'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }

创建示例

-- 创建计算两个数之和的函数
DELIMITER //
CREATE FUNCTION calculate_sum(a INT, b INT)
RETURNS INT
DETERMINISTIC
NO SQL
BEGIN
    DECLARE result INT;
    SET result = a + b;
    RETURN result;
END //
DELIMITER ;

-- 创建获取当前日期的函数
DELIMITER //
CREATE FUNCTION get_current_date()
RETURNS DATE
DETERMINISTIC
NO SQL
BEGIN
    RETURN CURRENT_DATE();
END //
DELIMITER ;

-- 创建根据用户ID获取用户名的函数
DELIMITER //
CREATE FUNCTION get_username(user_id INT)
RETURNS VARCHAR(50)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE username VARCHAR(50);
    SELECT name INTO username FROM users WHERE id = user_id;
    RETURN username;
END //
DELIMITER ;

自定义函数的调用

直接调用

-- 直接调用函数
SELECT calculate_sum(10, 20);
SELECT get_current_date();
SELECT get_username(1);

在SQL语句中使用

-- 在SELECT语句中使用函数
SELECT id, name, get_username(created_by) AS creator_name
FROM orders;

-- 在WHERE子句中使用函数
SELECT * FROM orders
WHERE YEAR(created_at) = YEAR(get_current_date());

-- 在INSERT语句中使用函数
INSERT INTO orders (name, created_by, created_at)
VALUES ('Order 1', 1, get_current_date());

-- 在UPDATE语句中使用函数
UPDATE orders
SET updated_at = get_current_date()
WHERE id = 1;

自定义函数的管理

查看函数

-- 查看所有函数
SHOW FUNCTION STATUS;

-- 查看特定数据库的函数
SHOW FUNCTION STATUS WHERE Db = 'database_name';

-- 查看函数的创建语句
SHOW CREATE FUNCTION function_name;

-- 查询information_schema获取函数信息
SELECT * FROM information_schema.routines
WHERE routine_type = 'FUNCTION' AND routine_schema = 'database_name';

修改函数

-- 修改函数的特性
ALTER FUNCTION function_name [characteristic ...];

-- 示例：修改函数的注释
ALTER FUNCTION calculate_sum COMMENT 'Calculates the sum of two numbers';

-- 示例：修改函数的安全特性
ALTER FUNCTION get_username SQL SECURITY INVOKER;

删除函数

-- 删除自定义函数
DROP FUNCTION IF EXISTS function_name;

函数的特性选项

DETERMINISTIC

• DETERMINISTIC：函数在相同的输入下总是返回相同的结果
• NOT DETERMINISTIC：函数在相同的输入下可能返回不同的结果（如使用RAND()函数）

SQL访问类型

• CONTAINS SQL：函数包含SQL语句，但不读取或修改数据
• NO SQL：函数不包含SQL语句
• READS SQL DATA：函数包含读取数据的SQL语句
• MODIFIES SQL DATA：函数包含修改数据的SQL语句

SQL SECURITY

• DEFINER：函数以创建者的权限执行
• INVOKER：函数以调用者的权限执行

内置函数的使用

数学函数

-- 基本数学运算
SELECT ABS(-10), CEIL(3.14), FLOOR(3.14), ROUND(3.14159, 2);

-- 三角函数
SELECT SIN(PI()/2), COS(PI()), TAN(PI()/4);

-- 随机数生成
SELECT RAND(), RAND(123); -- 带种子的随机数生成

-- 幂运算和对数
SELECT POWER(2, 3), SQRT(16), LOG(100, 10);

字符串函数

-- 字符串操作
SELECT CONCAT('Hello', ' ', 'World'), SUBSTRING('MySQL', 2, 3), LENGTH('MySQL');

-- 字符串转换
SELECT UPPER('mysql'), LOWER('MYSQL'), TRIM('  MySQL  ');

-- 字符串替换
SELECT REPLACE('Hello World', 'World', 'MySQL'), REVERSE('MySQL');

-- 字符串比较
SELECT STRCMP('MySQL', 'mysql'), INSTR('Hello World', 'World');

日期和时间函数

-- 获取当前日期和时间
SELECT NOW(), CURDATE(), CURTIME();

-- 日期和时间格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'), TIME_FORMAT(CURTIME(), '%H:%i:%s');

-- 日期和时间运算
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY), DATE_SUB(NOW(), INTERVAL 1 HOUR);

-- 日期和时间差值
SELECT DATEDIFF('2023-12-31', '2023-01-01'), TIMEDIFF('12:00:00', '10:30:00');

聚合函数

-- 基本聚合函数
SELECT COUNT(*), SUM(amount), AVG(amount), MAX(amount), MIN(amount)
FROM orders;

-- 分组聚合
SELECT category, COUNT(*), SUM(amount)
FROM products
GROUP BY category;

-- 带有HAVING子句的聚合
SELECT category, COUNT(*)
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

函数的性能优化

避免在WHERE子句中使用函数

-- 不推荐：在WHERE子句中使用函数，会导致索引失效
SELECT * FROM orders WHERE YEAR(created_at) = 2023;

-- 推荐：直接比较日期，利用索引
SELECT * FROM orders WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

优化自定义函数

-- 优化前：多次调用函数
SELECT id, name, get_username(created_by), get_username(updated_by)
FROM orders;

-- 优化后：减少函数调用次数
SELECT o.id, o.name, u1.name AS creator_name, u2.name AS updater_name
FROM orders o
LEFT JOIN users u1 ON o.created_by = u1.id
LEFT JOIN users u2 ON o.updated_by = u2.id;

使用DETERMINISTIC特性

-- 推荐：对于相同输入总是返回相同结果的函数，使用DETERMINISTIC特性
DELIMITER //
CREATE FUNCTION calculate_discount(amount DECIMAL(10,2), discount_rate DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
NO SQL
BEGIN
    RETURN amount * discount_rate;
END //
DELIMITER ;

函数的权限管理

函数相关权限

-- 创建函数需要的权限
CREATE ROUTINE

-- 修改函数需要的权限
ALTER ROUTINE

-- 执行函数需要的权限
EXECUTE

-- 删除函数需要的权限
DROP ROUTINE

授予和撤销权限

-- 授予创建函数的权限
GRANT CREATE ROUTINE ON database_name.* TO 'user'@'host';

-- 授予执行函数的权限
GRANT EXECUTE ON FUNCTION database_name.function_name TO 'user'@'host';

-- 授予所有函数的执行权限
GRANT EXECUTE ON database_name.* TO 'user'@'host';

-- 撤销权限
REVOKE EXECUTE ON FUNCTION database_name.function_name FROM 'user'@'host';

版本差异

MySQL 5.7 vs 8.0 函数差异

特性	MySQL 5.7	MySQL 8.0
函数创建限制	需要设置log_bin_trust_function_creators参数	仍需设置该参数，但默认值更安全
函数加密	不支持	支持，使用ALTER FUNCTION ... ENCRYPTION = 'Y'
函数注释	支持	增强，支持更详细的元数据
函数监控	基本支持	增强，更多performance_schema视图
函数安全性	基本支持	增强，支持更细粒度的权限控制
函数语法	基本支持	增强，支持更多特性和选项

常见问题及解决方法

1. 无法创建函数

问题：创建函数时提示"This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled"

解决方法：

-- 方法1：设置全局参数
SET GLOBAL log_bin_trust_function_creators = 1;

-- 方法2：在创建函数时添加特性选项
CREATE FUNCTION function_name()
RETURNS return_type
DETERMINISTIC
NO SQL
BEGIN
    -- 函数体
END;

2. 函数执行报错

问题：调用函数时提示"FUNCTION function_name does not exist"

解决方法：

-- 检查函数是否存在
SHOW FUNCTION STATUS WHERE Db = 'database_name';

-- 检查函数名是否正确，包括大小写
SELECT get_username(1); -- 区分大小写

-- 检查是否在正确的数据库中调用函数
USE database_name;
SELECT function_name();

3. 函数权限问题

问题：调用函数时提示"Access denied; you need (at least one of) the EXECUTE privilege(s) for this operation"

解决方法：

-- 授予执行权限
GRANT EXECUTE ON FUNCTION database_name.function_name TO 'user'@'host';
FLUSH PRIVILEGES;

最佳实践

1. 函数命名规范

• 使用有意义的函数名，遵循驼峰命名法或下划线命名法
• 为函数添加详细的注释，说明功能、参数和返回值
• 避免使用MySQL关键字作为函数名

2. 函数设计原则

• 保持函数的单一职责，一个函数只做一件事
• 尽量减少函数的复杂度，函数体不宜过长
• 合理使用函数特性选项，提高函数的性能和安全性
• 避免在函数中执行耗时操作，如大量数据查询或复杂计算

3. 函数使用注意事项

• 避免在WHERE子句中频繁调用函数，以免影响查询性能
• 对于频繁调用的函数，考虑优化或替换为更高效的实现
• 定期检查和维护函数，删除不再使用的函数
• 注意函数的安全性，避免在函数中执行危险操作

4. 函数测试和调试

• 在正式环境中使用函数前，先在测试环境中充分测试
• 使用SELECT语句或CALL语句测试函数的返回值
• 利用MySQL的调试工具或日志记录函数的执行过程
• 定期监控函数的执行性能，及时发现和解决问题

常见问题（FAQ）

Q1: 自定义函数和存储过程的区别是什么？

A1: 函数必须返回一个值，而存储过程可以返回多个值或不返回值；函数可以在SQL语句中直接调用，而存储过程需要使用CALL语句调用。

Q2: 如何查看MySQL中的所有自定义函数？

A2: 可以使用SHOW FUNCTION STATUS命令或查询information_schema.routines表查看所有自定义函数。

Q3: 为什么创建函数时提示权限不足？

A3: 创建函数需要CREATE ROUTINE权限，同时如果启用了二进制日志，还需要设置log_bin_trust_function_creators参数或在函数声明中添加适当的特性选项。

Q4: 如何优化函数的性能？

A4: 优化函数性能的方法包括：避免在WHERE子句中使用函数、减少函数调用次数、使用DETERMINISTIC特性、保持函数简洁高效等。

Q5: 函数可以返回哪些数据类型？

A5: 函数可以返回MySQL支持的所有数据类型，包括数值型、字符串型、日期时间型、布尔型等。

Q6: 如何删除自定义函数？

A6: 可以使用DROP FUNCTION IF EXISTS function_name语句删除自定义函数。

Q7: 函数的安全特性SQL SECURITY有什么作用？

A7: SQL SECURITY特性用于指定函数执行时使用的权限，DEFINER表示以创建者的权限执行，INVOKER表示以调用者的权限执行。

Q8: 如何在函数中使用事务？

A8: 函数中可以使用事务，但需要注意的是，如果函数在事务中执行，当函数执行失败时，整个事务会回滚。

Q9: 函数可以调用其他函数吗？

A9: 是的，函数可以调用其他内置函数或自定义函数，但需要注意避免循环调用导致的性能问题。

Q10: 如何查看函数的执行计划？

A10: 可以使用EXPLAIN语句查看包含函数调用的SQL语句的执行计划，或者使用MySQL的性能监控工具查看函数的执行情况。