做了几年MySQL运维和开发经常遇到一种尴尬内置函数不够用存储过程又没法在SELECT里当函数一样直接调用。比如业务方提了个需求——要把查询结果里的手机号、身份证号脱敏后直接吐给下游系统CONCAT、LEFT、RIGHT组合起来倒也能凑合但写出来的SQL又长又丑遇到要脱敏的字段多了简直就是一场灾难。后来我接触了MySQL UDFUser Defined Function也就是用户自定义函数用C写一个.so或.dll丢进插件目录就能像LENGTH()、SUBSTRING()一样在SQL里直接调用。这篇文章就完整记录一个能用的UDF例子从思路到代码、从编译到排错希望能给有同样需求的朋友省点时间。1. UDF到底是什么为什么值得自己写1.1 一次性说清UDF的定位UDF是MySQL提供的一种扩展机制允许用户用C或C编写函数编译成共享库Linux下是.soWindows下是.dll然后在MySQL里通过CREATE FUNCTION注册成可用的函数。注册之后这个函数就拥有了和内置函数几乎一样的地位可以在SELECT列表里用可以在WHERE条件里用也可以在GROUP BY、ORDER BY里参与计算。这是存储过程做不到的——存储过程只能通过CALL调用没法作为表达式的一部分嵌进SQL语句里。我当时的需求场景就很典型。有一个订单表里面有用户手机号字段数据分析平台要取这部分数据做报表但手机号不能明文给出去。传统做法是在应用层脱敏也就是Java、Python先把数据取出来处理完再输出。可问题是团队里有好几个应用在直连MySQL每个应用都要改代码工作量不小。如果用UDF直接在SQL层解决SELECT mask_phone(phone) FROM orders所有应用无需改动一次部署全局生效这就是UDF存在的最大价值——把计算逻辑下沉到数据库层让所有SQL调用方共享同一个函数。1.2 UDF和存储过程的区别很多人会问这个活存储过程也能干吧能是能但用起来差异很大。存储过程侧重的是流程控制、多条语句的事务性操作返回值一般是结果集或者OUT参数没办法像函数一样嵌入到表达式里。比如你想写WHERE LENGTH(mask_phone(phone)) 5存储过程做不到这种用法。而UDF本质是一个函数只要有输入参数就有返回结果语法和内置函数完全一致。另外两者的性能画像也不一样。存储过程在MySQL 5.7及之前版本里每次调用都有SQL解析和权限检查的开销复杂的循环逻辑执行效率也比较一般。UDF因为是编译后的原生代码执行路径短、没有SQL解析过程在简单计算场景下性能更稳定。我实测过一个字符串处理类UDF百万行级别的SELECT调用整体耗时比等价的存储过程循环要低不少这个差距在数据量大了之后会更明显。1.3 适合谁来用、用来解决什么问题如果你属于下面这几类人UDF值得了解一下数据库开发者或DBA遇到内置函数解决不了的定制化计算需求比如特殊的加密解密、复杂的字符串变换、业务特有的编码规则。应用后端工程师不想在多个应用里重复写同一套数据处理逻辑想把公共逻辑下沉到数据库。数据平台或数据仓库的维护者需要在SQL层面统一数据治理规则比如脱敏、标准化格式化、自定义校验。2. 动手前的准备环境、版本和思路2.1 开发和运行环境到底怎么选UDF是编译成原生共享库的所以编译环境和MySQL的运行环境必须匹配这点非常重要。Linux环境下我用的是一台CentOS 7.9服务器MySQL版本是5.7.44开发机上装了gcc和MySQL的头文件包。头文件不一定非要通过mysql-community-devel装其实也只需要一个mysql.h文件通常位于/usr/include/mysql/目录下。如果你只想写简单的UDF直接从MySQL源码包里拷贝include目录也能凑合用但强烈建议还是装官方devel包省心且版本匹配。Windows环境则是另一个路子。MySQL在Windows上加载的是.dll需要Visual Studio或者MinGW来编译。VS版本的话Visual Studio 2015及以上都能用编译的时候要链接MySQL安装目录下的libmysql.lib并在项目属性里把mysql.h所在的include目录加进去。这里有个很多新手会踩的坑Windows下编译的.dll依赖了特定版本的VC运行时库如果目标机器上没装对应的Visual C Redistributable包MySQL加载这个.dll的时候会直接报错所以部署的时候要记得把运行库一并装上。2.2 MySQL版本对UDF的影响MySQL 5.7和8.0对UDF的基本接口是一致的UDF_INIT、UDF_ARGS这些核心结构体没有变所以一份代码在两个大版本上都能编译通过。差别主要出现在编译环境上MySQL 8.0开始官方对头文件的组织方式和依赖做了一些调整如果你用的是8.0的devel包编译命令和5.7会略有差异。另外MySQL 8.0默认的plugin_dir路径和5.7也可能不一样可以通过下面这个SQL直接查看当前实例的插件目录SHOW VARIABLES LIKE plugin_dir;这个路径很关键编译好的共享库文件必须要放到这个目录下MySQL才能加载到。我见很多人卡在这一步明明文件放了却一直报Cant open shared library一查才发现是放错了目录。2.3 先想清楚函数要干什么写UDF之前我建议先花点时间把函数签名和预期行为列清楚。比如我要写的手机号脱敏函数函数名mask_phone输入参数一个字符串手机号输出脱敏后的字符串比如13812345678变成138****5678边界情况输入为空、输入长度不足7位、输入包含非数字字符把边界情况想清楚写代码和调试的时候会顺畅很多。UDF毕竟跑在MySQL服务进程内一旦崩溃影响的不只是当前会话而是整个数据库实例所以代码的健壮性必须放在第一位。我在代码里会对参数个数、参数类型、输入长度都做防御性检查宁可返回NULL也不能让进程崩溃。3. 核心代码实现一个能跑的脱敏UDF3.1 UDF的两个关键接口MySQL的普通标量函数需要实现三个函数名字是函数名、函数名_init、函数名_deinit。拿mask_phone来说就是三件套mask_phone()核心计算逻辑被调用时执行。mask_phone_init()初始化函数在第一次调用前执行用于检查参数、分配内存、设置max_length等。mask_phone_deinit()清理函数在语句执行完毕或连接结束前调用用于释放init()里分配的资源。MySQL执行一条包含UDF的SQL时执行流程是这样的先调用_init进行参数检查和内存准备然后对每一行数据调用主函数计算结果整个语句结束后调用_deinit做清理。理解了这个流程你就知道max_length为什么要在这里设置——它告诉MySQL这个函数返回的字符串最长是多少MySQL据此分配接收缓冲区。如果没有设置或者设置得太小字符串被截断甚至缓冲区溢出都是可能的。3.2 完整代码给了#include stdio.h #include string.h #include stdlib.h #include mysql.h /* 初始化函数检查参数设置返回长度 */ my_bool mask_phone_init(UDF_INIT *initid, UDF_ARGS *args, char *message) { /* 参数个数必须为1且必须是字符串类型 */ if (args-arg_count ! 1 || args-arg_type[0] ! STRING_RESULT) { strcpy(message, mask_phone() requires one string argument); return 1; } /* 手机号11位返回结果最大11字节留一个结束符空间 */ initid-max_length 11; /* 这里没有分配堆内存ptr置空即可 */ initid-ptr NULL; initid-maybe_null 1; initid-const_item 0; return 0; } /* 清理函数有malloc就要在这里free */ void mask_phone_deinit(UDF_INIT *initid) { /* 没有分配内存什么都不用做 */ /* 如果init里用了malloc这里一定要free */ } /* 主函数真正干活的 */ char *mask_phone(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error) { char *input args-args[0]; unsigned long input_len args-lengths[0]; unsigned long out_len; /* 参数为NULL时返回值也设为NULL */ if (input NULL) { *is_null 1; return NULL; } /* 长度不足7位的字符串无法完成中间四位脱敏原样返回 */ if (input_len 7) { *length input_len; return input; } /* 核心脱敏逻辑前3位保留中间4位打码后续保留 */ /* 比如 13812345678 - 138****5678 */ memcpy(result, input, 3); memcpy(result 3, ****, 4); memcpy(result 7, input 7, input_len - 7); out_len 3 4 (input_len - 7); result[out_len] \0; *length out_len; return result; }这里有个细节很多人容易忽视result这个指针指向的是MySQL预先分配好的内存大小就是initid-max_length指定的值。所以如果你的输出长度超过了max_length就存在缓冲区溢出的风险这是UDF最常见的崩溃原因之一。这个例子里手机号都是11位脱敏之后还是11位所以max_length设成11就够了。如果要做身份证号脱敏18位输入中间8位打码返回结果长度还是18位max_length就设成18。规则很简单max_length至少要大于等于最大可能的返回长度。3.3 为什么返回值是指针而不是值细心的读者会发现主函数返回的是char *也就是说返回的是一个指向字符串内存的指针。这个内存可以是args-args[0]即输入参数的原始内存。如果函数只是原样返回或简单变换现有数据可以直接返回输入参数比如上面代码里长度不足7位的分支就是直接返回了input。result也就是MySQL预先分配的缓冲区。大多数需要构造新字符串的场景把结果写到result里再返回result即可。自己malloc出来的内存。但这样就必须在_deinit里free掉否则每个连接都会内存泄漏。能用result解决的事情尽量不要自己malloc不仅麻烦还容易漏。另外一个容易忽略的是*is_null和*error标志。当函数遇到特殊情况要返回NULL就把*is_null设为1同时返回NULLMySQL会把这个结果当作SQL的NULL处理。如果发生了无法恢复的错误把*error设为1MySQL会直接终止这条语句并返回错误这个机制用来防止把错误数据继续往上抛。3.4 UDF_ARGS结构体里的细节UDF_ARGS结构体里值得注意的几个字段arg_count参数个数_init里做个数校验就靠它。arg_type参数类型数组常见取值为STRING_RESULT、INT_RESULT、REAL_RESULT、DECIMAL_RESULT。注意如果调用时传的是字符串类型就是STRING_RESULT如果传的是数字类型可能是INT_RESULT或REAL_RESULT不要搞混。args参数指针数组。对字符串类型args[i]是char *对整数类型args[i]是指向long long的指针需要强制类型转换才能取值。lengths参数长度数组对于字符串类型尤其重要。字符串不一定是以\0结尾的所以必须用lengths[i]来获取实际长度而不是依赖strlen()。这也是UDF编写中的高频坑。4. 编译和部署从源码到能用的.so4.1 Linux下的编译命令代码写好了接下来要编成共享库。Linux下我用的编译命令是gcc -O2 -fPIC -shared -I/usr/include/mysql -o mask_phone.so mask_phone.c逐个解释一下参数-O2开启优化虽然不开也能跑但UDF是高频函数能优化一点是一点。-fPIC生成位置无关代码编译共享库的必要选项不加这个链接阶段会报错。-shared生成动态库而不是可执行文件。-I/usr/include/mysql指定mysql.h头文件所在目录具体路径根据你的MySQL安装位置调整。-o mask_phone.so输出文件名这里建议固定成.so后缀。编译完成后用nm命令检查一下导出符号nm -D mask_phone.so | grep mask_phone正常情况下能看到mask_phone、mask_phone_init、mask_phone_deinit这几个符号。如果看不到说明符号导出有问题MySQL加载时会报function mask_phone ... not found之类的错误。4.2 Windows下的编译方式Windows平台的编译要稍微折腾一点。推荐直接用Visual Studio的命令行工具或者用MinGW的gcc也可以。以VS2017为例在“开发人员命令提示符”里可以这样编cl /O2 /LD mask_phone.c /IC:\Program Files\MySQL\MySQL Server 8.0\include /link /LIBPATH:C:\Program Files\MySQL\MySQL Server 8.0\lib libmysql.lib编译产物是mask_phone.dll。除了编译本身还要确保目标机器上有VS对应的运行库。另外MySQL是64位的你的.dll也必须是64位编译的这个别搞错了。4.3 部署把.so放到该放的地方编译好之后把.so文件复制到plugin_dir目录下。如果你不知道plugin_dir在哪执行SHOW VARIABLES LIKE plugin_dir;复制后可别着急先确认权限。MySQL服务用户通常是mysql要对这个文件有读权限。我见过一次诡异现象文件明明在路径也对权限也是755MySQL还是报打不开后来发现是SELinux拦截了MySQL读取自定义目录下的共享库。如果你的服务器开了SELinux需要执行chcon -t lib_t /usr/lib64/mysql/plugin/mask_phone.so或者临时关闭SELinux验证问题是不是它引起的。这一步很隐蔽排查起来很费时间写出来帮你避坑。4.4 在MySQL里注册函数文件就位后登录MySQL执行注册语句CREATE FUNCTION mask_phone RETURNS STRING SONAME mask_phone.so;语法拆开看CREATE FUNCTION后面的mask_phone是函数名RETURNS STRING声明返回类型SONAME指定共享库的文件名。文件名的路径不用写完整MySQL会自动去plugin_dir下找。注册成功后就正常用了SELECT mask_phone(13812345678);预期输出是138****5678。如果想删除函数DROP FUNCTION mask_phone;DROP FUNCTION并不会删掉.so文件只是把注册信息移除.so仍然在插件目录里躺着。如果文件被占用或者删除失败也基本不影响MySQL运行但下次再CREATE FUNCTION同名函数时会用到已有的文件。5. 把UDF用在实际SQL里效果和场景5.1 在查询中直接使用函数注册好之后它的用法和内置函数完全一样。比如我在测试环境跑一个实际业务查询SELECT id, mask_phone(phone) AS masked_phone, mask_phone(id_card) AS masked_id_card FROM users WHERE created_at DATE_SUB(NOW(), INTERVAL 7 DAY) LIMIT 100;这条SQL会返回脱敏后的手机号和身份证号下游系统拿到数据就能直接用不需要再做额外处理。由于mask_phone对NULL值返回NULL对异常短字符串原样返回所以数据的可用性也有保障。5.2 在WHERE条件里参与过滤UDF不仅可以出现在SELECT列表里还能出现在WHERE里。比如只查询脱敏后仍然包含某几个数字的号码SELECT * FROM users WHERE mask_phone(phone) LIKE 138%;当然实际生产环境不建议在WHERE里用UDF因为这类函数普遍无法走索引MySQL会对每行数据调用一次函数这属于全表扫描的性能开销。但对于一些数据量不大的临时候查偶尔用一次问题不大心里要清楚成本在哪里。5.3 和内置函数组合使用UDF之间、UDF和内置函数之间可以自由组合。写SQL的时候可以这样SELECT CONCAT(手机号: , mask_phone(phone)), IF(mask_phone(phone) IS NULL, 无号码, mask_phone(phone)) FROM users;这种组合能力让UDF用起来很灵活。这个例子虽然简单但思路可以延展你可以写一个mask_phone再写一个mask_id_card、mask_email、mask_bank_card每个都遵循同一套编写规范然后在SQL里按需调用。6. 踩坑实录UDF常见问题与排查技巧6.1 加载失败类报错ERROR 1126 (HY000): Cant open shared library mask_phone.so这个报错信息最容易误导人。文件不存在、文件没有读权限、SELinux拦截、文件格式不对、依赖的库缺失都可能导致这个统一的报错。排查思路可以按顺序来确认plugin_dir路径下文件名完全一致注意大小写Linux是区分大小写的。ls -l看权限确保mysql用户能读。执行ldd mask_phone.so看依赖的共享库是否都存在有没有not found。如果服务器开了SELinux用ausearch -m avc查一下是否有拦截记录。用file mask_phone.so确认编译格式是64位还是32位和MySQL位数不匹配也会报错。6.2 符号找不到类报错ERROR 1125 (HY000): Cant find function mask_phone in library mask_phone.so这个报错说明库加载成功了但里面找不到mask_phone这个符号。常见原因有两个一是编译的时候符号没导出二是函数名拼写不一致。检查方法就是前面提到的nm -D mask_phone.so | grep mask_phone另外要注意C编译器会做名字重整也就是mask_phone可能被编译成_Z10mask_phoneP9UDF_INITP8UDF_ARGS...这种形式MySQL根本识别不了。所以UDF源码一定要用C语言编译器编译或者用C写但必须用extern C包裹导出函数。6.3 函数注册冲突报错ERROR 1125 (HY000): Function mask_phone already exists这个好理解同名函数已经注册过了。用DROP FUNCTION mask_phone;先移除旧注册再重新注册。如果是测试开发阶段反复改代码建议流程是DROP FUNCTION、替换.so文件、再CREATE FUNCTION。记得先DROP再替换文件否则文件被MySQL句柄占用替换可能失败。6.4 运行时崩溃和错误结果这个是最恐怖的。UDF跑在MySQL服务进程里一旦段错误整个MySQL实例都会跟着挂掉。常见原因有这几个缓冲区溢出返回内容超出了max_length指定的长度写坏了内存。排查思路是检查任何可能的长字符串分支字段长度的极端值都要覆盖到。对NULL处理不当参数为NULL时直接解引用args-args[0]轻则返回垃圾数据重则崩溃。每个函数都要有NULL判断。类型误判调用时传的是INT但在代码里按字符串处理本质上是野指针访问。所以_init里一定要检查arg_type。忘记了*length赋值MySQL拿到了未定义的返回长度后果不可预期。主函数每个返回路径都要设置*length。测试阶段建议在一个专门的MySQL实例上做别直接在生产库上试这不是胆小而是UDF崩溃的代价太大。我自己的习惯是先在一个Docker容器里起个独立MySQL测试稳定后再上生产。热搜词里正好有人提到“docker安装mysql失败了”如果你也在用Docker折腾MySQL环境建议先确认容器内的plugin_dir路径和宿主机挂载卷的权限配置UDF文件放进去之前先确认容器内能看到、能读。6.5 二进制日志相关的坑MySQL开启log_bin时CREATE FUNCTION这种语句会被写入二进制日志如果从库或者同步工具无法执行CREATE FUNCTION主从同步就会中断。另外创建UDF需要INSERT权限且如果log_bin_trust_function_creators是0还要求你有SUPER权限。测试环境里遇到ERROR 1418这类报错时检查一下这个变量SHOW VARIABLES LIKE log_bin_trust_function_creators;如果是0可以在会话级别临时设置成1来测试但生产环境要慎重评估安全影响毕竟这会让非SUPER用户也能创建存储函数之类的对象。6.6 常见问题速查表报错现象可能原因解决方案Cant open shared library文件位置/权限/SELinux/位数不匹配按顺序检查plugin_dir、权限、ldd、fileCant find function符号未导出或名字被修改用nm -D检查确保用C编译器或extern C注册后调用崩溃缓冲区溢出、NULL解引用检查max_length、增加NULL判断返回乱码/长度不对没设置*length每个返回路径都补上*length赋值主从同步中断二进制日志里的CREATE FUNCTION被从库拒绝从库手动注册同名函数或调整binlog策略ERROR 1418log_bin_trust_function_creators0且无SUPER权限评估后临时或永久调整变量7. 性能和安全上的几条经验7.1 UDF的性能画像UDF虽然比存储过程快但也不是完全免费。每行数据调用时MySQL需要跨越C和SQL层之间交换数据这个开销虽然不大但在WHERE里用还是会放大很多。我的建议是UDF适合用在不涉及索引的字段转换场景比如脱敏、格式化、编码转换。如果你要在WHERE里对索引字段做复杂变换后再筛选不如在应用层或者ETL阶段提前处理好否则索引失效带来的性能损失比UDF本身的执行代价大得多。7.2 线程安全和全局状态MySQL的UDF调用是并发的你的函数可能被多个线程同时调用。因此不要在函数里使用静态局部变量来缓存数据、不要依赖全局可变状态。如果需要每个连接有独立的上下文可以考虑用initid-ptr指向一个每次_init时malloc出来的结构体在_deinit里释放这样每个连接之间的数据天然隔离。简单函数最好什么全局状态都不用无状态函数最安全。7.3 替代方案怎么选UDF不是唯一的路。如果你的逻辑可以写成SQL表达式组合优先用SQL完成如果逻辑复杂但只在应用层用可以考虑在Java、Python里实现如果逻辑涉及多表事务存储过程是更恰当的工具。UDF适合的是被SQL频繁调用、逻辑相对独立、要求高性能、并且内置函数无法直接满足的场景。想清楚需求边界再动手别为了一点点便利引入不必要的原生代码维护成本。8. 最后的实操建议8.1 版本管理很重要UDF源码虽然不大但一定要纳入版本管理和业务代码一样对待。我在实际项目里吃过亏线上UDF跑了大半年某天业务方要求改脱敏规则结果翻遍了服务器也找不到源码只能对着二进制反推逻辑极其痛苦。后来我规定所有UDF源码统一放在一个代码仓库的udf/目录下编译产物可以不用入库但源码必须入库部署脚本也一并管起来。8.2 测试用例覆盖边界UDF上线前写个简单的测试SQL把边界情况都过一遍SELECT mask_phone(13812345678) AS normal_case, mask_phone(NULL) AS null_case, mask_phone(123456) AS too_short_case, mask_phone(123456789012345678) AS long_case;这四条SQL分别对应正常值、NULL值、过短字符串、超长字符串每个返回路径都要验证。特别是NULL情况如果忘了处理一个NULL姓名字段就能让整个查询报错。这类基础测试谁都能写但实际跑到这里的人不多写出来提醒一下。8.3 多平台编译的一次教训之前开发机是Windows生产环境是Linux我图省事直接在Windows上编好.dll传上去结果MySQL加载报错。后来才意识到共享库格式完全不一样Windows上编出来的.dll在Linux上根本没戏。所以现在我的流程固定成源码在Git仓库编译产物在目标平台现场编译交叉编译的方案除非你很精通工具链否则不建议碰那块水太深。8.4 写完这个函数之后的延展mask_phone只是最入门的一个例子。同样的框架可以扩展出一整套脱敏函数库比如mask_id_card、mask_email、mask_bank_card甚至可以做自定义哈希函数、业务编号生成器、字符串相似度函数。核心脚手架都是一样的换的主要是主函数里的业务逻辑。从写第一行C代码到函数上线我花了大概一个下午大部分时间都耗在编译环境配置和调试上真正写代码的时间反而很少。希望这个例子能让你少走几步路快速跨过UDF这道门槛。 
长河君 · 2026/10/7 11:01:26 · 阅读 1.2k MySQL UDF实战：用C语言编写一个手机号脱敏函数
做了几年MySQL运维和开发经常遇到一种尴尬内置函数不够用存储过程又没法在SELECT里当函数一样直接调用。比如业务方提了个需求——要把查询结果里的手机号、身份证号脱敏后直接吐给下游系统CONCAT、LEFT、RIGHT组合起来倒也能凑合但写出来的SQL又长又丑遇到要脱敏的字段多了简直就是一场灾难。后来我接触了MySQL UDFUser Defined Function也就是用户自定义函数用C写一个.so或.dll丢进插件目录就能像LENGTH()、SUBSTRING()一样在SQL里直接调用。这篇文章就完整记录一个能用的UDF例子从思路到代码、从编译到排错希望能给有同样需求的朋友省点时间。1. UDF到底是什么为什么值得自己写1.1 一次性说清UDF的定位UDF是MySQL提供的一种扩展机制允许用户用C或C编写函数编译成共享库Linux下是.soWindows下是.dll然后在MySQL里通过CREATE FUNCTION注册成可用的函数。注册之后这个函数就拥有了和内置函数几乎一样的地位可以在SELECT列表里用可以在WHERE条件里用也可以在GROUP BY、ORDER BY里参与计算。这是存储过程做不到的——存储过程只能通过CALL调用没法作为表达式的一部分嵌进SQL语句里。我当时的需求场景就很典型。有一个订单表里面有用户手机号字段数据分析平台要取这部分数据做报表但手机号不能明文给出去。传统做法是在应用层脱敏也就是Java、Python先把数据取出来处理完再输出。可问题是团队里有好几个应用在直连MySQL每个应用都要改代码工作量不小。如果用UDF直接在SQL层解决SELECT mask_phone(phone) FROM orders所有应用无需改动一次部署全局生效这就是UDF存在的最大价值——把计算逻辑下沉到数据库层让所有SQL调用方共享同一个函数。1.2 UDF和存储过程的区别很多人会问这个活存储过程也能干吧能是能但用起来差异很大。存储过程侧重的是流程控制、多条语句的事务性操作返回值一般是结果集或者OUT参数没办法像函数一样嵌入到表达式里。比如你想写WHERE LENGTH(mask_phone(phone)) 5存储过程做不到这种用法。而UDF本质是一个函数只要有输入参数就有返回结果语法和内置函数完全一致。另外两者的性能画像也不一样。存储过程在MySQL 5.7及之前版本里每次调用都有SQL解析和权限检查的开销复杂的循环逻辑执行效率也比较一般。UDF因为是编译后的原生代码执行路径短、没有SQL解析过程在简单计算场景下性能更稳定。我实测过一个字符串处理类UDF百万行级别的SELECT调用整体耗时比等价的存储过程循环要低不少这个差距在数据量大了之后会更明显。1.3 适合谁来用、用来解决什么问题如果你属于下面这几类人UDF值得了解一下数据库开发者或DBA遇到内置函数解决不了的定制化计算需求比如特殊的加密解密、复杂的字符串变换、业务特有的编码规则。应用后端工程师不想在多个应用里重复写同一套数据处理逻辑想把公共逻辑下沉到数据库。数据平台或数据仓库的维护者需要在SQL层面统一数据治理规则比如脱敏、标准化格式化、自定义校验。2. 动手前的准备环境、版本和思路2.1 开发和运行环境到底怎么选UDF是编译成原生共享库的所以编译环境和MySQL的运行环境必须匹配这点非常重要。Linux环境下我用的是一台CentOS 7.9服务器MySQL版本是5.7.44开发机上装了gcc和MySQL的头文件包。头文件不一定非要通过mysql-community-devel装其实也只需要一个mysql.h文件通常位于/usr/include/mysql/目录下。如果你只想写简单的UDF直接从MySQL源码包里拷贝include目录也能凑合用但强烈建议还是装官方devel包省心且版本匹配。Windows环境则是另一个路子。MySQL在Windows上加载的是.dll需要Visual Studio或者MinGW来编译。VS版本的话Visual Studio 2015及以上都能用编译的时候要链接MySQL安装目录下的libmysql.lib并在项目属性里把mysql.h所在的include目录加进去。这里有个很多新手会踩的坑Windows下编译的.dll依赖了特定版本的VC运行时库如果目标机器上没装对应的Visual C Redistributable包MySQL加载这个.dll的时候会直接报错所以部署的时候要记得把运行库一并装上。2.2 MySQL版本对UDF的影响MySQL 5.7和8.0对UDF的基本接口是一致的UDF_INIT、UDF_ARGS这些核心结构体没有变所以一份代码在两个大版本上都能编译通过。差别主要出现在编译环境上MySQL 8.0开始官方对头文件的组织方式和依赖做了一些调整如果你用的是8.0的devel包编译命令和5.7会略有差异。另外MySQL 8.0默认的plugin_dir路径和5.7也可能不一样可以通过下面这个SQL直接查看当前实例的插件目录SHOW VARIABLES LIKE plugin_dir;这个路径很关键编译好的共享库文件必须要放到这个目录下MySQL才能加载到。我见很多人卡在这一步明明文件放了却一直报Cant open shared library一查才发现是放错了目录。2.3 先想清楚函数要干什么写UDF之前我建议先花点时间把函数签名和预期行为列清楚。比如我要写的手机号脱敏函数函数名mask_phone输入参数一个字符串手机号输出脱敏后的字符串比如13812345678变成138****5678边界情况输入为空、输入长度不足7位、输入包含非数字字符把边界情况想清楚写代码和调试的时候会顺畅很多。UDF毕竟跑在MySQL服务进程内一旦崩溃影响的不只是当前会话而是整个数据库实例所以代码的健壮性必须放在第一位。我在代码里会对参数个数、参数类型、输入长度都做防御性检查宁可返回NULL也不能让进程崩溃。3. 核心代码实现一个能跑的脱敏UDF3.1 UDF的两个关键接口MySQL的普通标量函数需要实现三个函数名字是函数名、函数名_init、函数名_deinit。拿mask_phone来说就是三件套mask_phone()核心计算逻辑被调用时执行。mask_phone_init()初始化函数在第一次调用前执行用于检查参数、分配内存、设置max_length等。mask_phone_deinit()清理函数在语句执行完毕或连接结束前调用用于释放init()里分配的资源。MySQL执行一条包含UDF的SQL时执行流程是这样的先调用_init进行参数检查和内存准备然后对每一行数据调用主函数计算结果整个语句结束后调用_deinit做清理。理解了这个流程你就知道max_length为什么要在这里设置——它告诉MySQL这个函数返回的字符串最长是多少MySQL据此分配接收缓冲区。如果没有设置或者设置得太小字符串被截断甚至缓冲区溢出都是可能的。3.2 完整代码给了#include stdio.h #include string.h #include stdlib.h #include mysql.h /* 初始化函数检查参数设置返回长度 */ my_bool mask_phone_init(UDF_INIT *initid, UDF_ARGS *args, char *message) { /* 参数个数必须为1且必须是字符串类型 */ if (args-arg_count ! 1 || args-arg_type[0] ! STRING_RESULT) { strcpy(message, mask_phone() requires one string argument); return 1; } /* 手机号11位返回结果最大11字节留一个结束符空间 */ initid-max_length 11; /* 这里没有分配堆内存ptr置空即可 */ initid-ptr NULL; initid-maybe_null 1; initid-const_item 0; return 0; } /* 清理函数有malloc就要在这里free */ void mask_phone_deinit(UDF_INIT *initid) { /* 没有分配内存什么都不用做 */ /* 如果init里用了malloc这里一定要free */ } /* 主函数真正干活的 */ char *mask_phone(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error) { char *input args-args[0]; unsigned long input_len args-lengths[0]; unsigned long out_len; /* 参数为NULL时返回值也设为NULL */ if (input NULL) { *is_null 1; return NULL; } /* 长度不足7位的字符串无法完成中间四位脱敏原样返回 */ if (input_len 7) { *length input_len; return input; } /* 核心脱敏逻辑前3位保留中间4位打码后续保留 */ /* 比如 13812345678 - 138****5678 */ memcpy(result, input, 3); memcpy(result 3, ****, 4); memcpy(result 7, input 7, input_len - 7); out_len 3 4 (input_len - 7); result[out_len] \0; *length out_len; return result; }这里有个细节很多人容易忽视result这个指针指向的是MySQL预先分配好的内存大小就是initid-max_length指定的值。所以如果你的输出长度超过了max_length就存在缓冲区溢出的风险这是UDF最常见的崩溃原因之一。这个例子里手机号都是11位脱敏之后还是11位所以max_length设成11就够了。如果要做身份证号脱敏18位输入中间8位打码返回结果长度还是18位max_length就设成18。规则很简单max_length至少要大于等于最大可能的返回长度。3.3 为什么返回值是指针而不是值细心的读者会发现主函数返回的是char *也就是说返回的是一个指向字符串内存的指针。这个内存可以是args-args[0]即输入参数的原始内存。如果函数只是原样返回或简单变换现有数据可以直接返回输入参数比如上面代码里长度不足7位的分支就是直接返回了input。result也就是MySQL预先分配的缓冲区。大多数需要构造新字符串的场景把结果写到result里再返回result即可。自己malloc出来的内存。但这样就必须在_deinit里free掉否则每个连接都会内存泄漏。能用result解决的事情尽量不要自己malloc不仅麻烦还容易漏。另外一个容易忽略的是*is_null和*error标志。当函数遇到特殊情况要返回NULL就把*is_null设为1同时返回NULLMySQL会把这个结果当作SQL的NULL处理。如果发生了无法恢复的错误把*error设为1MySQL会直接终止这条语句并返回错误这个机制用来防止把错误数据继续往上抛。3.4 UDF_ARGS结构体里的细节UDF_ARGS结构体里值得注意的几个字段arg_count参数个数_init里做个数校验就靠它。arg_type参数类型数组常见取值为STRING_RESULT、INT_RESULT、REAL_RESULT、DECIMAL_RESULT。注意如果调用时传的是字符串类型就是STRING_RESULT如果传的是数字类型可能是INT_RESULT或REAL_RESULT不要搞混。args参数指针数组。对字符串类型args[i]是char *对整数类型args[i]是指向long long的指针需要强制类型转换才能取值。lengths参数长度数组对于字符串类型尤其重要。字符串不一定是以\0结尾的所以必须用lengths[i]来获取实际长度而不是依赖strlen()。这也是UDF编写中的高频坑。4. 编译和部署从源码到能用的.so4.1 Linux下的编译命令代码写好了接下来要编成共享库。Linux下我用的编译命令是gcc -O2 -fPIC -shared -I/usr/include/mysql -o mask_phone.so mask_phone.c逐个解释一下参数-O2开启优化虽然不开也能跑但UDF是高频函数能优化一点是一点。-fPIC生成位置无关代码编译共享库的必要选项不加这个链接阶段会报错。-shared生成动态库而不是可执行文件。-I/usr/include/mysql指定mysql.h头文件所在目录具体路径根据你的MySQL安装位置调整。-o mask_phone.so输出文件名这里建议固定成.so后缀。编译完成后用nm命令检查一下导出符号nm -D mask_phone.so | grep mask_phone正常情况下能看到mask_phone、mask_phone_init、mask_phone_deinit这几个符号。如果看不到说明符号导出有问题MySQL加载时会报function mask_phone ... not found之类的错误。4.2 Windows下的编译方式Windows平台的编译要稍微折腾一点。推荐直接用Visual Studio的命令行工具或者用MinGW的gcc也可以。以VS2017为例在“开发人员命令提示符”里可以这样编cl /O2 /LD mask_phone.c /IC:\Program Files\MySQL\MySQL Server 8.0\include /link /LIBPATH:C:\Program Files\MySQL\MySQL Server 8.0\lib libmysql.lib编译产物是mask_phone.dll。除了编译本身还要确保目标机器上有VS对应的运行库。另外MySQL是64位的你的.dll也必须是64位编译的这个别搞错了。4.3 部署把.so放到该放的地方编译好之后把.so文件复制到plugin_dir目录下。如果你不知道plugin_dir在哪执行SHOW VARIABLES LIKE plugin_dir;复制后可别着急先确认权限。MySQL服务用户通常是mysql要对这个文件有读权限。我见过一次诡异现象文件明明在路径也对权限也是755MySQL还是报打不开后来发现是SELinux拦截了MySQL读取自定义目录下的共享库。如果你的服务器开了SELinux需要执行chcon -t lib_t /usr/lib64/mysql/plugin/mask_phone.so或者临时关闭SELinux验证问题是不是它引起的。这一步很隐蔽排查起来很费时间写出来帮你避坑。4.4 在MySQL里注册函数文件就位后登录MySQL执行注册语句CREATE FUNCTION mask_phone RETURNS STRING SONAME mask_phone.so;语法拆开看CREATE FUNCTION后面的mask_phone是函数名RETURNS STRING声明返回类型SONAME指定共享库的文件名。文件名的路径不用写完整MySQL会自动去plugin_dir下找。注册成功后就正常用了SELECT mask_phone(13812345678);预期输出是138****5678。如果想删除函数DROP FUNCTION mask_phone;DROP FUNCTION并不会删掉.so文件只是把注册信息移除.so仍然在插件目录里躺着。如果文件被占用或者删除失败也基本不影响MySQL运行但下次再CREATE FUNCTION同名函数时会用到已有的文件。5. 把UDF用在实际SQL里效果和场景5.1 在查询中直接使用函数注册好之后它的用法和内置函数完全一样。比如我在测试环境跑一个实际业务查询SELECT id, mask_phone(phone) AS masked_phone, mask_phone(id_card) AS masked_id_card FROM users WHERE created_at DATE_SUB(NOW(), INTERVAL 7 DAY) LIMIT 100;这条SQL会返回脱敏后的手机号和身份证号下游系统拿到数据就能直接用不需要再做额外处理。由于mask_phone对NULL值返回NULL对异常短字符串原样返回所以数据的可用性也有保障。5.2 在WHERE条件里参与过滤UDF不仅可以出现在SELECT列表里还能出现在WHERE里。比如只查询脱敏后仍然包含某几个数字的号码SELECT * FROM users WHERE mask_phone(phone) LIKE 138%;当然实际生产环境不建议在WHERE里用UDF因为这类函数普遍无法走索引MySQL会对每行数据调用一次函数这属于全表扫描的性能开销。但对于一些数据量不大的临时候查偶尔用一次问题不大心里要清楚成本在哪里。5.3 和内置函数组合使用UDF之间、UDF和内置函数之间可以自由组合。写SQL的时候可以这样SELECT CONCAT(手机号: , mask_phone(phone)), IF(mask_phone(phone) IS NULL, 无号码, mask_phone(phone)) FROM users;这种组合能力让UDF用起来很灵活。这个例子虽然简单但思路可以延展你可以写一个mask_phone再写一个mask_id_card、mask_email、mask_bank_card每个都遵循同一套编写规范然后在SQL里按需调用。6. 踩坑实录UDF常见问题与排查技巧6.1 加载失败类报错ERROR 1126 (HY000): Cant open shared library mask_phone.so这个报错信息最容易误导人。文件不存在、文件没有读权限、SELinux拦截、文件格式不对、依赖的库缺失都可能导致这个统一的报错。排查思路可以按顺序来确认plugin_dir路径下文件名完全一致注意大小写Linux是区分大小写的。ls -l看权限确保mysql用户能读。执行ldd mask_phone.so看依赖的共享库是否都存在有没有not found。如果服务器开了SELinux用ausearch -m avc查一下是否有拦截记录。用file mask_phone.so确认编译格式是64位还是32位和MySQL位数不匹配也会报错。6.2 符号找不到类报错ERROR 1125 (HY000): Cant find function mask_phone in library mask_phone.so这个报错说明库加载成功了但里面找不到mask_phone这个符号。常见原因有两个一是编译的时候符号没导出二是函数名拼写不一致。检查方法就是前面提到的nm -D mask_phone.so | grep mask_phone另外要注意C编译器会做名字重整也就是mask_phone可能被编译成_Z10mask_phoneP9UDF_INITP8UDF_ARGS...这种形式MySQL根本识别不了。所以UDF源码一定要用C语言编译器编译或者用C写但必须用extern C包裹导出函数。6.3 函数注册冲突报错ERROR 1125 (HY000): Function mask_phone already exists这个好理解同名函数已经注册过了。用DROP FUNCTION mask_phone;先移除旧注册再重新注册。如果是测试开发阶段反复改代码建议流程是DROP FUNCTION、替换.so文件、再CREATE FUNCTION。记得先DROP再替换文件否则文件被MySQL句柄占用替换可能失败。6.4 运行时崩溃和错误结果这个是最恐怖的。UDF跑在MySQL服务进程里一旦段错误整个MySQL实例都会跟着挂掉。常见原因有这几个缓冲区溢出返回内容超出了max_length指定的长度写坏了内存。排查思路是检查任何可能的长字符串分支字段长度的极端值都要覆盖到。对NULL处理不当参数为NULL时直接解引用args-args[0]轻则返回垃圾数据重则崩溃。每个函数都要有NULL判断。类型误判调用时传的是INT但在代码里按字符串处理本质上是野指针访问。所以_init里一定要检查arg_type。忘记了*length赋值MySQL拿到了未定义的返回长度后果不可预期。主函数每个返回路径都要设置*length。测试阶段建议在一个专门的MySQL实例上做别直接在生产库上试这不是胆小而是UDF崩溃的代价太大。我自己的习惯是先在一个Docker容器里起个独立MySQL测试稳定后再上生产。热搜词里正好有人提到“docker安装mysql失败了”如果你也在用Docker折腾MySQL环境建议先确认容器内的plugin_dir路径和宿主机挂载卷的权限配置UDF文件放进去之前先确认容器内能看到、能读。6.5 二进制日志相关的坑MySQL开启log_bin时CREATE FUNCTION这种语句会被写入二进制日志如果从库或者同步工具无法执行CREATE FUNCTION主从同步就会中断。另外创建UDF需要INSERT权限且如果log_bin_trust_function_creators是0还要求你有SUPER权限。测试环境里遇到ERROR 1418这类报错时检查一下这个变量SHOW VARIABLES LIKE log_bin_trust_function_creators;如果是0可以在会话级别临时设置成1来测试但生产环境要慎重评估安全影响毕竟这会让非SUPER用户也能创建存储函数之类的对象。6.6 常见问题速查表报错现象可能原因解决方案Cant open shared library文件位置/权限/SELinux/位数不匹配按顺序检查plugin_dir、权限、ldd、fileCant find function符号未导出或名字被修改用nm -D检查确保用C编译器或extern C注册后调用崩溃缓冲区溢出、NULL解引用检查max_length、增加NULL判断返回乱码/长度不对没设置*length每个返回路径都补上*length赋值主从同步中断二进制日志里的CREATE FUNCTION被从库拒绝从库手动注册同名函数或调整binlog策略ERROR 1418log_bin_trust_function_creators0且无SUPER权限评估后临时或永久调整变量7. 性能和安全上的几条经验7.1 UDF的性能画像UDF虽然比存储过程快但也不是完全免费。每行数据调用时MySQL需要跨越C和SQL层之间交换数据这个开销虽然不大但在WHERE里用还是会放大很多。我的建议是UDF适合用在不涉及索引的字段转换场景比如脱敏、格式化、编码转换。如果你要在WHERE里对索引字段做复杂变换后再筛选不如在应用层或者ETL阶段提前处理好否则索引失效带来的性能损失比UDF本身的执行代价大得多。7.2 线程安全和全局状态MySQL的UDF调用是并发的你的函数可能被多个线程同时调用。因此不要在函数里使用静态局部变量来缓存数据、不要依赖全局可变状态。如果需要每个连接有独立的上下文可以考虑用initid-ptr指向一个每次_init时malloc出来的结构体在_deinit里释放这样每个连接之间的数据天然隔离。简单函数最好什么全局状态都不用无状态函数最安全。7.3 替代方案怎么选UDF不是唯一的路。如果你的逻辑可以写成SQL表达式组合优先用SQL完成如果逻辑复杂但只在应用层用可以考虑在Java、Python里实现如果逻辑涉及多表事务存储过程是更恰当的工具。UDF适合的是被SQL频繁调用、逻辑相对独立、要求高性能、并且内置函数无法直接满足的场景。想清楚需求边界再动手别为了一点点便利引入不必要的原生代码维护成本。8. 最后的实操建议8.1 版本管理很重要UDF源码虽然不大但一定要纳入版本管理和业务代码一样对待。我在实际项目里吃过亏线上UDF跑了大半年某天业务方要求改脱敏规则结果翻遍了服务器也找不到源码只能对着二进制反推逻辑极其痛苦。后来我规定所有UDF源码统一放在一个代码仓库的udf/目录下编译产物可以不用入库但源码必须入库部署脚本也一并管起来。8.2 测试用例覆盖边界UDF上线前写个简单的测试SQL把边界情况都过一遍SELECT mask_phone(13812345678) AS normal_case, mask_phone(NULL) AS null_case, mask_phone(123456) AS too_short_case, mask_phone(123456789012345678) AS long_case;这四条SQL分别对应正常值、NULL值、过短字符串、超长字符串每个返回路径都要验证。特别是NULL情况如果忘了处理一个NULL姓名字段就能让整个查询报错。这类基础测试谁都能写但实际跑到这里的人不多写出来提醒一下。8.3 多平台编译的一次教训之前开发机是Windows生产环境是Linux我图省事直接在Windows上编好.dll传上去结果MySQL加载报错。后来才意识到共享库格式完全不一样Windows上编出来的.dll在Linux上根本没戏。所以现在我的流程固定成源码在Git仓库编译产物在目标平台现场编译交叉编译的方案除非你很精通工具链否则不建议碰那块水太深。8.4 写完这个函数之后的延展mask_phone只是最入门的一个例子。同样的框架可以扩展出一整套脱敏函数库比如mask_id_card、mask_email、mask_bank_card甚至可以做自定义哈希函数、业务编号生成器、字符串相似度函数。核心脚手架都是一样的换的主要是主函数里的业务逻辑。从写第一行C代码到函数上线我花了大概一个下午大部分时间都耗在编译环境配置和调试上真正写代码的时间反而很少。希望这个例子能让你少走几步路快速跨过UDF这道门槛。