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.
