Click here to Skip to main content
15,868,141 members
Articles / Database Development / MySQL
Article

MySQL User Defined Functions

Rate me:
Please Sign up or sign in to vote.
4.85/5 (5 votes)
20 Sep 20066 min read 125K   2.3K   61   3
Tutorial on writing your own MySQL User Defines Functions

Sample Image - UDF.jpg

This tutorial explains what an User Defined Function (UDF) is, what it does and why/when they are useful.

1. What is an User Defined Function?

Basically an User Defined Function (UDF) is a piece code that extends the functionality of a MySQL server by adding a new function that behaves just like a native (built-in) MySQL function like abs() or concat(). UDFs are written in C (or C++ if you really need to)! ... well maybe there is a way to write them in BASIC, .NET or whatever but I don't see why anybody would want to do that.

2. Why/When are UDFs useful?

As implied by the name UDFs are useful when you need to extend the functionality of your MySQL server. This little table should make it clear which method is best for a given situation:

Method

Speed

Language

Development

Stored Procedures

slow

SQL

~minutes (for small functions)

UDF

fast

C

~hour

Native Function

fast

C

major pain in the ***

And by "slow" I mean: "slower than the others"! Stored Procedures are still much faster then normal SQL statements!

A little explanation on native functions: The code you have to write here is essentially the same as the one for an UDF. BUT you have to write it in the MySQL source code and recompile the whole thing. This will (believe me) be a lot of work because you have to do it again and again with every new version of MySQL.

3. How to use UDFs?

This part is really easy. When you have your UDF finished you just use it like every other native function. For example : "SELECT MyFunction(data1, data2) FROM table"

4. Writing the UDF

