MySQL Functions: String, Numeric, User-Defined, Stored

What are functions?

MySQL can do much more than just store and retrieve data. We can also perform manipulations on the data before retrieving or saving it. That’s where MySQL Functions come in. Functions are simply pieces of code that perform some operations and then return a result. Some functions accept parameters while other functions do not accept parameters.

Let’ briefly look at an example of MySQL function. By default, MySQL saves date data types in the format “YYYY-MM-DD”. Suppose we have built an application and our users want the date to be returned in the format “DD-MM-YYYY”, we can use MySQL built in function DATE_FORMAT to achieve this. DATE_FORMAT is one of the most used functions in MySQL. We will look at it in more details as we unfold the lesson.

Why use functions?

Why use Functions

Based on the example given in the introduction, people with experience in computer programming may be thinking “Why bother MySQL Functions? The same effect can be achieved with scripting/programming language?” It’s true we can achieve that by writing some procedures/function in the application program.

Getting back to our DATE example in the introduction, for our users to get the data in the desired format, business layer will have to do necessary processing.

This becomes a problem when the application has to integrate with other systems. When we use MySQL functions such as the DATE_FORMAT, then we can have that functionality embedded into the database and any application that needs the data gets it in the required format. This reduces re-work in the business logic and reduce data inconsistencies.

Another reason why we should consider using MySQL functions is the fact that it can help reducing network traffic in client/server applications. Business Layer will only need to make call to the stored functions without the need manipulate data .On average, the use of functions can help greatly improve overall system performance.

Types of functions

Built-in functions

MySQL comes bundled with a number of built in functions. Built in functions are simply functions come already implemented in the MySQL server. These functions allow us to perform different types of manipulations on the data. The built in functions can be basically categorized into the following most used categories.

  • Strings functions – operate on string data types
  • Numeric functions – operate on numeric data types
  • Date functions – operate on date data types
  • Aggregate functions – operate on all of the above data types and produce summarized result sets.
  • Other functions – MySQL also supports other types of built in functions but we will limit our lesson to the above named functions only.

Let’s now look at each of the functions mentioned above in detail. We will be explaining the most used functions using our “Myflixdb”.

String functions

We already looked at what string functions do. We will look at a practical example that uses them. In our movies table, the movie titles are stored using combinations of lower and upper case letters. Suppose we want to get a query list that returns the movie titles in upper case letters. We can use the “UCASE” function to do that. It takes a string as a parameter and converts all the letters to upper case. The script shown below demonstrates the use of the “UCASE” function.

SELECT `movie_id`,`title`, UCASE(`title`)  FROM `movies`;

HERE

  • UCASE(`title`) is the built in function that takes the title as a parameter and returns it in upper case letters with the alias name `upper_case_title`.

Executing the above script in MySQL workbench against the Myflixdb gives us the following results shown below.

movie_id title UCASE('title')
16 67% Guilty 67% GUILTY
6 Angels and Demons ANGELS AND DEMONS
4 Code Name Black CODE NAME BLACK
5 Daddy's Little Girls DADDY'S LITTLE GIRLS
7 Davinci Code DAVINCI CODE
2 Forgetting Sarah Marshal FORGETTING SARAH MARSHAL
9 Honey mooners HONEY MOONERS
19 movie 3 MOVIE 3
1 Pirates of the Caribean 4 PIRATES OF THE CARIBEAN 4
18 sample movie SAMPLE MOVIE
17 The Great Dictator THE GREAT DICTATOR
3 X-Men X-MEN

MySQL supports a number of string functions. For a complete list of all the built in string functions, refere to this link http://dev.mysql.com/doc/refman/5.0/en/string-functions.html on MySQL website.

Numeric functions

As earlier mentioned, these functions operate on numeric data types. We can perform mathematic computations on numeric data in the SQL statements.

Arithematic operators

MySQL supports the following arithmatic operators that can be used to perform computations in the SQL statements.

Name Description
DIV Integer division
/ Division
Subtraction
+ Addition
* Multiplication
% or MOD Modulus

Let’s now look at examples of each of the above operator

Integer Division (DIV)

SELECT 23 DIV 6 ;

Executing the above script gives us the following results.

3

Division operator (/)

Let’s now look at the division operator example. We will modify the DIV example.

SELECT 23 / 6 ;

