The UDF for MySQL

Recently used the MySQL UDF, checked the relevant literature, the user user to implement function and aggregate function method to do a description.

Quickly write a MySQL UDF

To be able to quickly understand what the UDF (user-defined function) is, we first build the simplest UDF and then introduce more detail. This article is tested under Ubuntu16.04, and you need to install MySQL and related libraries before you start, under Ubuntu:

sudo apt-get install libmysqlclient-dev

STEP1: Writing C + + code and generating a dynamic-link library

The sample C + + code is as follows:

extern "C"{Long LongMyadd (Udf_init *initid, Udf_args *args,Char*is_null,Char*error); My_bool myadd_init (Udf_init *initid, Udf_args *args,Char*message);}Long LongMyadd (Udf_init *initid, Udf_args *args,Char*is_null,Char*error) {intA = * ((Long Long*) args->args[0]);intb = * ((Long Long*) args->args[1]);returnA + b;} My_bool Myadd_init (Udf_init *initid, Udf_args *args,Char*message) {return 0;}

When you are finished, save the file as Udf.cpp and then compile the dynamic-link library with the following command:

g++-shared-fPIC-I-o udf.so udf.cpp

Get udf.so file.

STEP2: Adding functions to MySQL

After completing the above writing, the udf.so file is copied to the MySQL plugin directory, the default in Ubuntu16.04 is:

/usr/lib/mysql/plugin/

Then use the following command to install the dynamic link library in MySQL.

CREATE FUNCTION myadd RETURNS INTEGER SONAME ‘udf.so‘

STEP3: Calling functions and related queries

• Calling functions

select myadd(1,2);

Can get the results of the calculation 3

• Query Installation List

select * from mysql.func;

You can view the list of. So libraries that are currently installed on the database.

• Delete functions with drop function:

FUNCTION myadd;

UDF Authoring Process Explained

As you can see, we have added our own function myadd, which can be executed by MySQL after it is installed. We also define the Myadd_init, which is the system-defined initialization function that must be used. When writing a MySQL UDF, on the one hand we want to define the function we need, on the other hand, we want to write a series of other functions together, these functions are named with a fixed rule. For example, the user-defined function named XXX, the matching function is Xxx_init, xxx_deinit, etc., its parameter list is fixed, the following is introduced.

Writing User main functions

The first is the definition of the user function, we assume that the function needs to define the name of XXX, then our function needs to have a parameter list and return value, which can not be arbitrarily specified by the user, there is a fixed rule.

The return type supports 5 types:

enumItem_result {STRING_RESULT=0, REAL_RESULT, INT_RESULT, ROW_RESULT,  

For these 5 kinds of return values, the function headers defined are as follows:

• The return value is either a string type or a decimal type

*xxx*initid*args,          *result*length,          *is_null*error);

For this definition, the return value can point to result, copy the required content, and set the length as follows:

memcpy(result"result string"13);*length13;

• Integer type

longlong xxx(UDF_INIT *initid, UDF_ARGS *args,              charchar *error);

• Real type

double xxx(UDF_INIT *initid, UDF_ARGS *args,              charchar *error);

• Row type

Not implemented

Writing system built-in functions

After you have completed the user-defined main function, you also need to write the matching system built-in functions. The relevant instructions are as follows:

Xxx_init

This function is called before the custom xxx function is called, and the basic initialization is done, and it is fully defined as follows:


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


• Return value: 1 represents an error, the error message can be given in the message and returned to the client, 0 means that the correct execution. Information length cannot be greater than mysql_errmsg_size
• function function: The main function of the function is to allocate space, function parameter check and so on. If you do not need to do anything, return 0 directly.

Xxx_deinit

The function is used to free up the requested space, which is fully defined as follows:

void xxx_deinit(UDF_INIT *initid);

• function function: The function is mainly to release resources, if the application of memory in Xxx_init, can be released here, the function after the user function xxx execution

For ordinary UDFs, the above two built-in functions are sufficient, but for the aggregate function, like sum, the count function must be given an extra function:

Xxx_clear

The complete definition is as follows:

voidcharchar *error);

Xxx_add

*initid*args,             *is_null*error);

Introduction to two types of UDF execution flow

These two types of UDFs do not have the following execution flow:

normal function Execution flow

• Call Xxx_init to initialize and request memory space to store the results
• Call XXX
• Call Xxx_deinit to free up space

For the execution of normal functions, you can refer to the Myadd function given at the beginning.

aggregate function Execution Flow

• Call Xxx_init to initialize and request memory space to store the results
• Use GROUP by to sort tables to form multiple or group
• Xxx_clear calls, for each new group, calls the
• For each row of the group, call Xxx_add
• Call XXX
• Repeat 3-5 until all group is processed
• Call Xxx_deinit to free up space

For the execution flow of the aggregate function, you can refer to the MySum function given later.

Function parameter list Introduction

For the function described above, its argument list and return value is our concern, for some function-specific parameters, described above, the function has been introduced, and now introduce its public parameters part:

Udf_init

This structure is mainly used for communication between user function and system built-in function, and its structure is as follows:

member	function
My_bool Maybe_null	A value of 1 indicates that the function can return null, and the default value is 1
unsigned int decimals	If the parameter is a decimal, the number of digits after the decimal point
unsigned int max_length	Returns the maximum length of the result
Char *ptr	Users can request their own memory space, and then use this pointer to their own space for their own functions to use
My_bool Const_item	If the user function always has the same output for the same input, its value is 1, which is the default value. Otherwise, it is set to 0

