MySQL中文参考手册

翻译：晏子(yanzi)       主页：http://linuxdb.yeah.net

第一章, 前一章, 下一章, 最后一章，目录.

14 为MySQL增加新函数

有2种方法把新函数加到MySQL中：

• 你可以通过用户定义函数(UDF)接口加入函数。用户定义函数用CREATE FUNCTION和DROP FUNCTION语句动态地增加和删除。见7.30 CREATE FUNCTION/DROP FUNCTION句法。
• 你可以加入函数作为一个原生的(内置的)MySQL函数。原生函数被编译进mysqld服务器并且在一个永久的基础上可得到。

每种方法都有优点和缺点：

• 如果你编写一个用户定义函数，你必须安装服务器外还得自己安装对象文件。如果你编译函数进服务器中，你不需要那样做。
• 你能把UDF加到MySQL二进制代码发行中。原生函数要求你修改源代码分发。
• 如果你升级你的MySQL分发，你能继续使用你的以前安装的UDF。对于原生函数，你必须在每次升级时重复你的修改。

无论你使用哪种方法增加新函数，他们可以象原生函数例如ABS()或SOUNDEX()那样使用。

14.1 增加一个新的用户定义函数

对于UDF的工作机制，函数必须用C或C++编写并且你的操作系统必须支持动态装载。MySQL源代码分发包括一个文件“sql/udf_example.cc”，它定义了5个新函数。请教这个文件看UDF调用约定怎样工作。

对每一个你想在SQL语句中使用的函数，你应该定义对应的C(或 C++)函数。在下面的讨论中，“xxx”用于一个函数名的例子。为了区别SQL和C/C++用法，XXX()(大写)表明SQL函数调用，而xxx()((小写)表明C/C++函数调用。

你编写实现XXX()的接口的C/C++函数是：

xxx()（必需的）

主函数。这是计算函数结果的地方。SQL 类型于你的C/C++函数返回类型的对应关系如下：

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

xxx_init()（可选）

为xxx()的初始化函数，它可用于：

• 检查传到XXX()的参数数量。
• 检查参数是一种所需的类型，或，另外地，当主函数被调用时，告诉MySQL，为了强制参数到你想要的类型。
• 分配任何由主函数所需的内存。
• 指定结果的最大长度。
• 指定(对REAL函数)小数位的最大数目。
• 指定结果是否能是NULL。

xxx_deinit()（可选）

为xxx()的结束函数，它应该释放初始化函数分配了的任何内存。

当一条SQL语句调用XXX()时，MySQL调用初始化函数xxx_init()，让它执行任何所需的设置，例如参数检查或内存分配。如果xxx_init()返回一个错误，SQL语句用一条错误消息并被放弃而主函数和结束函数不被调用，否则，为每行调用主函数xxx()一次。在所有行被处理完后，结束函数xxx_deinit()被调用，因此它能执行任何必要的清除。

所有函必须是线程安全的(不只是主函数，还有初始化和结束函数)。这意味着，你不允许分配任何改变的全局或静态变量！如果你需要内存，你应该在xxx_init()种分配它并且在xxx_deinit()中释放它。

14.1.1 UDF的调用顺序

主函数应该如下定义。注意返回类型和参数不同，取决于你是否在CREATE FUNCTION语句中声明SQL函数XXX()返回STRING、INTEGER或REAL：

对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);

初始化和结束函数象这样被声明：

my_bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message);

void xxx_deinit(UDF_INIT *initid);

initid参数被传给所有3个函数，它指向一个UDF_INIT结构，被用来在函数之间传递信息。UDF_INIT结构成员列在下面。初始化函数应该填写它想要改变的任何成员。（对一个成员使用缺省值，不改变它。）

my_bool maybe_null

如果xxx()能返回NULL，xxx_init()应该设置maybe_null为1。如果参数的任何一个被声明maybe_null，缺省值是1。

unsigned int decimals

小数位数目。缺省值是在被传给主函数的参数中小数位的最大数目。（例如，如果函数传递1.34、1.345和1.3，缺省值将是3，因为1.345有3个小数位。

unsigned int max_length

字符串结果的最大长度。缺省值不同，取决于函数的结果类型。对字符串函数，缺省是最长的参数的长度。对整数函数，缺省是21位。对实数函数，缺省是13加上由initid->decimals指出的小数位数。（对数字函数，长度包括任何符号位或小数点字符。）