Executing the above script gives us the following results.

3.8333

Subtraction operator (-)

Let’s now look at the subtraction operator example. We will use the same values as in the previous two examples

SELECT 23 - 6 ;

Executing the above script gives us 17

Addition operator (+)

Let’s now look at the addition operator example. We will modify the previous example.

SELECT 23 + 6 ;

Executing the above script gives us 29

Multiplication operator (*)

Let’s now look at the multiplication operator example. We will use the same values as in the previous examples.

SELECT 23 * 6 AS `multiplication_result`;

Executing the above script gives us the following results.

multiplication_result
138

Modulo operator (-)

The modulo operator divides N by M and gives us the remainder. Let’s now look at the modulo operator example. We will use the same values as in the previous examples.

SELECT 23 % 6 ;

OR

SELECT 23 MOD 6 ;

Executing the above script gives us 5

Let’s now look at some of the common numeric functions in MySQL.

Floor – this function removes decimals places from a number and rounds it to the nearest lowest number. The script shown below demonstrates its usage.

SELECT FLOOR(23 / 6) AS `floor_result`;

Executing the above script gives us the following results.

Floor_result
3

Round – this function rounds a number with decimal places to the nearest whole number. The script shown below demonstrates its usage.

SELECT ROUND(23 / 6) AS `round_result`;

Executing the above script gives us the following results.

Round_result
4

Rand – this function is used to generate a random number, its value changes every time that the function is called. The script shown below demonstrates its usage.

SELECT RAND() AS `random_result`;

Stored functions

Stored functions are just like built in functions except that you have to define the stored function yourself. Once a stored function has been created, it can be used in SQL statements just like any other function. The basic syntax for creating a stored function is as shown below

CREATE FUNCTION sf_name ([parameter(s)])
   RETURNS data type
   DETERMINISTIC
   STATEMENTS

HERE

  • “CREATE FUNCTION sf_name ([parameter(s)]) “ is mandatory and tells MySQL server to create a function named `sf_name’ with optional parameters defined in the parenthesis.
  • “RETURNS data type” is mandatory and specifies the data type that the function should return.
  • “DETERMINISTIC” means the function will return the same values if the same arguments are supplied to it.
  • “STATEMENTS” is the procedural code that the function executes.

Let’s now look at a practical example that implements a built in function. Suppose we want to know which rented movies are past the return date. We can create a stored function that accepts the return date as the parameter and then compares it with the current date in MySQL server. If the current date is less than the return movie date, then we return “No” else we return “Yes”. The script shown below helps us to achieve that.

DELIMITER |
CREATE FUNCTION sf_past_movie_return_date (return_date DATE)
  RETURNS VARCHAR(3)
   DETERMINISTIC
    BEGIN
     DECLARE sf_value VARCHAR(3);
        IF curdate() > return_date
            THEN SET sf_value = 'Yes';
        ELSEIF  curdate() <= return_date
            THEN SET sf_value = 'No';
        END IF;
     RETURN sf_value;
    END|

Executing the above script created the stored function `sf_past_movie_return_date`.

Let’s now test our stored function.

SELECT `movie_id`,`membership_number`,`return_date`,CURDATE() ,sf_past_movie_return_date(`return_date`)  FROM `movierentals`;

Executing the above script in MySQL workbench against the myflixdb gives us the following results.

movie_id membership_number return_date CURDATE() sf_past_movie_return_date('return_date')
1 1 NULL 04-08-2012 NULL
2 1 25-06-2012 04-08-2012 yes
2 3 25-06-2012 04-08-2012 yes
2 2 25-06-2012 04-08-2012 yes
3 3 NULL 04-08-2012 NULL

User-defined functions

MySQL also supports user defined functions that extend MySQL. User defined functions are functions that you can create using a programming language such as C, C++ etc. and then add them to MySQL server. Once added, they can be used just like any other function.

Summary

  • Functions allow us to enhance the capabilities of MySQL.
  • Functions always return a value and can optionally accept parameters.
  • Built in functions are functions that are shipped with MySQL. They can be categorized according to the data types that they operate on i.e. strings, date and numeric built in functions.
  • Stored functions are created by the user within MySQL server and can be used in SQL statements.
  • User defined functions are created outside MySQL and can be incorporated into MySQL server.