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
MySQL source distributions.
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 host name.
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). Copy the shared object to the
server's plugin directory and name it
udf_example.so. This directory is given
by the value of the
plugin_dir system variable.
This is a change in MySQL 5.1. For earlier versions of MySQL, the shared object can be located in any directory that is searched by your system's dynamic linker.
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.1. See Section 2.1.2, “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
sqldirectory. There are files named
udf_example.cthere. 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