mysql 查看 udf_MySQL的UDF

最近用到MySQL的UDF, 查了一下相关文献, 对用户用户实现function和Aggregate function的方法做个介绍.

快速编写一个MySQL UDF

为了能够快速了解UDF(user-defined function)是什么, 我们首先构建一个最简单的UDF, 然后再介绍更细节的内容. 本文在Ubuntu16.04下测试, 开始之前需要先安装MySQL以及相关的库, 在Ubuntu下是:

sudo apt-get install libmysqlclient-dev

Step1: 编写c++代码并生成动态链接库

示例c++代码如下:

extern “c”{

long long myadd(UDF_INIT *initid, UDF_ARGS *args,

char *is_null, char *error);

my_bool myadd_init(UDF_INIT *initid, UDF_ARGS *args,

char *message);

}

long long myadd(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;

}

my_bool myadd_init(UDF_INIT *initid, UDF_ARGS *args,

char *message){

return 0;

}

完成以后, 将文件保存为udf.cpp 然后使用如下的命令编译动态链接库:

g++ -shared -fPIC -I /usr/include/mysql -o udf.so udf.cpp

获得udf.so文件.

Step2: 在MySQL中添加函数

完成上述的编写以后, 讲udf.so文件拷贝到MySQL的plugin目录下, 在Ubuntu16.04中默认是:

/usr/lib/mysql/plugin/

然后使用如下的命令在MySQL中安装动态链接库.

CREATE FUNCTION myadd RETURNS INTEGER SONAME ‘udf.so’

Step3: 调用函数以及相关查询

调用函数

select myadd(1,2);

可以获得计算结果3

查询安装列表

select * from mysql.func;

可以查看数据库当前被安装的.so的库列表.

用drop function来删除函数:

DROP FUNCTION myadd;

UDF编写过程解释

可以看到, 我们在上面添加了自己的函数myadd, 这个函数被安装以后可以被MySQL执行. 我们同时定义了myadd_init, 这是系统规定的必须使用的初始化函数. 在编写MySQL的UDF的时候, 一方面我们要定义自己需要的函数, 另一方面, 我们要同时编写配套的一系列其他函数, 这些函数的命名有固定规则. 比如用户自定义的函数名为xxx, 则配套的函数为xxx_init, xxx_deinit等, 其参数列表也是固定的,下面进行介绍.

编写用户主函数

首先是用户函数的定义, 我们假设需要定义的函数名字为为xxx, 则我们的函数需要有参数列表和返回值, 这不能由用户随意指定, 是有固定规则的.

其中返回类型支持5种:

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

对于这5种返回值, 定义的函数头分别如下:

返回值是STRING 类型或DECIMAL类型

char *xxx(UDF_INIT *initid, UDF_ARGS *args,

char *result, unsigned long *length,

char *is_null, char *error);

对于这种定义, 返回值可以指向result, 把需要的内容拷贝进去, 并设置长度, 如下:

memcpy(result, “result string”, 13);

*length = 13;

INTEGER类型

long long xxx(UDF_INIT *initid, UDF_ARGS *args,

char *is_null, char *error);

REAL类型

double xxx(UDF_INIT *initid, UDF_ARGS *args,

char *is_null, char *error);

ROW类型

未实现

编写系统内置函数

在完成了用户定义的主函数以后, 还需要编写配套的系统内置函数. 其相关说明如下:

xxx_init

这个函数会在自定义的xxx函数调用前被调用, 进行基本的初始化工作, 其完整定义如下:

my_bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message);

返回值: 1代表出错, 可以在message中给出错误信息并且返回给客户端, 0表示正确执行.信息长度不能大于MYSQL_ERRMSG_SIZE

函数功能: 该函数的主要功能一般是分配空间, 函数参数检查的等. 如果不需要做任何操作, 直接返回0即可.

xxx_deinit

该函数用于释放申请的空间, 其完整定义如下:

void xxx_deinit(UDF_INIT *initid);

函数功能: 该函数的功能主要是释放资源, 如果在xxx_init中申请了内存, 可以在此处释放, 该函数在用户函数xxx执行以后执行

对于普通的UDF, 上面两个内置函数足够了, 但是对于Aggregate函数, 像sum, count函数, 必须额外给出如下的函数:

xxx_clear

其完整定义如下:

void xxx_clear(UDF_INIT *initid, char *is_null, char *error);

xxx_add

void xxx_add(UDF_INIT *initid, UDF_ARGS *args,

char *is_null, char *error);

两种UDF的执行流程介绍

这两种UDF非别有如下的执行流程:

普通函数执行流程

调用xxx_init来初始化, 并申请内存空间用于存储结果

调用xxx

调用xxx_deinit释放空间

对于普通函数的执行流程, 可以参照一开始给出的myadd函数.

Aggregate函数执行流程

调用xxx_init来初始化, 并申请内存空间用于存储结果

对表使用group by 排序, 形成多个或一个group

xxx_clear调用, 对每个新的group, 调用之

对每个group的每一行,调用xxx_add

调用xxx

重复3-5, 直到处理完所有的group

调用xxx_deinit释放空间

对于Aggregate函数的执行流程, 可以参照后面给出的mysum函数.

函数参数列表介绍

对于上面介绍的函数, 其参数列表和返回值是我们所关注的, 对于某些函数特有的参数, 在上面介绍函数的同时已经做了介绍, 现在介绍其公有的参数部分:

UDF_INIT

该结构主要用于用户函数与系统内置函数的通信, 其结构如下:

成员

作用

my_bool maybe_null

其值为1表示函数可以返回NULL, 默认值是1

