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 TypeC/C++ Type
STRINGchar *
INTEGERlong long
REALdouble

也可以声明一个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 BYtable 达式对 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语句中返回STRINGINTEGERREAL

对于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.341.3451.3,则默认值为 3,因为1.345具有 3 个十进制数字。

对于没有固定小数位数的参数,decimals值设置为 31,这比DECIMALFLOATDOUBLE数据类型所允许的最大小数位数大 1.该值在mysql_com.h头文件中作为常量NOT_FIXED_DEC可用。

在诸如FLOATDOUBLE列等声明没有显式小数位数(例如FLOAT而不是FLOAT(10,3))的情况下,31 的值用于参数,以及1345E-3等浮点常量。它也用于可能在函数内转换为数字形式的字符串和其他非数字参数。

decimals成员初始化的值仅是默认值。可以在函数中更改它以反映实际执行的计算。确定默认值后,将使用最多十进制数的参数。如果对于其中一个参数,小数位数为NOT_FIXED_DEC,则该值是decimals的值。

  • unsigned int max_length

结果的最大长度。默认的max_length值因函数的结果类型而异。对于字符串函数,默认值为最长参数的长度。对于整数函数,默认值为 21 位。对于实函数,默认值为 13 加上initid->decimalstable 示的小数位数。 (对于数字函数,长度包括任何符号或小数点字符.)

如果要返回 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_nullerror的指针参数对于xxx_reset()xxx_clear()xxx_add()xxx()的所有调用都相同。您可以使用它来记住您遇到错误或xxx()函数是否应返回NULL。您不应该将字符串存储到*errorerror指向单字节变量,而不是字符串缓冲区。

每个组都会重置*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_RESULTINT_RESULTREAL_RESULTDECIMAL_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.3DECIMAL列值)以DECIMAL_RESULT类型传递。但是,值以字符串形式传递。要接收数字,请使用初始化函数指定参数应强制为REAL_RESULT值:

args->arg_type[2] = REAL_RESULT;
  • char **args

args->args将有关传递给函数的参数的一般性质的信息传达给初始化函数。对于常量参数iargs->args[i]指向参数值。 (有关如何正确访问该值的说明,请参阅后面的内容.)对于非恒定参数,args->args[i]0。常量参数是仅使用常量的 table 达式,例如34*7-2SIN(3.14)。非恒定参数是一个 table 达式,它引用的行之间可能会发生变化,例如列名或使用非恒定参数调用的函数。

对于主函数的每次调用,args->args包含为当前正在处理的行传递的实际参数。

如果参数itable 示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_RESULTREAL_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);

在这种情况下,attributesattribute_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 longdouble函数,主要函数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

如果您将 gccCMake 一起使用(这是 MySQL 本身的配置方式),则应该能够使用更简单的命令创建udf_example.so

make udf_example

编译包含 UDF 的共享库后,必须安装它并告知 MySQL。使用 gccudf_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.defudf_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 FUNCTIONDROP FUNCTION语句更新mysql系统数据库中用作 UDF 注册 table 的functable。函数的名称,类型和共享库名称保存在mysql.functable 中。要创建函数,您必须对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 FUNCTIONDROP FUNCTION,您必须分别对mysql数据库具有INSERTDELETE特权。这是必需的,因为这些语句在mysql.functable 中添加和删除行。

除了与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选项。否则,应避免启用它。