How to create function in MySQL ?

In this article you will learn how to create a function and call the function in MySQL. You have to create the function using create function statement in MySQL and call the function using the select getYesterdayDate() statement.

You can also use the function in your SQL statement instead of calling using select statement. The main advantages of function is, you can call the function from multiple places in your queries, avoiding code duplication and promoting code reusability.

Please execute delimiter // statement in MySQL shell before creating function in MySQL.

Run the below SQL statement to create function -

 create function getYesterdayDate() 
 returns date
 begin
    return adddate(current_date(), interval -1 day);
 end //

Call function using below statement

select getYesterdayDate() //

Output :

mysql> select getYesterdayDate() //
+--------------------+
| getYesterdayDate() |
+--------------------+
| 2024-02-20         |
+--------------------+
1 row in set (0.00 sec)

Call the function wherever you need it in your query

If you want to use the getYesterdayDate function within a SQL query other than a SELECT statement, you can simply call the function wherever you need it in your query. For example, you could use it in an INSERT, UPDATE, or DELETE statement like below.

INSERT INTO posts(title, body, url, user_id, created_at) values('My Fourth Post', 'Lorem ipsum dummy text', 'My-Fourth-Post', 1, getYesterdayDate()) //

Output

Query OK, 1 row affected (0.40 sec)

mysql> select * from posts;
+----+----------------+------------------------+----------------+---------+------------+
| id | title          | body                   | url            | user_id | created_at |
+----+----------------+------------------------+----------------+---------+------------+
|  1 | My First Post  | Lorem ipsum            | My-first-post  |       1 | 2024-02-21 |
|  2 | My Second Post | Lorem ipsum            | my-second-post |       1 | 2024-02-21 |
|  3 | My third post  | Lorem ipsum 3          | my-third-post  |       2 | 2024-02-21 |
|  6 | My Fourth Post | Lorem ipsum dummy text | My-Fourth-Post |       1 | 2024-02-20 |
+----+----------------+------------------------+----------------+---------+------------+
4 rows in set (0.00 sec)