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

What are MySQL Functions?

MySQL can do much more than just store and retrieve data. It can also perform manipulations on the data before retrieving or saving it. That is where MySQL Functions come in. Functions are simply pieces of code that perform an operation and then return a result. Some functions accept parameters, while other functions accept none.

Let’s briefly look at an example. 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 returned in the format “DD-MM-YYYY”. We can use the MySQL built-in function DATE_FORMAT to achieve this. DATE_FORMAT is one of the most used functions in MySQL, and we look at it in detail later in this lesson.

Whatever its type, a function always returns a single value, may accept zero or more parameters inside parentheses, and can be used anywhere an expression is allowed — in a SELECT list, a WHERE clause, or an ORDER BY clause.

Why Use MySQL Functions?

Now that we know what a function is, the next question is why we should push this work into the database at all.

As the diagram above shows, a function takes an input value, applies the logic once inside the database engine, and hands back a single result to every application that asks for it.

Programmers may be thinking, “Why bother with MySQL Functions? The same effect can be achieved with a scripting or programming language.” It is true that we can achieve that by writing a procedure in the application program.

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

This becomes a problem when the application has to integrate with other systems. When we use MySQL functions such as DATE_FORMAT, that functionality is 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 reduces data inconsistencies.

Another reason to consider MySQL functions is that they can help reduce network traffic in client/server applications. The business layer only needs to call the stored function, without pulling raw rows across the network to manipulate them. On average, the use of functions can greatly improve overall system performance.

Types of MySQL Functions

With the “what” and the “why” settled, we can now look at the three families of functions MySQL offers: built-in functions, stored functions, and user-defined functions.

Built-in functions

MySQL comes bundled with a number of built-in functions — functions already implemented in the MySQL server. They allow us to perform many types of manipulation on the data, and fall into the following commonly used groups.

• String 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 limit this lesson to the groups named above.

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

String functions

String functions operate on text values. In our movies table, titles are stored using a mix of lower and upper case letters. Suppose we want a query that returns the titles in upper case. The “UCASE” function takes a string as a parameter and converts every letter to upper case, as the script below demonstrates.

SELECT `movie_id`, `title`, UCASE(`title`) AS `upper_case_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.
• AS `upper_case_title` gives the computed column an alias, so the result set carries a readable header instead of the raw expression.

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

Two companions are worth remembering alongside UCASE: LCASE converts a string to lower case, and CONCAT joins two or more strings into one. For the complete list, refer to the MySQL string function reference.

Numeric functions

As mentioned earlier, numeric functions operate on numeric data types. We can also perform mathematical computations on numeric data directly in our SQL statements.

Arithmetic operators

MySQL supports the following arithmetic operators, which can be used to perform computations in SQL statements.

Examples of each operator follow.

Integer division (DIV) — DIV discards the fractional part and returns only the whole number.

SELECT 23 DIV 6;

Executing the above script gives us 3.

Division operator (/) — unlike DIV, the division operator keeps the decimal part of the result.

SELECT 23 / 6;

Executing the above script gives us 3.8333.

Subtraction operator (-)

SELECT 23 - 6;

Executing the above script gives us 17.

Addition operator (+)

SELECT 23 + 6;

Executing the above script gives us 29.

Multiplication operator (*)

SELECT 23 * 6 AS `multiplication_result`;

Result:

Modulo operator (% or MOD)

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

SELECT 23 % 6;
-- OR, equivalently:
SELECT 23 MOD 6;

Executing either script gives us 5.

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

FLOOR – this function removes the decimal places from a number and rounds it down to the nearest whole number. The script shown below demonstrates its usage.

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

Result:

ROUND – this function rounds a number to the nearest whole number. Because 23 / 6 evaluates to 3.8333, ROUND returns 4 while FLOOR returns 3 — the two are not interchangeable.

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

Result:

RAND – this function generates a random number. Its value changes every time the function is called. The script shown below demonstrates its usage.

SELECT RAND() AS `random_result`;

Date functions

Date functions operate on date and date-time data types. DATE_FORMAT is the function that solves the “YYYY-MM-DD versus DD-MM-YYYY” problem described in the introduction.

DATE_FORMAT takes two parameters: the date value to format, and a format string built from placeholders. The script below returns each release date in the day-month-year style our users asked for.

SELECT `title`, DATE_FORMAT(`date_released`, '%d-%m-%Y') AS `formatted_date`
FROM `movies`;

The most frequently used format placeholders are listed below.

Three other date functions appear constantly in day-to-day work:

• CURDATE() returns the current date as YYYY-MM-DD.
• NOW() returns the current date and time.
• DATEDIFF(d1, d2) returns the number of days between two dates — the basis of any overdue-rental report.

For the full list, see the MySQL date and time function reference.

Stored functions

Built-in functions cover the common cases. When a business rule is more specific, we write our own — and that is what a stored function is for.

Stored functions behave just like built-in functions, except that you define them yourself. Once created, a stored function can be used in SQL statements exactly like any other function. The basic syntax is shown below.

CREATE FUNCTION sf_name ([parameter(s)])
RETURNS data_type
[DETERMINISTIC | NOT DETERMINISTIC]
BEGIN
    -- procedural statements
END

HERE

• “CREATE FUNCTION sf_name ([parameter(s)])” is mandatory and tells the MySQL server to create a function named `sf_name` with optional parameters defined inside the parentheses.
• “RETURNS data_type” is mandatory and specifies the data type that the function returns.
• “DETERMINISTIC” declares that the function returns the same value whenever the same arguments are supplied. “NOT DETERMINISTIC” declares the opposite.
• “BEGIN … END” wraps the procedural code that the function executes.

Suppose we want to know which rented movies are past their return date. We can create a stored function that accepts the return date as a parameter and compares it with the current date on the server. If the current date is greater than the return date, the movie is overdue and we return “Yes”; otherwise we return “No”.

DELIMITER |
CREATE FUNCTION sf_past_movie_return_date (return_date DATE)
RETURNS VARCHAR(3)
NOT 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|
DELIMITER ;

⚠️ Warning — do not label this function DETERMINISTIC. The body calls CURDATE(), so the same argument can return “No” today and “Yes” tomorrow. Declaring a time-dependent function DETERMINISTIC misleads the optimizer and is unsafe for statement-based replication. Use NOT DETERMINISTIC whenever the body calls CURDATE(), NOW(), or RAND().

Executing the above script creates the stored function `sf_past_movie_return_date`. Let’s now test it.

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

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

Notice the two NULL rows. When `return_date` is NULL, both comparisons evaluate to NULL rather than TRUE or FALSE, so neither IF branch runs and the function returns NULL — the expected result, since an unreturned movie has no return date to compare against.

User-defined functions

When SQL alone is not fast enough, MySQL allows a third option. User-defined functions (UDFs) are written in a compiled language such as C or C++, built into a shared library, and registered with the server. Once added, they are called just like any other function. Because a UDF runs as native code inside the server process, it suits heavy computation — but a bug in one can crash the server, so UDFs are used far less often than stored functions.

Built-in vs Stored vs User-Defined Functions: Which Should You Use?

All three families return a single value and can be called from any SQL statement, but they differ in who writes them, where they run, and how much risk they carry. The table below summarizes those differences.

As a rule of thumb, start with a built-in function. If none fits, write a stored function so the rule lives in one place. Reach for a UDF only when a stored function is measurably too slow.

FAQs