How to Write a MySQL UDF

From: http://blogread.cn/it/article/731? F = WB

The first time I heard about UDF, I was confused. It was also fooled by a half-bucket trainer to say that the storage function is UDF. It's incredible to think about it now.

From the name, we can know that the UDF (user define function) is a user-defined function. To some extent, udfs allow common users to customize their own MySQL function libraries to reduce the dependency on internal functions. UDF is very powerful.

Various technical personnel can develop corresponding udfs.

The administrator can develop udfs that interact with the system. Developers can replace common functions with udfs, which greatly improves the execution speed and efficiency.

This blog aims to help readers who want to write udfs, but they should add some specific functions as needed.

Now, the MySQL Manual provides some MySQL UDF rules. For more information, seeLink.

Create a UDF in four steps:

1. Write code
2. Compile
3. Install
4. Use

A simple UDF is provided below. The function is to write statements into the error log. To do this experiment, we will use the following Platform:

• OS: opensolaris,
• DB: MySQL 5.1.39
• C compiler: Sun C
  

The instance code is as follows:

<Tr

The following is a code snippet: /* You owed me When you copy this code without informing dingze.zhu@gmail.com * It's owned by mysqlsystems.com */ # If defined (_ Win32) # Define dllexp _ declspec (dllexport) # Else # Define dllexp # Endif # Include <stdlib. h> # Include <string. h> # Include <stdio. h> # Include <mysql. h> # Define libversion "mysqlsystems_udf_log version 0.1 beta" # Define err_arg "Hiro tells you: Exactly one argument expected !" # Define err_mem "out of memory !" # Ifdef _ cplusplus Extern "C "{ # Endif /** * Mysqlsystems_udf_log */ Dllexp My_bool mysqlsystems_udf_log_init ( Udf_init * initid , Udf_args * ARGs , Char * message ){ Return 0; } Dllexp Void mysqlsystems_udf_log_deinit ( Udf_init * initid ){} Dllexp Char * mysqlsystems_udf_log ( Udf_init * initid , Udf_args * ARGs , Char * result , Unsigned long * length , Char * is_null , Char * Error ){ * Length = strlen (libversion ); Return libversion; } /** * Another One: log2error */ Dllexp My_bool log2error_init ( Udf_init * initid , Udf_args * ARGs , Char * message ){ If (ARGs-> arg_count = 1 ){ // Alloc mem for format pattern: // "% S0.xxxx \ n" // Where XXXX is the max // 7 = length of % s0. \ n + trailing \ 0 If (! (Initid-> PTR = (char *) malloc (7 + 4 ))){ Strcpy (message, err_mem ); Return 1; } ARGs-> arg_type [0] = string_result; Initid-> maybe_null = 0; } Else { Strcpy (message, err_arg ); Return 1; } Return 0; } Dllexp Void log2error_deinit ( Udf_init * initid ){ If (initid-> PTR) { Fflush (stderr ); Free (initid-> PTR ); } } Dllexp My_ulonglong log2error ( Udf_init * initid , Udf_args * ARGs , Char * is_null , Char * Error ){ Int numdigits; Char * FMT; * Is_null = 0; If (ARGs-> ARGs [0] = NULL ){ Fprintf (stderr, "null \ n "); } Else { FMt = (char *) initid-> PTR; Memcpy (FMT, "% 0.", 3 ); Sprintf (FMT + 3, "% d", argS-> lengths [0] <= 9998? ARGs-> lengths [0]: 9998 ); Numdigits = strlen (FMT + 4 ); Memcpy (FMT + 4 + numdigits, "S \ n \ 0", 3 ); Fprintf (stderr, FMT, argS-> ARGs [0]); } Return 0; } # Ifdef _ cplusplus } # Endif

After writing the code, you need to compile the source code into a dynamic library. So file. Then copy the dynamic library to the LIB/plugin MySQL installation directory. (We can also find the dynamic library file of InnoDB)

Based on your OS, MySQL, and other environments. There may be some discrepancies. In this experiment, use the following

CC-I/user/local/MySQL/include-shared-O mysqlsystems_udf_log.so mysqlsystems_udf_log.c

Is the process of installation and use:

View the MySQL Error Log:

As you can see, on Error Log 22nd, the Hello world message is displayed.