Back to Top

How to create MySQL User Defined Functions


Do you know you can make custom functions for use in your MySQL queries? If No, Let’s get some basic idea of it.User defined Functions is known as UDF, good feature in MySQL that you can use in a query.

What is an User Defined Function?

User Defined Function is the code that extends the functionality of MySQL server by adding external code can work same as inbuilt functions like concat(), find_in_set() in MySQL.User-defined functions are compiled as object files which can be added with statement CREATE FUNCTION and can be removed from the server with statement DROP FUNCTION dynamically.

User Defined functions are useful when you want to extend the functionalities of your MySQL server.

Some Important Points:

  1. User-defined functions take zero or more input parameters, and return a single value such as an string, integer, or real values.
  2. You can define simple function that operate on a single row at a time or an aggregate functions that operate on groups of rows.
  3. You can indicate that a function returns NULL or that an error occurred.

User defined function syntax is very similar to stored procedures in MySQL.Here I have created simple user-defined functions which are to calculate available credits in the user account.


DROP FUNCTION CalculateAmount//

CREATE FUNCTION CalculateAmount(userid INT) RETURNS float(10,2)
	DECLARE totalCredits FLOAT;
	SELECT SUM(amount) INTO totalAmount FROM credit_user WHERE id =userid;
	RETURN totalAmount;

NOTE: Do not take parameter variable in function same as column field of database if you use it with database query otherwise its conflict.

Above Function is to calculate the total available amount of the user of specified userid.DROP FUNCTION is to drop the function if its already exists in the database.CREATE FUNCTION is to create a user-defined function like here by the name of ‘CalculateAmount’.

The function CalculateAmount will take in an integer userid that will be a call value and return out a float value because the return type is RETURNS float(10,2).You can define any variable in a function with statement DECLARE.


DECLARE variablename datatype

Use that variable into a function like here I have used in a query to get total amount.

How to call UDF in MySQL?


SELECT function_name(parameters);


SELECT CalculateAmount(1);

Here we checked simple user defined function which will be work like any other MySQL function.You can create the functions as simple or complex as you need.

So there you have it. Now go and create your own functions to use into MySQL. 🙂

Further Reading:

To learn MySQL PACK_KEYS with example
To pass limits with store procedure in MySQL
Mysql String Function FIND_IN_SET( )

I hope you have enjoyed this tutorial. Don’t Forget to Follow us on Twitter or Subscribe us to Get the Latest Updates.

Comments (5)

  1. This will only work with small databases. Once your databases get to a couple of GB in size, this probably be EXTREMELY slow or not work at all.

  2. Awesome Post! Great template for other advanced data manipulation operations too! Thanks

  3. You should use requests from INFORMATION_SCHEMA database insted of using SHOW TABLES, this is more accurate and light :

    More than that, you can easily adapt the above request to get only table names you want to backup.

  4. Yes, we can use INFORMATION_SCHEMA.! Thanks for the reply Laurent.

  5. Thanks a Lot for thid Post….this worked

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Most Popular Posts

To Swap Database Row in PHP

Posted on 7 years ago


How to use Index in MySQL

Posted on 4 years ago


Hello world

Updated 5 days ago

Nick Carter

In depth Understanding of Laravel Core

Updated 2 months ago


ECMAScript 6 New Features – 2

Updated 1 year ago


The Reader’s Poll – August 2014

Posted on 4 years ago


The Reader’s Poll – June 2014

Posted on 5 years ago


The Readers’ Poll – May 2014

Posted on 5 years ago