How to create a stored function in MySQL?

This tutorial shows you how to create a stored function and user-defined functions in MySQL using the CREATE FUNCTION statement.

In MySQL, a stored function is a set of SQL statements that you can call in to perform a group of operations from the parameters provided and return a single value. It provides a flexible way to process and manipulate data as you can call it from within a SQL statement.

Note: Similar to CREATE PROCEDURE statement, to use the CREATE FUNCTION statement, you need to have the CREATE ROUTINE database privilege.

Syntax

Here is the simple syntax for creating a stored function in MySQL:

CREATE FUNCTION function_name(parameter1, parameter2, …)
  RETURNS datatype
  DETERMINISTIC
  statements

Syntax explained:

• Name your function after CREATE FUNCTION clause. Avoid using the name of built-in functions.
• List all the parameters will be used by the function in its statements. By default, all parameters are the IN parameters and you can’t specify IN, OUT or INOUT modifiers to the parameters.
• You have to specify the datatype returned by the function.
• You have to specify at least one of these characteristics: DETERMINISTIC, NO SQL, or READS SQL DATA.
• And the final part is the set of operations that the function will perform. These operations must contain at least one RETURN statement.

MySQL stored function example:

Here is a simple demonstration of how a MySQL stored function works

• 1. Create a stored function:

CREATE FUNCTION hello (s CHAR(20))
  RETURNS CHAR(50) DETERMINISTIC
  RETURN CONCAT('Hello, ',s,'!');

• 2. Calling the stored function created just now:

SELECT hello('Susan') AS 'greeting';

• 3. And it returns:

greeting
Hello, Susan!

---

Need a good GUI tool for MySQL? TablePlus is a modern, native tool with an elegant GUI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.
 It’s free, forever!

Download TablePlus for macOS.

Not on macOS? Download TablePlus for Windows here