How to create a function in MySQL database server

In programming, a function is a piece of business process or calculation that’s encapsulated under a function name.

When you need to run the process or calculation, you can do so by just calling the function name.

Here’s an example function in JavaScript:

function hello() {
  console.log("Hello World")
}

hello() // prints Hello World

In MySQL, you can create a stored function by using the CREATE FUNCTION syntax.

The basic syntax for creating a function in MySQL is as follows:

CREATE FUNCTION [function name] (
    [parameter name] [parameter type],
    [parameter name] [parameter type],
    ...
)

RETURNS [data type] [DETERMINISTIC]
RETURN [expression]

First, you need to specify the name of the function in [function name].

Next, you need to declare any parameters you need to receive for the function.

You can declare as many parameters as you need, but it’s best for a single function to have between 0 to 5 parameters. Too many parameters may confuse you when maintaining the program later.

Once you have the parameters, specify the data type returned by the function using the RETURNS keyword.

You also need to specify whether the function is DETERMINISTIC or NOT DETERMINISTIC next to the RETURNS keyword.

DETERMINISTIC means that the function will always return the same output for the same input, allowing MySQL to optimize the function execution.

When you’re not sure, just specify DETERMINISTIC because that’s the default MySQL will use for functions.

Finally, you need to specify the [expression] returned by the function using the RETURN keyword.

Here’s a MySQL function that’s similar to the JavaScript function above:

CREATE FUNCTION hello ()
RETURNS VARCHAR(20) DETERMINISTIC
RETURN "Hello World";

Once you run the statement above, you can query the function with a SELECT statement as follows:

SELECT hello();

-- Output:
-- +-------------+
-- | hello()     |
-- +-------------+
-- | Hello World |
-- +-------------+

A MySQL function name is unique, which means you can only have one function with a certain name.

Running the CREATE FUNCTION syntax twice will cause ERROR 1304 even when the same function has different parameters:

CREATE FUNCTION hello (
  input1 VARCHAR(10)
)
RETURNS VARCHAR(20) DETERMINISTIC
RETURN "Hello World";

ERROR 1304 (42000): FUNCTION hello already exists

To modify an existing function, you need to first drop the function using DROP FUNCTION syntax, then run the CREATE FUNCTION syntax again.

DROP FUNCTION IF EXISTS hello;
CREATE FUNCTION hello 
-- the rest of the function ...

Create a MySQL function with multiple statements

By default, a MySQL function can only execute one RETURN statement in its body.

When you need to run a complex process with multiple SQL statements, then you need to add a DELIMITER clause and the BEGIN ... END compound statement in your CREATE FUNCTION syntax

Here’s an example of a function with multiple statements:

DELIMITER //
CREATE FUNCTION calculateScore (
  score INT
)
RETURNS VARCHAR(50) DETERMINISTIC
BEGIN
  DECLARE comment VARCHAR(30);
  IF score <= 500 THEN 
     SET comment = 'Try again!';
  ELSE 
     SET comment = 'Awesome!';
    END IF;
  RETURN comment;
END; //

The function calculateScore above calculates an arbitrary score and returns a different comment based on the score value.

When the value is 500 or below, the comment will be 'Try again!'. Else, it will be 'Awesome!'.

Because the function has multiple statements, the delimiter is changed from the default semicolon ; to a double slash // using the DELIMITER keyword.

This allows you to write the DECLARE, IF-ELSE, and RETURN statements above as a whole inside the BEGIN ... END statement.

Without the DELIMITER statement, then MySQL will interpret each statement above one by one, causing an error.

Here’s the result when you try to query the function using a SELECT statement as follows:

SELECT calculateScore(200), calculateScore(600);

+---------------------+---------------------+
| calculateScore(200) | calculateScore(600) |
+---------------------+---------------------+
| Try again!          | Awesome!            |
+---------------------+---------------------+

And that’s how you can create a function in MySQL database server.

By storing a piece of process or calculation inside a function, you can execute the same process as many times as you need by simply calling the function as shown in the SELECT statement above.

Great job on learning how to create a MySQL function! 👍