什么是用户定义函数(UDF, User-Defined Functions)

在MySQL中，可以通过UDF扩充MySQL的功能，加入一个新的SQL函数类似于内置的函数（如，ABS() or CONCAT()等。UDF使用C/C++实现， 编译成动态库文件（Linux对应.so文件），可以使用 CREATE FUNCTION动态加载到 mysqld服务进程里，使用DROP FUNCTION从mysqld 服务进程里移除。本文在MySQL 8.0上首先对MySQL UDF的接口进行了介绍，然后给出了一个简单的例子。 通过使用 UDF：

  • 可以返回 string, integer 或 real 类型的值或作为函数参数
  • 能够定义简单函数或聚集函数（aggregate function），本文只讲解简单函数，聚集函数请参考MySQL用户手册

如何实现MySQL UDF

MySQL UDF必须使用C/C++实现，同时要求操作系统必须支持动态装载，如果使用了mysqld中已经存在的符号， 那么链接动态库的时候必须得使用链接选项 -rdynamic。

UDF接口

为了定义UDF，需要为每个UDF生成对应C/C++函数，为了下文描述方便，我们用“xxx”表示函数名，用大写的XXX()表示一个SQL 函数调用，用小写的xxx()表示一个C/C++函数。下面是实现一个 SQL 函数XXX()所需要定义的C/C++函数。

xxx()

主函数，在SQL调用函数XXX()时最终会调用到这里，SQL的数据类型和C/C++的数据类型对应关系如下：

SQL类型C/C++ 类型
STRINGchar *
INTEGERlong long
REALdouble

这些数据类型用于函数的返回值和函数参数。

函数定义如下：

  • 对于SQL函数的返回值是STRING的 (这个函数原型同样适用于SQL函数返回类型是DECIMAL)
  1. char * xxx(UDF_INIT *initid, UDF_ARGS *args,
  2.    char *result, unsigned long *length,
  3.    char *is_null, char *error);
  • 对于返回值是INTEGER的
  1. long long xxx(UDF_INIT *initid, UDF_ARGS *args,
  2.    char *is_null, char *error);
  • 对于返回值是REAL的
  1. double xxx(UDF_INIT *initid, UDF_ARGS *args,
  2.    char *is_null, char *error);

xxx_init()

xxx()函数的初始化函数，这个函数的作用包括：

  • 检查传入XXX()函数的参数个数
  • 检验传入XXX()的参数的数据类型，而且它还可以让MySQL将传入XXX()的参数转成xxx()需要的数据类型
  • 分配xxx()函数需要的内存
  • 指定返回值的最大长度
  • 指定返回值是REAL的函数的返回值的精度
  • 指定返回值是不是NULL

函数原型如下：

  1. bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message);

xxx_deinit()

xxx()的析构函数，用于释放初始化函数分配的内存或做其它清理工作，这个函数是可选的。 函数原型如下：

  1. void xxx_deinit(UDF_INIT *initid);

UDF执行流程

当在一个SQL语句中调用XXX()时，MySQL首先调用xxx_init()函数做必要的初始化工作，比如：参数检查、内存分配等。 如果xxx_init()返回错误，则主函数xxx()和析构函数xxx_deinit()不会被调用，整个语句会报错退出。如果xxx_init() 执行成功MySQL会调用主函数xxx()，通常情况下会每行数据调用一次，依赖于XXX()在SQL语句中的位置。当所有的主函数xxx() 都调用完成后，MySQL会调用对应的析构函数xxx_deinit()做必要的清理工作。

注意，以上所有的函数都要求是线程安全的。同时如果是用C++实现的，那么在定义的函数开头必须要加上 extern “C”，以便 MySQL可以找到相应的符号。

