Sometimes, if you spend a lot of time living in the database (using SQL routines and functions), you might either find missing functionality or find that you need to interface to other projects or libraries, directly. You might want to call a stronger random-number generator with a better entropy-source. You might want to invoke a YAML library.

MySQL has a couple of ways to add new functions: native functions (using libraries that are statically-linked into the server) and UDFs (“user-defined functions”, using libraries that are dynamically-linked with the server). Essentially the difference is whether you want to package your functionality into the server or whether you’d be willing to build it, copy it into the right place, and then tell MySQL to import it via a “create” query. In the case of the latter, you’ll have to “drop” it later, first, if it needs to be updated.

We’re going to do a quick run-through of how to write a UDF with MySQL Server 5.5 . For more information on “native” MySQL functions, you can look here. Note that we differentiate between C functions and SQL functions below by referring to the C functions as “native” functions. This is not meant to refer to MySQL’s “native” function support, which will not be referred to after this point.

It’s actually quite simple:

• Define a native “init” function or a native “deinit” function, or both, to setup and teardown your library.
• Define the main native function to do the work. You’ll get an array of argument types and values, and use a NULL to determine if you were given a NULL for an argument.
• Set the (*is_null) parameter to 1 if you’re returning a NULL (but you have to indicate this possibility from the native “init” function).
• You will return the value directly. If you’re returning a string, set the “length” parameter. You will tell MySQL what type you’re returning when you first import the function.
• You’ll also have to define native “add” and “clear” functions if you’re writing an aggregate function (e.g. COUNT, SUM, etc..). You’ll be writing an accumulator where values are loaded and then evaluated.

Writing the Plugin

#include <mysql.h>
#include <m_string.h>

#ifdef HAVE_DLOPEN

my_bool testfunc_init(
    UDF_INIT *initid, 
    UDF_ARGS *args, 
    char *message);

longlong testfunc(
    UDF_INIT *initid, 
    UDF_ARGS *args, 
    char *is_null,
    char *error);

my_bool testfunc_init(
    UDF_INIT *initid __attribute__((unused)),
    UDF_ARGS *args __attribute__((unused)),
    char *message __attribute__((unused)))
{
    if(args->arg_count != 1)
    {
        strcpy(message, "testfunc must have exactly one argument.");
        return 1;
    }

    // Allow positive or negative integers.

    if(args->arg_type[0] != REAL_RESULT && 
       args->arg_type[0] != INT_RESULT)
    {
        strcpy(message, "testfunc must have an integer.");
        return 1;
    }

    return 0;
}

longlong testfunc(
    UDF_INIT *initid __attribute__((unused)), 
    UDF_ARGS *args,
    char *is_null __attribute__((unused)),
    char *error __attribute__((unused)))
{
    longlong value;

    if(args->arg_type[0] == REAL_RESULT) 
    {
        value = (longlong)*((double *)args->args[0]);
    }
    else //if(args->arg_type[0] == INT_RESULT)
    {
        value = *((longlong *)args->args[0]);
    }

    return value * 2;
}

#endif

This example SQL function obviously just returns the original integer doubled. The difference between an “integer” and a “real” integer is, also obviously, whether or not the value is negative.

Notes:

• Plugin support can be disabled in the server. You should check for HAVE_DLOPEN to be defined.
• Reportedly, at least one of the native “init” or “deinit” functions should be defined.

Building is simple:

$ gcc -shared -o udf_test.so -I /usr/local/Cellar/mysql/5.6.16/include/mysql udf_test.c

Using the Plugin

To use the plugin, copy it into your server’s plugin directory. You can determine this from your server’s variables:

mysql> SHOW VARIABLES LIKE "plugin_dir";
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| plugin_dir    | /usr/local/Cellar/mysql/5.6.16/lib/plugin/ |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

To import a function (you may have defined more than one):

mysql> CREATE FUNCTION `testfunc` RETURNS INTEGER SONAME 'udf_test.so';
Query OK, 0 rows affected (0.00 sec)

MySQL will install it into its “func” table:

mysql> SELECT * FROM `mysql`.`func`;
+----------+-----+-------------+----------+
| name     | ret | dl          | type     |
+----------+-----+-------------+----------+
| testfunc |   2 | udf_test.so | function |
+----------+-----+-------------+----------+
1 row in set (0.00 sec)

If you need to unload it (or need to update it and unload it before doing so):

mysql> DROP FUNCTION `testfunc`;
Query OK, 0 rows affected (0.00 sec)

Testing

mysql> SELECT testfunc(111);
+---------------+
| testfunc(111) |
+---------------+
|           222 |
+---------------+
1 row in set (0.00 sec)

For more general information, see 24.3.2 Adding a New User-Defined Function. For more information on arguments, see here. For more information on return values and errors, see 22.3.2.4 UDF Return Values and Error Handling.