MySQL user-defined function (UDF)

UDF resource file

UDF Test Program

Here we will explain what a user-defined function (UDF) is, what it is, and how it is used.
1) What is a user-defined function (UDF?
The basic user-defined function is a type of code that expands the functions of the MySQL server. By adding new functions, the nature is like using the local MySQL function ABS () or Concat (). UDF is written in C (or C ++. Maybe you can also use basic,. net, or anything else, although I have not seen anyone doing this.
2) why and how to use the UDF function?
It is very useful to understand udfs literally, especially when you need to extend the functions of the MySQL server. The following table compares the best solutions:

Method speed Language Development
Method speed Language Development
Stored Procedures slow SQL ~ Minutes (for small functions)
Slow SQL in Stored Procedures ~ Minutes (for small functions)
UDF fast C ~ Hour
UDF fast C ~ Hours
Native function fast C major pain in ***
Local function fast C unknown

Slow means to compare with other. The Comparison Between stored procedures and general SQL statements is still very fast.
An explanation of the local function: in essence, it is not much different from the UDF. However, you must use the MySQL resource code to write and recompile all the data. This will be a huge workload. You must use the latest version of MySQL on the other side to complete this work.
3) How to Use UDF?
This part is simple. After you complete a UDF, you just need to use it. Example: "select myfunction (data1, data2) from table"
4) Write a UDF
Now we develop and write a UDF:
Create a new shared-Library Project (in this example, I used VC ++ 6.0 to create a standard DLL)
First, I need some header files. These header files are standard header files and files in the MySQL server's include directory.
# 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 ulonglong;
Typedef 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;

 
Now I have to decide what types of functions we need. There are essentially two options:
Is this aggregate function? (We will learn a lot about Aggregate functions later)
What is the return type? There are four options:
Type description
String is a valid string and converted to the char * type.
Integer is a common integer variable and is converted to a 64-bit integer.
Converts a real-type Number of captured points to a double-type number.
The deciaml type does not really end. MySQL will treat it as a string.

 
Now we will discuss about non-Aggregate functions. You must declare and execute some functions used by MySQL to use udfs, But first, some necessary structures must be correct:
Udf_init:
Type Name Description
<Code>
My_bool maybe_null is 1. If the 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 0 if the result is independent

Udf_args:
Type Name Description
Unsigned int arg_count
Enum item_result * arg_type array of the member type
Char ** ARGs: array of pointers to members
Unsigned long * array of lengths member lengths (for strings)
Array marked by char * maybe_null "maybe_null"
Char ** attributes: array of pointers pointing to member attributes
Unsigned long * attribute_lengths attribute length Array

Now let's take a look at this function:
De-/initialization:

Collapseextern "C" my_bool mytest_init (udf_init * initid, udf_args * ARGs,
Char * message)
{

// One of the most important tasks is to create memory.
// You need
// We need a long variable to save the detection count.
// Although this example is not required
Longlong * I = new Longlong; // create a variable
* I = 0; // set the Initial Value


// The pointer variable is saved as a character pointer.
// Confirm that you will not encounter Type Problems
Initid-> PTR = (char *) I;

// Check the member 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)
{

// The allocated memory must be cleared.
// Introduce the Function
Delete (Longlong *) initid-> PTR;
}

The actual function:

Extern "C" Longlong mytest (udf_init * initid, udf_args * ARGs,
Char * is_null, char * error)
{

/* This is the actual part of our work. This function is called for each record. The returned value or pointer to the current value is stored in the udf_args variable. We must obtain the value, complete the calculation, and return the value. Note that you can use the udf_init variable to enter the memory allocated in mytest_init. In this example, we will set each value to 5.
*/
Return * (Longlong *) ARGs-> ARGs [0]) + 5;
}
All done! Now you must compile the Connection Library and copy it to a directory that can be loaded by the operating system. In Windows, the path of system variables is usually defined. I personally use the bin directory of the MySQL server. Make sure that the directory is inaccessible to other MySQL databases. Then confirm all functions required by MySQL.
We must tell MySQL that this must be executed directly: execute the following SQL command:
Create [aggregate] function mytest
Returns [integer | string | real | decimal] soname the_libraries_exact_name
Now you can use it like other functions.
5) member functions:
Let's talk about the member functions. When your UDF is a member function, you must add some functions and use them in different ways. The call order is:
Call ytest_init to allocate memory (just like a UDF)
MySQL classifies a table by group
The first line in each group calls mytest_clear.
The first column in each group calls mytest_add
Call mytest after the group is changed or after the last column is changed to get the result.
Repeat 3 to 5 until all columns are processed.
Call mytest_deinit to clear the memory
Now let's take a look at the functions required by the new aggregate function. In this example, all values are simply added. (Like the local sum function)
Void mytest_clear (udf_init * initid, char * is_null, char * error)
{

/* Set the total number to 0 for each new group. Of course, you must assign a Longlong variable in the init function and assign it to the pointer.
*/
* (Longlong *) initid-> PTR) = 0;
}

Void mytest_add (udf_init * initid, udf_args * ARGs, char * is_null, char * error)
{

// Add the current value to the total number for each column
* (Longlong *) initid-> PTR) = * (Longlong *) initid-> PTR) +
* (Longlong *) ARGs-> ARGs [0]);
}

Longlong mytest (udf_init * initid, udf_args * ARGs, char * is_null, char * error)
{

// Returns the final total value.
Return * (Longlong *) initid-> PTR );
}

6) further problems:
Pay attention to the following issues when writing complex udfs:
A string function should return a pointer to the result and Set * result and * length as the length value of the Directory and return value. For example:
Memcpy (result, "result string", 13 );
* Length = 13;
The result buffer created by mytest is 255 bytes. If your results are saved in. You don't have to worry about the memory allocation of the results.
If your string function needs to return a string larger than 255 bytes. You must use malloc or the new mytest_init or mytest function to allocate and then use mytest_deinit to release it. You can use the udf_init pointer to save the allocated memory address and reuse it in mytest.
Specify an error response in the main function and Set * error to 1. If mytest () is set to any column and * error is set to 1, the function value is null for the current column, and any concurrent column requests in the declaration called through mytest.
For more information, see MySQL online help.

7) Guidelines:
If you really want your UDF to run well, here are some suggestions :)
Do not call any other program or process in UDF
Do not save any local information. (These have been shared in common libraries)
Do not assign any global or static variables.
Always check the member type. Just as MySQL converts all types to character types. An error may occur if you convert a character type to an integer pointer.
Pay special attention to memory allocation. If you have a memory leakage problem, you will cause the server to crash completely!

8) tune the UDF
It takes courage to debug a UDF because if a UDF has a problem, the entire MySQL server will die every time. So I wrote a command line tool to work around this problem. Run it only, it will imitate the "select" Call Command to save the results to the library file, you can print all the result lines. Therefore, when a UDF has some errors, the program crashes instead of the entire server.