Documentation Home
Extending MySQL 5.7
Download this Manual
PDF (US Ltr) - 410.2Kb
PDF (A4) - 409.1Kb


Extending MySQL 5.7  /  Adding Functions to MySQL

Chapter 6 Adding Functions to MySQL

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. SeeUsing Stored Routines.

  • Create a native (built-in) MySQL function. A native function is added by modifying the MySQL source code to be compiled into themysqld server and become available on a permanent basis. SeeSection 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 theCREATE FUNCTION andDROP FUNCTION statements. SeeSection 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 asABS() orSOUNDEX().

For the rules describing how the server interprets references to different kinds of functions, seeFunction 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 thesql/udf_example.cc file that is provided in MySQL source distributions.