How to Create a Function in MySQL: Syntax and Examples

In MySQL, you create a function using the CREATE FUNCTION statement followed by the function name, parameters, return type, and body. The function must return a value using RETURN and is defined inside a BEGIN ... END block.

Syntax

The basic syntax to create a function in MySQL includes the function name, input parameters, return type, and the function body enclosed in BEGIN ... END. You must specify DETERMINISTIC or NOT DETERMINISTIC and the SQL data access type.

• CREATE FUNCTION: starts the function definition.
• function_name: the name you choose for your function.
• (parameters): input values with their data types.
• RETURNS: the data type of the value the function returns.
• DETERMINISTIC: declares if the function always returns the same result for the same inputs.
• BEGIN ... END: the block containing the function logic.
• RETURN: returns the result value.

sql

CREATE FUNCTION function_name(parameter_name datatype) RETURNS datatype
DETERMINISTIC
BEGIN
  -- function logic
  RETURN value;
END

Example

This example creates a function named get_square that takes an integer and returns its square.

sql

DELIMITER $$
CREATE FUNCTION get_square(n INT) RETURNS INT
DETERMINISTIC
BEGIN
  RETURN n * n;
END$$
DELIMITER ;

-- Usage example:
SELECT get_square(5) AS square_result;

Output

square_result
25

Common Pitfalls

Common mistakes when creating functions in MySQL include:

• Not changing the DELIMITER before and after the function definition, causing syntax errors.
• Forgetting to include the RETURN statement inside the function.
• Using variables or statements not allowed in stored functions, like COMMIT or ROLLBACK.
• Not specifying DETERMINISTIC or NOT DETERMINISTIC, which can cause warnings.

Example of a wrong and right way to set delimiter:

sql

/* Wrong way - missing DELIMITER change */
CREATE FUNCTION wrong_func() RETURNS INT
BEGIN
  RETURN 1;
END;

/* Right way - change DELIMITER */
DELIMITER $$
CREATE FUNCTION right_func() RETURNS INT
BEGIN
  RETURN 1;
END$$
DELIMITER ;

Quick Reference

Part	Description
CREATE FUNCTION	Starts the function definition
function_name	Name of the function
(parameters)	Input parameters with data types
RETURNS datatype	Data type of the return value
DETERMINISTIC / NOT DETERMINISTIC	Indicates if function always returns same result for same inputs
BEGIN ... END	Block containing function logic
RETURN value	Returns the result from the function
DELIMITER	Change delimiter to define function body

Key Takeaways

Use CREATE FUNCTION with parameters, RETURNS, and a BEGIN...END block to define a MySQL function.

Always change the DELIMITER before and after the function definition to avoid syntax errors.

Include a RETURN statement to send back the function result.

Specify DETERMINISTIC or NOT DETERMINISTIC to clarify function behavior.

Avoid using disallowed statements like COMMIT inside functions.