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 manual UDF loading at runtime and automatic loading during server startup.

While a UDF is loaded, information about it is available as described in Section 5.6.2, “Obtaining User-Defined Function Information”.

Installing User-Defined Functions

To load a UDF manually, 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.

CREATE FUNCTION has these effects:

It loads the UDF into the server to make it available immediately.
It registers the UDF in the mysql.func system table to make it persistent across server restarts. For this reason, CREATE FUNCTION requires the INSERT privilege for the mysql system database.

Automatic UDF loading occurs during the normal server startup sequence. The server loads UDFs registered in the mysql.func table. If the server is started with the --skip-grant-tables option, UDFs registered in the table are not loaded and are unavailable.

Uninstalling User-Defined Functions

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

DROP FUNCTION metaphon;

DROP FUNCTION has these effects:

It unloads the UDF to make it unavailable.
It removes the UDF from the mysql.func system table. For this reason, DROP FUNCTION requires the DELETE privilege for the mysql system database. With the UDF no longer registered in the mysql.func table, the server does not load the UDF during subsequent restarts.

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”. CREATE FUNCTION adds the UDF to the table and DROP FUNCTION removes it.

Reinstalling or Upgrading User-Defined Functions

To reinstall or upgrade the shared library associated with a UDF, issue a DROP FUNCTION statement, upgrade the shared library, and then issue a CREATE FUNCTION statement. If you upgrade the shared library first and then use DROP FUNCTION, the server may unexpectedly shut down.