我要评论平时写sql，我们一直在用mysql内置函数，ifnull、substring、date_format 这些随手就来。但遇到业务规则重复、逻辑固定的场景，很多人会想到写自定义函数。
不过我发现一个现象：很多团队一上来就疯狂写udf，把复杂业务全部塞到mysql函数里面，最后上线才发现查询性能雪崩、主从同步异常、问题难以排查。
自定义函数不是银弹，它有自己的适用边界。这篇就聊聊，到底该怎么设计mysql自定义函数。
什么时候才考虑自定义函数？
先划一条底线：能在应用层实现的逻辑，尽量不要放在mysql自定义函数里。
适合写自定义函数的场景：
- 简单的格式化转换，逻辑固定，多处复用。比如手机号脱敏、统一编码规则、状态码映射。
- 轻量计算，无io、不查询其他表。比如根据时间计算某个业务标签、金额简单换算。
- 报表、统计sql中反复使用的纯表达式逻辑，不想到处复制一大串
case when。
不适合，强烈不建议写自定义函数的场景：
- 函数内部查询表（select），这种非常容易产生性能灾难。
- 复杂业务逻辑、多分支嵌套、循环量大的计算。
- 用于where条件里过滤大表，大概率直接让索引失效。
- 需要操作多表、事务、更新数据。这类交给应用层或者存储过程，函数只适合返回单个值。
mysql自定义函数（udf）本质就是输入若干参数，返回单个值。它不能返回多行、不能返回结果集，这是最基础的限制。
自定义函数设计核心原则
1. 保持函数无状态
好的自定义函数，同样输入一定得到同样输出。也就是所谓的deterministic。
- 确定性函数：相同参数，每次调用结果一致，例如字符串脱敏、数值换算。
- 非确定性函数：每次调用结果不一样，比如
rand()、uuid()、读取会话变量。
如果你的函数是非确定性的，定义时不要随便加上deterministic标记。错误标记会导致主从复制、查询优化器产生意想不到的bug。
很多人忽略这个点：binlog在statement模式下，非确定性函数极易造成主从数据不一致。
2. 尽量轻，尽量简单
函数每被调用一次，就要执行一遍。如果放在where子句，数据库会对每一行执行这个函数。表一旦变大，全表扫描的代价直接拉满。
反面例子：
where my_func(user_id) = 1
这种写法，只要my_func是自定义函数，基本无法使用user_id上的索引。数据库不能利用索引预先计算函数结果。
正确思路：优先在应用层预处理，或者使用生成列+索引方案，而不是在where里套自定义函数。
3. 参数与返回值类型严格对齐
设计的时候，提前定好输入、输出的数据类型，不要隐式类型转换。
- 参数长度、类型要明确：varchar就指定长度，数字区分int、decimal。
- 返回值不要随意切换类型，不要有时候返回数字，有时候返回null字符串。
- 做好null兼容。参数传null的时候，想好返回值是什么，避免到处抛出异常。
举个简单例子：手机号脱敏函数。输入varchar，输出固定varchar，对null做兜底处理。
delimiter //
create function mask_phone(phone varchar(20))
returns varchar(20)
deterministic
begin
if phone is null or length(phone) <> 11 then
return phone;
end if;
return concat(substring(phone,1,3), '****', substring(phone,8));
end //
delimiter ;
调用：
select mask_phone('13812345678');
-- 输出：138****5678
这个就是典型适合自定义函数的场景：逻辑简单，纯字符串处理，无表查询，多处复用。
编写自定义函数的规范
- 命名：函数名加业务前缀，区分系统函数，比如
biz_mask_phone，避免和内置函数重名。 - 注释：写清楚入参含义、返回值、异常场景（传入null会怎样）。mysql函数不支持多行注释写在定义里，建议在文档或者建函数语句旁写。
- 权限控制：创建函数需要
create routine权限。线上不要给普通业务账号开放创建函数权限。 - 避免副作用：自定义函数里面不要修改变量、不要执行insert/update。函数应该只读，只做计算。
- 减少异常抛出：提前校验参数，不要依赖数据库报错来拦截非法数据。
几个高频踩坑点
坑1：在函数里查询其他表
-- 强烈不推荐
create function get_status_name(status int) returns varchar(32)
begin
select name into @res from status_table where id = status;
return @res;
end
这种写法问题很多：
- 每一行都要查表，性能极差。
- 并发场景容易锁表。
- 主从同步风险高。
状态映射这类字典转换，更好方案：case when，或者应用层字典映射。
坑2：误用在where条件，索引失效
select * from user where mask_phone(phone) = '138****5678';
phone字段上就算有索引，也走不了。数据库无法利用索引匹配函数计算后的结果。
坑3：忘记deterministic标记，主从异常
deterministic告诉优化器：相同输入输出固定。如果函数里面用了rand()、now()，就不能标记成确定性函数。
在statement复制模式下，非确定性函数会有主从不一致风险。线上生产库，推荐binlog使用row模式规避这类问题。
坑4：函数过多，业务逻辑散落在数据库
如果数据库里几十上百个自定义函数，业务逻辑分布在db和代码两层。排查问题的时候，你不知道逻辑是在java/python代码里，还是藏在mysql函数中。调试、版本管理、灰度发布都会变得很麻烦。
什么时候可以考虑替代方案？
当你想写自定义函数前，可以依次评估下面方案：
- 用原生sql表达式 / case when 代替。
- 应用层预处理数据。
- 生成列（generated column）+索引（需要按函数结果检索的时候优先考虑）。
- 存储过程（适合批量更新，不能用于select里直接取值）。
总结
mysql自定义函数的设计核心一句话：只放简单、无io、无副作用、纯计算的复用逻辑，绝不把核心业务下沉到数据库。
设计时重点关注这几点：
- 判断场景：简单格式化转换才考虑udf；查表、复杂业务尽量不要。
- 定义属性：合理标记deterministic，注意主从复制风险。
- 类型严谨：入参、返回值明确，做好null兜底。
- 使用边界：不要在where中随意使用，防止索引失效。
数据库是存储层，不是业务逻辑层。自定义函数只是一个简化sql的小工具，不是承载业务的容器。把复杂逻辑留在应用代码里，数据库只管存储与简单计算，才是更稳妥的架构。
以上就是一文浅析mysql中自定义函数应该如何设计的详细内容，更多关于mysql自定义函数的资料请关注代码网其它相关文章！
发表评论