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.
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's 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 number for a
reverse_lookup() returns the hostname
for an IP number. The function may be called either with a
single string argument of the form
'xxx.xxx.xxx.xxx' or with four numbers.
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
variable. (Note: This 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:
You need to obtain the BitKeeper source repository for MySQL 5.1. See 「開発ソース ツリーからのインストール」.
You must obtain the CMake build utility from http://www.cmake.org. (Version 2.4.2 or later is required).
In the source repository, look in the
sql directory. There are files named
udf_example.c there. Copy both files
from this directory to your working directory.
Create a CMake
makefile with these
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 these statements:
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 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';
Functions can be deleted using
DROP FUNCTION metaphon;mysql>
DROP FUNCTION myfunc_double;mysql>
DROP FUNCTION myfunc_int;mysql>
DROP FUNCTION lookup;mysql>
DROP FUNCTION reverse_lookup;mysql>
DROP FUNCTION avgcost;
CREATE FUNCTION and
FUNCTION statements update the
func system table in the
mysql database. The function's name, type
and shared library name are saved in the table. You must have
privileges for the
mysql database to create
and drop functions.
You should not use
CREATE FUNCTION to add a
function that has previously been created. If you need to
reinstall a function, you should remove it with
FUNCTION 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 with
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 unavailable.