MySQL 用户自定义函数

• 下载源文件 - 10 Kb
• 下载测试器 - 17 Kb

本教程解释了什么是用户自定义函数（UDF），它有什么作用以及为什么/何时它们有用。

1. 什么是用户自定义函数？

基本上，用户自定义函数（UDF）是一段代码，通过添加一个新函数来扩展 MySQL 服务器的功能，该函数的行为就像一个原生（内置）MySQL 函数，例如 abs() 或 concat()。UDF 用 C 语言（如果你真的需要，也可以用 C++）编写！……嗯，也许有办法用 BASIC、.NET 或其他语言编写，但我不明白为什么会有人想那样做。

2. 为什么/何时 UDF 有用？

顾名思义，当您需要扩展 MySQL 服务器的功能时，UDF 会很有用。这个小表格应该能清楚地说明哪种方法最适合特定情况

方法	速度	语言	开发
存储过程	慢	SQL	~分钟（对于小函数）
UDF	快速	C	~小时
原生函数	快速	C	非常痛苦

“慢”我的意思是：“比其他方法慢”！存储过程仍然比普通 SQL 语句快得多！

关于原生函数的一点解释：您在这里必须编写的代码与 UDF 的代码本质上是相同的。但是您必须将其写入 MySQL 源代码并重新编译整个程序。这**将**（相信我）是一项大量的工作，因为您必须在每个新版本的 MySQL 中一遍又一遍地做。

3. 如何使用 UDF？

这部分真的很简单。当您完成 UDF 后，只需像使用其他任何原生函数一样使用它。例如：“SELECT MyFunction(data1, data2) FROM table”

4. 编写 UDF

现在让我们开始一步一步地编写我们的第一个 UDF

1. 创建一个新的共享库项目（在本例中，我使用了 VC++ 6.0 和一个标准 DLL）

2. 首先我们需要一些头文件。这些头文件要么是标准库头文件，要么来自 MySQL 服务器的 include 目录

   #ifdef STANDARD
   /* STANDARD is defined, don't use any mysql functions */
   #include <stdlib.h>
   #include <stdio.h>
   #include <string.h>
   #ifdef __WIN__
   typedef unsigned __int64 ulonglong;     /* Microsofts 64 bit types */
   typedef __int64 longlong;
   #else
   typedef unsigned long long ulonglong;
   typedef long long longlong;
   #endif /*__WIN__*/
   #else
   #include <my_global.h>
   #include <my_sys.h>
   #endif
   #include <mysql.h>
   #include <ctype.h>
   static pthread_mutex_t LOCK_hostname;

3. 现在我们必须决定我们想要哪种类型的函数。基本上有两种选择

   • 该函数是否为聚合函数？（我们稍后会了解更多关于聚合函数的信息）

   • 函数应该返回哪种类型的返回值？这里我们有 4 种选择

     类型	描述
     STRING (字符串)	字符串字面量。在 C 中转换为 char*
     INTEGER	普通整数。在 C 中转换为 64 位整数
     REAL	浮点数。在 C 中转换为 double
     DECIAML	这个目前还没有真正完成。MySQL 将其视为 STRING

4. 我们先谈谈非聚合函数。现在我们必须声明和实现 MySQL 服务器使用我们的 UDF 所需的一些函数。但首先是一些我们将需要的结构体

   • UDF_INIT:

     类型	名称	描述
     <code> my_bool	maybe_null	如果函数可以返回 NULL，则为 1
     无符号整数	decimals	用于 REAL 函数
     unsigned long	max_length	用于字符串函数
     char *	ptr	函数数据的空闲指针
     my_bool	const_item	如果结果与参数无关，则为 0

   • UDF_ARGS

     类型	名称	描述
     无符号整数	arg_count	参数数量
     enum Item_result *	arg_type	包含参数类型的数组
     char **	args	指向参数的指针数组
     unsigned long *	lengths	参数长度数组（仅字符串需要）
     char *	maybe_null	"maybe_null" 标志数组（如果参数可能为 null，则为 1）
     char **	属性	指向参数属性的指针数组（详情请参阅第 x 章）
     unsigned long *	attribute_lengths	属性长度数组

   现在让我们看看这些函数

   初始化/反初始化

   extern "C" my_bool MyTest_init(UDF_INIT *initid, UDF_ARGS *args, 
                                  char *message)
   {
           // The most important thing to do here is setting up the memory
           // you need...
           // Lets say we need a lonlong type variable to keep a checksum
           // Although we do not need one in this case
           longlong* i = new longlong; // create the variable
           *i = 0;                     // set it to a value
           
           // store it as a char pointer in the pointer variable
           // Make sure that you don`t run in typecasting troubles later!!
           initid->ptr = (char*)i;
           
           // check the arguments format
           if (args->arg_count != 1)
           {
               strcpy(message,"MyTest() requires one arguments");
               return 1;
           }
   
           if (args->arg_type[0] != INT_RESULT)
           {
               strcpy(message,"MyTest() requires an integer");
               return 1;
           }       
           return 0;            
   }
   
   extern "C" void MyTest_deinit(UDF_INIT *initid)
   {
           // Here you have to free the memory you allocated in the 
           // initialization function
           delete (longlong*)initid->ptr;
   }

   实际功能

   extern "C" longlong MyTest(UDF_INIT *initid, UDF_ARGS *args,
                              char *is_null, char *error)
   {
           /* So finally this is the part were we do the real work. This 
           function is called for every record and the current value(s) 
           or better pointers to the current values are stroed in the 
           UDF_ARGS variable. We have to get the values, do our calculation 
           and return the result. NOTE: You can access the memory 
           allocated in MyTest_init through the UDF_INIT variable.
           In this example we will just add 5 to every value...*/
           return *((longlong*)args->args[0])+5;
   }