UDF实现相关数据结构说明

  1. UDF_INIT

    是参数initid的类型，该参数是3个函数都需要的，可以在xxx_init函数中初始化。该结构的主要成员如下：

    • bool maybe_null

    如果xxx()可以返回NULL，xxx_init函数需要把它设置成true，如果函数参数有maybe_null是true的，该值的默认值就是true。

    • unsigned int max_length

    返回值的最大长度。对于不同返回类型该值的默认值不同，对于STRING，默认值和和最长的函数参数相等。对于INTEGER， 默认值是21。如果是BLOB类型的，可以将它设置成65KB或16MB。

    • char *ptr

    一个透明的指针，UDF的实现可以自己根据需要使用。该指针一般在xxx_init()里分配内存，在xxx_deinit()里进行释放。

    • bool const_item

    如果xxx()函数总是返回相同的值，xxx_init()中可以把该值设置成true。

  2. UDF_ARGS

    是参数args是数据类型，主要成员如下：

    • unsigned int arg_count

    SQL函数参数的个数，也是下面其他成员的数组长度。可以在xxx_init()函数里检查是否与预期一致，如：

    1. if (args->arg_count != 2)
    2. {
    3.   strcpy(message, "XXX() requires two arguments");
    4.   return 1;
    5. }
    • enum Item_result *arg_type

    是一个定义了每个参数类型的数组，每个元素可能的取值：TRING_RESULT, INT_RESULT, REAL_RESULT, 和DECIMAL_RESULT。 也可以通过它在xxx_init()里指定某个参数的数据类型，MySQL会将输入的参数强制转化为该类型。

    • char **args

    对于xxx_init()，当参数是常量时，比如 3、4*7-2或SIN(3.14) args->args[i]指向参数值，当参数是非常量时 args->args[i]为NULL；对于主函数xxx()总是指向参数的值，如果参数i为null，则args->args[i]为NULL。

    • 对于STRING_RESULT类型，args->args[i]指向对应的字符串，args->lengths[i]是字符串长度。
    • 对于INT_RESULT类型，需要强制转化成long long:
    1. long long int_val = *(long long *) args->args[i];
    • 对于REAL_RESULT类型，需要转成double:
    1. double real_val = *(double *) args->args[i]
    • unsigned long *lengths

    对于xxx_init()函数该数组包含每个参数的最大长度，对于xxx()函数为参数的实际长度。

    • char *maybe_null

    对于xxx_init()该成员表示对应的参数是否可以为null。

    • **attributes

    表示传入参数的参数名，参数名的长度在args->attribute_lengths[i]中。

UDF返回值及错误处理

如果有错误发生xxx_init()应该返回true，同时将错误消息保存在message参数中，message参数的buffer长度为 MYSQL_ERRMSG_SIZE(512)。对于long long和double的SQL函数的返回值通过主函数xxx()的返回值返回。字符串类型的SQL函数 如果字符串长度小于255,可以通过参数result参数返回，实际长度存在*length中，xxx()函数要返回result；如果要返回的字 符串长度大于255，需要自己分配内存并通过xxx()返回值返回。分配的内存需要在xxx_deinit里释放。可以通过设置*is_null = 1来表示SQL函数返回值为null。另外如果函数发生错误需要设置 *error = 1。

UDF的编译和安装

这里只讲Linux下编译和安装，编译可以使用如下命令：

  1. c++ -I$(MYSQL_INSTALLDIR)/include  -fPIC -g -shared  \
  2.   -o $(MYSQL_INSTALLDIR)/lib/plugin/libmyudf.so myudf.cc

这里MYSQL_INSTALLDIR指的是MySQL的安装目录。编译完成后生成的目标动态库直接写到了MySQL的安装目录的plugin目录 下，mysqld只在这个目录上寻找UDF实现动态库。

UDF函数的使用

使用mysql命令连接到MySQL server，执行以下查询在数据库中生成SQL函数

  1. CREATE FUNCTION myudf RETURNS INT SONAME 'libmyudf.so';

这里在的libmyudf.so是前面编译生成的动态库。可以通过系统表mysql.func和performance_schema下的user_defined_functions 来跟踪系统中已经安装的UDF。

一个简单的例子

  1. #include "mysql.h"
  2. #include <sys/types.h> /* getpid() */
  3. #include <unistd.h> /* getpid() */
  5. extern "C" bool
  6. mysqld_pid_init(UDF_INIT *initid __attribute__((unused)),
  7.     UDF_ARGS *args __attribute__((unused)),
  8.     char *message __attribute__((unused)))
  9. {
  10.   return false;
  11. }
  13. extern "C" long long
  14. mysqld_pid(UDF_INIT *initid __attribute__((unused)),
  15.     UDF_ARGS *args __attribute__((unused)),
  16.     char *is_null __attribute__((unused)),
  17.     char *error __attribute__((unused)))
  18. {
  19.   return getpid();
  20. }

这个例子实现了一个简单的UDF：mysqld_pid()，该UDF可以拿到mysqld进程的PID，可以通过SQL语句select mysqld_pid()调用。 将以上例子拷贝到一个文件，比如mysqld_pid.cc然后进行编译：

  1. c++ -I$(MYSQL_INSTALLDIR)/include  -fPIC -g -shared  -o \
  2.    $(MYSQL_INSTALLDIR)/lib/plugin/libmysqld_pid.so mysqld_pid.cc

最后通过mysql连接到数据库执行如下SQL语句，将mysqld_pid安装到数据库，用户就可以在SQL语句上使用了。

  1. CREATE FUNCTION mysqld_pid RETURNS INT SONAME 'libmysqld_pid.so';