MySQL:如何编写UDF_MySQL
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权限
作者 zhaiwx1987
bitsCN.com