MySQL:如何编写UDF

1.什么是UDF

UDF顾名思义,就是User defined Function,用户定义函数。我们知道,MySQL本身支持很多内建的函数,此外还可以通过创建存储方法来定义函数。UDF为用户提供了一种更高效的方式来创建函数。

 

UDF与普通函数类似,有参数,也有输出。分为两种类型:单次调用型和聚集函数。前者能够针对每一行数据进行处理,后者则用于处理Group By这样的情况。

 

2.为什么用UDF

既然MySQL本身提供了大量的函数,并且也支持定义函数,为什么我们还需要UDF呢?这主要基于以下几点:

1)UDF的兼容性很好,这得益于MySQL的UDF基本上没有变动

2)比存储方法具有更高的执行效率,并支持聚集函数

3)相比修改代码增加函数,更加方便简单

 

当然UDF也是有缺点的,这是因为UDF也处于mysqld的内存空间中,不谨慎的内存使用很容易导致mysqld crash掉。

 

3.如何编写UDF

UDF的API包括

name_init():

在执行SQL之前会被调用,主要做一些初始化的工作,比如分配后续用到的内存、初始化变量、检查参数是否合法等。

 

name_deinit()

在执行完SQL后调用,大多用于内存清理等工作。init和deinit这两个函数都是可选的

 

name()

UDF的主要处理函数,当为单次调用型时,可以处理每一行的数据;当为聚集函数时,则返回Group by后的聚集结果。

 

name_add()

在每个分组中每行调用

 

name_clear()

在每个分组之后调用

 

为了便于理解,这里给出两种UDF类型的API调用图:

 

 加载中...

下面将就上述几个API进行详细的讲解:

 

1). name_init

原型:

my_boolname_init(UDF_INIT *initid, UDF_ARGS *args, char *message)

UDF_INIT结构体如下:

 

字段

类型

描述

maybe_null

my_bool

如果为1表示该UDF可以返回NULL

decimals

unsigned int

返回值为实数时,表示精度,范围0~30

max_length

unsigned long

对于返回值为INTEGER类型值为21,对于REAL类型值为17,对于字符串类型,存储函数最长参数的长度

ptr

char*

额外的指针,我们可以在这里分配内存。通过initd传递给其他API

const_item

my_bool

为1表示函数总是返回相同的值

extension

void*

用于扩展?

 

UDF_ARGS结构体如下:

字段

类型

描述

arg_count

unsigned int

参数个数

arg_type

enum Item_result*

参数类型数组,记录每一个参数的类型,可以是STRING_RESULT、REAL_RESULT、INT_RESULT以及DECIMAL_RESULT

args

char **

同样是一个数组,用于存储实际数据。

STRING_RESULT与DECIMAL_RESULT类型为char*,INT_RESULT类型为long long*,REAL_RESULT类型为double*,或者一个NULL指针

lengths

unsigned long*

数组,用于存储每一个参数的长度

maybe_null

char *

该数组用于表明每个参数是否可以为NULL,例如

attributes

char **

每个参数的名字

attribute_lengths

unsigned long*

每个参数名字的长度

extension

void*

用于扩展?

 

Message:用于打印错误信息,该指针本身提供长度为MYSQL_ERRMSG_SIZE,来存储信息;

2).name_deinit

原型:

void name_deinit(UDF_INIT*initid)

该函数会进行一些内存释放和清理的工作,在之前我们提到initid->ptr,我们可以在该区域·进行内存的动态分配,这里就可以直接进行内存释放。

 

3).name()

原型:针对不同的返回值类型,有不同的函数原型:

返回值类型

函数原型

STRING or DECIMAL

char *name(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error)

INTEGER

long long name(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)

REAL

double name(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);

 

当返回值为STRING类型时,参数result开辟一个buffer来存储返回值,但不超过766字节,在length参数中存储了字符串的长度。

每个函数原型还包括了is_null和error参数,当*is_null被设置为1时,返回值为NULL,设置*error为1,表明发生了错误。

 

4).name_add()和name_clear()

原型:

void name_add(UDF_INIT *initid, UDF_ARGS *args, char *is_null,char *error)

void name_clear(UDF_INIT *initid, char *is_null, char *error)

对于聚合类型的UDF,name_addd和name_clear会被反复调用。

 

 

4. 两个例子

下面将举两个简单的例子,一个单次调用型函数,一个聚集类型函数,来描述写一个UDF的过程。

 

