MySQL UDF


MySQL UDF

MySQL除了具备一个成熟稳定的高性能关系型数据库之外,灵活多变的拓展支持也是它的一大特性,包括支持多种存储引擎。 MySQL UDF: User-Define Function UDF属于MySQL拓展的一部分。MySQL为用户提供了三种拓展方式。

  • UDF: MySQL提供了一系列API,用户可以使用这些API注册自定义实现的方法。UDF以动态库形式放到MySQL指定的插件目录下, 然后在MySQL中使用CREATE FUNCTIONDROP_FUNCTION语句来动态创建或销毁用户自定义功能。

  • MySQL代码开源,直接修改MySQL源码。

  • 另外一种方式是依靠创建stored functions方式来拓展。

每种方式都有优缺点:

  • UDF采用动态库的方式,这意味者除了MySQL自身的组件之外,开发者还必须自己安装这些实现UDF的动态库。
  • Native Functions的方式需要修改MySQL源码,这相对来说需要更高的能力要求。
  • 如果MySQL的UDF API不变,当MySQL升级时,UDF插件仍旧可用。对于Native Functions这种方式来说,开发者必须在新的MySQL源码 基础上再针对新版的MySQL重新开发一遍。

UDF功能及特性

  • 函数支持接收或返回string, integer和real类型。(c/c++中分别对应 char*, long long, double类型)
  • 用户可以通过一个简单的函数来操作单行或group聚合之后的多行结果。
  • MySQL为UDF提供了检查它们的数值,类型,或者传递给UDF的参数名称的信息。
  • 在MySQL调用UDF时,用户可以让MySQL传递指定的类型参数。
  • 指示结果可以为NULL(mysql中的空值)或者错误。

UDF开发

MySQL源码中带有sql/udf_example.cc的例子。

注意事项

xxx表示用户侧自定义函数(c/c++)名称,XXX表示SQL函数

  1. include/mysql_com.h这个头文件定义了UDF相关的符号或结构定义。但用户不必直接包含这个头文件,它被包含进了mysql.h中。
  2. UDF代码作为MySQL server的一部分,用户在开发时,应当注意编写的代码受到MySQL server的一些限制,比如,在UDF中使用了libstd++ 库中的函数,当将来Server版本变更时,也需要考虑版本变更对UDF功能的影响。
  3. UDFs必须可以被mysqld动态加载链接。如果用户在UDF中想要调用mysqld中的某个变量或者函数,例如自带例子(sql/udf_examplecc)中的metaphone函数中使用了default_charset_info,须使用-rdynamic选项来编译UDF动态库。
  4. 使用C++来编写时,需要使用extern "C" {...}来指示编译器编译。
  5. UDF库文件必须放到server指定的目录下。例如我的ubuntu系统(16.04)下apt-get方式安装的mysql位于/usr/lib/mysql/plugin目录下。
  6. 要使用CREATE_FUNCTION或者DROP FUNCTION语句,用户必须具有INSERTUPDATE权限。这是必须的,因为这些语句与mysql中的mysql.func系统表的添加和删除相关。
  7. UDFs至少要实现一些MySQL规定的有特殊作用的函数。这些辅助类的函数通常为:
1
xxx_init() xxx_deinit() xxx_reset() xxx_clear() xxx_add()

另外,mysqld支持--allow-suspicious-udfs选项来控制UDFs是否只允许有xxx符号可以被加载。这个选项默认 是关闭的,这样做是为了阻止尝试从动态库中加载除了这些合法UDFs之外的其他符号。说白了,就是为了更加安全。

xxx()

主函数。在这里需要返回处理结果。 SQL函数与C/C++的类型对照如下:

MySQLC/C++
STRINGchar*
INTEGERlong long
REALdouble

用户也可以声明一个DECIMAL类型的函数,但现阶段这种函数返回的结果会被mysql作为string类型来对待。

xxx_init()

