添加用户定义的功能

为了使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：

1. 调用xxx_init()以使聚合函数分配存储结果所需的任何内存。
2. 根据GROUP BY表达式对表进行排序。
3. 调用xxx_clear()每个新组中的第一行。
4. 调用xxx_add()属于同一组的每一行。
5. xxx()当组更改时或处理完最后一行后，调用以获取聚合结果。
6. 重复步骤3到5，直到处理完所有行
7. 调用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为1if xxx()始终返回相同的值，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 作为组函数的结果返回。*errorxxx()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_namealias_nameattributes

  假设my_udf()如下调用UDF ：

  SELECT my_udf(expr1, expr2 AS alias1, expr3 alias2);
  

  在这种情况下，attributesand 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参数中存储以空值结尾的错误消息。该消息返回给客户端。消息缓冲区为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上，使用以下过程编译用户定义的函数：

1. 获取MySQL源分发。
2. 如果需要，请从http://www.cmake.org 获取CMake构建实用程序。（需要2.6版或更高版本）。
3. 在源代码树中，在sql目录中查找名为udf_example.def和的文件udf_example.cc。将两个文件都从该目录复制到您的工作目录。

4. 使用以下内容创建一个CMakemakefile（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 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选项。否则，应避免启用它。