5.6.1 Installing and Uninstalling User-Defined Functions

User-defined functions (UDFs) must be loaded into the server before they can be used. MySQL supports UDF loading at runtime.

To load a UDF, use the CREATE FUNCTION statement. For example:

CREATE FUNCTION metaphon
  RETURNS STRING
  SONAME 'udf_example.so';

The UDF file base name depends on your platform. Common suffixes are .so for Unix and Unix-like systems, .dll for Windows.

While a UDF is loaded, information about it is available from the mysql.func system table. See Section 5.6.2, “Obtaining User-Defined Function Information”.

The statement also registers the UDF in the mysql.func system table to cause the server to load it on subsequent restarts. For this reason, CREATE FUNCTION requires the INSERT privilege for the mysql database.

To remove a UDF, use the DROP FUNCTION statement. For example:

DROP FUNCTION metaphon;

CREATE FUNCTION unloads the UDF and removes it from the mysql.func system table. For this reason, DROP FUNCTION statement requires the DELETE privilege for the mysql database. With the UDF no longer registered in the table, the server does not load the UDF automatically for subsequent restarts.

You cannnot use CREATE FUNCTION to reinstall a function that has previously been installed. To reinstall a function, first remove it with DROP FUNCTION, then install it again with CREATE FUNCTION. You would need to do this, for example, if you upgrade to a new version of MySQL that provides an updated implementation of the function, or you recompile a new version of a function that you have written. Otherwise, the server continues to use the old version.

If the server is started with the --skip-grant-tables option, it does not consult the mysql.func table and does not load the UDFs listed there.