扩展 MySQL 8.0  / 第 6 章 向 MySQL 添加函数  /  6.2 添加可加载函数

6.2 添加可加载函数

用于可加载函数的 MySQL 接口提供以下特性和功能:

  • 函数可以返回字符串、整数或实数值,并且可以接受相同类型的参数。

  • 您可以定义一次对单行进行操作的简单函数,或对多组行进行操作的聚合函数。

  • 向函数提供的信息使它们能够检查传递给它们的参数的数量、类型和名称。

  • 您可以告诉 MySQL 在将参数传递给函数之前将它们强制转换为给定类型。

  • 您可以指示函数返回 NULL或发生错误。

要使可加载函数机制起作用,函数必须用 C 或 C++ 编写,并且您的操作系统必须支持动态加载。MySQL 源代码分发包括一个文件 ,该文件sql/udf_example.cc定义了五个可加载函数接口函数。查阅此文件以了解可加载函数调用约定的工作原理。头 include/mysql_com.h文件定义了可加载函数相关的符号和数据结构,虽然你不需要直接包含这个头文件;它包含在mysql.h.

可加载函数包含成为正在运行的服务器的一部分的代码,因此当您编写可加载函数时,您将受到适用于编写服务器代码的任何和所有约束的约束。例如,如果您尝试使用libstdc++库中的函数,您可能会遇到问题。这些约束在服务器的未来版本中可能会发生变化,因此服务器升级可能需要修改最初为旧服务器编写的可加载函数。有关这些约束的信息,请参阅 MySQL Source-Configuration OptionsDealing with Problems Compiling MySQL

为了能够使用可加载函数,您必须动态链接 mysqld。如果您想使用需要从 mysqld访问符号的可加载函数(例如, uses 中的metaphone函数 ),您必须将程序链接到(请参阅参考资料)。 sql/udf_example.ccdefault_charset_info-rdynamicman dlopen

对于要在 SQL 语句中使用的每个函数,您应该定义相应的 C(或 C++)函数。在下面的讨论中,名称xxx用作示例函数名称。为了区分SQL和C/C++的用法,XXX()(大写)表示SQL函数调用,xxx()(小写)表示C/C++函数调用。

笔记

使用 C++ 时,将 C 函数封装在此结构中:

extern "C" { ... }

这确保您的 C++ 函数名称在完成的函数中保持可读。

可加载函数接口函数

以下列表描述了您为实现名为 的函数的接口而编写的 C/C++ 函数 XXX()。主函数 xxx()是必需的。此外,出于可加载函数安全预防措施中讨论的原因,可加载函数至少需要此处描述的其他函数之一 。

  • xxx()

    主要功能。这是计算函数结果的地方。SQL 函数数据类型与 C/C++ 函数的返回类型之间的对应关系如下所示。

    类型 C/C++ 类型
    STRING char *
    INTEGER long long
    REAL double

    也可以声明一个 DECIMAL函数,但返回的值是一个字符串,所以你应该像写函数一样编写STRING 函数。ROW功能没有实现。

  • xxx_init()

    的初始化函数xxx()。如果存在,它可用于以下目的:

    • 检查参数的数量 XXX()

    • 验证参数是否为所需类型,或者告诉 MySQL 在调用 main 函数时将参数强制为所需类型。

    • 分配主函数所需的任何内存。

    • 指定结果的最大长度。

    • 指定(对于REAL 函数)结果中的最大小数位数。

    • 指定结果是否可以 NULL

  • xxx_deinit()

    的去初始化函数 xxx()。如果存在,它应该释放初始化函数分配的任何内存。

当 SQL 语句调用XXX()时,MySQL 调用初始化函数 xxx_init()让它执行任何所需的设置,例如参数检查或内存分配。如果 xxx_init()返回错误,MySQL 将中止带有错误消息的 SQL 语句,并且不会调用主函数或去初始化函数。xxx()否则,MySQL为每一行调用一次主函数。处理完所有行后,MySQL 调用取消初始化函数xxx_deinit(),以便它可以执行任何需要的清理。

