Shulou(Shulou.com)05/31 Report--

How to write UDF in MySQL, I believe many inexperienced people don't know what to do about it. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. What is UDF?

UDF, as its name implies, is User defined Function, a user-defined function. We know that MySQL itself supports many built-in functions, and you can also define functions by creating storage methods. UDF provides a more efficient way for users to create functions.

UDF is similar to ordinary functions in that it has parameters and output. There are two types: single call type and aggregate function. The former can be processed for each row of data, while the latter is used to deal with situations such as Group By.

two。 Why use UDF?

Since MySQL itself provides a large number of functions, and also supports defining functions, why do we need UDF? This is mainly based on the following points:

1) the compatibility of UDF is very good, thanks to the fact that MySQL's UDF is basically unchanged.

2) it has higher execution efficiency than storage method, and supports aggregation function.

3) compared with modifying the code and adding functions, it is more convenient and simple.

Of course, UDF also has shortcomings, because UDF is also in the memory space of mysqld, careless memory use can easily lead to mysqld crash drop.

3. How to write UDF

UDF's API includes

Name_init ():

It will be called before the SQL is executed, mainly to do some initialization work, such as allocating the memory used later, initializing variables, checking whether the parameters are legal, and so on.

Name_deinit ()

Called after the SQL is executed, it is mostly used for memory cleanup and other tasks. Both init and deinit functions are optional

Name ()

The main processing function of UDF can process the data of each row when it is a single call; when it is an aggregation function, it returns the aggregate result after Group by.

Name_add ()

Call each line in each group

Name_clear ()

Called after each grouping

For ease of understanding, here are the API call diagrams for two UDF types:

The above API will be explained in detail below:

1)。 Name_init

Prototype:

My_boolname_init (UDF_INIT * initid, UDF_ARGS * args, char * message)

The UDF_INIT structure is as follows:

The UDF_ARGS structure is as follows:

Message: used to print error messages, and the pointer itself provides a length of MYSQL_ERRMSG_SIZE to store information

2). Name_deinit

Prototype:

Void name_deinit (UDF_INIT*initid)

This function will do some memory release and cleanup work. We mentioned initid- > ptr earlier. We can dynamically allocate memory in this area, and memory can be freed directly here.

3). Name ()

Prototype: there are different function prototypes for different return value types:

When the return value is of type STRING, the parameter result opens up a buffer to store the return value, but no more than 766 bytes, and stores the length of the string in the length parameter.

Each function prototype also includes is_null and error parameters. When * is_null is set to 1, the return value is NULL and * error is set to 1, indicating that an error has occurred.

4). Name_add () and name_clear ()

Prototype:

Void name_add (UDF_INIT * initid, UDF_ARGS * args, char * is_null,char * error)

Void name_clear (UDF_INIT * initid, char * is_null, char * error)

UDF,name_addd and name_clear for aggregation types are called repeatedly.

4. Two examples

Here are two simple examples, a single call function and an aggregate function, to describe the process of writing a UDF.

1) accept a parameter and return the value of the parameter

/ / initialize

My_booludf_int_init (UDF_INIT * initid, UDF_ARGS * args, char * message)

{

If (args- > arg_count! = 1) {/ / check the number of parameters

Strcpy (message

"udf_intexample () can onlyaccept one argument")

Return 1

}

If (args- > arg_type [0]! = INT_RESULT) {/ / check the parameter type

Strcpy (message

"udf_intexample () argumenthas to be an integer")

Return 1

}

Return 0

}

/ / cleanup operation

Voidudf_int_deinit (UDF_INIT * initid)

{

}

/ / main function

Long long udf_int (UDF_INIT * initid, UDF_ARGS * args, char * is_null, char * error)

{

Long long num = (* (long long *) args- > args [0]); / / get the first parameter value

Return num

}

2) accept a parameter of a floating point type and sum each group

/ / initialize

My_booludf_floatsum_init (UDF_INIT * initid, UDF_ARGS * args, char * message)

{

Double * total = (double *) malloc (sizeof (double))

If (total = = NULL) {/ / memory allocation failed

Strcpy (message, "udf_floatsum:alloc mem failed!")

Return 1

}

* total = 0

Initid- > ptr = (char *) total

If (args- > arg_count! = 1) {/ / check the number of parameters

Strcpy (message, "too moreargs,only one!")

Return 1

}

If (args- > arg_type [0]! = REAL_RESULT) {/ / check the parameter type

Strcpy (message, "wrongtype")

Return 1

}

Initid- > decimals = 3; / / set the precision of returned values

Return 0

}

/ / Clean up and release the memory allocated in the init function

Voidudf_floatsum_deinit (UDF_INIT * initid)

{

Free (initid- > ptr)

}

/ / this function is called on every line.

Voidudf_floatsum_add (UDF_INIT * initid, UDF_ARGS * args, char * is_null, char * error)

{

Double* float_total

Float_total = (double*) initid- > ptr

If (args- > args [0])

* float_total + = * (double*) args- > args [0]

}

/ / after each grouping is completed, the result is returned

Doubleudf_floatsum (UDF_INIT * initid, UDF_ARGS * args, char * is_null, char * error)

{

Double* float_total

Float_total = (double *) initid- > ptr

Return * float_total

}

/ / it is called before the next grouping, and the value pointed to by initid- > ptr is set to 0, so that the next grouping is counted.

Voidudf_floatsum_clear (UDF_INIT * initid, char * is_null, char * error)

{

Double * float_total

Float_total = (double *) initid- > ptr

* float_total = 0

}

3) Mysql-udf-http is an open source UDF, which can use HTTP protocol for REST operation. What is the REST operation? REST is a kind of web service architecture style, and its implementation is based on four methods of HTTP protocol: POST, GET, PUT and DELETE operation. The corresponding functions in mysql-udf-http are http_post, http_get (), http_put (), http_delete ().

Source code: http://curl.haxx.se/download/curl-7.21.1.tar.gz

. / configure-prefix= {installation directory}-with-mysql=/usr/local/webserver/mysql/bin/mysql_config

Make&& make install

The implementation principle of the UDF is relatively simple, mainly using libcurl library functions to achieve http protocol communication, a total of more than 300 lines of code. Here is the use and introduction of http://blog.s135.com/mysql-udf-http/

Some of the more interesting features:

For example, we can obtain personal information in Weibo through the GET method, where 1821798401 is the user ID

Selecthttp_get ('http://api.t.sina.com.cn/statuses/user_timeline/1821798401.json?count=1&source=1561596835')

UDF has a very high degree of freedom, you can write any function you want to implement, and you can even reference MySQL kernel code and variables.

Of course, UDF also has limitations, as follows:

A) there must be a func table under the mysql library, and when-- skip-grant-tables is turned on, UDF will be disabled

B) when UDF hangs up, it may cause mysqld crash to drop

C) all UDF functions must be thread safe. If you have to use global variables, they need to be mutually exclusive. Try to allocate resources in name_init and release them in name_deinit.

D) insert permission is required

After reading the above, have you mastered how to write UDF in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.