Files implementing UDFs must be compiled and installed on the
host where the server runs. This process is described below
for the example UDF file
sql/udf_example.c that is included in the
MySQL source distribution.
If a UDF will be referred to in statements that will be replicated to slave servers, you must ensure that every slave also has the function available. Otherwise, replication will fail on the slaves when they attempt to invoke the function.
The immediately following instructions are for Unix. Instructions for Windows are given later in this section.
udf_example.c file contains the
metaphon() returns a metaphon string of
the string argument. This is something like a soundex
string, but it is more tuned for English.
myfunc_double() returns the sum of the
ASCII values of the characters in its arguments, divided
by the sum of the length of its arguments.
myfunc_int() returns the sum of the
length of its arguments.
sequence([const int]) returns a
sequence starting from the given number or 1 if no number
has been given.
lookup() returns the IP address for a
reverse_lookup() returns the host name
for an IP address. The function may be called either with
a single string argument of the form
'xxx.xxx.xxx.xxx' or with four numbers.
avgcost() returns an average cost. This
is an aggregate function.
A dynamically loadable file should be compiled as a sharable object file, using a command something like this:
gcc -shared -o udf_example.so udf_example.c
If you are using gcc with
configure and libtool
(which is how MySQL is configured), you should be able to
udf_example.so with a simpler
After you compile a shared object containing UDFs, you must
install it and tell MySQL about it. Compiling a shared object
gcc directly produces a file named
udf_example.so. Compiling the shared
object using make produces a file named
.libs directory (the exact name may
vary from platform to platform).
As of MySQL 5.0.67, copy the shared object to server's plugin
directory and name it
This directory is given by the value of the
plugin_dir system variable.
Prior to MySQL 5.0.67, or if the value of
plugin_dir is empty, the
shared object should be placed in a directory such as
/usr/lib that is searched by your
system's dynamic (runtime) linker, or you can add the
directory in which you place the shared object to the linker
configuration file (for example,
On many systems, you can also set the
LD_LIBRARY_PATH environment variable to
point at the directory where you have the files for your UDF.
You should set the variable in mysql.server
or mysqld_safe startup scripts and restart
mysqld. You might do this if you want to
place the object file in a directory accessible only to the
server and not in a public directory. The
dlopen manual page tells you which variable
to use on your system.
The dynamic linker name is system-specific (for example, ld-elf.so.1 on FreeBSD, ld.so on Linux, or dyld on OS X). Consult your system documentation for information about the linker name and how to configure it.
On some systems, the ldconfig program that
configures the dynamic linker does not recognize a shared
object unless its name begins with
this case you should rename a file such as
On Windows, you can compile user-defined functions by using the following procedure:
Obtain the development source for MySQL 5.0. See Section 2.5, “How to Get MySQL”.
Obtain the CMake build utility, if necessary, from http://www.cmake.org. (Version 2.6 or later is required).
In the source tree, look in the
directory. There are files named
udf_example.c there. Copy both files
from this directory to your working directory.
Create a CMake
CMakeLists.txt) with these contents:
PROJECT(udf_example) # Path for MySQL include directory INCLUDE_DIRECTORIES("c:/mysql/include") ADD_DEFINITIONS("-DHAVE_DLOPEN") ADD_LIBRARY(udf_example MODULE udf_example.c udf_example.def) TARGET_LINK_LIBRARIES(udf_example wsock32)
Create the VC project and solution files:
cmake -G "<Generator>"
Invoking cmake --help shows you a list of valid Generators.
devenv udf_example.sln /build Release
After the shared object file has been installed, notify
mysqld about the new functions with the
following statements. If object files have a suffix different
.so on your system, substitute the
correct suffix throughout (for example,
.dll on Windows).
CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';mysql>
CREATE FUNCTION myfunc_double RETURNS REAL SONAME 'udf_example.so';mysql>
CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME 'udf_example.so';mysql>
CREATE FUNCTION sequence RETURNS INTEGER SONAME 'udf_example.so';mysql>
CREATE FUNCTION lookup RETURNS STRING SONAME 'udf_example.so';mysql>
CREATE FUNCTION reverse_lookup->
RETURNS STRING SONAME 'udf_example.so';mysql>
CREATE AGGREGATE FUNCTION avgcost->
RETURNS REAL SONAME 'udf_example.so';
Once installed, a function remains installed until it is uninstalled.
To delete functions, use
DROP FUNCTION metaphon;mysql>
DROP FUNCTION myfunc_double;mysql>
DROP FUNCTION myfunc_int;mysql>
DROP FUNCTION sequence;mysql>
DROP FUNCTION lookup;mysql>
DROP FUNCTION reverse_lookup;mysql>
DROP FUNCTION avgcost;
CREATE FUNCTION and
DROP FUNCTION statements update
func system table in the
mysql database. The function's name, type
and shared library name are saved in the table. You must have
DELETE privilege for the
mysql database to create or drop functions,
You should not use
FUNCTION to add a function that has previously been
created. If you need to reinstall a function, you should
remove it with
and then reinstall it with
FUNCTION. You would need to do this, for example, if
you recompile a new version of your function, so that
mysqld gets the new version. Otherwise, the
server continues to use the old version.
An active function is one that has been loaded with
CREATE FUNCTION and not removed
DROP FUNCTION. All active
functions are reloaded each time the server starts, unless you
start mysqld with the
--skip-grant-tables option. In
this case, UDF initialization is skipped and UDFs are