How to call mysql udf function

1) The basic user-defined function is a kind of code that extends the function of MYSQL server. By adding new functions, it is like using local MYSQL functions abs () or concat (). UDF is written in C language (or C++). Maybe you can also use BASIC. NET or something, although I haven't seen anyone do it yet.

2) How do you know that UDF is literally useful, especially when you need to extend MYSQL server functions? The following table gives a comparison of the best solutions:

Methods to speed up language development

Methods of accelerating language development

Stored procedures slow down SQL by ~ minutes (for small functions)

Stored procedures are SQL ~ minutes slow (for small functions)

UDF fast c ~ hours

UDF is about 10 hour faster.

The main pain of native function fast C is * * *

Local function fast c unknown

Compared with others, slow means slow. Compared with ordinary SQL statements, stored procedures are still very fast.

Explain the local function: it is not much different from UDF in essence. But it must be written in MYSQL resource code and then recompiled. This will be a lot of work and must be done with the latest version of MYSQL.

3) This part is very simple. When a UDF is completed, use it. For example, "Select my function (data 1, data2) from the table".

4) writing UDF

Now develop and write a UDF:

Create a new shared library project (in this case, use VC++ 6.0 to create a standard DLL).

You need some header files first. These header files are standard header files and files in the include directory of MYSQL server.

#ifdef standard

/* The standard has been defined, and no mysql function is used */

# Including

# Including

# Including

#ifdef __WIN__

typedef unsigned _ _ int 64 ulonglong； /* Microsoft 64 bit type */

typedef _ _ int64 longlong

# Otherwise

Typedef unsigned long long ulonglong

typedef long long longlong

#endif /*__WIN__*/

# Otherwise

# Including

# Including

#endif

# Including

# Including

Static pthread _ mutex _ t LOCK _ hostname

Now you must decide what kind of function you need. There are basically two options:

Is this function an aggregate function? You will learn a lot about aggregate functions in the future. )

What is the return type? There are four options:

Type description

STRING legal string, converted to char* type.

INTEGER is an ordinary integer variable, which is converted into a 64-bit integer.

Real type captures some points and converts them into double precision type.

DECIAML type This is not really over, MYSQL will treat it as a string.

Now let's talk about non-aggregate functions. Some functions used by MYSQL when using UDF must be declared and executed, but first some necessary structures must be confirmed:

UDF _ initialization:

Type name description

If the function can return NULL, My _ boolmabe _ NULL is 1.

Unsigned integer decimal of real function.

The unsigned long integer max_length of a string function.

The Char * ptr free pointer refers to the data of the function.

My_bool const_item 0 If the result is independent,

ARGS UDF:

Type name description

Number of unsigned integer arg_count members

Enumerates an array of Item_result * arg_type member types.

Char ** args Pointer array to members.

Unsigned long * lengths member length array (for strings)

An array marked with char * maybe _ null“maybe _ null "

Char ** attributes An array of pointers to member attributes.

Unsigned Length * Attribute Length Attribute Length Array

Now look at this function:

Cancel/Initialize:

collapse extern " C " my _ bool MyTest _ INIT(UDF _ INIT * initid，UDF_ARGS *args，

Char * message)

{

//It's very important to build memory.

//need

//A long variable is needed to store the detection number.

//Although not required in this example.

Longlong * i = New Longlong; //Create a variable

* I = 0； //Set the initial value

//A pointer stored as a character in a pointer variable.

//Confirm that you will not encounter type problems.

initid-& gt； ptr =(char *)I；

//Detect the format of the member.

if(args-& gt； arg_count！ = 1)

{

Strcpy(message, "MyTest () requires a parameter");

Returns1;

}

if(args-& gt； arg_type[0]！ = INT_RESULT

{

Strcpy(message, "MyTest () needs an integer");

Returns1;

}

Returns 0;

}

extern " C " void MyTest _ deinit(UDF _ INIT * initid)

{

//The allocated memory must be cleared here.

//Introduce a function

Delete (long *) initid-> ptr

}

Actual function:

extern " C " long long MyTest(UDF _ INIT * initid，UDF_ARGS *args，

char *is_null，char *error)

{

/* Finally, this is the actual work part. This function is called for each record, and the return value or pointer to the current value is stored in UDF_ARGS variable. You must get the value, complete the calculation and return the value. Note that the memory allocated in MyTest_init can be accessed through the UDF_INIT variable. In this case, each value of this variable is set to 5.

*/

return *(longlong *)args-& gt； args[0])+5；

}

