添加用户定义的功能
为了使UDF机制起作用,必须使用C ++编写函数,并且您的操作系统必须支持动态加载。MySQL源代码发行版包含一个sql/udf_example.cc
定义五个UDF函数的文件。请查阅此文件,以了解UDF调用约定如何工作。该include/mysql_com.h
头文件定义UDF相关的符号和数据结构,虽然你不必直接包含这个头文件;它包含在中mysql.h
。
UDF包含成为正在运行的服务器的一部分的代码,因此在编写UDF时,您将受到适用于编写服务器代码的所有约束的约束。例如,如果您尝试使用libstdc++
库中的函数,则可能会遇到问题。这些限制在将来的服务器版本中可能会更改,因此服务器升级可能需要对最初为较旧的服务器编写的UDF进行修订。有关这些约束的信息,请参见“ MySQL源配置选项”和“处理MySQL编译问题”。
为了能够使用UDF,必须动态链接mysqld。如果你想使用UDF需要访问符号从mysqld的(例如,metaphone
在功能sql/udf_example.cc
用途default_charset_info
),您必须使用链接程序-rdynamic
(见man dlopen
)。
对于要在SQL语句中使用的每个函数,应定义相应的C ++函数。在下面的讨论中,名称“ xxx ”用作示例功能名称。为了区分SQL和C ++的用法,XXX()
(大写)表示SQL函数调用,xxx()
(小写)表示C ++函数调用。
注意使用C ++时,请将C函数封装在以下结构中:
extern "C" { ... }
这样可以确保您的C ++函数名称在完整的UDF中保持可读性。
- 用户定义功能接口功能
- 简单函数的UDF调用序列
- 集合函数的UDF调用序列
- UDF参数处理
- UDF返回值和错误处理
- 用户定义的功能字符集处理
- UDF编译和安装
- UDF安全注意事项
用户定义功能接口功能
下表描述了为实现名为的函数的接口而编写的C ++函数XXX()
。xxx()
需要主要功能。另外,出于UDF安全注意事项中讨论的原因,UDF至少需要这里描述的其他功能之一。
xxx()
主要功能。这是函数结果的计算位置。SQL函数数据类型和C ++函数的返回类型之间的对应关系如下所示。
SQL类型 C ++类型 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()
每行调用一次main函数。处理完所有行后,MySQL调用反初始化函数,xxx_deinit()
以便它可以执行任何必需的清除操作。
对于类似于的聚合函数SUM()
,您还必须提供以下函数:
xxx_clear()
重置当前合计值,但不要将参数作为新组的初始合计值插入。
xxx_add()
将参数添加到当前合计值。
MySQL按照以下方式处理聚合UDF:
- 调用
xxx_init()
以使聚合函数分配存储结果所需的任何内存。 - 根据
GROUP BY
表达式对表进行排序。 - 调用
xxx_clear()
每个新组中的第一行。 - 调用
xxx_add()
属于同一组的每一行。 xxx()
当组更改时或处理完最后一行后,调用以获取聚合结果。- 重复步骤3到5,直到处理完所有行
- 调用
xxx_deinit()
以让UDF释放已分配的所有内存。
所有功能必须是线程安全的。这不仅包括主要功能,还包括初始化和取消初始化功能,以及聚合功能所需的其他功能。此要求的结果是不允许您分配任何更改的全局或静态变量!如果需要内存,则必须将其放入xxx_init()
并释放xxx_deinit()
。
简单函数的UDF调用序列
本节描述了创建简单UDF时必须定义的不同功能。有关MySQL调用这些函数的顺序的信息,请参见用户定义函数接口函数。
xxx()
应该如本节所示声明 main 函数。注意,返回类型和参数不同,这取决于你是否声明SQL函数XXX()
返回STRING
,INTEGER
或REAL
在CREATE FUNCTION
语句:
对于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
功能未实现。
声明这样的初始化和取消初始化功能:
bool xxx_init(UDF_INIT *initid, UDF_ARGS *args,char *message);void xxx_deinit(UDF_INIT *initid);
该initid
参数将传递给所有三个函数。它指向UDF_INIT
用于在功能之间传递信息的结构。该UDF_INIT
结构成员遵循。初始化函数应填写任何希望更改的成员。(要对成员使用默认值,请保持不变。)
bool maybe_null
xxx_init()
应该设置maybe_null
为1
是否xxx()
可以返回NULL
。默认值是1
是否声明了任何参数maybe_null
。unsigned int decimals
小数点右边的小数位数。默认值为传递给主函数的参数中的最大十进制数字。例如,如果函数传递了
1.34
,1.345
和1.3
,则默认值为 3,因为它1.345
有3个十进制数字。对于那些没有固定小数点后的位数参数,
decimals
值被设置为31,其为1以上的允许的小数的最大数量DECIMAL
,FLOAT
和DOUBLE
数据类型。该值可作为恒定NOT_FIXED_DEC
在mysql_com.h
头文件。甲
decimals
的31值用于在箱子参数诸如FLOAT
或DOUBLE
列上声明无小数的明确数量(例如,FLOAT
而不是FLOAT(10,3)
)和浮点常数如1345E-3
。它也用于可能在函数内转换为数字形式的字符串和其他非数字参数。decimals
成员初始化到的值仅是默认值。可以在函数中更改它以反映实际执行的计算。确定默认值后,将使用最多十进制数的参数。如果小数位数仅NOT_FIXED_DEC
用于其中一个参数,则该数值用于decimals
。unsigned int max_length
结果的最大长度。默认
max_length
值根据函数的结果类型而有所不同。对于字符串函数,默认值为最长参数的长度。对于整数函数,默认值为21位。对于实函数,默认值为13加上由表示的十进制数字initid->decimals
。(对于数字函数,长度包括任何符号或小数点字符。)如果要返回blob值,则可以设置
max_length
为65KB或16MB。该内存未分配,但是如果需要临时存储数据,则该值用于确定使用哪种数据类型。char *ptr
该函数可用于其自身目的的指针。例如,函数可以
initid->ptr
用来相互之间分配已分配的内存。xxx_init()
应该分配内存并将其分配给此指针:initid->ptr = allocated_memory;
在
xxx()
和中xxx_deinit()
,指initid->ptr
使用或释放内存。bool const_item
xxx_init()
应该设置const_item
为1
ifxxx()
始终返回相同的值,0
否则设置为。
集合函数的UDF调用序列
本节介绍创建聚合UDF时需要定义的不同功能。有关MySQL调用这些函数的顺序的信息,请参见用户定义函数接口函数。
xxx_reset()
当MySQL在新组中找到第一行时,将调用此函数。它应该重置所有内部摘要变量,然后将给定
UDF_ARGS
参数用作该组的内部摘要值中的第一个值。声明xxx_reset()
如下:void xxx_reset(UDF_INIT *initid, UDF_ARGS *args,char *is_null,char *error);xxx_reset()
MySQL 8.0中不需要或不使用UDF接口,xxx_clear()
而使用UDF接口代替。但是,您可以同时定义xxx_reset()
和xxx_clear()
,如果你想与旧版本的服务器你的UDF工作。(如果确实包含这两个函数,则xxx_reset()
在许多情况下,可以通过调用xxx_clear()
重置所有变量,然后调用xxx_add()
以将UDF_ARGS
参数添加为组中的第一个值的方式在内部实现该函数。)xxx_clear()
当MySQL需要重置汇总结果时,将调用此函数。每个新组的开头都会调用它,但也可以调用它来重置没有匹配行的查询的值。声明
xxx_clear()
如下:void xxx_clear(UDF_INIT *initid,char *is_null,char *error);is_null
被设置为指向CHAR(0)
之前调用xxx_clear()
。如果出现问题,可以在
error
参数指向的变量中存储一个值。error
指向单字节变量,而不是字符串缓冲区。xxx_clear()
MySQL 8.0是必需的。xxx_add()
属于同一组的所有行都将调用此函数。您应该使用它将
UDF_ARGS
参数中的值添加到内部摘要变量中。void xxx_add(UDF_INIT *initid, UDF_ARGS *args,char *is_null,char *error);
xxx()
聚合UDF 的函数应该以与非聚合UDF相同的方式声明。有关简单函数,请参见 UDF调用序列。
对于聚合UDF,xxx()
在处理完该组中的所有行后,MySQL会调用该函数。通常,您永远不要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
永远不会重置。
如果在返回时设置了*is_null
或,则MySQL 作为组函数的结果返回。*error
xxx()
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
。常量参数是仅使用常量(例如3
或4*7-2
或)的表达式SIN(3.14)
。非恒定参数是一个表达式,它引用的行之间可能会发生变化,例如列名或使用非恒定参数调用的函数。对于主函数的每次调用,都
args->args
包含为当前正在处理的行传递的实际参数。如果参数
i
表示NULL
,args->args[i]
则为空指针(0)。如果参数不是NULL
,则函数可以按以下方式引用它:类型参数
STRING_RESULT
作为字符串指针加一个长度给出,以便能够处理二进制数据或任意长度的数据。字符串内容可用,args->args[i]
字符串长度为args->lengths[i]
。不要假设该字符串以空值结尾。有关字符串参数的更多信息,请参见用户定义函数字符集处理。
对于type参数
INT_RESULT
,必须将其强制args->args[i]
转换为long long
值:long long int_val; int_val = *((long long *) args->args[i]);对于type参数
REAL_RESULT
,必须将其强制args->args[i]
转换为double
值:double real_val; real_val = *((double *) args->args[i]);- 对于type的参数
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参数的名称是用于指定参数的表达式的文本。对于UDF,参数还可以具有可选子句,在这种情况下,参数名称为。因此,每个参数的值取决于是否给定别名。
[AS]alias_name
alias_name
attributes
假设
my_udf()
如下调用UDF :SELECT my_udf(expr1, expr2AS alias1, expr3 alias2);在这种情况下,
attributes
andattribute_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
参数中存储以空值结尾的错误消息。该消息返回给客户端。消息缓冲区为MYSQL_ERRMSG_SIZE
字符长。尝试使消息少于80个字符,以使其适合标准终端屏幕的宽度。
main函数的返回值xxx()
是long long
和double
函数的函数值。字符串函数应返回一个指向结果的指针,并将其设置*length
为返回值的长度(以字节为单位)。例如:
memcpy(result, "result string", 13); *length = 13;
MySQL xxx()
使用result
参数将缓冲区传递给函数。该缓冲区足够长,可以容纳255个字符,可以是多字节字符。该xxx()
功能可如果它适合,在这种情况下返回值应该是一个指针到缓冲区中,结果存储在缓冲区中。如果函数将结果存储在另一个缓冲区中,则应返回一个指向该缓冲区的指针。
如果您的字符串函数不使用提供的缓冲区(例如,如果它需要返回长度超过255个字符的字符串),则必须malloc()
在xxx_init()
函数或xxx()
函数中为自己的缓冲区分配空间,并在函数中释放它xxx_deinit()
。您可以将分配的内存存储在结构的ptr
插槽中,以UDF_INIT
供将来的xxx()
调用重用。有关简单函数,请参见 UDF调用序列。
有关字符串参数的更多信息,请参见用户定义函数字符集处理。
要NULL
在主函数中指示返回值,请设置*is_null
为1
:
*is_null = 1;
要在主函数中指示错误返回,请设置*error
为1
:
*error = 1;
如果xxx()
套*error
到1
任何行,函数值是NULL
当前行,并通过在该语句处理任何后续行XXX()
被调用。(xxx()
甚至不要求后续行。)
用户定义的功能字符集处理
在MySQL 8.0.19之前,用户定义函数(UDF)不考虑字符集或字符串参数或返回值的排序规则。实际上,字符串参数和返回值被视为二进制字符串,这意味着只能可靠地处理包含单字节字符的字符串参数。
从MySQL 8.0.19开始,默认情况下,UDF的行为仍然相同,但是编写UDF的接口已经扩展,使UDF能够确定字符集和字符串参数的排序规则,并返回具有特定字符集和整理。这些功能对于UDF编写者是可选的,他们可以根据需要利用它们。
在与MySQL一起分发的UDF中,与以下功能和扩展相关联的UDF已经过修改,以利用字符集功能:MySQL企业审计,MySQL企业防火墙,MySQL企业数据屏蔽和取消标识,MySQL密钥环(一般用途的钥匙圈UDF(仅限UDF)和组复制。该修改仅在有意义的地方适用。例如,返回加密数据的UDF 旨在返回二进制字符串,而不是字符串。
UDF的字符集功能是使用mysql_udf_metadata
服务器组件服务实现的。有关此服务的信息,请参见MySQL Server Doxygen文档,网址为https://dev.mysql.com/doc/index-other.html(搜索s_mysql_mysql_udf_metadata
和udf_metadata_imp
)。MySQL密钥环UDF的源代码在社区源发行版中可用,并且可以作为希望将自己的UDF修改为可识别字符集的第三方UDF编写者的示例进行检查。
如果UDF接受字符串参数或返回字符串值,并被修改为可识别字符集,则适用以下兼容性注意事项:
- 关于传递给UDF的参数,应用程序将继续工作,因为UDF现在能够处理任何字符集(包括二进制字符串)中的字符串参数。
- 如果UDF返回的字符串结果的字符集与其参数的字符集不同,则UDF必须在内部执行字符集转换。例如,如果UDF接受
latin1
参数但返回utf8mb4
结果,就是这种情况。
UDF编译和安装
必须将实现UDF的文件编译并安装在服务器运行的主机上。这里针对sql/udf_example.cc
MySQL源代码分发中包含的示例UDF文件描述了该过程。有关UDF安装的更多信息,
如果将在复制到从属服务器的语句中引用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。udf_example.cc
使用gcc直接编译共享对象会产生一个名为的文件udf_example.so
。将共享库复制到服务器的插件目录并命名udf_example.so
。该目录由plugin_dir
系统变量的值给出。
在某些系统上,配置动态链接程序的ldconfig程序无法识别共享库,除非其名称以开头lib
。在这种情况下,你应该在文件重命名,例如udf_example.so
到libudf_example.so
。
在Windows上,使用以下过程编译用户定义的函数:
- 获取MySQL源分发。
- 如果需要,请从http://www.cmake.org 获取CMake构建实用程序。(需要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将显示有效生成器的列表。
创建
udf_example.dll
:devenv udf_example.sln /build Release
在所有平台上,将共享库文件复制到plugin_dir
目录后,使用以下语句将新功能通知mysqld。每个平台的文件名后缀都不相同(例如,.so
对于Unix和类似Unix的系统,.dll
对于Windows),因此.so
请根据需要调整平台的后缀。
CREATE FUNCTION metaphonRETURNS STRING SONAME 'udf_example.so';CREATE FUNCTION myfunc_doubleRETURNS REALSONAME 'udf_example.so';CREATE FUNCTION myfunc_intRETURNS INTEGERSONAME 'udf_example.so';CREATE FUNCTION sequenceRETURNS INTEGERSONAME 'udf_example.so';CREATE FUNCTION lookupRETURNS STRING SONAME 'udf_example.so';CREATE FUNCTION reverse_lookupRETURNS STRING SONAME 'udf_example.so';CREATE AGGREGATE FUNCTION avgcostRETURNS REALSONAME '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
语句更新func
表中mysql
充当UDF登记系统数据库。函数的名称,类型和共享库的名称保存在mysql.func
表中。要创建函数,您必须具有数据库的INSERT
特权mysql
。要删除功能,您必须具有数据库的DELETE
特权mysql
。
您不能用于CREATE FUNCTION
重新安装先前已安装的功能。要重新安装功能,请先使用删除它DROP FUNCTION
,然后使用进行安装CREATE FUNCTION
。例如,如果升级到提供该函数的更新实现的MySQL新版本,或者重新编译已编写的函数的新版本,则需要执行此操作。否则,服务器将继续使用旧版本。
活动函数是已加载但CREATE FUNCTION
未被删除的函数DROP FUNCTION
。每次服务器启动时都会重新加载所有活动功能,除非您使用该选项启动mysqld--skip-grant-tables
。在这种情况下,服务器在启动期间不会加载UDF,并且UDF不可用。
UDF安全注意事项
MySQL采取了几种措施来防止滥用用户定义的函数。
UDF库文件不能放在任意目录中。它们必须位于服务器的插件目录中。该目录由plugin_dir
系统变量的值给出。
要使用CREATE FUNCTION
或DROP FUNCTION
,您必须分别具有数据库的INSERT
或DELETE
特权mysql
。这是必需的,因为这些语句会在mysql.func
表中添加和删除行。
除了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
选项。否则,应避免启用它。