xxx()的初始化函数。通常可以用这个函数来处理以下事情:

  • 检查传入到xxx()中的参数个数
  • 检查参数类型,或者在这里告诉mysql主函数被调用时接受参数类型。
  • 为主函数分配内存空间
  • 指定最大结果的长度
  • 对REAL类型的函数,可用来指定存放结果数字的最大个数。
  • 指定结果是否可以为空

xxx_deinit()

主函数的清理函数。如果有必要,可以回收xxx_init()中分配的内存。 当SQL语句执行XXX()时,MySQL调用初始化函数xxx_init()中的所需准备工作,例如分配内存,参数检查等。 如果xxx_init()返回错误,MySQL携带错误信息放弃SQL语句的执行,这并不会执行到住函数或者清理函数。 否则,MySQL会每行都执行一次xxx()。当所有行都处理完成时,MySQL会呼叫清理函数xxx_deinit(),开展任何 必要的清理工作。

对于像SUM()这类聚合函数,必须提供接下来的系列函数

xxx_clear()

重设当前的聚合值,当前值不会作为初始参数传递给下一次聚合操作。

xxx_add()

将参数与当前聚合值相加。

MySQL处理聚合类的UDFs流程如下:

  1. 调用xxx_init()分配存储结果所需内存。
  2. 根据GROUP BY表达式排序。
  3. 对每个新的group首行调用xxx_clear()
  4. 对所属相同group的每行调用xxx_add()
  5. 当group改变或者最后一行被处理之后调用xxx()来获取聚合结果。
  6. 重复3~5步直到所有行被处理完。
  7. 调用xxx_deinit()释放UDFs分配内存。

注意:所有函数必须是线程安全的。不仅仅是主函数,像初始化和清理函数等同样要求线程安全。 这就要求在编写UDF时,不能分配任何全局或者静态类型的变量。如果你需要内存空间,应当在xxx_init()中 申请,在xxx_deinit()中释放。

UDF系列函数定义说明

以下描述了开发UDFs时可以使用的一系列函数形式(包括函数参数和函数返回值)。 具体采用哪种形式取决于将如何在SQL中如何使用: 结果是返回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);

DECIMAL函数返回的是STRING,所以与STRING形式一致。ROW函数没有被实现

init 和 deinit函数形式

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

void xxx_deinit(UDF_INIT *initid);

在以上的函数形式中都有一个initid形参:它指向UDF_INIT这个结构体。这个结构体用来在一系列相关函数之间传递相关信息。

它的定义在mysql_com.h中。

/* This holds information about the result */

typedef struct st_udf_init
{
  my_bool maybe_null;          /* 1 if function can return NULL */
  unsigned int decimals;       /* for real functions */
  unsigned long max_length;    /* For string functions */
  char *ptr;                   /* free pointer for function data */
  my_bool const_item;          /* 1 if function always returns the same value */
  void *extension;
} UDF_INIT;

从定义及注释可以看出,除了指示mysql一些基本的属性之外,开发者可以利用这个结构体作为UDFs系列函数之间的上下文。

  • my_bool maybe_nll

    如何xxx()主函数可能返回NULL,则在xxx_init()中应该将这个值设成1.

  • unsigned int decimals

    对于REAL类型函数,这个字段表示浮点型小数点的数字个数。这个值默认等于传递到主函数的所有浮点型参数中小数部分最大个数。 例如:将1.34, 1.345和1.3传递给xxx()时,默认为3,因为1.345有三个小数个数。

  • unsigned int max_length

    最大结果长度。不同类型函数具有具体的含义: STRING类型函数默等于最长参数长度。 INTEGER类型函数默认为21。 REAL类型参数默认等于: 13 + decimals 如果想要返回BLOB类型,可以将这个值设成 65K或16MB.这并不表示分配同样大小的内存空间。它按需分配作为临时存储空间。

  • char *ptr

    这个字段可根据自己具体目的来使用:比如可以指向一块内存,在UDFs系列相关函数中来使用。 xxx_init()可分配内存:

        initid->ptr = allocated_memory;
    
1
在xxx()中使用或者在xxx_deinit()中释放。
  • my_bool const_item

    如果主函数返回常量值,则在xxx_init()中应设为1。否则设成0。


