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.

How to Create function in MySQL and execute it?


Contents

  1. Create Tables
  2. Function Creation Syntax
  3. Create Function
  4. Calling Function
  5. Conclusion

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

To create a function use the following syntax –

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

3. Create Function

Creating 2 functions to do the following operations –

  1. Update user age by ID
  2. Get Comma-separated user group ids

1. Update user age by ID

Create updateAge() function that takes 2 parameters of INT type – userid and age. It returns an INT type value. Update age field value with passed value where id = userid and return 1.

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

2. Get Comma-separated user group ids

Create getUserGroups() function that takes 1 parameter of INT type – userid. It returns TEXT type data. Create a local variable using DECLARE of TEXT type.

Select group_id using GROUP_CONCAT from the user_groups table where userid = userid. Using into to assign selected group ids to usergroups variable.

Return usergroups.

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 ;

4. Calling Function

  • Calling updateAge() function –
SELECT update(4, 27);

Here, 4 is the userid and 27 is a new age.

After execution users Table –

SELECT * FROM users WHERE id=4
ID Fullname Age City
4 Anil 27 Bhopal

  • Calling getUserGroups() function –
SELECT id, fullname, getUserGroups(id) as group_ids FROM users

Here, using getUserGroups() while selecting records from the users table. Passed id as parameter.

Output –

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

5. Calling Function

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.

Leave a Comment