char *ptr

函数可为它自己的目的使用的一个指针。例如，函数能使用initid->ptr在函数之间传递分配的内存。在xxx_init()中，分配内存并将它赋给这个指针：

initid->ptr = allocated_memory;

在xxx()和xxx_deinit()中，参照initid->ptr来使用或释放内存。

14.1.2 参数处理

args参数指向一个UDF_ARGS成员，其结构列在下面：

unsigned int arg_count

参数个数。如果你想要函数用一个特定数量的参数被调用，在初始化函数中检查这个值。例如：

if (args->arg_count != 2)
{
    strcpy(message,"XXX() requires two arguments");
    return 1;
}

enum Item_result *arg_type

为每个参数的类型。可能的类型值是STRING_RESULT、INT_RESULT和REAL_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;
}

作为另一种要求你的函数的参数类型是特定类型的选择，你可以使用初始化函数设置arg_type成员是你想要的类型。这导致MySQL为每个xxx()调用强制参数为那些类型，例如，为了指定头 2个参数到字符串和整数的强制，在xxx_init()中做这些：

args->arg_type[0] = STRING_RESULT;
args->arg_type[1] = INT_RESULT;

char **args

args->args将关于你的函数用它调用的参数的一般特性的信息传递到初始化函数。对一个常数参数i，args->args[i]指向参数值。（见下面关于如何正确存取值的指令) 对一个非常数的参数，args->args[i]是0。一个常数参数只是使用常数的一个表达式，例如3或4*7-2或SI(3.14)。一个非常数参数是引用可能每行不同的值的一个表达式，例如列名字或用非常数参数调用的函数。对主函数的每次调用，args->args包含对当前正在处理的行所传递的实际参数。函数可以如下地引用一个参数i：

• 一个STRING_RESULT类型的参数由一个字符串指针加一个长度给出，允许处理任意的长度的二进制的数据或数据。字符串内容可由args->args[i]得到并且字符串长度是args->lengths[i]。你不应该假设字符串是以空(null)结束的。
• 对于一个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]);
  
  

unsigned long *lengths

对初始化函数，lengths数组指出每个参数的最大字符串长度。对于主函数调用，lengths包含为当前正在被处理的行传递的任何字符串参数的实际长度。对INT_RESULT或REAL_RESULT类型的参数，lengths仍然包含参数的最大长度(就象对初始化函数)。

14.1.3 返回值和出错处理

如果没有出现错误，初始化函数应该返回0，否则返回1。如果发生一个错误，xxx_init()应该在message参数中存储一条空字符结束的错误消息，消息将被返回给客户。消息缓冲区是MYSQL_ERRMSG_SIZE个字符长，但是你应该试着保持消息不到80个字符以便它适合一幅标准终端屏幕的宽度。

对long long和double函数，主函数xxx()的返回值是函数值。对字符串函数，字符串在result和length参数中被返回。result是至少255个字节长的一个缓冲区，设置这些为返回值的内容和长度。例如：

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

字符串函数返回值也通常指向结果。

为了在主函数中表明一个NULL返回值，设定is_null为1：

*is_null = 1; 

为了在函数中表明一个错误返回，设定error参数为1：

*error = 1; 

如果对任何行xxx()设置*error为1，对当前行函数值是NULL，并且在该语句中处理的后续行，XXX()被调用。(xxx()甚至将不为随后的行被调用。）注意：在MySQL 3.22.10以前的版本中，你应该都设置*error和*is_null：

*error = 1;
*is_null = 1;

14.1.4 编译并安装用户定义函数

实现UDF的文件必须在服务器运行的主机上被编译并且安装。这个过程下面描述，UDF例子文件包含在MySQL源代码分发的“udf_example.cc”中，这个文件包含下列函数：

• metaphon()返回字符串参数的一个变音位(metaphon)字符串。这有点象一个soundex字符串，但是它更针对英语音调。
• myfunc_double()返回在其参数中所有字符的ASCII值的和，除以其参数长度之和。
• myfunc_int()返回其参数长度之和。
• lookup()返回对主机名的IP数。
• reverse_lookup()返回对一个IP数的主机名。函数可以以一个字符串"xxx.xxx.xxx.xxx"或4位数字被调用。

一个可动态装载的文件应该编译为一个共享的对象文件，使用象这样的命令：

shell> gcc -shared -o udf_example.so myfunc.cc

通过运行在你的MySQL源代码树的“sql”目录下的下列命令，你能很容易地找出对你的系统正确的编译器选项：

shell> make udf_example.o

你应该运行一个类似于make显示的编译命令，除了你应该删除接近行结尾的-c选项并且在行最后增加-o udf_example.so。（在一些系统上，你可能需要在命令上保留-c。）

一旦你编译了包含UDF 的一个共享对象，你必须安装它并且把它告诉MySQL。自“udf_example.cc”编译一个共享对象产生一个名字类似“udf_example.so”的文件（准确的名字可以依平台不同而不同)。拷贝这个文件到被某个ld寻找的目录，例如“/usr/lib”。在许多系统上，你能设定LD_LIBRARY或LD_LIBRARY_PATH环境变量，指向有UDF函数文件的目录。dopen手册页告诉你你应该在你的系统上使用哪个变量。你应该在mysql.server或safe_mysqld中设置它并且重启mysqld。

在库被安装以后，用这些命令通知mysqld有关新的函数的信息：

mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so";
mysql> CREATE FUNCTION myfunc_double RETURNS REAL SONAME "udf_example.so";
mysql> CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "udf_example.so";
mysql> CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so";
mysql> CREATE FUNCTION reverse_lookup RETURNS STRING SONAME "udf_example.so";

函数可使用DROP FUNCTION删除：

mysql> DROP FUNCTION metaphon;
mysql> DROP FUNCTION myfunc_double;
mysql> DROP FUNCTION myfunc_int;
mysql> DROP FUNCTION lookup;
mysql> DROP FUNCTION reverse_lookup;

CREATE FUNCTION和DROP FUNCTION语句在mysql数据库中更新系统表func。函数名、类型和共享库名被保存在该表中。你必须有对mysql的insert和delete权限以创建和抛弃函数。

你不应该使用CREATE FUNCTION增加一个已经被创建的函数。如果你需要重新安装函数，你应该用DROP FUNCTION删除它，然后用CREATE FUNCTION重新安装它。你将需要这样做，例如，如果你重新编译你的函数的一个新版本，以便mysqld获得新版本，否则服务器将继续使用旧版本。

活跃函数在每次服务器启动时再次装载，除非你使用--skip-grant-tables选项启动mysqld。在这种情况下，UDF初始化被跳过并且UDF不可用。（活跃函数是一个用CREATE FUNCTION装载并且没有用DROP FUNCTION删除的函数。)

14.2 增加一个新的原生函数

增加一个新的原生函数的过程在下面描述。注意，你不能往一个二进制分发中加入新函数，因为该过程涉及修改MySQL源代码。你必须从源代码分发自行编译MySQL。也要注意，如果你迁移到MySQL的其他版本（例如，当一个新版本被释放时)，你将需要用新版本重复该过程。

为了加入一个新的原生MySQL函数，遵循这些步骤：

1. 在“lex.h”加入1行，它在sql_functions[]数组中定义函数名。
2. 在“sql_yacc.yy”加入2行。一行指出示yacc应该定义的预处理器符号(这应该加在文件的开始)，然后定义函数参数并且将一个具有这些参数“项目”加到simple_expr语法分析规则中。有一个例子，检查在“sql_yacc.yy 所有的SOUNDEX出现看看它使怎样做的。
3. 在“item_func.h”中，声明一个继承Item_num_func或Item_str_func的类，取决于你的函数是返回一个数字或是一个字符串。
4. 在“item_func.cc”，增加下列声明之一，取决于你是正在定义一个数字或是字符串函数：

   double   Item_func_newname::val()
   longlong Item_func_newname::val_int()
   String  *Item_func_newname::Str(String *str)
   

5. 你也可能应该定义下列函数：

   void Item_func_newname::fix_length_and_dec()
   

   这个函数至少应该基于给定的参数计算max_length，max_length是函数可以返回的字符的最大数目。如果主函数不能返回一个NULL值，这个函数也应该设置maybe_null = 0。函数可以通过检查参数的maybe_null变量以便检查函数参数的任何一个是否能返回NULL。

所有函数必须是线程安全的（thread-safed）。

对字符串函数，已知有一些额外的考虑：

• String *str参数提供一个可以用来保存结果的字符串缓冲区。
• 函数应该返回保存结果的字符串。
• 所有的当前字符串函数试图避免分配任何内存，除非绝对必要！

第一章, 前一章, 下一章, 最后一章，目录.