UDF Callings Sequences for Aggregate Functions

xxx_reset()

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

MySQL在一个新的group中找到第一行结果时调用此方法。 此函数应实现清除所有内部汇总变量值,使用UDF_ARGS参数值作为内部汇总的初始值。 这个函数在MySQL5.7版本之后不再被使用,它已被xxx_clear()函数取代。但如果为了兼容 之前版本,开发者可以同时定义xxx_reset()和xxx_clear()这两个函数。这时,可在xxx_reset() 调用xxx_clear()函数重置所有变量,然后调用xxx_add()函数将UDF_ARGS参数作为初始值

xxx_clear()

    void xxx_clear(UDF_INIT *initid, char *is_null, char *error);
    

MySQL5.7中新增的函数形式。 MySQL需要重设汇总结果初始值时此函数被调用。调用发生在每个新的group开始或者 在条件查询(query where)没有查到匹配结果时。

is_null在调用xxx_clear()之前被设置为CHAR(0)

如果遇到错误,可将错误信息附给error这个指针变量。需要注意的是,error指向的是单字节 变量,不是一个string buffer

xxx_add()

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

属于同一个分组的每行结果处理时被调用。 开发者应使用UDF_ARGS参数来处理内部汇总结果。

聚合类的xxx()主函数与非聚合类的UDF主函数形式相同。请参照非聚合类主函数的函数说明。 对于聚合类的UDF,MySQL在一个group中所有行被处理之后调用xxx()。

The pointer arguments to is_null and error are the same for all calls to xxx_reset(), xxx_clear(), xxx_add() and xxx(). You can use this to remember that you got an error or whether the xxx() function should return NULL. You should not store a string into *error! error points to a single-byte variable, not to a string buffer. *is_null is reset for each group (before calling xxx_clear()). *error is never reset. If *is_null or *error are set when xxx() returns, MySQL returns NULL as the result for the group function.

UDF Argument Processing

UDF_ARGS

1
其定义在`mysql_com.h`中。
    typedef struct st_udf_args
    {
      unsigned int arg_count;		/* Number of arguments */
      enum Item_result *arg_type;		/* Pointer to item_results */
      char **args;				/* Pointer to argument */
      unsigned long *lengths;		/* Length of string arguments */
      char *maybe_null;			/* Set to 1 for all maybe_null args */
      char **attributes;                    /* Pointer to attribute name */
      unsigned long *attribute_lengths;     /* Length of attribute arguments */
      void *extension;
    } UDF_ARGS;
    
  • unsigned int arg_count

参数个数。UDF_ARGS其他结构体成员是一种数组类型。

  • enum Item_result *arg_type

每个参数类型的数组指针。枚举常量值为: STRING_RESULT, INT_RESULT, REAL_RESULT, DECIMAL_RESULT

其中, DECIMAL_RESULT实际是strings,处理方式应与STRING_RESULT类型一样。

另外,开发者可以通过这个字段来告诉MySQL,接下来的xxx()主函数需要的参数类型,让MySQL在调用主函数时可以强制转换成期望的参数的数据类型。 例如,前两个参数类型分别为string和integet:

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

这个字段携带了主要的实际数据。每调用一次xxx(),args->args包含了将要传递 给当前要处理的行的实际数据。

  1. 对参数类型是STRING_RESULT,则args->args[i]为一个字符串类型指针。注意:不可将其直接作为传统意义上的C字符串来处理(\0结尾)。这应该与args->lengths[i]共同处理。这样使得二进制数据得到处理。

  2. 对于INT_RESULT类型参数,则必须将其强制转换为long long类型。

    long long int_val;
    int_val = *((long long*) args->args[i]);
    
  1. 对于是REAL_RESULT类型,则将其强制转化为double类型:
    double real_val;
    real_val = *((double*)args->args[i]);
    
  1. 对于DECIMAL_RESULT类型,需要将其按照STRING_RESULT类型对待。

  2. ROW_RESULT类型暂未实现。