对于像 一样工作的聚合函数 SUM(),您还必须提供以下函数:

  • xxx_clear()

    重置当前聚合值但不插入参数作为新组的初始聚合值。

  • xxx_add()

    将参数添加到当前聚合值。

MySQL 处理聚合可加载函数如下:

  1. 调用xxx_init()以让聚合函数分配存储结果所需的任何内存。

  2. 根据GROUP BY表达式对表格进行排序。

  3. 调用xxx_clear()每个新组中的第一行。

  4. 调用xxx_add()属于同一组的每一行。

  5. 当组更改时或处理完最后一行后调用xxx()以获取聚合结果。

  6. 重复步骤 3 到 5,直到处理完所有行

  7. 调用xxx_deinit()让函数释放它分配的任何内存。

所有函数都必须是线程安全的。这不仅包括主函数,还包括初始化和取消初始化函数,以及聚合函数所需的附加函数。此要求的结果是不允许您分配任何更改的全局或静态变量!如果您需要内存,则必须在 中分配它并在 中xxx_init()释放它 xxx_deinit()

简单函数的可加载函数调用序列

本节介绍您必须定义以创建简单的可加载函数的不同接口函数。有关 MySQL 调用这些函数的顺序的信息,请参阅 Loadable Function Interface Functions

xxx()应按本节所示声明 主函数。请注意,返回类型和参数会有所不同,具体取决于您是否将 SQL 函数声明XXX()为 return STRINGINTEGERREALCREATE 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函数返回字符串值并以与函数相同的方式声明 STRINGROW 功能没有实现。

像这样声明初始化和取消初始化函数:

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_init()应该设置 maybe_null1if xxx()can return NULL。默认值是 1是否声明了任何参数 maybe_null

  • unsigned int decimals

    小数点右边的小数位数。默认值是传递给主函数的参数中的最大小数位数。例如,如果函数传递 1.341.3451.3,则默认值为 3,因为 1.345有 3 个小数位。

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

    decimals31 用于参数,如声明的 FLOATDOUBLE列没有明确的小数位数(例如, 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是使用或释放​​内存。

  • my_bool const_item

    xxx_init()如果 总是返回相同的值,则应设置 const_item为,否则应设置为。 1xxx()0

聚合函数的可加载函数调用序列

本节介绍创建聚合可加载函数时需要定义的不同接口函数。有关 MySQL 调用这些函数的顺序的信息,请参阅 Loadable Function Interface Functions

  • xxx_reset()

    当 MySQL 找到新组中的第一行时调用此函数。它应该重置任何内部摘要变量,然后使用给定的 UDF_ARGS参数作为组的内部摘要值中的第一个值。声明 xxx_reset()如下:

    void xxx_reset(UDF_INIT *initid, UDF_ARGS *args,
                   char *is_null, char *error);

    xxx_reset()在 MySQL 5.7 中不需要或使用,其中使用可加载函数接口xxx_clear()。但是,您可以同时定义两者 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);

    is_nullCHAR(0)在调用 之前 设置为指向 xxx_clear()

    如果出现问题,您可以在error参数指向的变量中存储一个值。error指向单字节变量,而不是字符串缓冲区。

    xxx_clear()MySQL 5.7 要求。

  • xxx_add()

    为属于同一组的所有行调用此函数。您应该使用它将 UDF_ARGS参数中的值添加到内部摘要变量。

    void xxx_add(UDF_INIT *initid, UDF_ARGS *args,
                 char *is_null, char *error);

聚合可加载函数的xxx()函数声明方式应与非聚合可加载函数相同。请参阅 简单函数的可加载函数调用序列

对于聚合可加载函数,MySQL xxx()在处理完组中的所有行后调用该函数。你通常不应该 UDF_ARGS在这里访问它的参数,而是根据你的内部摘要变量返回一个值。

处理返回值的方式xxx()应与处理非聚合可加载函数的方式相同。请参阅 可加载函数返回值和错误处理