1)接受一个参数,并返回该参数的值

//初始化

 

 

my_booludf_int_init(UDF_INIT *initid, UDF_ARGS *args, char *message) 

    if (args->arg_count != 1){             //检查参数个数 

        strcpy(message, 

                "udf_intexample() can onlyaccept one argument"); 

        return 1; 

    }   

  

    if (args->arg_type[0] != INT_RESULT){   //检查参数类型 

        strcpy(message, 

                "udf_intexample() argumenthas to be an integer"); 

        return 1; 

    }   

    

    return 0; 

 

 

//清理操作

 

voidudf_int_deinit(UDF_INIT  *initid) 

  

 

 

//主函数

 

long long udf_int(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error) 

    

    long long num = (*(long long *)args->args[0]);   //获取第一个参数值 

    

    return num; 

 

 

 

2)接受一个浮点数类型的参数,并对每个分组进行求和

 

 

//初始化 

my_booludf_floatsum_init(UDF_INIT *initid, UDF_ARGS *args, char *message) 

    double *total = (double *) malloc (sizeof(double)); 

  

    if (total == NULL){                  //内存分配失败 

        strcpy(message,"udf_floatsum:alloc mem failed!"); 

        return 1; 

    } 

  

    *total = 0; 

    initid->ptr = (char *)total; 

  

    if (args->arg_count != 1){            //检查参数个数 

        strcpy(message, "too moreargs,only one!"); 

        return 1; 

    } 

  

    if (args->arg_type[0] != REAL_RESULT){  //检查参数类型 

        strcpy(message, "wrongtype"); 

        return 1; 

    } 

  

    initid->decimals = 3;         //设置返回值精度 

    return 0; 

 

 

 

//清理、释放在init函数中分配的内存

 

voidudf_floatsum_deinit(UDF_INIT *initid) 

    free(initid->ptr); 

 

 

 

//每一行都会调用到该函数

 

voidudf_floatsum_add(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error) 

    double* float_total; 

    float_total = (double*)initid->ptr; 

  

    if (args->args[0]) 

        *float_total += *(double*)args->args[0]; 

  

 

 

 

//每个分组完成后,返回结果

 

doubleudf_floatsum(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error) 

    double* float_total; 

    float_total = (double *)initid->ptr; 

    return *float_total; 

 

 

 

//在进行下一个分组前调用,设置initid->ptr指向的值为0,以便下一次分组统计

 

voidudf_floatsum_clear(UDF_INIT *initid, char *is_null, char *error) 

    double *float_total; 

    float_total = (double *)initid->ptr; 

    *float_total = 0; 

  

 

 

3) Mysql-udf-http是一个开源的UDF,可以利用HTTP协议进行REST操作。什么是REST操作呢?REST是一种web service架构风格,其实现基于HTTP协议的四种方法:POST、GET、PUT以及Delete操作,在mysql-udf-http里分别对应 的函数是http_post、http_get()、http_put()、http_delete()。

源码下载:http://curl.haxx.se/download/curl-7.21.1.tar.gz

./configure–prefix={mysql安装目录} –with-mysql=/usr/local/webserver/mysql/bin/mysql_config

Make&& make install

该UDF的实现原理比较简单,主要使用libcurl库函数来实现http协议通信,总共三百多行代码。这里有使用和介绍http://blog.s135.com/mysql-udf-http/

有些比较有趣的功能:

例如,我们可以通过GET方法获取微博中的个人信息,其中1821798401为用户ID

selecthttp_get('http://api.t.sina.com.cn/statuses/user_timeline/1821798401.json?count=1&source=1561596835')

 

UDF具有非常高的自由度,你可以编写你任何想要实现的功能函数,甚至可以引用MySQL内核的代码和变量。

当然,UDF也有着局限性,如下:

a)        在mysql库下必须有func表,并且在‑‑skip‑grant‑tables开启的情况下,UDF会被禁止;

b)        当UDF挂掉时,有可能会导致mysqld crash掉;

c)        所有的UDF的函数必须是线程安全的,如果非要用全局变量,需要加互斥,尽量在name_init中分配资源,并在name_deinit中释放

d)        需要有insert权限





有什么问题可以加群,100852896
点击这里加入此群 在线提问
文章来自: 本站原创
Tags:
评论: 0 | 查看次数: 5787
博主QQ: 友情链接请找我
QQ群: 灰色档案
返回顶部 关闭