In this example, we use only PTR, with the rest using the default values. The so-called communication, is that we have this parameter in the Xxx_init, after the subsequent xxx and xxx_deinit, we can still get this type of pointer, so that we can request a space in the Xxx_init function, and the PTR point to this space, Use this space in the XXX function and then free up space in the Xxx_deinit, which is the basic usage of the MySQL UDF.

Udf_args

The structure is mainly used to pass parameters, the parameters are provided by MySQL, for the table, is a row of table data, which is described as follows:

member	function
unsigned int arg_count	The number of function parameters, can be in the Xxx_init function through this member of the user input parameters of the number of checks, if the number of arguments is wrong, do not execute or return an error
Enum Item_result *arg_type	The type of the parameter can be checked in this type of parameter, or you can force the specified type yourself, there are 5 types, which are described in the previous section
Char **args	If the parameter is a string_result type, you can get the content by args->args[i] to get the length through args->lengths[i]; If Int_result can be obtained by Int_val = ((Long long) args->args[i]), to get the content Real_result or String_result type can be real_val = ((double) args->args[i]); To get content
unsigned long *lengths	For the initialization function, the maximum length of the parameter is saved. For a user-defined main function, the length of each parameter is maintained, which is useful for string types because the string here does not necessarily end with
Char *maybe_null	A value of 0 indicates that a parameter cannot be null, and 1 indicates that it can be
Char **attributes	You can get the name of the parameter. For example select MY_UDF (expr1, expr2 as ALIAS1, Expr3 ALIAS2); Args->attributes[0] = "Expr1" Args->attribute_ Lengths[0] = 5 after the same
unsigned long *attribute_lengths	The length of each parameter name

As you can see, this parametric structure provides a lot of functionality, and this article only focuses on getting the specific parameter content through the args members.

An example of a aggregate function

With the above foundation, we can implement a SUM function mysum, its function and the built-in sum has the same function, the following code and explanation:

#include <mysql/mysql.h>extern "C"{My_bool Mysum_init (Udf_init *ConstInitid, Udf_args *ConstArgsChar*Constmessage);voidMysum_deinit (Udf_init *ConstInitid);voidMysum_clear (Udf_init *ConstInitid,Char*ConstIs_null,Char*ConstError);voidMysum_add (Udf_init *ConstInitid, Udf_args *ConstArgsChar*ConstIs_null,Char*ConstError);Long LongMySum (Udf_init *ConstInitid, Udf_args *ConstArgsChar*ConstResultunsigned Long*ConstLengthChar*ConstIs_null,Char*ConstError);}//Initialize before execution, allocate spaceMy_bool Mysum_init (Udf_init *ConstInitid, Udf_args *ConstArgsChar*ConstMessage) {Long Long* i =New Long Long; Initid->ptr = (Char*) I;return 0;}before executing the function, the group by is executed, and then each new group is encountered, and the function is called first. If there is no group BY, then all is a group.voidMysum_clear (Udf_init *ConstInitid,Char*ConstIs_null,Char*ConstError) {* (Long Long*) (initid->ptr)) =0;}//each row of data is processed by the Add functionvoidMysum_add (Udf_init *ConstInitid, Udf_args *ConstArgsChar*ConstIs_null,Char*ConstError) {* (Long Long*) (INITID-&GT;PTR)) = * ((Long Long*) (INITID-&GT;PTR)) + * ((Long Long*) args->args[0]);}//All data processing is complete, call the user-defined MySum function to return the result; the next group is encountered and re-executed from clear.Long LongMySum (Udf_init *ConstInitid, Udf_args *ConstArgsChar*ConstResultunsigned Long*ConstLengthChar*ConstIs_null,Char*ConstError) {return*((Long Long*) (INITID-&GT;PTR));}//execution end, free spacevoidMysum_deinit (Udf_init *ConstInitid) {DeleteInitid->ptr;}

After compiling and copying to the corresponding plugin directory using the above method, you can add the function with the following command, note that this is not the same as adding a normal function method.

CREATE AGGREGATE FUNCTION mysum RETURNS INTEGER SONAME ‘udf.so‘;

The function execution results are as follows:

Database changedmysql> select * from student;+------+-----------+| id   | name      |+------+-----------+|    1 | zhangfei  ||    2 | zhangfei  ||    3 | zhangfei  ||    4 | zhangliao ||    5 | zhangliao ||    6 | zhangliao ||    7 | shaoyiwen |+------+-----------+7 rows in set (0.00 sec)mysql> select mysum(id) from student;+-----------+| mysum(id) |+-----------+|        28 |+-----------+1 row in set (0.00 sec)

Summarize

As you can see, the MySQL UDF can be used to process the data in the MySQL table, it provides the common function and the aggregate function interface, the ordinary function handles a row of data, and the aggregate function processes the data of a group. Its function head is fixed and provides 5 data types externally. It is important to note that the MySQL UDF that we write must be thread-safe.

Related information

[1] http:/dev.mysql.com/doc/refman/5.7/en/adding-udf.html
[2] http:/blog.csdn.net/luoqiya/article/details/12888553
[3] Http:/www.codeproject.com/articles/15643/mysql-user-defined-functions

Original link: Yiwenshao.github.io/2016/11/20/mysql's udf/

Article Author: Yiwen Shao

License Agreement: Attribution-noncommercial 4.0

Reproduced please keep The above information, thank you!

The UDF for MySQL