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.
Contents
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 –
- Update user age by ID
- 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.