[+/-]
For the UDF mechanism to work, functions must be written in C or
C++ (or another language that can use C calling conventions),
and your operating system must support dynamic loading. The
MySQL source distribution includes a file
sql/udf_example.cc
that defines 5 new
functions. Consult this file to see how UDF calling conventions
work. UDF-related symbols and data structures are defined in the
include/mysql_com.h
header file. (You need
not include this header file directly because it is included by
mysql.h
.)
A UDF contains code that becomes part of the running server, so
when you write a UDF, you are bound by any and all constraints
that otherwise apply to writing server code. For example, you
may have problems if you attempt to use functions from the
libstdc++
library. Note that these
constraints may change in future versions of the server, so it
is possible that server upgrades will require revisions to UDFs
that were originally written for older servers. For information
about these constraints, see
Section 2.9.2, “Typical configure Options”, and
Section 2.9.4, “Dealing with Problems Compiling MySQL”.
To be able to use UDFs, you need to link
mysqld dynamically. Don't configure MySQL
using --with-mysqld-ldflags=-all-static
. If you
want to use a UDF that needs to access symbols from
mysqld (for example, the
metaphone
function in
sql/udf_example.cc
that uses
default_charset_info
), you must link the
program with -rdynamic
(see man
dlopen
). If you plan to use UDFs, the rule of thumb is
to configure MySQL with
--with-mysqld-ldflags=-rdynamic
unless you have
a very good reason not to.
If you must use a precompiled distribution of MySQL, use MySQL-Max, which contains a dynamically linked server that supports dynamic loading.
For each function that you want to use in SQL statements, you
should define corresponding C (or C++) functions. In the
following discussion, the name “xxx” is used for an
example function name. To distinguish between SQL and C/C++
usage, XXX()
(uppercase) indicates an SQL
function call, and xxx()
(lowercase)
indicates a C/C++ function call.
When using C++ you can encapsulate your C functions within:
extern "C" { ... }
This will ensure that your C++ function names remain readble in the completed UDF.
The C/C++ functions that you write to implement the interface
for XXX()
are:
xxx()
(required)
The main function. This is where the function result is computed. The correspondence between the SQL function data type and the return type of your C/C++ function is shown here.
xxx_init()
(optional)
The initialization function for xxx()
. It
can be used for the following purposes:
To check the number of arguments to
XXX()
.
To check that the arguments are of a required type or, alternatively, to tell MySQL to coerce arguments to the types you want when the main function is called.
To allocate any memory required by the main function.
To specify the maximum length of the result.
To specify (for REAL
functions) the maximum number of decimal places in the
result.
To specify whether the result can be
NULL
.
xxx_deinit()
(optional)
The deinitialization function for xxx()
.
It should deallocate any memory allocated by the
initialization function.
When an SQL statement invokes XXX()
, MySQL
calls the initialization function xxx_init()
to let it perform any required setup, such as argument checking
or memory allocation. If xxx_init()
returns
an error, MySQL aborts the SQL statement with an error message
and does not call the main or deinitialization functions.
Otherwise, MySQL calls the main function
xxx()
once for each row. After all rows have
been processed, MySQL calls the deinitialization function
xxx_deinit()
so that it can perform any
required cleanup.
For aggregate functions that work like
SUM()
, you must also provide the
following functions:
xxx_reset()
(required before 4.1.1)
Reset the current aggregate value and insert the argument as the initial aggregate value for a new group.
xxx_clear()
(required starting from
4.1.1)
Reset the current aggregate value but do not insert the argument as the initial aggregate value for a new group.
xxx_add()
Add the argument to the current aggregate value.
MySQL handles aggregate UDFs as follows:
Call xxx_init()
to let the aggregate
function allocate any memory it needs for storing results.
Sort the table according to the GROUP BY
expression.
Before MySQL 4.1.1, call xxx_clear()
for
the first row in each new group. As of 4.1.1, call
xxx_clear()
for the first row in each new
group.
Before MySQL 4.1.1, call xxx_add()
for
each new row that belongs in the same group, except for the
first row. As of 4.1.1, call xxx_add()
for each new row that belongs in the same group, including
the first row.
Call xxx()
to get the result for the
aggregate when the group changes or after the last row has
been processed.
Repeat 3–5 until all rows has been processed
Call xxx_deinit()
to let the UDF free any
memory it has allocated.
All functions must be thread-safe. This includes not just the
main function, but the initialization and deinitialization
functions as well, and also the additional functions required by
aggregate functions. A consequence of this requirement is that
you are not allowed to allocate any global or static variables
that change! If you need memory, you should allocate it in
xxx_init()
and free it in
xxx_deinit()
.
User Comments
Note that in aggregate functions, the order in which MySQL calls xxx_add is not necessarily the order of the rows in the original table. I don't know if there is a way to set the order.
Add your own comment.