mySql的UDF是什么

mySql的UDF是什么
CRUD：添删改查
UDF是mysql的⼀个拓展接⼝，UDF（Userdefined function）可翻译为⽤户⾃定义函数，这个是⽤来拓展Mysql的技术⼿段。

1 MySQL简介
MySQL是最流⾏的开放源码SQL数据库管理系统，相对于Oracle，DB2等⼤型数据库系统，MySQL由于其开源性、易⽤性、稳定性等特点，受到个⼈使⽤者、中⼩型企业甚⾄⼀些⼤型企业的⼴泛欢迎，MySQL具有以下特点：
l MySQL是⼀种关联数据库管理系统，关联数据库将数据保存在不同的表中，⽽不是将所有数据放在⼀个⼤的仓库内，这样就增加了速度并提⾼了灵活性。

l MySQL软件是⼀种开放源码软件。

l MySQL数据库服务器具有快速、可靠和易于使⽤的特点。

l MySQL服务器⼯作在客户端/服务器模式下，或嵌⼊式系统中。

l 有⼤量可⽤的共享MySQL软件。

2 MySQL内置函数
使⽤过MySQL的⼈都知道，MySQL有很多内置函数提供给使⽤者，包括字符串函数、数值函数、⽇期和时间函数等，给开发⼈员和使⽤者带来了很多⽅便。

下⾯给⼏个例⼦：
l 字符串函数
mysql> select ASCII('2');
+------------+
| ASCII('2') |
+------------+
| 50 |
+------------+
打印字符的ASCII编码。

l 数值函数
mysql> SELECT LOG(10,100);
+-------------+
| LOG(10,100) |
+-------------+
| 2 |
+-------------+
打印以10为底，100的对数值。

l ⽇期和时间函数
mysql> SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2011-11-11 |
+------------+
打印当前的⽇期。

3 扩展MySQL函数------ UDF
MySQL的内置函数虽然丰富，但毕竟不能满⾜所有⼈的需要，有时候我们需要对表中的数据进⾏⼀些处理⽽内置函数不能满⾜需要的时候，就需要对MySQL进⾏⼀些扩展，幸运的是，MySQL给使⽤者提供了添加新函数的机制，这种使⽤者⾃⾏添加的MySQL函数就称为UDF(User Define Function)。

其实除了UDF外，使⽤者还可以将函数添加为MySQL的固有（内建）函数，固有函数被编译进mysqld服务器中，称为永久可⽤的，不过这种⽅式较添加UDF
复杂，升级维护都较为⿇烦，这⾥我们不做讨论。

⽆论你使⽤哪种⽅法去添加新函数，它们都可以被SQL声明调⽤，就像 ABS()或SUM()这样的固有函数⼀样。

3.1 UDF的特性
l 函数能返回字符串，整数或实数。

l 你可以定义⼀次作⽤于⼀⾏的简单函数，或作⽤于多⾏的组的集合函数。

l 提供给函数的信息使得函数可以检查传递给它们的参量的数⽬和类型。

l 你可以让MySQL在将某参量传递给函数之前强制其为某⼀类型。

l 你可以表⽰函数返回NULL 或发⽣错误。

3.2 CREATE FUNCTION/DROP FUNCTION语法
CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|INTEGER|REAL}
SONAME shared_library_name
DROP FUNCTION function_name
⼀个⾃定义函数 (UDF) 就是⽤⼀个象ABS()或SUM()这样的固有（内建）函数⼀样作⽤的新函数去扩展MySQL。

function_name是⽤在SQL声明中以备调⽤的函数名字。

RETURNS ⼦句说明函数返回值的类型。

shared_library_name是共享⽬标⽂件的基本名，共享⽬标⽂件含有实现函数的代码。

该⽂件必须位于⼀个能被你系统的动态连接者搜索的⽬录⾥。

你必须有mysql 数据库的INSERT 权限才能创建⼀个函数，你必须有mysql 数据库的DELETE权限才能撤销⼀个函数。

这是因为CREATE FUNCTION 往记录函数名字，类型和共享名的mysql.func系统表⾥添加了⼀⾏，⽽DROP FUNCTION则是从表中删掉这⼀⾏。

值得注意的是，要使得UDF机制能够起作⽤，必须使⽤C或者C++编写函数，你的系统必须⽀持动态加载，⽽且你必须是动态编译
的mysqld（⾮静态）。

3.3 定义UDF
对于每个你想要使⽤在SQL声明中的函数，你应该定义相应的C（或C++）函数。

你为xxx()编写来实现接⼝的C/C++函数如下：
l xxx() (必有)
主函数。

这是函数结果被计算的地⽅。

SQL函数数据类型与C/C++函数返回类型的对应关系如下：
SQL 类型C/C++ 类型
STRING char *
INTEGER long long
REAL double
l xxx_init() (可选)
对xxx()的初始化函数。

它可以被⽤来：
检查传递给xxx()的参量数⽬。

检查参量是否为必需的类型，或者，除此之外，在主函数被调⽤的时候告诉MySQL将参量强制为想要的类型。

分配主函数需要的内存。

指定结果的最⼤长度。

指定（对于REAL 函数）⼩数的最多位数。

指定结果是否可以为 NULL。

l xxx_deinit() （可选）
对xxx()的去初始化函数。

它释放初始化函数分配的内存。

当SQL声明调⽤XXX()时，MySQL调⽤初始化函数xxx_init()，让它执⾏必要的设置，⽐如，检查参量或分配内存。

如果xxx_init()返回⼀个错误，SQL声明会退出并给出错误信息，⽽主函数和去初始化函数并没有被调⽤。

否则，主函数xxx()对每⼀⾏都被调⽤⼀次。

所有⾏都处理完之后，调⽤去初始化函数xxx_deinit()执⾏必要的清除。

对于象SUM()⼀样⼯作的集合函数，你也必须提供如下的函数：
l xxx_clear()（在5.1版本中必须）
对⼀个新组重置当前集合值为初试集合值，但不插⼊任何参量。

l xxx_add()（必须）
添加参量到当前集合值。

MySQL按下列操作来处理集合UDF：
1. 调⽤ xxx_init() 让集合函数分配它需要⽤来存储结果的内存。

2. 按照GROUP BY表达式来排序表。

3. 为每个新组中的第⼀⾏调⽤xxx_clear()函数。

4. 为属于同组的每⼀个新⾏调⽤xxx_add()函数。

5. 当组改变时或每组的最后⼀⾏被处理完之后，调⽤xxx()来获取集合结果。

6. 重复，以上3步直到所有⾏被处理完。

7. 调⽤xxx_deinit() 函数去释放UDF分配的内存。

所有函数必须时线程安全的，这不仅对主函数，对初始化和去初始化函数也⼀样，也包括集合函数要求的附加函数。

这个要求的⼀个结果就是，你不能分配任何变化的全局或静态变量。

如果你需要内存，你可以在xxx_init()函数分配内存，然后在xxx_deinit()函数释放掉。

3.3.1 主要数据结构
UDF_INIT
typedef struct st_udf_init
{
my_bool maybe_null; /* 1 if function can return NULL */
unsigned int decimals; /* for real functions */
unsigned long max_length; /* For string functions */
char *ptr; /* free pointer for function data */
my_bool const_item; /* 0 if result is independent of arguments */
} UDF_INIT;
l my_bool maybe_null
如果xxx()能返回NULL，xxx_init()应使maybe_null为1。

其默认值是1。

l unsigned int decimals
⼩数位数。

默认值是传到主函数的参量⾥⼩数的最⼤位数。

（例如，如果函数传递 1.34, 1.345, 和1.3, 那么默认值为3，因为1.345 有3位⼩数。

l unsigned int max_length
结果的最⼤长度。

max_length的默认值因函数的结果类型⽽异。

对字符串函数，默认值是结果的最⼤长度。

对整型函数，默认是21位。

对实型函数，默认是13再加上initid->decimals指⽰的⼩数位数。

（对数字函数，长度包含正负号或者⼩数点符）。

如果想返回团值，你可以把max_length 设为从65KB到16MB。

这个内存不会被分配，但是如果有临时数据需要存储，这个设置了的值被⽤来决定使⽤哪种列的类型。

l char *ptr
函数可以⽤作本⾝⽬的的指针。

⽐如，函数可以⽤initid->ptr来在分配了的内存内部通讯。

xxx_init()应该分配内存，并指派给这个指针：
initid->ptr = allocated_memory;
在 xxx() 和 xxx_deinit()中，借⽤initid->ptr来使⽤或释放内存。

UDF_ARGS
enum Item_result /* 返回结果类型 */
{
STRING_RESULT=0,
REAL_RESULT,
INT_RESULT,
ROW_RESULT,
DECIMAL_RESULT
};
typedef struct st_udf_args
{
unsigned int arg_count; /* Number of arguments */
enum Item_result *arg_type; /* Pointer to item_results */
char **args; /* Pointer to argument */
unsigned long *lengths; /* Length of string arguments */
char *maybe_null; /* Set to 1 for all maybe_null args */
char **attributes; /* Pointer to attribute name */
unsigned long *attribute_lengths;/* Length of attribute arguments */
} UDF_ARGS;
l unsigned int arg_count
参数个数。

如果你需要你的函数带着某个数⽬的参量被调⽤，在初始化函数检查这个值，例如：
if (args->arg_count != 2)
{
strcpy(message,"XXX() requires two arguments");
return 1;
}
l enum Item_result *arg_type
参数类型列表。

要确信⼀个参量是给定类型的，并且如果不是的话就返回⼀个错误，请检查初始化函数中的arg_type数列。

⽐如：
if (args->arg_type[0] != STRING_RESULT ||
args->arg_type[1] != INT_RESULT)
{
strcpy(message,"XXX() requires a string and an integer");
return 1;
}
要求你函数的参量是某⼀类型的另⼀⽅法是，使⽤初始化函数设置arg_type元素为你想要的类型。

对所有对xxx()的调⽤⽽⾔，这会导致MySQL强制参量为这些类型。

⽐如，要指定头两个参量强制成字符串和整数，在xxx_init()中分别：
args->arg_type[0] = STRING_RESULT;
args->arg_type[1] = INT_RESULT;
l char **args 参数列表
对主函数的每次调⽤，args->args 包含为每个当前处理的⾏传递的实际参量。

如下使⽤参量i的函数：
给⼀个STRING_RESULT 型的参量作为⼀个字符串加⼀个长度，可以允许所有⼆进制数或任意长度的数处理。

字符串内容作为args-
>args[i]，⽽字符串长度为args->lengths[i]。

你不能采⽤null结尾的字符串。

对⼀个INT_RESULT型的参量，你必须转换args->args[i]为⼀个long long值：
long long int_val;
int_val = *((long long*) args->args[i]);
对⼀个REAL_RESULT型参量，你必须转换args->args[i]为⼀个双精度值：
double real_val;
real_val = *((double*) args->args[i]);
l unsigned long *lengths
对初始化函数，lengths数列表⽰对每个参量的最⼤字符串长度。

你不要改变它。

对主函数的每次调⽤，lengths包含了对当前处理⾏传递的任何字符串参量的实际长度。

对于INT_RESULT或 REAL_RESULT类型的参量，lengths仍包含参量的最⼤长度（对初始化函数）。

3.3.2 简单函数
这⾥说明简单SQL函数的C/C++主函数xxx()的编写，注意返回值和参数会有所不同，这取决于你说明的SQL函数xxx()在CREATE FUNCTION声明中返回的是STRING，INTEGER类型还是REAL类型。

对于STRING型函数：
char *xxx(UDF_INIT *initid, UDF_ARGS *args,
char *result, unsigned long *length,
char *is_null, char *error);
对于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);
初始化和去初始化函数如下说明：
my_bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
void xxx_deinit(UDF_INIT *initid);
initid参数被传递给所有的三个函数。

它指向UDF_INIT结构，这个结构被⽤来在函数之间交换信息。

3.3.3 集合函数
这⾥介绍创建集合UDF之时需要定义的不同函数。

l xxx_reset()
当MySQL在⼀个新组中发现第⼀⾏时调⽤这个函数。

它对这个组重置任何内部总和变量，然后使⽤给定的UDF_ARGS参量作为内部总和值的第⼀个值。

如下说明 xxx_reset() 函数：
char *xxx_reset(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);
在MySQL5.1版中UDF接⼝不需要或不使⽤xxx_reset()函数，⽽是使⽤xxx_clear()函数作为替代。

但是如果你想让UDF也能在⽼版本的服务
器上运⾏，你也可以定义 xxx_reset() 和 xxx_clear()函数。

（如果你使⽤了这两个函数，xxx_reset()函数在很多情况下可以通过调⽤函数来内部实现，即调⽤xxx_clear()函数重置所有变量，然后添加UDF_ARGS参量作为组的第⼀个值。

）
l xxx_clear()
当MySQL需要重置总和结果时调⽤此函数。

对每⼀个新组，在开始之时调⽤它，但是它也可以被调⽤来为⼀个没有匹配⾏在其中的查询重置值。

如下说明xxx_clear()：
char *xxx_clear(UDF_INIT *initid, char *is_null, char *error);
在调⽤xxx_clear()之前is_null被设置指向CHAR(0)。

如果发⽣错误，你可以存储⼀个值在error参量指向的变量中。

error指向⼀单字节变量，⽽不是⼀个字符串缓冲区。

xxx_clear()是MySQL 5.1必须的。

l xxx_add()
为同组所有的⾏调⽤这个函数。

你应该⽤它在UDF_ARGS参量中向内部总和变量加值。

char *xxx_add(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);
对集合UDF⽽⾔xxx() 函数应该⽤与⾮集合UDF⼀样的⽅法来说明。

对⼀个集合UDF，MySQL在组内所有⾏被处理之后调⽤xxx()函数。

这⾥你应该⼀般不会接触到它的UDF_ARGS参量，但是取⽽代之地根据内部总和变量返回给你值。

is_null和error的指针参量和所有到xxx_reset(), xxx_clear(), xxx_add() 和 xxx()调⽤⼀样。

你可以⽤这个来提醒你获取⼀个错误或⽆论xxx()是否返回NULL的⼀个结果。

你不能把⼀个字符串存到error！error指向单字节变量⽽不是字符串缓冲区。

*is_null 对每⼀个组都重置（调⽤xxx_clear()前），*error 从不重置。

如果xxx()返回时，*is_null或*error被设置，MySQL返回NULL作为组函数的结果。

3.3.4 错误处理
如果没有错误发⽣，初始化函数应该返回0，否则就返回1。

如果有错误发⽣，xxx_init() 应该在message 参数存储⼀个以null结尾的错误消息。

该消息被返回给客户端。

消息缓冲区是MYSQL_ERRMSG_SIZE 字符长度，但你应该试着把消息保持在少于80个字符，以便它能适合标准终端屏幕的宽度。

对于long long 和 double 类型的函数，主函数 xxx()的返回值是函数值。

字符函数返回⼀个指向结果的指针，并且设置 *result 和 *length 为返回值的内容和长度。

例如：
memcpy(result, "result string", 13);
*length = 13;
被传给 xxx() 函数的结果缓冲区是 255 字节长。

如果你的结果适合这个长度，你就不需要担⼼对结果的内存分配。

如果字符串函数需要返回⼀个超过255字节的字符串，你必须⽤ malloc() 在你的 xxx_init() 函数或者xxx()函数⾥为字符串分配空间，并且
在 xxx_deinit() 函数⾥释放此空间。

你可以将已分配内存存储在UDF_INIT 结构⾥的ptr位置以备将来 xxx() 调⽤。

要在主函数中指明⼀个NULL的返回值，设置*is_null为1：
*is_null = 1;
要在主函数中指明错误返回，设置 *error 为 1：
*error = 1;
如果xxx()对任意⾏设置*error为1 ，对于任何 XXX()被调⽤的语句处理的当前⾏和随后的任意⾏，该函数值为NULL（甚⾄都不为随后的⾏调⽤ xxx()）。

4 范例
4.1 编译安装
安装mysql开发包
[root@rocket mysql_udf]# yum -y install mysql-devel
编译udf链接库
代码：udf_str.cpp
#include <mysql.h>
#include <mysql_com.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <algorithm>
extern "C"
{
// str_reverse
my_bool str_reverse_init(UDF_INIT* initid, UDF_ARGS* args, char* message);
void str_reverse_deinit(UDF_INIT* initid);
char* str_reverse(UDF_INIT* initid, UDF_ARGS* args, char* result, unsigned long* length, char* is_null, char *error); // LengthAll
my_bool mysum_init(UDF_INIT* initid, UDF_ARGS* args, char* message);
void mysum_deinit(UDF_INIT* initid);
void mysum_reset(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);
void mysum_clear(UDF_INIT *initid, char *is_null, char *error);
void mysum_add(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);
long long mysum(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error); }
char* StrData = 0;
int gSum = 0;
// str_reverse ==================================================
my_bool str_reverse_init(UDF_INIT* initid, UDF_ARGS* args, char* message)
{
if (args->arg_count != 1)
{
strcpy(message,"wrong number of arguments: str_reverse() requires one argument");
return 1;
}
if (args->arg_type[0] != STRING_RESULT)
{
strcpy(message,"str_reverse() requires a string as parameter");
return 1;
}
StrData = (char*)malloc(4096);
memset(StrData, 0, 4096);
initid->maybe_null = 1;
initid->max_length = 32;
initid->ptr = StrData;
return 0;
}
void str_reverse_deinit(UDF_INIT* initid)
{
free(StrData);
}
char* str_reverse(UDF_INIT* initid, UDF_ARGS* args, char* result, unsigned long* length, char* is_null, char *error) {
if (args->arg_type[0] == STRING_RESULT)
{
if (strlen(args->args[0]) > 256)
{
strncpy(StrData, args->args[0], 4096);
StrData[4096-1] = 0;
std::reverse(StrData, StrData + strlen(StrData));
return StrData;
}
else
{
strncpy(result, args->args[0], 256);
result[256-1] = 0;
std::reverse(result, result + strlen(result));
*length = (unsigned long)strlen(result);
return result;
}
}
return NULL;
}
// LengthAll ==================================================
my_bool mysum_init(UDF_INIT* initid, UDF_ARGS* args, char* message)
{
if (args->arg_count != 1)
{
strcpy(message,"wrong number of arguments: mysum() requires one argument");
return 1;
}
if (args->arg_type[0] != INT_RESULT)
{
strcpy(message,"wrong argument type of arguments: mysum() requires int");
return 1;
}
gSum = 0;
return 0;
}
void mysum_deinit(UDF_INIT* initid)
{
}
void mysum_reset(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)
{
gSum = 0;
}
void mysum_clear(UDF_INIT *initid, char *is_null, char *error)
{
gSum = 0;
}
void mysum_add(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)
{
gSum += *(int*)(args->args[0]);
}
long long mysum(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error) {
return gSum;
}
[root@rocket mysql_udf]# g++ -I/usr/include/mysql -shared -fPIC -o udf_str.so udf_str.cpp
查找插件路径
安装插件函数
DROP FUNCTION IF EXISTS str_reverse;
DROP FUNCTION IF EXISTS mysum;
CREATE FUNCTION str_reverse RETURNS string SONAME 'udf_str.so';
CREATE AGGREGATE FUNCTION mysum RETURNS INTEGER SONAME 'udf_str.so';
注意这⾥的返回值不能写错，不然运⾏的时候mysql服务器会崩溃！
查看安装结果
4.2 运⾏
运⾏str_reverse
运⾏mysum，先创建⼀些数据
mysql> create database test;
mysql> use test;
mysql> CREATE TABLE salary( name varchar(64) NOT NULL DEFAULT '' COMMENT 'name', salary int(11) NOT NULL DEFAULT 0 COMMENT 'salary', primary key(name) )ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'test';
mysql> insert into salary values ('zhangsan', 11380), ('lisi', 12000), ('wangwu', 8789);
mysql> select mysum(name) from salary;
ERROR 1123 (HY000): Can't initialize function 'mysum'; wrong argument type of arguments: mysum() requires int
这⾥故意使⽤name为参数，可以看到我们在程序⾥打印的错误信息。

执⾏正确的语句
可以看到mysum实现了和内置函数sum⼀样的功能。