MySQL Functions Overview

A function takes zero or more values (arguments) and returns one value. MySQL has several hundred built in. You have already used a few: CONCAT, ROUND, NOW, COUNT.

1

SELECT UPPER('mysql') AS shouting, LENGTH('hello') AS bytes, ROUND(3.14159, 2) AS pi_short, CONCAT('Hello', ', ', 'world') AS greeting;

1
2
3
4
5
6

+----------+-------+----------+--------------+
| shouting | bytes | pi_short | greeting     |
+----------+-------+----------+--------------+
| MYSQL    |     5 |     3.14 | Hello, world |
+----------+-------+----------+--------------+
1 row in set

Write the name, then the arguments in brackets, separated by commas. Even a function with no arguments needs the brackets: NOW().

Two kinds

Aggregate functions are the subject of the next chapters. A scalar function runs for every row of the result:

1
2
3
4

CREATE TABLE people (id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(20), last_name VARCHAR(20));
INSERT INTO people (first_name, last_name) VALUES ('asha', 'RAO'), ('ben', 'carter');

SELECT id, CONCAT(UPPER(LEFT(first_name, 1)), LOWER(SUBSTRING(first_name, 2)), ' ', UPPER(last_name)) AS display_name FROM people;

1
2
3
4
5
6
7

+----+--------------+
| id | display_name |
+----+--------------+
|  1 | Asha RAO     |
|  2 | Ben CARTER   |
+----+--------------+
2 rows in set

Nesting

The result of one function can be the argument of another. MySQL works from the inside out: LEFT(first_name, 1) first, then UPPER(...) on its result.

Categories you will meet

NULL and functions

Most functions return NULL when an argument is NULL. Notable exceptions are CONCAT_WS, COALESCE, IFNULL, IF and the aggregate functions (which skip NULL).

1

SELECT CONCAT('a', NULL, 'b') AS concat_null, CONCAT_WS('-', 'a', NULL, 'b') AS concat_ws_skips, UPPER(NULL) AS upper_null;

1
2
3
4
5
6

+-------------+-----------------+------------+
| concat_null | concat_ws_skips | upper_null |
+-------------+-----------------+------------+
| NULL        | a-b             | NULL       |
+-------------+-----------------+------------+
1 row in set

Deterministic and non-deterministic

A function is deterministic if the same input always gives the same output (UPPER('a')). NOW(), RAND() and UUID() are non-deterministic. This matters in generated columns (which need deterministic functions), in replication (statements with non-deterministic functions need care) and in query caching.

Functions and indexes

A function applied to a column in a WHERE clause usually prevents the use of an index on that column (Sargable Conditions). Apply functions to the values, not to the column.

The full list

The MySQL manual has a page called “Built-In Function and Operator Reference” that lists every function, with the version in which it was added. Use it whenever you wonder, “Is there a function for this?”. There often is.