xxx_reset()xxx_add()函数处理它们的 UDF_ARGS参数的方式与非聚合可加载函数的函数相同 。请参阅 可加载函数参数处理

和的指针参数对于对、 is_null和 的error所有调用都是相同 的。您可以使用它来记住您遇到了一个错误或 函数是否应该返回。您不应该将字符串存储到! 指向单字节变量,而不是字符串缓冲区。 xxx_reset()xxx_clear()xxx_add()xxx()xxx()NULL*errorerror

*is_null为每个组重置(在调用之前xxx_clear())。 *error永远不会重置。

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

可加载函数参数处理

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. 常量参数是仅使用常量的表达式,例如3or 4*7-2SIN(3.14)。非常量参数是一个表达式,它引用可能在行与行之间发生变化的值,例如列名或使用非常量参数调用的函数。

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

    如果参数i表示 NULLargs->args[i]则为空指针 (0)。如果参数不是NULL,函数可以按如下方式引用它:

    • 类型参数STRING_RESULT 以字符串指针加长度的形式给出,以支持处理二进制数据或任意长度的数据。字符串内容可用, args->args[i]字符串长度为args->lengths[i]。不要假设字符串以 null 结尾。

    • 对于 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_RESULTor 类型的参数REAL_RESULTlengths 仍然包含参数的最大长度(对于初始化函数)。

  • char *maybe_null

    对于初始化函数,该 maybe_null数组指示每个参数的参数值是否可能为空(如果否则为 0,如果是则为 1)。

  • char **attributes

    args->attributes传达有关函数参数名称的信息。对于 argument i,属性名称可作为字符串使用 args->attributes[i],属性长度为 args->attribute_lengths[i]。不要假设字符串以 null 结尾。

    默认情况下,函数参数的名称是用于指定参数的表达式的文本。对于可加载函数,参数也可以有一个可选 子句,在这种情况下参数名称是 . 因此,每个参数的 值取决于是否给出了别名。 [AS] alias_namealias_nameattributes

    假设一个可加载函数 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数组指示每个参数名称的长度。

可加载函数返回值和错误处理

0 如果没有发生错误 ,初始化函数应该返回,1否则。如果发生错误,应在参数xxx_init()中存储一个以 null 结尾的错误消息 。message消息返回给客户端。消息缓冲区是 MYSQL_ERRMSG_SIZE字符长的。尝试将消息保持在 80 个字符以内,以适应标准终端屏幕的宽度。

main函数的返回值xxx() 就是函数值,forlong longdoublefunctions。字符串函数应返回指向结果的指针,并设置 *length为返回值的长度(以字节为单位)。例如:

memcpy(result, "result string", 13);
*length = 13;

xxx()MySQL使用参数 将缓冲区传递给函数result。此缓冲区足够长以容纳 255 个字符,这些字符可以是多字节字符。如果适合,该xxx()函数可以将结果存储在此缓冲区中,在这种情况下,返回值应该是指向缓冲区的指针。如果函数将结果存储在不同的缓冲区中,它应该返回指向该缓冲区的指针。

如果您的字符串函数不使用提供的缓冲区(例如,如果它需要返回一个长度超过 255 个字符的字符串),您必须malloc()xxx_init()函数或 xxx()函数中为您自己的缓冲区分配空间并在函数中释放它 xxx_deinit()。您可以将分配的内存存储在结构中的ptr插槽中,以 UDF_INIT供将来 xxx()调用重用。请参阅 简单函数的可加载函数调用序列

NULL要在主函数中 指示返回值,请设置*is_null1

*is_null = 1;

要指示主函数中的错误返回,请设置 *error1

*error = 1;

如果为任何行xxx()设置*error1,则函数值 NULL适用于当前行以及 XXX()调用语句处理的任何后续行。(xxx()甚至不会为后续行调用。)

可加载函数编译安装

实现可加载功能的文件必须在服务器运行的主机上编译安装。sql/udf_example.cc此处针对MySQL 源代码分发中包含的示例可加载函数文件描述了该过程 。有关可加载函数安装的其他信息,请参阅 安装和卸载可加载函数

