How to Create function in MySQL and execute it?

Functions are very useful to perform the same task with different parameters. Similar to programming languages you can create your own custom function to perform a task and return a response in MySQL.

After function creation, it is called similar to inbuilt functions.

In this tutorial, I show how you can create a function in MySQL and use it.

1. Create Tables

Creating 2 tables for example –

users
user_groups

users Table –

CREATE TABLE `users` (
    `id` int(10) NOT NULL,
    `fullname` varchar(80) NOT NULL,
    `age` smallint(2) NOT NULL,
    `city` varchar(60) NOT NULL
);

ALTER TABLE `users`
ADD PRIMARY KEY (`id`);

ALTER TABLE `users`
MODIFY `id` int(10) NOT NULL AUTO_INCREMENT;

It has the following data –

ID	Fullname	Age	City
1	Yogesh	29	Bhopal
2	Sonarika	29	Indore
3	Vishal	31	Jaipur
4	Anil	24	Bhopal

user_groups Table –

CREATE TABLE `user_groups` (
    `id` int(10) NOT NULL,
    `user_id` int(10) NOT NULL,
    `group_id` int(10) NOT NULL
);

ALTER TABLE `user_groups`
ADD PRIMARY KEY (`id`);

ALTER TABLE `user_groups`
MODIFY `id` int(10) NOT NULL AUTO_INCREMENT;

It has the following data –

ID	User_ID	Group_ID
1	1	1
2	1	3
3	2	1
4	3	2
5	3	4
6	4	1
7	4	2
8	4	4

2. Function Creation Syntax in MySQL

To create a function use the following syntax –

DELIMITER //
CREATE Function function-name(param1 [DataType],param2 [DataType], ....)
    RETURNS [DataType]
    BEGIN
        // Statement
    END 
//
DELIMITER ;

3. Creating Function in MySQL

Creating 2 functions to do the following operations –

Update user age by ID
Get Comma-separated user group ids

1. Update user age by ID

Imagine you need to update a user’s age in your database. The updateAge() function makes this process a breeze. Here’s how you create it:

DELIMITER //
     Create Function updateAge(userid INT,age INT) RETURNS INT
          BEGIN
              UPDATE users SET age = age WHERE id = userid;
              RETURN 1;
          END 
     //
DELIMITER ;

Create updateAge() function and it takes two inputs: userid and age.
It finds the user with the provided ID and updates their age.
The function returns 1 to confirm the update.

2. Get Comma-separated user group ids

Have you ever needed to fetch a user’s group IDs in a single string? The getUserGroups() function helps you achieve this:

DELIMITER //
     Create Function getUserGroups(userid INT) RETURNS TEXT
         BEGIN
             DECLARE usergroups TEXT;
             SELECT GROUP_CONCAT(group_id) into usergroups
                 FROM user_groups
                 WHERE user_id=userid;
             RETURN usergroups;
         END 
     //
DELIMITER ;

Create getUserGroups() function, it accepts userid as input.
Inside the function, a handy variable named usergroups keeps track of the group IDs.
By using GROUP_CONCAT function, fetch, and compiles the group IDs seamlessly.
The function graciously hands you back the list of group IDs.

4. Calling Function in MySQL

Congratulations on creating your MySQL functions! Now, it’s time to see these functions in action.

Calling updateAge() function –

You’ve crafted the updateAge() function with care, and now it’s time to use it to update a user’s age effortlessly. Let’s take a look at how it’s done:

SELECT updateAge(4, 27);

In this example, calling the updateAge() function and passing in two parameters: 4 (userid) and 27 (new age). As a result, the user with ID 4 experiences an age transition.

After executing this function call, let’s take a peek at the users table:

SELECT * FROM users WHERE id=4

ID	Fullname	Age	City
4	Anil	27	Bhopal

Calling getUserGroups() function –

The getUserGroups() function allows you to unveil the intricate connections between users and their group affiliations. As we fetch user details from the users table, we’ll simultaneously employ the function to display their associated group IDs:

SELECT id, fullname, getUserGroups(id) as group_ids FROM users

In the example, utilizing the getUserGroups() function while selecting records from the users table. By passing the user’s ID as a parameter, providing a clear snapshot of each user’s group affiliations.

Output –

ID	Fullname	Group_ids
1	Yogesh	1,3
2	Sonarika	1
3	Vishal	2,4
4	Anil	1,2,4

5. Conclusion

When you are unable to perform an action using the inbuilt function or need to do repeated operations then you can create a custom function by following the above example. You can use them in multiple queries.

If you’re using phpMyAdmin then simply copy and paste the above-created function code after updating it. The created functions are visible in the Database Routines menu, from where you can also edit them.

If you found this tutorial helpful then don't forget to share.