All done! Now, you must compile the connection library and copy it to a directory that the operating system can load. Usually in WINDOWS, it is the defined path of the system variable. I personally use the bin directory of MYSQL server. You must confirm that other MYSQL cannot access the directory. Then confirm all the functions that MYSQL needs.

MYSQL must be told, and this must be straightforward: execute the following SQL instructions:

Create the aggregate function MyTest

Returns [integer | string | real | decimal] soname _ libraries _ exact _ name.

Now you can use it just like any other function.

5) Member functions:

Now let's talk about member functions. When UDF is a member function, some functions must be added and used in different ways. Call order is:

Call yTest_init to allocate memory (just like ordinary UDF)

MYSQL classifies tables through GROUP BY.

The first line in each group calls MyTest_clear.

The first column in each group calls MyTest_add.

Call MyTest to get the result after the group changes or the last column changes.

Repeat 3 to 5 until all columns have been processed.

Call MyTest_deinit to clear the memory.

Now let's look at the functions required for the new aggregate function. In this example, all the values will simply add up. (Just like the local summation function)

Void MyTest_clear(UDF initialization *initid, char *is_null, char *error)

{

/* Resets the total number of each new group to 0. Of course, you must assign a variable of type longlong in the init function and assign it to the pointer.

*/

*((longlong *)initid-& gt； ptr)= 0；

}

Void MyTest _ add(UDF _ initial * initial id, UDF _ ARGS * parameter, char *is_null, char * error)

{

//Add the current value to the total of each column.

*((longlong *)initid-& gt； ptr)= *(longlong *)initid-& gt； ptr) +

*((longlong *)args-& gt； args[0])；

}

Longmytest (UDF _ initial * initial id, UDF _ ARGS * parameter, char *is_null, char * error)

{

//finally return the total value

return *(longlong *)initid-& gt； ptr)；

}

6) Other questions:

When writing some complex UDFs, we need to pay attention to several issues:

The string function should return a pointer to the result and set *result and *length as the length values of the directory and the return value. For example:

Memcpy (result, "result string",13);

* Length =13;

The result buffer created by MyTest is 255 bytes. If the results are saved in it. Don't worry about the resulting memory allocation.

If the string function of needs to return a string longer than 255 bytes. It must be allocated with malloc or the new MyTest_init or MyTest function, and then released with MyTest_deinit. You can save the allocated memory address with a pointer to UDF_INIT and reuse it in MyTest.

Specify an error return in the main function and set *error to 1: If MyTest () is any column and *error is set to 1, the value of this function is NULL for the current column and for any concurrent column request in the declaration called by MyTest ().

For more information, please read MYSQL online help.

7) Some guidelines:

If you really want 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 enjoyed in ordinary libraries.)

Do not assign any global or static variables.

Always detect the type of member. Just like MYSQL converts all types into character types. If you convert the character type to an integer pointer, an error may occur.

Pay special attention to the allocation of memory. If there is a memory leak, the server will completely crash!

8)UDF mode

It takes courage to debug UDF, because if something goes wrong with UDF, the whole MYSQL server will crash every time. So I wrote a command line tool to solve this problem. As long as you run it, it will imitate the "SELECT" call instruction to save the results to the library file, and all the result lines can be printed. Therefore, when there are some errors in UDF, only the program crashes, not the whole server.