Now let's get started on writing our first UDF in steps:

  1. Create a new shared-library project (in the example I used VC++ 6.0 with a standard DLL)

  2. First we need some headers. These headers are either standard library headers or from the MySQL Server's include directory

    C++
    #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 long ulonglong;
    typedef long 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;
  3. Now we have to decide what kind of function we want. There are essentially two choices to be made:

    • is the function an aggregate function or not? (we will learn more about aggregate functions later)

    • which type of return value should the function return? Here we have 4 options:

      Type

      Description

      STRING

      A string literal. Translates to char* in C

      INTEGER

      A normal integer. Translates to a 64 bit integer in C

      REAL

      A floating point number. Translates to double in C

      DECIAML

      This one isn't really finished at this time. MySQL treats it as STRING

  4. Let's talk about non-aggregate functions first. Now we have to declare and implement some functions the MySQL server needs to use our UDF. But first some structs we'll need for that:

    • UDF_INIT:

      Type

      Name

      Description

      <code>
      <p>my_bool</p>

      maybe_null

      1 if 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 result is independent of argument

    • UDF_ARGS:

      Type

      Name

      Description

      unsigned int

      arg_count

      Number of argument

      enum Item_result *

      arg_type

      Array containing the types of the arguments

      char **

      args

      Array of pointer to the arguments

      unsigned long *

      lengths

      Array of the argument's lengths (only needed for strings)

      char *

      maybe_null

      Array of "maybe_null" flags (1 if argument maybe null)

      char **

      attributes

      Array of pointers to the arguments' attributes (see chapter x for details)

      unsigned long *

      attribute_lengths

      Array of attributes lengths

    now let's take a look at the functions:

    De-/Initialization:

    C++
    extern "C" my_bool MyTest_init(UDF_INIT *initid, UDF_ARGS *args, 
                                   char *message)
    {
            // The most important thing to do here is setting up the memory
            // you need...
            // Lets say we need a lonlong type variable to keep a checksum
            // Although we do not need one in this case
            longlong* i = new longlong; // create the variable
            *i = 0;                     // set it to a value
            
            // store it as a char pointer in the pointer variable
            // Make sure that you don`t run in typecasting troubles later!!
            initid->ptr = (char*)i;
            
            // check the arguments 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)
    {
            // Here you have to free the memory you allocated in the 
            // initialization function
            delete (longlong*)initid->ptr;
    }

    The actual function:

    extern "C" longlong MyTest(UDF_INIT *initid, UDF_ARGS *args,
                               char *is_null, char *error)
    {
            /* So finally this is the part were we do the real work. This 
            function is called for every record and the current value(s) 
            or better pointers to the current values are stroed in the 
            UDF_ARGS variable. We have to get the values, do our calculation 
            and return the result. NOTE: You can access the memory 
            allocated in MyTest_init through the UDF_INIT variable.
            In this example we will just add 5 to every value...*/
            return *((longlong*)args->args[0])+5;
    }
  5. All done! Now we have to compile the library and copy it to a directory where our OS can find it. On Windows that would be anywhere the PATH System variable says. Personally I use the MySQL servers bin directory. You have to make sure that the library is in one of those directories otherwise MySQL can't use it! And also make sure to export all the functions MySQL needs!

  6. And at last we have to tell MySQL about it. This is really straightforward: Just execute the following SQL command:

    SQL
    CREATE [AGGREGATE] FUNCTION MyTest 
    RETURNS [INTEGER|STRING|REAL|DECIMAL] SONAME the_libraries_exact_name
    Now you can use it like any other function.

5. Aggregate functions

Now some words to aggregate functions. When your UDF is an aggregate function you have to add some more functions and some functions are used in a different way. The calling sequence is:

  1. Call MyTest_init to allocate memory (just like a normal UDF)
  2. MySQL sorts the table according to the GROUP BY statement
  3. Call MyTest_clear for the first row in each group
  4. Call MyTest_add for each row that belongs to the same group
  5. Call MyTest to get the result when the group changes or the last row has been processed
  6. Repeat 3 to 5 until all rows have been processed
  7. Call MyTest_deinit to free any used memory

Now let's look at the new functions needed for the aggregate function. In this example we'll simply add up all the values. (like the native SUM function)

C++
void MyTest_clear(UDF_INIT *initid, char *is_null, char *error)
{
        /* The clear function resets the sum to 0 for each new group
        Of course you have to allocate a longlong variable in the init 
        function and assign it to the pointer as seen above */
        *((longlong*)initid->ptr) = 0;
}

void MyTest_add(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)
{
        // For each row the current value is added to the sum
        *((longlong*)initid->ptr) = *((longlong*)initid->ptr) + 
                                    *((longlong*)args->args[0]);
}

longlong MyTest(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)
{
        // And in the end the sum is returned
        return *((longlong*)initid->ptr);
}

6. Advanced topics

Here are some things you should know when you write more complex UDFs:

  • A string function should return a pointer to the result and set *result and *length to the contents and length of the return value. For example:

    C++
    memcpy(result, "result string", 13);
    *length = 13;

    The result buffer that is passed to the MyTest function is 255 bytes long. If your result fits in this, you don't have to worry about memory allocation for results.

    If your string function needs to return a string longer than 255 bytes, you must allocate the space for it with malloc or new in your MyTest_init function or your MyTest function and free it in your MyTest_deinit function. You can store the allocated memory in the ptr slot in the UDF_INIT structure for reuse by future MyTest calls.

  • To indicate an error return in the main function, set *error to 1: If MyTest() sets *error to 1 for any row, the function value is NULL for the current row and for any subsequent rows processed by the statement in which MyTest() was invoked.

  • For more information see the MySQL Online Manual

7. Some guidelines

Here are some guidelines that you should follow if you want to make sure your UDF runs smoothly ;-)

  • Do not call any other applications or processes inside an UDF!

  • Do not store any information locally! (This goes for shared libraries in general)

  • Do not allocate any global or static variables!

  • Always check the type of your arguments. As you can see MySQL converts everything to char pointers. This can lead to major troubles if you convert a string literal to a integer pointer and so on.

  • Be extra careful with the memory allocation! If you have memory leaks you can bring down the sever in no time.

