关于Mysql中UDF函数的思考（一）

一点背景

从大学二年纪接触编程，几乎我阅读过的所有的编程语言教材都会有那么一章专门讲述数据库编程，而讲述的内容都无非是介绍某个数据库历史，对应的安装过程，最后才会讲解一点SQL语句，像这样的教材我个人认为是完全充字数（特别是嵌入式类别的书籍），这个也是国人出书的一大特色。所以到头来作为研发人员所知道的数据库工程技术也就是创建表删除表，对表的增删改查。当然有希望对数据库理解精进的时候，会去查看一些专门的数据库理论的书籍，但是一看到理论中模式和范式的数学语义介绍，必然会望而却步。我也是这样的，让我表达范式介绍，我的确表达不出，但是给我表格组成，我能说明达到哪种范式标准，我个人理解作为应用工程师，做到这一步就基本达标了。在最近的项目中，由于需要使用Mysql的UDF（user defined function），这个特性从未使用过，而且个人觉得这个特性以后应该会经常使用，所以写下博文，记录和分享这个特性的用法。

开发环境

操作系统:ubuntu 12.04 Desktop

编译器：gcc 4.6.3

数据库：mysql server 5.5

UDF介绍

官方介绍请点击http://dev.mysql.com/doc/refman/5.5/en/adding-functions.html

UDF是mysql的一个拓展接口，UDF（Userdefined function）可翻译为用户自定义函数，这个是用来拓展Mysql的技术手段。

示例业务范围

假设存在这样的业务流程，假设某个C/S架构程序如下（不纠结与细节，了解即可）：

客户端<----->业务服务器<----->缓存<----->Mysql数据库

正常的升级过程：

1.发布新的客户端程序版本

2.版本记录写入Mysql

3.版本记录同步到缓存服务器

4.客户端连接业务服务器

5.业务服务器进行版本升级运算，在运算过程中会向缓存服务器请求最新的版本记录。

6.回复客户端是否存在升级。

需要讨论的过程就是在缓存服务器和Mysql数据库的数据同步过程。

在不知道有UDF特性的时候，我们的设计是可以设计一个监控程序，循环监控Mysql的记录是否发生改变，如果发生改变就将缓存发送新的记录，这样的一个监控程序，必然是死循环的方式一直检查记录，这种方式想乃笨重不堪，自然不是上上之策，所以考虑使用其他方式。

对于这种有触发条件的需求，数据库熟手都知道这种记录方式可以使用触发器，但是如何通过触发器将更新的记录同步到缓存(假定缓存采用的是redis)?这个就需要借助UDF特性。

如何使用？

在Mysql的官方文档中有说明如何给Mysql添加新的函数。

添加的函数可分为3种：

1.自行编写函数的源码，将源码编译成动态库，然后使用Mysql的CREATE FUNCTION语句来将函数添加进Mysql。

2.在源码等级上加入自行编写的函数，这样编译之后，新加入的函数就和Native函数一样，永久使用。

3.通过SQL语句脚本来完成函数的功能。

就这3中方式，第三种明显不足以完成我们所需要的业务工作，因为SQL语句不存在连接缓存的功能。第二种方法看似美好，但是对于需要更换缓存方法（从redis更换成memcached），重新编译数据库源码，这种不符合工程化商务化。所以采用第一种方法自行编写函数代码，动态导入函数到mysql之中。

代码如下：文件名test_add.cpp

#include <mysql.h>extern "C" long long testadd(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error){int a = *((long long *)args->args[0]);int b = *((long long *)args->args[1]);return a + b;}extern "C" my_bool testadd_init(UDF_INIT *initid, UDF_ARGS *args, char *message){return 0;}

这个文件，应该是最小的UDF组成单元了，我们需要测试的就是在Mysql中使用testadd这个函数。

如何将testadd加入mysql之中？

1.生成动态库。

luoqiya@ubuntu:$ g++-shared -fPIC -I /usr/include/mysql-o test_add.sotest_add.cpp

注意:在这个步骤之前确保安装了libmysqlclient-dev.使用gcc生成的共享库文件同样可以使用，这里使用g++是为了体现出extern"C"的必要性。

