MySQL UDF (Custom function)

Introduction to UDFs

Official Introduction Please click Http://dev.mysql.com/doc/refman/5.5/en/adding-functions.html

UDF is an extension of MySQL interface, UDF (UserDefined function) can be translated into user-defined functions, this is used to expand the technical means of MySQL.

Background: SELECT COUNT (*) from table;

The above count is the internal method of MySQL, but after all, is MySQL's own definition, sometimes does not meet our own needs, so MySQL provides such an interface, allowing users to define their own function functions;

The development UDF needs to use C + +, I use C++,ide is vc++6.0, then write the UDF needs MySQL to provide the file header, of course, if you write in C + + to access the MySQL database program, this is not a problem; These files are My_ Alloc.h,my_global.h,my_list.h,mysql.h,mysql_com.h,mysql_time.h,mysql_ Version.h,typelib.h; These header files can be found from the Include folder of your MySQL database server installed, but if you do not have the option to install the development package when you install MySQL, you will not find this folder, so you are a tragedy, the corresponding header file from the Internet, the results Did not frighten oneself want, but down down some malicious software, I also did not install, so I very tragic the database to collapse, and then reinstall a full version of;

Okay, open vs. Create a new Win32 dynamic link library project named my_udf;

In a new source file;

Check into the following simple code to do the test, of course, do not forget the project under the above mentioned several header files (import MySQL library file);

#ifDefined (_WIN32)#defineDllexp __declspec (dllexport)#else#defineDllexp#endif#include<stdlib.h>#include<string.h>#include<stdio.h>#include<mysql.h>#ifndef __mysqludf#define__mysqludfextern "C"{#endif    /** * My_name*/dllexp my_bool my_name_init (udf_init*initid, Udf_args *args,Char*message) {            return 0; } dllexpvoidMy_name_deinit (Udf_init *Initid) {} DllexpChar* My_name (Udf_init *initid, Udf_args *args,Char*is_null,Char*error) {            return "mysql_udf"; } #ifdef __MYSQLUDF}#endif

Good, now you can generate a dynamic chain library, I generated here is My_udf.dll, copy it to your MySQL installation directory in the Lib directory inside the Pulgin (E:\MySQL\MySQL Server 5.6\lib\plugin),

Of course, here in fact, as long as the test to the system path can be found;

Then you need to register your defined functions in MySQL;

CREATE [AGGREGATE] FUNCTION function_name  RETURNS {string| integer| REAL} SONAME shared_library_nameDROP FUNCTION function_name    

The first sentence means to create an aggregate or non-aggregate function, the name of the call in SQL is function_name, and the return type is {string| integer| REAL} One of these, and then Shared_library_name is the name of the dynamic link library that you generated,

Seemingly but not sure is your function name and the above source code in the method must be consistent, this does not doubt, in addition to the seemingly dynamic link library, the name also has to drink the name of the function of the same;

Execute the following SQL statement loading method:

CREATE FUNCTION RETURNS ' My_udf.dll ';

After successful creation, a corresponding record is added from the Func table of the MySQL server's MySQL database;

If you want to delete this custom function then use the second statement to remove it;

DROP FUNCTION IF EXISTS my_name;

As for the above code to run without problems, but the key is that the parameters are how a situation, here do not do too much introduction, can refer to

Http://dev.mysql.com/doc/refman/5.1/zh/extending-mysql.html

Record the information I need:

Description main function xxx (). Note that the return value and parameters vary, depending on whether the SQL function that you are stating, XXX (), returns the String,integer type or the real type in the CREATE function declaration:

For a String type function:

Char Char Long Char Char *error);

For an integer type function:

Long Long Char Char *error);

For a real type function:

Double Char Char *error);

For each invocation of the main function, Args->args contains the actual parameters passed for each currently processed row.

Use the function of parameter I as follows:

Give a string_result parameter as a string plus a length that allows all binary numbers or any number of lengths to be processed. String content as Args->args[i] and string length is args->lengths[i]. You cannot use a null-terminated string.

• For a int_result type parameter, you must convert args->args[i] to a long Long value:

Longlong Int_val; Int_val = * ((longlong*) args->args[i]);

• For a real_result type parameter, you must convert args->args[i] to a double value:

double real_val; Real_val = * (double*) args->args[i]);

• unsigned long *lengths

For initialization functions, the lengths sequence represents the maximum string length for each parameter. You don't change it. For each invocation of the main function, lengths contains the actual length of any string parameter passed to the current processing line. For Int_result or Real_result type parameters, lengths still contains the maximum length of the parameter (for the initialization function).

Finally, you can call select My_name (); To call the method, return the mysql_udf

MySQL UDF (Custom function)