Chapter 6 Adding Functions to MySQL

Table of Contents

6.1 Adding a Native Function

6.2 Adding a Loadable Function

There are three ways to add a new function to MySQL:

• Create a stored function (a type of stored object). A stored function is written using SQL statements rather than by compiling object code. The syntax for writing stored functions is not covered here. See Using Stored Routines.

• Create a native (built-in) MySQL function. A native function is added by modifying the MySQL source code to be compiled into the mysqld server and become available on a permanent basis. See Section 6.1, “Adding a Native Function”.

• Use the loadable function interface. A loadable function is compiled as a library file and then loaded and unloaded from the server dynamically using the CREATE FUNCTION and DROP FUNCTION statements. See Section 6.2, “Adding a Loadable Function”.

Note

Loadable functions previously were known as user-defined functions (UDFs). That terminology was something of a misnomer because “user-defined” also can apply to stored functions written using SQL and native functions added by modifying the server source code.

Each method of creating compiled functions has advantages and disadvantages:

• Adding a native function requires modifying a source distribution. Adding a loadable function does not; it can be added to a binary MySQL distribution with no access to MySQL source necessary.

• A loadable function is contained in an object file that you must install in addition to the server itself. For a function compiled into the server, that is unnecessary.

• If you upgrade your MySQL distribution, you can continue to use previously installed loadable functions, unless you upgrade to a newer MySQL version for which the loadable function interface changes. For native functions, you must repeat your source code modifications each time you upgrade.

Regardless of the method used to add a function, it can be invoked in SQL statements just like native functions such as ABS() or SOUNDEX().

For the rules describing how the server interprets references to different kinds of functions, see Function Name Parsing and Resolution.

The following sections describe features of the loadable function interface, provide instructions for writing loadable functions, discuss security precautions that MySQL takes to prevent loadable function misuse, and describe how to add native MySQL functions.

For example source code that illustrates how to write loadable functions, take a look at the sql/udf_example.cc file that is provided in MySQL source distributions.