如果将在将复制到副本的语句中引用可加载函数,则必须确保每个副本也具有可用的函数。否则,当副本尝试调用该函数时,副本上的复制将失败。

udf_example.cc文件包含以下函数:

  • metaphon()返回字符串参数的元音字符串。这有点像 soundex 字符串,但更适合英语。

  • myfunc_double()返回其参数中字符的 ASCII 值之和除以其参数长度之和。

  • myfunc_int()返回其参数长度的总和。

  • sequence([const int])返回从给定数字开始的序列,如果没有给出数字则返回 1。

  • lookup()返回主机名的 IP 地址。

  • reverse_lookup()返回 IP 地址的主机名。该函数可以使用 form 的单个字符串参数 'xxx.xxx.xxx.xxx'或四个数字调用。

  • avgcost()返回平均成本。这是一个聚合函数。

在 Unix 和类 Unix 系统上,使用以下过程编译可加载函数:

一个可动态加载的文件应该被编译为一个可共享的库文件,使用类似这样的命令:

gcc -shared -o udf_example.so udf_example.cc

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

make udf_example

编译包含可加载函数的共享对象后,您必须安装它并将它告诉 MySQL。udf_example.cc使用 gcc直接编译共享对象会生成一个名为 udf_example.so. 将共享对象复制到服务器的插件目录并将其命名为 udf_example.so. 该目录由 plugin_dir系统变量的值给出。

在某些系统上,配置动态链接器的ldconfig程序无法识别共享对象,除非其名称以lib. 在这种情况下,您应该将文件重命名 udf_example.solibudf_example.so.

在 Windows 上,使用以下过程编译可加载函数:

  1. 获取 MySQL 源代码分发。请参阅 如何获取 MySQL

  2. 如有必要,请从http://www.cmake.org 获取CMake构建实用程序。(需要 2.6 或更高版本)。

  3. 在源代码树中,在sql 目录中查找名为 udf_example.def和 的文件udf_example.cc。将这两个文件从该目录复制到您的工作目录。

  4. 使用以下内容 创建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)
  5. 创建 VC 项目和解决方案文件,替换为适当的generator值:

    cmake -G "generator"

    调用cmake --help会向您显示有效生成器的列表。

  6. 创建udf_example.dll

    devenv udf_example.sln /build Release

在所有平台上,将共享库文件复制到plugin_dir 目录后,使用以下语句通知mysqld有关新功能的信息。文件名后缀因平台而异(例如,.so 对于 Unix 和类 Unix 系统,.dll对于 Windows),因此.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 FUNCTIONand DROP FUNCTION语句更新 mysql.func用作可加载函数注册表的系统表 。 这些语句分别需要数据库的 INSERTDELETE权限mysql

在正常的启动序列中,服务器加载mysql.func表中注册的函数。如果服务器以该 --skip-grant-tables选项启动,则表中注册的功能不会加载且不可用。

可加载函数安全预防措施

MySQL 采取了几种措施来防止滥用可加载函数。

可加载的函数库文件不能放在任意目录下。它们必须位于服务器的插件目录中。该目录由 plugin_dir系统变量的值给出。

要使用 CREATE FUNCTIONor DROP FUNCTION,您必须 分别对数据库具有INSERTor 权限。这是必要的,因为这些语句在表中添加和删除行 。 DELETEmysqlmysql.func

可加载函数除了与主函数xxx对应 的符号外,还应至少定义一个符号。xxx()这些辅助符号对应于xxx_init()xxx_deinit()xxx_reset()xxx_clear()xxx_add()功能。 mysqld还支持一个 --allow-suspicious-udfs选项,该选项控制是否可以加载只有一个 xxx符号的 Loadable 函数。默认情况下,该选项被禁用,以防止尝试从包含合法可加载函数的共享库文件以外的共享库文件加载函数。如果您有仅包含xxx符号并且不能重新编译以包含辅助符号,则可能需要指定该 --allow-suspicious-udfs选项。否则,您应该避免启用它。