2.将test_add.so加入到Mysql中。

luoqiya@ubuntu:$ cp test_add.so /usr/lib/mysql/plugin

注意:记得使用管理员权限。

3.在Mysql中添加该函数。

以root进入Mysql，执行命令

mysql>CREATE FUNCTION testadd RETURNS INTEGER SONAME 'test_add.so';

添加成功则会显示：Query OK等字样。添加成功之后函数即可使用。

测试函数是否可以使用可以执行命令

mysql>select testadd(1,2);

会出现结果如下图所示：

如此过程，说明mysql已经可以使用我们自定义的函数，结合上面所说的业务流程，创建对应表格的

INSERT，DELETE  ，UPDATE触发器在触发器中调用自定义函数将数据传送到缓存中即可满足业务要求。

一点思考

按照上面的步骤创建了自定义函数，已经可以使用UDF函数了，但是这几个步骤可以分析分析，为什么Mysql需要这样设置，这个对于以后设计具备拓展性的程序颇有帮助。如果读者对Mysql的UDF特性还有特殊的功能需求，请参照官方手册，没有什么文章能比手册更加详细。

第一点，添加的testadd函数需要采用共享库的方式，这个不难理解，很多模块化的程序都使用dlopen函数族来进行模块的动态加载，比如Apache Web服务器就是用这种方式来动态加载模块，这个是创造可拓展程序的唯一系统级别方案（如果存在其他方式，请告知我，我立即修改）。

第二点，UDF函数对于Mysql来说的表现形式是什么样的？或者换个问法，UDF函数怎样存在与Mysql之中？

这个可以通过查询Msql下的database mysql中的func表格获得。

查询结果如下图：

func表格记录了所有UDF函数的信息，表格中ret代表的是返回值类型，之所以这里记录的是2，是因为我们返回的是整型数据，可以在mysql的源码中sql目录下的mysql_com.h找到类型的枚举定义。

enum Item_result {STRING_RESULT=0, REAL_RESULT, INT_RESULT, ROW_RESULT,                  DECIMAL_RESULT};

第三点，动态加载的安全性问题，在加载的.so文件中的函数存在非法操作(比如free2次指针)会产生什么影响？在使用dlopen函数族时，可以通过dlsym来获取函数指针，调用.so文件中的方法。这些调用过程是发生在同一个进程之中，所以在dlsym的函数指针调用出现非法操作时，原进程被kill然后核心转储。在Mysql之中导入的UDF函数面对同样的问题，是不是Mysql有方法保证Mysql的进程不被kill掉？可以做测试，将上面的test_add.cpp文件改为

#include <mysql.h>#include <malloc.h>extern "C" long long testadd(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error){int a = *((long long *)args->args[0]);int b = *((long long *)args->args[1]);int *p = (int *)malloc(sizeof(int));free(p);free(p);return a + b;}extern "C" my_bool testadd_init(UDF_INIT *initid, UDF_ARGS *args, char *message){return 0;}

按照上面所述步骤，添加函数至数据库，然后调用该函数，结果如下图：

可以发现和数据库的连接断了，然后到/var/log/mysql/error.log
检查Mysql的错误日志(采用apt-get install安装的方式，my.cnf下没有设置query的路径)，可以发现Mysql
是挂掉，然后又重新启动了（守护进程的存在）。
如此可见，Mysql对于动态加入的.so并没有做特殊的保护方式，就和dlopen函数族所提供的一样。之所以不设计特殊
的保护方式，就我的理解而言，要使用外部的.so那么.so的提供者就需要保证安全性，就算.so出现了问题，Mysql

的守护进程也保证了服务快速恢复，对业务影响并不是很大。而且从Mysql在对接口设计上，也有影射需要注意安全性，因为在创建xxx()函数时，至少提供xxx_init()或者xxx_deinit()函数只中一个（Mysql官网的5.1版本中文文档只是说明xxx_init()和xxx_deinit()为可选，而英文版则强调至少提供一个，这点看官注意），这两个函数分别对应与调用xxx函数之前的初始化操作和调用完毕xxx函数之后的清理工作。

更加详细的接口介绍，见下一篇。

未完待续$: