28.4.2 添加用户定义的功能
为了使 UDF 机制起作用,必须使用 C 或 C 编写函数,并且您的 os 必须支持动态加载。 MySQL 源代码发行版包含一个文件sql/udf_example.cc
,该文件定义了五个 UDF 函数。请查阅此文件,以了解 UDF 调用约定如何工作。 include/mysql_com.h
头文件定义了与 UDF 相关的符号和数据结构,尽管您不必直接包含此头文件;它包含在mysql.h
中。
UDF 包含成为正在运行的服务器的一部分的代码,因此在编写 UDF 时,您将受到适用于编写服务器代码的所有约束的约束。例如,如果您尝试使用libstdc++
库中的函数,则可能会遇到问题。这些限制在将来的服务器版本中可能会更改,因此服务器升级可能需要对最初为旧服务器编写的 UDF 进行修订。有关这些约束的信息,请参见第 2.9.7 节“ MySQL 源配置选项”和第 2.9.8 节“处理 MySQL 编译问题”。
为了能够使用 UDF,必须动态链接mysqld。如果要使用需要从mysqld访问符号的 UDF(例如sql/udf_example.cc
中的metaphone
函数使用default_charset_info
),则必须将程序与-rdynamic
链接(请参阅man dlopen
)。
对于要在 SQL 语句中使用的每个函数,应定义相应的 C(或 C)函数。在下面的讨论中,名称“ xxx”用作示例功能名称。为了区分 SQL 和 C/C 用法,XXX()
(大写)table 示 SQL 函数调用,而xxx()
(小写)table 示 C/C 函数调用。
Note
使用 C 时,将您的 C 函数封装在此构造中:
extern "C" { ... }
这样可以确保您的 C 函数名称在完整的 UDF 中保持可读性。
用户定义的功能接口功能
下 table 描述了为实现名为XXX()
的函数的接口而编写的 C/C 函数。主要功能xxx()
是必需的。另外,出于UDF 安全注意事项中讨论的原因,UDF 至少需要这里描述的其他功能之一。
xxx()
主要功能。这是函数结果的计算位置。 SQL 函数数据类型和 C/C 函数的返回类型之间的对应关系如下所示。
SQL Type | C/C++ Type |
---|---|
STRING | char * |
INTEGER | long long |
REAL | double |
也可以声明一个DECIMAL函数,但是该值以字符串形式返回,因此您应将 UDF 当作一个STRING
函数来编写。 ROW
功能未实现。
xxx_init()
xxx()
的初始化函数。如果存在,它可以用于以下目的:
-
检查
XXX()
的参数数量。-
验证参数是否为必需类型,或者在调用 main 函数时告诉 MySQL 将参数强制为必需类型。
-
分配主功能所需的任何内存。
-
指定结果的最大长度。
-
指定(对于REAL个函数)结果中小数位数的最大值。
-
指定结果是否可以为
NULL
。
-
-
xxx_deinit()
xxx()
的反初始化功能。如果存在,它应该释放由初始化函数分配的所有内存。
当一条 SQL 语句调用XXX()
时,MySQL 调用初始化函数xxx_init()
使其执行任何必需的设置,例如参数检查或内存分配。如果xxx_init()
返回错误,则 MySQL 会通过错误消息中止 SQL 语句,并且不调用 main 或 deinitialization 函数。否则,MySQL 每行调用一次主函数xxx()
。处理完所有行后,MySQL 调用反初始化函数xxx_deinit()
,以便它可以执行任何必需的清除操作。
对于像SUM()一样工作的聚合函数,您还必须提供以下函数:
xxx_clear()
重置当前的合计值,但不要将参数作为新组的初始合计值插入。
xxx_add()
将参数添加到当前合计值。
MySQL 按以下方式处理聚合 UDF:
-
调用
xxx_init()
,以使聚合函数分配存储结果所需的任何内存。 -
根据
GROUP BY
table 达式对 table 进行排序。 -
呼叫
xxx_clear()
进入每个新组的第一行。 -
为同一组中的每一行调用
xxx_add()
。 -
当组更改时或处理完最后一行后,调用
xxx()
以获取聚合结果。 -
重复步骤 3 到 5,直到处理完所有行
-
调用
xxx_deinit()
,以让 UDF 释放已分配的所有内存。
所有函数必须是线程安全的。这不仅包括主要功能,还包括初始化和取消初始化功能,以及聚合功能所需的其他功能。此要求的结果是不允许您分配任何更改的全局或静态变量!如果需要内存,则必须在xxx_init()
中将其释放,然后在xxx_deinit()
中将其释放。
简单函数的 UDF 调用 Sequences
本节描述了创建简单 UDF 时必须定义的不同功能。有关 MySQL 调用这些函数的 Sequences 的信息,请参见用户定义功能接口功能。
应该声明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);
DECIMAL函数返回字符串值,并且以与STRING
函数相同的方式声明。 ROW
功能未实现。
声明初始化和取消初始化功能,如下所示:
my_bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
void xxx_deinit(UDF_INIT *initid);
initid
参数传递给所有三个函数。它指向用于在函数之间传递信息的UDF_INIT
结构。 UDF_INIT
结构成员遵循。初始化功能应填写任何希望更改的成员。 (要对成员使用默认值,请保持不变.)
my_bool maybe_null
如果xxx()
可以返回NULL
,则xxx_init()
应将maybe_null
设置为1
。如果将任何参数声明为maybe_null
,则默认值为1
。
unsigned int decimals
小数点右边的小数位数。默认值是传递给主函数的参数中的最大十进制数字。例如,如果传递函数1.34
,1.345
和1.3
,则默认值为 3,因为1.345
具有 3 个十进制数字。
对于没有固定小数位数的参数,decimals
值设置为 31,这比DECIMAL,FLOAT和DOUBLE数据类型所允许的最大小数位数大 1.该值在mysql_com.h
头文件中作为常量NOT_FIXED_DEC
可用。
在诸如FLOAT或DOUBLE列等声明没有显式小数位数(例如FLOAT而不是FLOAT(10,3)
)的情况下,31 的值用于参数,以及1345E-3
等浮点常量。它也用于可能在函数内转换为数字形式的字符串和其他非数字参数。
decimals
成员初始化的值仅是默认值。可以在函数中更改它以反映实际执行的计算。确定默认值后,将使用最多十进制数的参数。如果对于其中一个参数,小数位数为NOT_FIXED_DEC
,则该值是decimals
的值。
unsigned int max_length
结果的最大长度。默认的max_length
值因函数的结果类型而异。对于字符串函数,默认值为最长参数的长度。对于整数函数,默认值为 21 位。对于实函数,默认值为 13 加上initid->decimals
table 示的小数位数。 (对于数字函数,长度包括任何符号或小数点字符.)
如果要返回 Blob 值,可以将max_length
设置为 65KB 或 16MB。该内存未分配,但是如果需要临时存储数据,则该值用于确定使用哪种数据类型。
char *ptr
该函数可用于其自身目的的指针。例如,函数可以使用initid->ptr
在彼此之间通信分配的内存。 xxx_init()
应该分配内存并将其分配给此指针:
initid->ptr = allocated_memory;
在xxx()
和xxx_deinit()
中,请参考initid->ptr
以使用或取消分配内存。
my_bool const_item
如果xxx()
始终返回相同的值,则xxx_init()
应将const_item
设置为1
,否则应设置为0
。
集合函数的 UDF 调用 Sequences
本节介绍创建聚合 UDF 时需要定义的不同功能。有关 MySQL 调用这些函数的 Sequences 的信息,请参见用户定义功能接口功能。
xxx_reset()
当 MySQL 在新组中找到第一行时,将调用此函数。它应该重置所有内部摘要变量,然后使用给定的UDF_ARGS
参数作为该组的内部摘要值中的第一个值。声明xxx_reset()
如下:
void xxx_reset(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);
在 MySQL 5.7(UDF 接口使用xxx_clear()
代替)中,不需要或使用xxx_reset()
。但是,如果要使 UDF 与服务器的较早版本一起使用,则可以定义xxx_reset()
和xxx_clear()
。 (如果确实包含这两个函数,则在许多情况下,可以在内部实现xxx_reset()
函数,方法是调用xxx_clear()
以重置所有变量,然后调用xxx_add()
以将UDF_ARGS
参数添加为组中的第一个值.)
xxx_clear()
当 MySQL 需要重置汇总结果时,将调用此函数。每个新组的开头都会调用它,但是也可以调用它来重置没有匹配行的查询的值。声明xxx_clear()
如下:
void xxx_clear(UDF_INIT *initid, char *is_null, char *error);
在调用xxx_clear()
之前,将is_null
设置为指向CHAR(0)
。
如果出现问题,可以将值存储在error
参数指向的变量中。 error
指向单字节变量,而不是字符串缓冲区。
MySQL 5.7 需要xxx_clear()
。
xxx_add()
属于同一组的所有行均调用此函数。您应该使用它将UDF_ARGS
参数中的值添加到内部摘要变量中。
void xxx_add(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);
聚合 UDF 的xxx()
函数的声明方式应与非聚合 UDF 相同。参见简单函数的 UDF 调用序列。
对于聚合 UDF,在处理完该组中的所有行后,MySQL 会调用xxx()
函数。通常,您通常不应在此处访问其UDF_ARGS
参数,而应根据内部摘要变量返回一个值。
xxx()
中的返回值处理应与非聚合 UDF 相同。参见UDF 返回值和错误处理。
xxx_reset()
和xxx_add()
函数处理其UDF_ARGS
参数的方式与非聚合 UDF 的函数相同。参见UDF 参数处理。
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
作为组函数的结果。
UDF 参数处理
args
参数指向具有以下列出的成员的UDF_ARGS
结构:
unsigned int arg_count
参数的数量。如果您需要使用特定数量的参数来调用函数,请在初始化函数中检查该值。例如:
if (args->arg_count != 2)
{
strcpy(message,"XXX() requires two arguments");
return 1;
}
对于其他作为数组的UDF_ARGS
成员值,数组引用从零开始。也就是说,使用索引值从 0 到args->arg_count
-1 引用数组成员。
enum Item_result *arg_type
指向包含每个参数类型的数组的指针。可能的类型值为STRING_RESULT
,INT_RESULT
,REAL_RESULT
和DECIMAL_RESULT
。
要确保参数为给定类型,如果参数不是给定类型,则返回错误,请检查初始化函数中的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;
}
类型DECIMAL_RESULT
的参数作为字符串传递,因此您使用与STRING_RESULT
值相同的方式处理它们。
除了要求函数的参数为特定类型之外,还可以使用初始化函数将arg_type
元素设置为所需的类型。这导致 MySQL 对每次xxx()
的调用都将这些类型的参数强制转换为这些类型。例如,要指定前两个参数应分别强制为字符串和整数,请在xxx_init()
中执行此操作:
args->arg_type[0] = STRING_RESULT;
args->arg_type[1] = INT_RESULT;
精确值的十进制参数(例如1.3
或DECIMAL列值)以DECIMAL_RESULT
类型传递。但是,值以字符串形式传递。要接收数字,请使用初始化函数指定参数应强制为REAL_RESULT
值:
args->arg_type[2] = REAL_RESULT;
char **args
args->args
将有关传递给函数的参数的一般性质的信息传达给初始化函数。对于常量参数i
,args->args[i]
指向参数值。 (有关如何正确访问该值的说明,请参阅后面的内容.)对于非恒定参数,args->args[i]
是0
。常量参数是仅使用常量的 table 达式,例如3
或4*7-2
或SIN(3.14)。非恒定参数是一个 table 达式,它引用的行之间可能会发生变化,例如列名或使用非恒定参数调用的函数。
对于主函数的每次调用,args->args
包含为当前正在处理的行传递的实际参数。
如果参数i
table 示NULL
,则args->args[i]
是空指针(0)。如果参数不是NULL
,则函数可以按以下方式引用它:
-
类型
STRING_RESULT
的参数作为字符串指针加一个长度给出,以便能够处理二进制数据或任意长度的数据。字符串内容为args->args[i]
,字符串长度为args->lengths[i]
。不要假设该字符串以空值结尾。- 对于类型为
INT_RESULT
的参数,必须将args->args[i]
强制转换为long long
值:
- 对于类型为
long long int_val;
int_val = *((long long*) args->args[i]);
- 对于类型为
REAL_RESULT
的参数,必须将args->args[i]
强制转换为double
值:
double real_val;
real_val = *((double*) args->args[i]);
-
对于
DECIMAL_RESULT
类型的参数,该值作为字符串传递,应像STRING_RESULT
值一样处理。 -
ROW_RESULT
参数未实现。 -
unsigned long *lengths
对于初始化函数,lengths
数组指示每个参数的最大字符串长度。您不应该更改这些。对于主函数的每次调用,lengths
包含为当前正在处理的行传递的任何字符串参数的实际长度。对于INT_RESULT
或REAL_RESULT
类型的参数,lengths
仍包含参数的最大长度(对于初始化函数)。
char *maybe_null
对于初始化函数,maybe_null
数组为每个参数指示参数值是否可能为 null(如果为否,则为 0,如果为,则为 1)。
char **attributes
args->attributes
传达有关 UDF 参数名称的信息。对于参数i
,属性名称可以作为args->attributes[i]
中的字符串使用,并且属性长度为args->attribute_lengths[i]
。不要假设该字符串以空值结尾。
默认情况下,UDF 参数的名称是用于指定参数的 table 达式的文本。对于 UDF,参数还可以具有可选的[AS] alias_name
子句,在这种情况下,参数名称为* alias_name
*。因此,每个参数的attributes
值取决于是否给定别名。
假设如下调用了 UDF my_udf()
:
SELECT my_udf(expr1, expr2 AS alias1, expr3 alias2);
在这种情况下,attributes
和attribute_lengths
数组将具有以下值:
args->attributes[0] = "expr1"
args->attribute_lengths[0] = 5
args->attributes[1] = "alias1"
args->attribute_lengths[1] = 6
args->attributes[2] = "alias2"
args->attribute_lengths[2] = 6
unsigned long *attribute_lengths
attribute_lengths
数组指示每个参数名称的长度。
UDF 返回值和错误处理
如果没有发生错误,初始化函数应返回0
,否则返回1
。如果发生错误,则xxx_init()
应该在message
参数中存储以空值结尾的错误消息。该消息返回给 Client 端。消息缓冲区的长度为MYSQL_ERRMSG_SIZE
个字符。尝试使消息的字符数少于 80 个,以使其适合标准终端屏幕的宽度。
对于long long
和double
函数,主要函数xxx()
的返回值是函数值。字符串函数应返回一个指向结果的指针,并将*length
设置为返回值的长度(以字节为单位)。例如:
memcpy(result, "result string", 13);
*length = 13;
MySQL 使用result
参数将缓冲区传递给xxx()
函数。此缓冲区足够长,可以容纳 255 个字符,可以是多字节字符。 xxx()
函数可以在适合的情况下将结果存储在此缓冲区中,在这种情况下,返回值应该是指向缓冲区的指针。如果函数将结果存储在另一个缓冲区中,则应返回一个指向该缓冲区的指针。
如果您的字符串函数不使用提供的缓冲区(例如,如果它需要返回长度超过 255 个字符的字符串),则必须在xxx_init()
函数或xxx()
函数中使用malloc()
为自己的缓冲区分配空间,并在其中释放空间您的xxx_deinit()
函数。您可以将分配的内存存储在UDF_INIT
结构的ptr
插槽中,以供将来的xxx()
调用重用。参见简单函数的 UDF 调用序列。
要在主函数中指示返回值NULL
,请将*is_null
设置为1
:
*is_null = 1;
要指示主要功能返回错误,请将*error
设置为1
:
*error = 1;
如果xxx()
将任何行的*error
设置为1
,则当前行以及由调用XXX()
的语句处理的任何后续行的函数值为NULL
。 (后续行甚至不调用xxx()
.)
UDF 编译和安装
必须将实现 UDF 的文件编译并安装在服务器运行所在的主机上。这里针对 MySQL 源代码分发中包含的示例 UDF 文件sql/udf_example.cc
描述了该过程。有关 UDF 安装的其他信息,请参见第 5.6.2 节“安装和卸载用户定义的函数”。
如果将在复制到从属服务器的语句中引用 UDF,则必须确保每个从属也具有可用功能。否则,当从站尝试调用该函数时,复制将失败。
udf_example.cc
文件包含以下功能:
-
metaphon()
返回字符串参数的变音字符串。这有点像 soundex 字符串,但更适合英语。 -
myfunc_double()
返回其参数中字符的 ASCII 值之和,除以其参数长度之和。 -
myfunc_int()
返回其参数长度的总和。 -
sequence([const int])
返回从给定数字开始的序列,如果没有给定数字,则返回 1. -
lookup()
返回主机名的 IP 地址。 -
reverse_lookup()
返回 IP 地址的主机名。可以使用格式为'xxx.xxx.xxx.xxx'
的单个字符串参数或四个数字来调用该函数。 -
avgcost()
返回平均费用。这是一个汇总函数。
在 Unix 和类似 Unix 的系统上,使用以下过程来编译用户定义的函数:
使用以下命令将可动态加载的文件编译为可共享的库文件:
gcc -shared -o udf_example.so udf_example.cc
如果您将 gcc 与 CMake 一起使用(这是 MySQL 本身的配置方式),则应该能够使用更简单的命令创建udf_example.so
:
make udf_example
编译包含 UDF 的共享库后,必须安装它并告知 MySQL。使用 gcc 从udf_example.cc
编译共享对象会直接产生一个名为udf_example.so
的文件。将共享库复制到服务器的插件目录,并将其命名为udf_example.so
。该目录由plugin_dir系统变量的值给出。
在某些系统上,配置动态链接程序的 ldconfig 程序无法识别共享库,除非其名称以lib
开头。在这种情况下,您应该将udf_example.so
之类的文件重命名为libudf_example.so
。
在 Windows 上,使用以下过程编译用户定义的函数:
-
获取一个 MySQL 源代码分发。参见第 2.1.2 节“如何获取 MySQL”。
-
如有必要,请从http://www.cmake.org获得 CMake 构建 Util。 (需要 2.6 或更高版本)。
-
在源代码树中,在
sql
目录中查找名为udf_example.def
和udf_example.cc
的文件。将两个文件从此目录复制到您的工作目录。 -
使用以下内容创建一个 CMake
makefile
(CMakeLists.txt
):
PROJECT(udf_example)
# Path for MySQL include directory
INCLUDE_DIRECTORIES("c:/mysql/include")
ADD_DEFINITIONS("-DHAVE_DLOPEN")
ADD_LIBRARY(udf_example MODULE udf_example.cc udf_example.def)
TARGET_LINK_LIBRARIES(udf_example wsock32)
- 创建 VC 项目和解决方案文件,用适当的*
generator
*值代替:
cmake -G "generator"
调用 cmake --help 会向您显示有效生成器的列 table。
- 创建
udf_example.dll
:
devenv udf_example.sln /build Release
在所有平台上,将共享库文件复制到plugin_dir目录后,使用以下语句将新功能通知mysqld。每个平台的文件名后缀都不同(例如,对于 Unix 和类 Unix 系统,为.so
;对于 Windows 为.dll
),因此请根据需要调整平台的.so
后缀。
CREATE FUNCTION metaphon RETURNS STRING
SONAME 'udf_example.so';
CREATE FUNCTION myfunc_double RETURNS REAL
SONAME 'udf_example.so';
CREATE FUNCTION myfunc_int RETURNS INTEGER
SONAME 'udf_example.so';
CREATE FUNCTION sequence RETURNS INTEGER
SONAME 'udf_example.so';
CREATE FUNCTION lookup RETURNS STRING
SONAME 'udf_example.so';
CREATE FUNCTION reverse_lookup RETURNS STRING
SONAME 'udf_example.so';
CREATE AGGREGATE FUNCTION avgcost RETURNS REAL
SONAME 'udf_example.so';
安装后,功能将保持安装状态,直到将其卸载。
要删除功能,请使用DROP FUNCTION:
DROP FUNCTION metaphon;
DROP FUNCTION myfunc_double;
DROP FUNCTION myfunc_int;
DROP FUNCTION sequence;
DROP FUNCTION lookup;
DROP FUNCTION reverse_lookup;
DROP FUNCTION avgcost;
CREATE FUNCTION和DROP FUNCTION语句更新mysql
系统数据库中用作 UDF 注册 table 的func
table。函数的名称,类型和共享库名称保存在mysql.func
table 中。要创建函数,您必须对mysql
数据库具有INSERT特权。要删除函数,您必须对mysql
数据库具有DELETE特权。
您不能使用CREATE FUNCTION重新安装以前安装的功能。要重新安装功能,请先使用DROP FUNCTION将其删除,然后使用CREATE FUNCTION进行安装。例如,如果升级到提供该函数的更新实现的 MySQL 新版本,或者重新编译已编写的函数的新版本,则需要执行此操作。否则,服务器将 continue 使用旧版本。
活动函数是已被CREATE FUNCTION加载但未被DROP FUNCTION删除的函数。每次服务器启动时都会重新加载所有活动功能,除非您使用--skip-grant-tables选项启动mysqld。在这种情况下,服务器在启动期间不会加载 UDF,并且 UDF 不可用。
UDF 安全注意事项
MySQL 采取了几种措施来防止滥用用户定义的函数。
UDF 库文件不能放在任意目录中。它们必须位于服务器的插件目录中。该目录由plugin_dir系统变量的值给出。
要使用CREATE FUNCTION或DROP FUNCTION,您必须分别对mysql
数据库具有INSERT或DELETE特权。这是必需的,因为这些语句在mysql.func
table 中添加和删除行。
除了与xxx()
主功能对应的xxx
符号外,UDF 还应至少定义一个符号。这些辅助符号对应于xxx_init()
,xxx_deinit()
,xxx_reset()
,xxx_clear()
和xxx_add()
功能。 mysqld还支持--allow-suspicious-udfs选项,该选项控制是否可以加载仅包含xxx
符号的 UDF。默认情况下,该选项为禁用状态,以防止尝试从共享库文件(而不是包含合法 UDF 的文件)中加载功能。如果您有较旧的 UDF,它们仅包含xxx
符号,并且无法重新编译以包含辅助符号,则可能需要指定--allow-suspicious-udfs选项。否则,应避免启用它。