5. 全部完成！现在我们必须编译库并将其复制到操作系统可以找到它的目录。在 Windows 上，这可以是 PATH 系统变量指定的任何位置。我个人使用 MySQL 服务器的 bin 目录。您必须确保库位于这些目录之一中，否则 MySQL 无法使用它！并且还要确保导出 MySQL 需要的所有函数！

6. 最后，我们必须将它告诉 MySQL。这非常简单：只需执行以下 SQL 命令

   CREATE [AGGREGATE] FUNCTION MyTest 
   RETURNS [INTEGER|STRING|REAL|DECIMAL] SONAME the_libraries_exact_name

   现在你可以像使用其他任何函数一样使用它。

5. 聚合函数

现在来谈谈聚合函数。当您的 UDF 是一个聚合函数时，您必须添加一些额外的函数，并且有些函数的使用方式也不同。调用顺序是

1. 调用 MyTest_init 分配内存（就像一个普通的 UDF）
2. MySQL 根据 GROUP BY 语句对表进行排序
3. 为每个组的第一行调用 MyTest_clear
4. 为属于同一组的每一行调用 MyTest_add
5. 当组改变或最后一行已被处理时，调用 MyTest 获取结果
6. 重复 3 到 5，直到所有行都被处理完毕
7. 调用 MyTest_deinit 释放所有已使用的内存

现在让我们看看聚合函数所需的新函数。在这个例子中，我们将简单地将所有值相加。（就像原生 SUM 函数一样）

void MyTest_clear(UDF_INIT *initid, char *is_null, char *error)
{
        /* The clear function resets the sum to 0 for each new group
        Of course you have to allocate a longlong variable in the init 
        function and assign it to the pointer as seen above */
        *((longlong*)initid->ptr) = 0;
}

void MyTest_add(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)
{
        // For each row the current value is added to the sum
        *((longlong*)initid->ptr) = *((longlong*)initid->ptr) + 
                                    *((longlong*)args->args[0]);
}

longlong MyTest(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)
{
        // And in the end the sum is returned
        return *((longlong*)initid->ptr);
}

6. 高级主题

以下是您在编写更复杂的 UDF 时应了解的一些事项

• 字符串函数应该返回指向结果的指针，并将 *result 和 *length 设置为返回内容和长度。例如

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

  传递给 MyTest 函数的结果缓冲区长度为 255 字节。如果您的结果符合此长度，则无需担心结果的内存分配。

  如果您的字符串函数需要返回一个长度超过 255 字节的字符串，您必须在 MyTest_init 函数或 MyTest 函数中使用 malloc 或 new 来为其分配空间，并在 MyTest_deinit 函数中释放它。您可以将分配的内存存储在 UDF_INIT 结构中的 ptr 槽中，以供未来的 MyTest 调用重复使用。

• 为了在主函数中指示错误返回，将 *error 设置为 1：如果 MyTest() 对任何行将 *error 设置为 1，则对于当前行以及由调用 MyTest() 的语句处理的任何后续行，函数值均为 NULL。

• 更多信息请参阅 MySQL 在线手册

7. 一些指导方针

以下是一些您应该遵循的指导方针，以确保您的 UDF 顺利运行 ;-)

• 不要在 UDF 中调用任何其他应用程序或进程！

• 不要在本地存储任何信息！（这适用于一般的共享库）

• 不要分配任何全局或静态变量！

• 始终检查参数的类型。正如您所看到的，MySQL 将所有内容转换为 char 指针。如果您将字符串文字转换为整数指针等等，这可能会导致严重的问题。

• 内存分配要格外小心！如果您有内存泄漏，很快就会导致服务器崩溃。

8. 调试 UDF

调试 UDF 可能非常令人 нер烦，因为每次 UDF 崩溃时，它都会把整个 MySQL 服务器一起拖垮。因此，我编写了一个小的命令行工具来解决这个问题。编译后执行它，它会完成剩下的工作。这意味着，它通过调用“SELECT”命令，然后将结果传递给库，并在命令行上打印结果来模拟函数调用。这样，当 UDF 产生严重错误时，只有这个小助手会崩溃，而不是整个服务器。它仍处于“测试版”，所以不要期望太高……

9. 来源

MySQL 在线手册