CREATE FUNCTION

A user-defined function is in essence a set of MySQL statements that may be called as a unit, processing any data it's given in its parameters and returning a value to the caller of the function.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

CREATE
[DEFINER = {'user'@'host'|CURRENT_USER}]
FUNCTION function ([parameter data_type[,...]])
RETURNS data_type
  [LANGUAGE SQL]
  [[NOT] DETERMINISTIC]
  [{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}]
  [COMMENT 'string']
  [SQL SECURITY {DEFINER|INVOKER}]
RETURN routine

Explanation

A user-defined function is in essence a set of MySQL statements that may be called as a unit, processing any data it's given in its parameters and returning a value to the caller of the function. This is similar to a stored procedure, except that a function returns a value and a stored procedure does not. A stored procedure normally places the values it generates in user variables that can then be retrieved in various ways.

The basic, minimum syntax is something like:

CREATE FUNCTION function_name (parameter) RETURNS INT RETURN routine

The function name given can be any MySQL non-reserved name; don't use the name of a built-in function. The name is case-insensitive. Within parentheses, give a comma-separated list of the parameters. For each parameter, specify the data type to be used (INT, CHAR, etc.). The keyword RETURNS is followed by the data type of the value that will be returned by the function. At the end comes the keyword RETURN followed by the routine to perform.

You may provide special parameters to indicate the characteristics of the stored procedure. Several may be given in any order, in a space-separated list. You can specify the language used as SQL with the LANGUAGE SQL parameter, but this is the default and usually unnecessary.

A function that returns the same results each time for the same given parameters is considered deterministic. You can save processing time on the server by specifying this property through the DETERMINISTIC parameter. NOT DETERMINISTIC is the default.

One set of keywords tells the server how the function will interact with it, allowing the server to optimize the function. The server does not enforce the restrictions on the function, however:

  • CONTAINS SQL
  • The function executes SQL statements, but does not read from or write to a table; one example is s function that queries server status. This is the default.

  • NO SQL
  • The function does not contain any SQL statements.

  • READS SQL DATA
  • The function might read data from at least one table, but doesn't write data to any tables.

  • MODIFIES SQL DATA
  • The function might write data to at least one table, as well potentially reading data from tables.

With the COMMENT clause you can add a comment describing the function for future reference.

This MySQL statement requires the CREATE ROUTINE privilege. The ALTER ROUTINE and EXECUTE privileges are granted to the user and account that creates or alters a routine, by default. With the DEFINER clause, you can specify the MySQL user and host to be used by MySQL for the function. Related to this clause is SQL SECURITY keyword, which instructs MySQL to use either the user account of the creator (DEFINER) of the function or the account that's calling the function (INVOKER). This can help to prevent some users from accessing restricted functions.

Examples

An example using this MySQL statement follows:

CREATE FUNCTION date_reformatted (new_date VARCHAR(13))
RETURNS DATE
RETURN STR_TO_DATE(REPLACE(new_date, '.', ''), '%b %d, %Y');

SELECT date_reformatted('Dec. 7, 2007') 
AS proper_date;

+-------------+
| proper_date |
+-------------+
| 2007-12-07  |
+-------------+

This function simply uses the STR_TO_DATE( ) function to convert a string to a particular date format (i.e., yyyy-mm-dd) based on a common string that users may give. It expects the data given to be no more than thirteen characters long. Because some users may include a period after the abbreviated month name and some may not, the function uses the REPLACE( ) function to remove the period. A function like this one can be used in any type of statement (e.g., an UPDATE statement to set a column value.

To change an existing user-defined function, use the ALTER FUNCTION statement. The MySQL statement, DROP FUNCTION removes a user-defined function. You cannot change standard, built-in functions.