8. Debugging UDFs

Debugging an UDF can be pretty nerve wracking because every time your UDF crashes it takes down the whole MySQL server along with it. So I wrote a little command line tool to work around that problem. Just execute it after compilation and it does the rest. Meaning, it emulates a call to the function by calling an "SELECT" command and then passing the results to the library and printing out the result on the command line . So when the UDF produces some serious errors only the little helper goes down and not the whole server. It is still in "beta" so don't expect to much....

9. Sources

MySQL Online Manual

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
Germany Germany
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionTesting gives me this error Pin
Umbrella CO18-May-14 11:46
professionalUmbrella CO18-May-14 11:46 
Questioncan I use this function in Java? Pin
yulin112-Oct-08 15:52
yulin112-Oct-08 15:52 
Generaltrying to compiling and execution the program. Pin
jan marco30-Sep-06 21:42
jan marco30-Sep-06 21:42 
Hello DarkLizener ,

You have made a very interesting program --) The concept of User Defined Function (UDF) is very useful to me. I use XP, Visualstudio2002 and 'MySQL 4.1.20a-nt'.

I get some problems with compiling and running of the program:

> c:\tmp\tester.cpp(469) : error C2065: 'MYSQL_TYPE_TINY' : undeclared identifier
> c:\tmp\tester.cpp(469) : error C2678: binary '==' : no operator found which takes a left-hand operand of type 'enum_field_types' (or there is no acceptable conversion)
> c:\tmp\tester.cpp(469) : error C2065: 'MYSQL_TYPE_SHORT' : undeclared identifier

1) I get 20 errors on not finding MySQL definitions. If I retrieve these definitions from mysql_com.h than I get error on redefinition (error C2011: 'enum_field_types' : 'enum' type redefinition). I put in Appendix A a suggestion what seems to work.

2) If I execute the tester.exe program I get the next message: “Wrong number of arguments. Please check readme.txt!Loading aggrgate functions failed!”. I can’t find the commandline with 16 parameters in the readme.txt file.

3) Variable udfpath is not used in tester.cpp. I see hardcoded path in the source: CallUDF("C:\\Programme\\MySQL\\MySQL Server 5.0\\bin\\TestUDF.dll",…

Greetings Jan Marco

P.S. Is the dll ‘TestUDF.dll’ really needed. The question in an other way: Can the dll source not be ported in the main program?

Appendix A: Definitions reworked out of mysql_com.h:
#define MYSQL_TYPE_DECIMAL 0
#define MYSQL_TYPE_TINY 1
#define MYSQL_TYPE_SHORT 2
#define MYSQL_TYPE_LONG 3
#define MYSQL_TYPE_FLOAT 4
#define MYSQL_TYPE_DOUBLE 5
#define MYSQL_TYPE_NULL 6
#define MYSQL_TYPE_TIMESTAMP 7
#define MYSQL_TYPE_LONGLONG 8
#define MYSQL_TYPE_INT24 9
#define MYSQL_TYPE_DATE 10
#define MYSQL_TYPE_TIME 11
#define MYSQL_TYPE_DATETIME 12
#define MYSQL_TYPE_YEAR 13
#define MYSQL_TYPE_NEWDATE 14
#define MYSQL_TYPE_VARCHAR 15
#define MYSQL_TYPE_BIT 16
#define MYSQL_TYPE_NEWDECIMAL 246
#define MYSQL_TYPE_ENUM 247
#define MYSQL_TYPE_SET 248
#define MYSQL_TYPE_TINY_BLOB 249
#define MYSQL_TYPE_MEDIUM_BLOB 250
#define MYSQL_TYPE_LONG_BLOB 251
#define MYSQL_TYPE_BLOB 252
#define MYSQL_TYPE_VAR_STRING 253
#define MYSQL_TYPE_STRING 254
#define MYSQL_TYPE_GEOMETRY 255

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.