【MySQL基础】用户自定义函数（UDF）入门

1 什么是用户自定义函数（UDF）？

用户自定义函数（User-Defined Function）是MySQL中由开发者编写的可复用逻辑单元，它能将复杂操作封装为简单的函数调用。与内置函数（如sum()、now()）相比，UDF具有以下优势：

• 定制业务逻辑：处理特定业务规则
• 简化复杂查询：将多步计算封装为单一函数
• 统一数据规范：实现跨项目的标准化处理

2 创建自定义函数

2.1 基础语法

delimiter //  -- 修改分隔符

create function 函数名(参数 数据类型) 
returns 返回值类型
begin
    -- 函数逻辑
    return 结果;
end//

delimiter ;  -- 恢复默认分隔符

# 示例1：计算商品折扣价
delimiter //

create function calc_discount_price(
    original_price decimal(10,2), 
    discount_rate float
) returns decimal(10,2)
begin
    declare final_price decimal(10,2);
    set final_price = original_price * (1 - discount_rate);
    return final_price;
end//

delimiter ;

# 示例2：状态码转文字描述
delimiter //

create function translate_status(
    code int
) returns varchar(20)
begin
    return case code
        when 200 then 'success'
        when 404 then 'not found'
        when 500 then 'server error'
        else 'unknown'
    end;
end//

delimiter ;

3 调用自定义函数

3.1 基础调用

-- 计算折扣价
select calc_discount_price(100.00, 0.2); 

-- 转换HTTP状态码
select translate_status(404);

3.2 在查询中使用

-- 商品表应用折扣
select 
    product_name,
    price,
    calc_discount_price(price, 0.3) as promo_price 
from products
where category = 'electronics';

-- 日志状态分析
select 
    translate_status(status_code) as status,
    count(*) as count
from access_log
group by status;

4 删除自定义函数

4.1 语法格式

drop function if exists 函数名;

4.2 操作示例

-- 安全删除函数
drop function if exists calc_discount_price;

-- 批量删除过时函数
drop function if exists old_function1, old_function2;

5 高级技巧

5.1 处理复杂逻辑（条件判断）

delimiter //

create function check_server_load(
    cpu_usage int, 
    mem_usage int
) returns varchar(20)
begin
    if cpu_usage > 90 or mem_usage > 90 then
        return 'critical';
    elseif cpu_usage > 70 or mem_usage > 70 then
        return 'warning';
    else
        return 'normal';
    end if;
end//

delimiter ;

5.2 多参数传递

delimiter //

create function format_duration(
    seconds int,
    show_seconds boolean
) returns varchar(20)
begin
    if show_seconds then
        return concat(
            floor(seconds/3600), 'h ',
            floor((seconds%3600)/60), 'm ',
            seconds%60, 's'
        );
    else
        return concat(
            floor(seconds/3600), 'h ',
            floor((seconds%3600)/60), 'm'
        );
    end if;
end//

delimiter ;

-- 使用示例
select format_duration(3665, true); 

6 注意事项

6.1 权限管理

• 创建函数需要CREATE ROUTINE权限
• 执行函数需要EXECUTE权限
• 推荐授权命令：grant create routine, execute on dbname.* to 'user'@'%';

6.2 性能优化

• 避免在UDF中使用复杂查询
• 对高频使用函数添加deterministic声明

# deterministic声明：
create function func_name() 
returns int deterministic
begin
    -- 逻辑
end

6.3 错误排查

• 查看函数定义：

show create function translate_status;

• 检查函数状态：

select * from information_schema.routines where routine_type = 'function';

7 应用场景示例

7.1 场景1：IP地址转换

create function ip_to_number(
    ip varchar(15)
) returns bigint
begin
    return 
        substring_index(ip, '.', 1) * 16777216 +
        substring_index(substring_index(ip, '.', 2), '.', -1) * 65536 +
        substring_index(substring_index(ip, '.', 3), '.', -1) * 256 +
        substring_index(ip, '.', -1);
end

7.2 场景2：磁盘空间报警

create function check_storage(
    used bigint, 
    total bigint
) returns varchar(20)
begin
    declare ratio float;
    set ratio = used / total;
    return case
        when ratio > 0.9 then 'critical'
        when ratio > 0.8 then 'warning'
        else 'normal'
    end;
end