MySQL UDF这个坑，最近我又踩了一遍，而且是在测绘数据落库这种不太容易被人注意的场景里。当时有一批GPS点位要和某个固定坐标做距离筛选，线上库是MySQL 5.7.44，数据量上了十万行之后，SQL里写经度纬度差的公式怎么都别扭，后来干脆自己写了一个UDF（用户自定义函数），编译成.so塞进MySQL，一条SELECT就搞定，性能还比原来快了将近一个量级。这篇就把完整的开发过程、编译部署、SQL注册和几个让我折腾到半夜的坑全部记录下来，给以后写UDF的人留一份能直接参考的作业。
UDF这个东西，简单说就是MySQL留出来的一个扩展口子，允许你用C或者C++写一个函数，编译成动态库，然后像用内置函数sum()、now()一样在SQL里直接调用。它和存储过程的区别在于，UDF是直接跑在mysqld进程里的原生代码，被调用时不需要像存储过程那样在SQL引擎层反复解析、做安全上下文切换，所以在做行级计算、表达式嵌入这类场景时，性能和灵活性都远高于存储过程。适合看的读者，是有一定C语言基础、正在被MySQL内置函数边界卡住的DBA、后端开发或者大数据工程同学。
1. 项目背景：一个不得不写UDF的测绘需求
1.1 需求来源与问题定位
需求本身不复杂。我们有一张POI表，结构大概就是id、name、latitude、longitude这四列，总行数在五十万左右。业务方要求是：给定一个中心点，把周围十公里以内的POI全部捞出来，并且按距离排序。这类需求在LBS类业务里非常常见，说白了就是“附近的人”“门店配送范围”那一套逻辑。
按理说这是个标准的地理围栏查询，不值得我去写UDF。问题出在当时这个项目运行在MySQL 5.7上，而5.7里内置的地理空间函数只有一堆空间关系判断，并没有一个开箱即用的“计算两点球面距离并返回公里数”的函数。虽然8.0里有ST_Distance_Sphere()，但线上库的版本不是说升就能升的。何况就算有内置函数，我们的业务后来还加了“高速优先”“直线距离折减系数”这类加权逻辑，内置函数也满足不了。
于是摆在面前的方案其实就四个：第一，在应用层用Java/PHP算好距离再做筛选，但几十万行数据全量拉回应用层，内存和网络开销都受不了；第二，在SQL里写一大串三角函数公式，每次查询都去解析那坨复杂表达式，维护成本极高；第三，用存储过程，但它同样要经过SQL引擎的解析执行，而且很难在SELECT表达式中灵活嵌入；第四，就是写一个UDF，把距离算法沉淀成原生代码，直接挂在MySQL里。我当时几乎是没有任何犹豫就选了第四条路。
1.2 为什么不用存储过程、临时表或内置函数
这里需要把“为什么是UDF”这个决策讲透，因为很多人第一反应是“存储过程不是也能算距离吗”。先说存储过程的问题：它本质上是一段SQL脚本的集合，每次调用都要经过语法解析、优化、权限检查这一整套流程，你在里面做循环、做计算，性能完全没法和编译好的原生代码比。更关键的是，存储过程不好放进SELECT的字段列表里，比如你想把算出来的距离作为结果集的一个列返回，用存储过程去做这件事就非常绕。
临时表方案也试过，先把全景数据导入临时表，再在临时表上用SQL写Haversine公式计算距离。看起来可行，但问题在于：一是五十万行数据的临时表创建、索引维护本身就耗时；二是那段SQL公式极其冗长，嵌套五六层三角函数，一旦字段名写错或者参数类型不匹配，排查起来非常痛苦；三是每次计算都要把cos、sin重复算一遍，重复分配临时结果集，性能瓶颈非常明显。
至于内置函数，当时5.7确实没有合适的。MySQL的GIS扩展有很多空间对象函数，但那些是拿来做包含、相交、重叠判断的，要么返回二值结果，要么返回的空间对象枚举，并不是直接给你一个公里数。而8.0虽然有了ST_Distance_Sphere，可我们的技术栈和运维体系还锁在5.7上，为了一个距离计算去升级数据库大版本，风险收益完全不成比例。所以最终选择很清晰：在这种“需要在表达式层面做频繁数学计算、且内置函数覆盖不到”的空白地带，UDF就是最合理的补充手段。
2. UDF开发的底层协议与准备工作
2.1 一个UDF必须实现哪三个函数
写UDF之前，先把它底层的调用约定摸清楚，不然写出来的.so就算编译成功，加载进MySQL也大概率崩。任何一个普通的非聚合UDF，在代码里最少要实现三个函数，名字要跟你要注册的SQL函数名对应上。
假设我们要注册的SQL函数叫distance_km，那么C代码里就必须有：
my_bool distance_km_init(UDF_INIT *initid, UDF_ARGS *args, char *message)double distance_km(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)void distance_km_deinit(UDF_INIT *initid)
init函数是初始化钩子。MySQL在第一次执行SQL时会对每一条要调UDF的表达式调用一次init，你可以在这里检查参数个数、参数类型，也可以给initid分配一些跨多次调用复用的一内存，比如预计算好的常量、缓存表指针，这些内存要在对应的deinit里释放。如果init返回1，说明初始化失败，MySQL会抛弃这次执行，并且把message里写的内容作为报错抛给客户端。
主函数才是真正干活的。因为我们要返回的是一个浮点数，所以主函数返回类型写成double。注意这里有个很容易被忽视的细节：is_null和error是两个指针，当你发现参数有NULL、或者计算过程中遇到异常，不要直接返回一个魔法值，更应该做的是把*is_null=1或者*error=1置位，然后返回一个占位值，否则MySQL会把这个返回值当成正常结果，造成数据脏了还不报错。
deinit函数对应释放init里分配的资源。很多基础教程根本不写deinit，如果你的init函数里没申请任何内存，那确实可以省略，但MySQL文档明确要求UDF应该提供这个函数，我个人的习惯是哪怕空实现也要写上，一是规范，二是将来扩展时防止遗忘。
2.2 参数结构UDF_ARGS到底长什么样
UDF_ARGS这个结构体是所有UDF参数处理的核心，一定要吃透。它在MySQL头文件里大概是这样的：有unsigned int arg_count表示参数个数，有enum Item_result *arg_type数组表示每个参数的类型，有char **args数组存参数的值，还有unsigned long *lengths数组存每个参数的长度。
这里有个坑，我必须单独拎出来讲：args这个字段声明的类型是char **，看起来像字符串数组，但实际上它里面装的是什么，完全由对应的arg_type决定。如果arg_type[i]是STRING_RESULT，那args[i]确实是一个以\0结尾的字符串指针；但如果arg_type[i]是REAL_RESULT，那args[i]其实是指向一个double类型内存的指针，你要把它强转成double *再取值；如果是INT_RESULT，则要转成long long *。
还有一个更容易踩的坑：MySQL里SQL字面量写39.9042这种小数，实际传给UDF的参数类型很可能不是REAL_RESULT，而是DECIMAL_RESULT或STRING_RESULT。因为MySQL的表达式优化器对无后缀的小数字面量，默认会建一个DECIMALItem，而DECIMAL参数在UDF里又是以字符串形式传给args[i]的。所以如果一个UDF在init阶段强硬地要求所有参数必须是REAL_RESULT，你拿到的可能全是初始化失败。最稳妥的做法，是在主函数里根据arg_type分别转double：实数类型就强转指针，字符串或DECIMAL类型就走atof。后面我会在源码里给你看这个helper怎么写。
2.3 编译环境搭建：mysql_config和gcc
环境我使用的是CentOS 7 x86_64，MySQL版本5.7.44社区版，编译器是系统自带的gcc的c99模式。写UDF不需要你去下载整个MySQL源码包，但你必须安装对应的开发包，目的是拿到两个东西：MySQL的头文件，以及mysql_config这个工具。
头文件路径一般是/usr/include/mysql/，里面至少有mysql.h、my_global.h、my_sys.h这几个文件。实际上我们可以用mysql_config --cflags把include路径打出来，用mysql_config --plugindir拿到当前实例的插件目录，非常方便。如果你的系统里没有mysql_config，多半是缺mysql-community-devel这个包，用yum装一下即可；Debian系则搜索libmysqlclient-dev。有一点要提醒：开发包的版本最好和线上mysqld版本一致，跨大版本编译的UDF经常出现符号不兼容，我在后面故障章节会专门讲。
编译本身也不需要链接libmysqlclient，因为UDF是运行在mysqld进程内部的，需要的MySQL内部符号在加载时由mysqld自己导出。所以最精简的编译命令就是：
gcc -shared -fPIC -O2 -o distance_km.so distance_km.c -I/usr/include/mysql -lm但还是建议在Makefile里顺手调一下mysql_config --cflags，万一以后开发包路径变了，不会编译得一头雾水。
3. 核心开发：实现经纬度距离计算函数
3.1 Haversine公式与算法选择
说回这个距离函数本身。计算两个经纬度坐标之间的球面距离，业界最常用的就是Haversine公式。它的核心思想是假设地球是一个球体，然后根据两点的经纬度差，求对应球面两点间的“大圆距离”。数学公式长这样：
a = sin²(Δlat/2) + cos(lat1)·cos(lat2)·sin²(Δlng/2) c = 2·atan2(√a, √(1−a)) d = R·c其中R是地球平均半径，我用6371公里这个值。这个公式对千米级别的距离精度已经足够，误差通常控制在0.5%以内，而如果业务距离本身就只有十公里范围，那这点误差完全可以接受。
为什么不直接选更复杂的Vincenty公式？因为那套东西迭代计算量大，每行数据都要跑好几轮循环，在UDF这种被频繁调用的场景里不划算。而那些更精确的椭球模型算法，在这个业务里属于杀鸡用牛刀。选型原则很简单：精度够用，计算量最小，代码可读性高。
3.2 完整源码与逐段解析
下面给出我实际用的完整源码，注释尽量详细：
#include <my_global.h> #include <my_sys.h> #include <mysql.h> #include <string.h> #include <math.h> static double get_double_arg(UDF_ARGS *args, int i) { if (args->args[i] == NULL) return 0.0; switch (args->arg_type[i]) { case REAL_RESULT: return *(double *)args->args[i]; case INT_RESULT: return (double)(*(long long *)args->args[i]); case STRING_RESULT: case DECIMAL_RESULT: return atof(args->args[i]); default: return 0.0; } } my_bool distance_km_init(UDF_INIT *initid, UDF_ARGS *args, char *message) { if (args->arg_count != 4) { strcpy(message, "distance_km() requires exactly 4 args: lat1,lng1,lat2,lng2"); return 1; } for (int i = 0; i < 4; i++) { if (args->arg_type[i] != REAL_RESULT && args->arg_type[i] != DECIMAL_RESULT && args->arg_type[i] != STRING_RESULT) { strcpy(message, "distance_km() args must be numeric or string-numeric"); return 1; } } initid->max_length = 20; initid->maybe_null = 1; return 0; } void distance_km_deinit(UDF_INIT *initid) { /* no extra memory allocated, nothing to do */ } double distance_km(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error) { double lat1 = get_double_arg(args, 0); double lng1 = get_double_arg(args, 1); double lat2 = get_double_arg(args, 2); double lng2 = get_double_arg(args, 3); if (lat1 == 0.0 && lng1 == 0.0) { *is_null = 1; return 0.0; } if (lat2 == 0.0 && lng2 == 0.0) { *is_null = 1; return 0.0; } double dlat = (lat2 - lat1) * M_PI / 180.0; double dlng = (lng2 - lng1) * M_PI / 180.0; double rlat1 = lat1 * M_PI / 180.0; double rlat2 = lat2 * M_PI / 180.0; double a = sin(dlat / 2.0) * sin(dlat / 2.0) + cos(rlat1) * cos(rlat2) * sin(dlng / 2.0) * sin(dlng / 2.0); double c = 2.0 * atan2(sqrt(a), sqrt(1.0 - a)); return 6371.0 * c; }这个函数的主逻辑其实很短，真正的重点在get_double_arg这个helper里。我见过很多网上教程，主函数一上来就写double lat1 = *(double*)args->args[0]，看起来简洁，但实际生产环境里根本扛不住。比如客户端传过来的是'39.9042'这个带引号的字符串，或者MySQL内部按DECIMAL存参数，args->args[0]对应的就是字符串内存，直接强转double指针去取值，轻则拿到一个无意义数字，重则触发越界读。所以按类型分流处理是写UDF的基本素养。
3.3 边界条件和内存安全处理
写UDF最大的风险是它运行在mysqld进程内，任何一次非法内存操作都可能直接让整个数据库实例崩溃。这点和写普通后台服务完全不一样，普通服务崩了进程重启拉倒，mysqld崩了那是在生产事故。
我处理边界条件的方法是：先判空值。入口处如果传入坐标是NULL，或者经纬度全是0，直接置*is_null=1，让MySQL内部把它转成NULL字段。这里特别提一下为什么经纬度全是0也当空处理：实际轨迹数据里，很多设备定位失败时上报的就是(0, 0)，这个坐标落在几内亚湾海域，全是0的记录几乎可以断定是脏数据。
内存安全方面，主函数里没有动态申请内存，只在栈上做计算，所以目前没有释放压力。但如果你的UDF要在init里用malloc申请缓存，然后通过initid->ptr保存指针，一定不要忘了在deinit里free，否则就是一次隐性的内存泄漏。另外，所有对args->args[i]的访问，都要先确认它不是NULL，空值参数在UDF里就表现为NULL指针，直接传给atof或强转会有不可预测的后果。
4. 编译、部署与SQL侧验证
4.1 Makefile与编译细节
编译这一步，我用一个非常简单的Makefile：
MYSQL_CFLAGS := $(shell mysql_config --cflags) PLUGIN_DIR := $(shell mysql_config --plugindir) all: gcc -shared -fPIC -O2 -o distance_km.so distance_km.c $(MYSQL_CFLAGS) -lm deploy: all cp distance_km.so $(PLUGIN_DIR)/ clean: rm -f distance_km.so这里有几个值得说的编译细节。-fPIC是必须的，因为.so要求位置无关代码；-shared表示生成共享库；-O2以上建议打开，UDF会被高频调用，这点优化很有价值。你还必须用mysql_config --cflags而不是瞎写-I/usr/include/mysql，因为不同发行版本的头文件路径差异很大，甚至5.7和8.0的头文件组织都不一样。
另一个是我自己踩过的坑：如果你习惯用g++而不是gcc编译C代码，一定要在所有函数外面包一层extern "C"{}，否则C++编译器会对函数名做name mangling，生成出来的符号变成_Z13distance_kmP8st_udf_initP9st_udf_argsPcS2_这种鬼样子，MySQL加载时按distance_km这个名字去找符号，怎么都找不到。我最早一遍就是直接用g++编，注册函数时报Symbol not found，排查了半天才意识到是符号表被改写了。
部署完成后，记得验证一下.so文件属主和权限。mysqld进程通常以mysql用户运行，如果.so文件root权限过强或者所在目录不可读，加载就会失败，这个放到下一章的故障表里细说。
4.2 部署.so文件和系统权限检查
编译生成了distance_km.so，部署其实就两步：把它拷到plugin_dir，然后在MySQL里注册。先查plugin_dir：
SHOW VARIABLES LIKE 'plugin_dir';我这边输出是/usr/lib64/mysql/plugin。拷贝时建议用cp，拷贝完还要检查一下mysqld进程能不能读：
cp distance_km.so /usr/lib64/mysql/plugin/ chmod 755 /usr/lib64/mysql/plugin/distance_km.so chown mysql:mysql /usr/lib64/mysql/plugin/distance_km.so这里特别说一句SELinux的坑。CentOS默认开启SELinux，如果你把所有东西都拷进去了，却在MySQL里一直报权限拒绝，那多半不是Unix权限问题，而是SELinux安全上下文不对。验证方法是执行ls -Z /usr/lib64/mysql/plugin/distance_km.so，看它是不是lib_t或类似MySQL插件应有的上下文标签，如果不是，用restorecon恢复一下：
restorecon -v /usr/lib64/mysql/plugin/distance_km.so这一条局部的SELinux命令能救回不少人的命，我身边至少有两个人卡在这里超过半天。
4.3 CREATE FUNCTION注册与业务验证
注册UDF的SQL非常简单，但语法细节还是容易错：
CREATE FUNCTION distance_km RETURNS REAL SONAME 'distance_km.so';注意RETURNS REAL是声明返回类型，REAL对应C代码里的double；如果把函数的返回类型写成了INTEGER，MySQL会调用一个返回long long的主函数签名，你提供的是返回double的函数，行为会完全不可预测。所以代码和声明必须一一对应。
注册成功后，立刻做一次冒烟测试：
SELECT distance_km(39.9042, 116.4074, 31.2304, 121.4737) AS dist_km;我这边算出来是1068.79左右，这个结果用在线经纬度距离工具核对过，误差在合理范围内。然后就可以放到业务SQL里了：
SELECT id, name, distance_km(latitude, longitude, 39.9042, 116.4074) AS dist FROM poi WHERE latitude BETWEEN 39.5 AND 40.3 AND longitude BETWEEN 116.0 AND 116.8 HAVING dist < 10 ORDER BY dist;这里一个很重要的性能技巧：UDF里计算的是精确距离，但你千万不要对全表五十万行直接就跑WHERE distance_km(...) < 10，那等于把每条记录都算一遍。标准做法是先利用经纬度的线性范围，也就是所谓的bounding box，把这个粗略筛选作为索引友好的过滤条件，让MySQL用上经纬度字段上的索引，先把集合缩小到几百几千行，再对剩下的行用UDF算精确距离，最后用HAVING过滤。我在源码注释里就强调了这一点，实践证明这条优化能让整个查询从秒级降到毫秒级。
5. 常见故障、性能与使用边界
5.1 我在生产环境遇到过的几个坑
UDF开发最容易让人灰心的阶段，就是编译和加载，因为报错信息又短又不直观。我把遇到过的坑整理成一张速查表，方便你对照排查：
| 现象 | 大概率原因 | 解决办法 |
|---|---|---|
CREATE FUNCTION报Can't open shared library，errno 13 | 权限不足或SELinux拦截 | 检查.so属主和chmod 755；用restorecon重置SELinux标签 |
CREATE FUNCTION报Symbol not found | 用g++编译导致C++弱符号；或函数名拼写不一致 | 改用gcc编译，或用extern "C"包裹；核对SQL函数名与C函数名完全一致 |
加载成功但一调用就报Illegal mix of collations | UDF声明返回STRING但实际返回二进制数据 | 检查RETURNS类型，字符串UDF一般加CHAR/NVARCHAR声明 |
| 调用后结果异常大 | 对DECIMAL/STRING类型的参数直接强转double指针 | 使用文中get_double_arg的分类型转换逻辑 |
| 升级MySQL大版本后UDF失效 | 5.7编译的.so放到8.0环境，内部符号或ABI变化 | 在目标版本环境上重新编译、重新部署 |
| 结果偶尔是NULL | 经纬度为(0,0)或NULL参数未处理 | init里设置maybe_null=1，主函数置*is_null=1 |
这里有个经验：MySQL的报错信息很多时候不会给出具体原因，比如权限失败它不会说“SELinux拒绝了”，你能做的就是按照“是不是没拷对目录→是不是属主不对→是不是x权限缺失→是不是SELinux上下文不对”的顺序逐层排查。如果你在云服务器上开了安全软件，还要看一眼有没有被库文件扫描功能误删。
5.2 线程安全与并发下的注意事项
UDF在MySQL里是并发执行的，同一个UDF在同一时刻可能被多个线程、多个连接调用。这里有一个很多新手都搞错的概念：UDF_INIT *initid并不是全局共享的，MySQL会对每一个连接中的每一次表达式评估单独调用一次init，所以initid指向的内存天然是线程隔离的，你不用加锁去保护它。
但如果你是c里定义了一个全局静态变量，想在函数调用间共享缓存或者做计数器，那就麻烦了。比如static double last_lat;这种，多个线程同时读写会存在数据竞争，轻则算错，重则Crash。所以我的原则是：UDF内部完全不使用可写的全局状态，所有可变量都放在栈上，需要缓存就挂到initid->ptr上，它是每连接私有的，安全且高效。
内存释放顺序也要注意。如果你在init里用malloc给initid->ptr分配了一块缓存，而deinit里又没有释放，每次连接新建都会泄漏一块。注意不是每行调用都泄漏，是每个连接周期泄漏一次，短连接业务大量建立连接时会持续涨内存。这个很难靠监控发现，但长时间运行后会拖垮mysqld。
5.3 什么时候不该写UDF
写UDF感觉很酷，但它不是万能的，我也见过不少被滥用UDF翻车的案例，这里必须泼一点冷水。第一种不该写的情况是：能用内置函数解决，就别写。比如MySQL 8.0已经有ST_Distance_Sphere，如果你的库可以升到8.0，那就不需要再来一个自定义UDF。第二种不该写的场景：函数体里做了非常重的IO操作，比如在UDF里连接外部服务或者读文件，这是绝对禁忌。因为UDF跑在mysqld进程里，任何慢IO都会直接卡住数据库线程，一次意外阻塞可能拖垮整个实例。第三种：如果你只是想在应用层算一个近似的距离，很多地理库已经足够，没必要把这种逻辑下沉到数据库层，要知道UDF的维护成本比普通代码高，因为它跟数据库版本、编译器、系统环境全部耦合。
还有一个容易被忽略的点：尽量不要用UDF来封装加密算法或者复杂的二进制协议解析，这类东西要么有现成的函数，要么应该放在应用层。MySQL的UDF接口本身是可返回二进制字符串的，但你要自己处理length参数和空字符问题，远比应用层处理复杂，而且一旦写错就是安全漏洞。
写在最后
这次写UDF最大的收获，不是学会了一个Haversine公式，而是对整个MySQL扩展机制有了更直观的理解。很多人在数据库这条路上习惯“能用就行”，遇到内置函数覆盖不到的需求就上临时表、上存储过程、甚至上外挂中间件，其实回头看一眼MySQL的UDF协议，它可能才是你这个场景里最精准、最省事的解法。我真正的体会是：UDF的价值不仅仅在于性能，更在于它把计算逻辑沉淀成数据库原生能力，让上层SQL变得简洁可控。
最后再分享一个小技巧：如果你以后要写多个UDF，建议每一个UDF都单独编译成独立的.so，不要全部塞进一个库。MySQL加载.so的时候是一次性把所有符号都加载进来的，混合在一起虽然少几个文件，但一旦某一个函数初始化崩溃，整个库和里面驻留的所有UDF都会受影响，隔离才是生产环境的第一原则。
我是从踩坑里学到这些的，希望你读完能少踩几个。