unsigned int decimals

参数如果是小数, 表示小数点后面的位数

unsigned int max_length

返回结果的最大长度

char *ptr

用户可以申请自己的内存空间, 然后用这个指针指向自己的空间供自己的函数使用

my_bool const_item

如果用户函数对于相同输入总有相同输出, 则其值为1, 这是默认值. 否则则设置为0

在本文例子中, 我们只用到ptr, 其余均使用默认值. 所谓的通信, 是指我们在xxx_init中就有这个参数了, 后后续的xxx与xxx_deinit中, 我们依然可以获取这个类型的指针, 这样, 我们就可以在xxx_init函数中申请一块空间, 并令ptr指向这块空间, 在xxx函数中使用这块空间, 然后在xxx_deinit中释放空间, 这是MySQL的UDF的基本用法.

UDF_ARGS

该结构主要用于传参数, 参数由MySQL提供, 对于表而言, 就是一行一行的表数据, 其介绍如下:

成员

作用

unsigned int arg_count

函数参数的个数, 可以在xxx_init函数中通过这个成员对用户输入的参数个数进行检查, 如果参数个数错误, 则不执行或返回错误

enum Item_result *arg_type

参数的类型, 可以在这不做参数类型的检查, 也可以自己强制指定类型,类型有5种, 在前一小节已有说明

char args

如果参数是STRING_RESULT类型,可以通过args->args[i]来获取内容, 通过args->lengths[i]来获取长度; 如果是 INT_RESULT可以通过int_val = ((long long) args->args[i]);来获取内容 REAL_RESULT或者STRING_RESULT类型可以通过real_val = ((double) args->args[i]);来获取内容

unsigned long *lengths

对于初始化函数, 保存了参数的最大长度. 对于用户定义的主函数, 保持了各个参数的长度, 这个对于string类型有用, 因为这里的string不一定是’\0’结尾的

char *maybe_null

其值为0表示一个参数不能是null, 1表示可以

char attributes

可以获得参数的名字.比如SELECT my_udf(expr1, expr2 AS alias1, expr3 alias2);则args->attributes[0] = “expr1” args->attribute_lengths[0] = 5 后面同理

unsigned long *attribute_lengths

每个参数名字的长度

可以看到, 这个参数结构提供了很多功能, 本文只关注通过args成员来获得具体的参数内容.

一个Aggregate 函数的例子

有了上面的基础, 我们就可以自己实现一个sum函数mysum, 其作用和内置的sum有一样的功能, 下面给出代码和解释:

#include

extern “C” {

my_bool mysum_init(UDF_INIT *const initid, UDF_ARGS *const args,

char *const message);

void mysum_deinit(UDF_INIT *const initid);

void mysum_clear(UDF_INIT *const initid, char *const is_null,

char *const error);

void mysum_add(UDF_INIT *const initid, UDF_ARGS *const args,

char *const is_null, char *const error);

long long mysum(UDF_INIT *const initid, UDF_ARGS *const args,

char *const result, unsigned long *const length,

char *const is_null, char *const error);

}

//执行前先进行初始化,分配空间

my_bool mysum_init(UDF_INIT *const initid, UDF_ARGS *const args,

char *const message){

long long * i = new long long;

initid->ptr = (char*)i;

return 0;

}

//在执行该函数前,先执行group by, 然后遇到每个新的group, 先调用该函数.如果没有group by, 则所有的都是一个group.

void mysum_clear(UDF_INIT *const initid, char *const is_null,

char *const error) {

*((long long *)(initid->ptr)) = 0;

}

//每一行数据都经过add函数处理

void mysum_add(UDF_INIT *const initid, UDF_ARGS *const args,

char *const is_null, char *const error) {

*((long long *)(initid->ptr)) = *((long long *)(initid->ptr)) +

*((long long *)args->args[0]);

}

//所有数据处理完成, 调用用户定义的mysum函数返回结果;遇到下一个group, 重新从clear开始执行.

long long mysum(UDF_INIT *const initid, UDF_ARGS *const args,

char *const result, unsigned long *const length,

char *const is_null, char *const error) {

return *((long long *)(initid->ptr));

}

//执行结束, 释放空间

void mysum_deinit(UDF_INIT *const initid){

delete initid->ptr;

}

使用上述的方法编译并复制到对应的plugin目录以后, 可以用如下的命令添加函数, 注意这里和添加普通的函数方法不一样.

CREATE AGGREGATE FUNCTION mysum RETURNS INTEGER SONAME ‘udf.so’;

函数执行结果如下:

Database changed

mysql> select * from student;

+——+———–+

| id | name |

+——+———–+

| 1 | zhangfei |

| 2 | zhangfei |

| 3 | zhangfei |

| 4 | zhangliao |

| 5 | zhangliao |

| 6 | zhangliao |

| 7 | shaoyiwen |

+——+———–+

7 rows in set (0.00 sec)

mysql> select mysum(id) from student;

+———–+

| mysum(id) |

+———–+

| 28 |

+———–+

1 row in set (0.00 sec)

总结

可以看到, MySQL的UDF可以用于处理MySQL表中的数据, 其对外提供了普通函数与Aggregate函数接口, 普通函数处理一行的数据, Aggregate函数处理一个group的数据. 其函数头是固定的, 对外提供了5种数据类型.需要注意的是, 我们编写的MySQL UDF必须保证是线程安全的.

相关资料

—

原始链接:yiwenshao.github.io/2016/11/20/MySQL的UDF/

文章作者:Yiwen Shao

许可协议: Attribution-NonCommercial 4.0

转载请保留以上信息, 谢谢!