MySQL – Built-in Functions | Love Me Some Coding

MySQL ships several hundred built-in functions. You will use perhaps twenty regularly. This is those, plus the two behaviours that surprise people: what CONCAT does with a NULL, and why ROUND gives different answers depending on the column's type.

Dates get their own lesson — see DATE_FORMAT and date functions.

String functions

SELECT CONCAT(name, ' (', type, ')') AS label,
       UPPER(LEFT(name, 3)) AS code,
       CHAR_LENGTH(name) AS len,
       REPLACE(name, 'Pizza', 'Pie') AS renamed
FROM   product WHERE id IN (1, 20) ORDER BY id;

+-------------------------+------+-----+---------------+
| label                   | code | len | renamed       |
+-------------------------+------+-----+---------------+
| Pepperoni Pizza (PIZZA) | PEP  |  15 | Pepperoni Pie |
| Pepsi (DRINK)           | PEP  |   5 | Pepsi         |
+-------------------------+------+-----+---------------+

SELECT LPAD(id, 6, '0') AS padded, TRIM('  spaced  ') AS trimmed,
       SUBSTRING(name, 1, 6) AS first6, LOCATE('Pizza', name) AS pizza_at
FROM   product WHERE id = 1;

+--------+---------+--------+----------+
| padded | trimmed | first6 | pizza_at |
+--------+---------+--------+----------+
| 000001 | spaced  | Pepper |       11 |
+--------+---------+--------+----------+

Two things to note. String positions are 1-based, not 0-based, in both SUBSTRING and LOCATE — LOCATE returns 0 for "not found", which is unambiguous precisely because 0 is not a valid position. And CHAR_LENGTH counts characters while LENGTH counts bytes, which differ the moment anyone uses a non-ASCII character. CHAR_LENGTH is almost always the one you want; see data types.

LPAD is not a toy: the pizza schema uses it to build deterministic UUIDs when backfilling, as LPAD(id, 12, '0').

CONCAT and NULL

SELECT CONCAT('a', NULL, 'b') AS concat_with_null,
       CONCAT_WS('-', 'a', NULL, 'b') AS concat_ws_with_null;

+------------------+---------------------+
| concat_with_null | concat_ws_with_null |
+------------------+---------------------+
| NULL             | a-b                 |
+------------------+---------------------+

One NULL anywhere in CONCAT makes the whole result NULL. That is consistent with the rest of SQL and it is a frequent source of blank fields in a UI — a customer with no middle name gets no name at all.

CONCAT_WS ("with separator") takes the separator first and skips NULLs, which is usually what you meant. It is the right tool for assembling an address out of columns that may be missing:

SELECT CONCAT_WS(', ', address_line1, city, state, postal_code) AS address
FROM   customer_order WHERE id IN (1, 2) ORDER BY id;

+----------------------------------------+
| address                                |
+----------------------------------------+
| 123 Main St, Salt Lake City, UT, 84101 |
|                                        |
+----------------------------------------+

The second order is a carryout with no address at all, so every argument is NULL and the result is an empty string rather than NULL. Wrap it in NULLIF(…, '') if you want to distinguish "no address" from "empty address" — see NULL and IS NULL.

Numeric functions

SELECT ROUND(16.994, 2) AS rounded, CEIL(16.01) AS ceil_, FLOOR(16.99) AS floor_,
       ABS(-5) AS abs_, MOD(17, 5) AS mod_, TRUNCATE(16.999, 2) AS truncated;

+---------+-------+--------+------+------+-----------+
| rounded | ceil_ | floor_ | abs_ | mod_ | truncated |
+---------+-------+--------+------+------+-----------+
|   16.99 |    17 |     16 |    5 |    2 |     16.99 |
+---------+-------+--------+------+------+-----------+

ROUND rounds; TRUNCATE chops. They agree here and disagree on 16.996, which rounds to 17.00 and truncates to 16.99. For money, decide which one the business means before you write either.

ROUND is not one function

SELECT ROUND(CAST(2.5 AS DECIMAL(10,1))) AS decimal_half, ROUND(CAST(2.5 AS DOUBLE)) AS double_half,
       ROUND(CAST(3.5 AS DECIMAL(10,1))) AS decimal_three_half, ROUND(CAST(3.5 AS DOUBLE)) AS double_three_half;

+--------------+-------------+--------------------+-------------------+
| decimal_half | double_half | decimal_three_half | double_three_half |
+--------------+-------------+--------------------+-------------------+
|            3 |           2 |                  4 |                 4 |
+--------------+-------------+--------------------+-------------------+

Read the middle two columns. ROUND(2.5) is 3 on a DECIMAL and 2 on a DOUBLE.

Exact types round half away from zero; floating-point types round half to even, which is why 3.5 goes to 4 in both cases and 2.5 does not. This is documented behaviour rather than a bug, and it is a very good reason to keep money in DECIMAL: the same expression against a FLOAT column gives a different total, and only sometimes.

GROUP_CONCAT

SELECT o.id, GROUP_CONCAT(i.product_name ORDER BY i.id SEPARATOR ' + ') AS items
FROM   customer_order o JOIN order_item i ON i.order_id = o.id
WHERE  o.id IN (1, 11) GROUP BY o.id ORDER BY o.id;

+----+-------------------------------------------+
| id | items                                     |
+----+-------------------------------------------+
|  1 | Pepperoni Pizza + Pepsi                   |
| 11 | Supreme Pizza + Meat Lovers Pizza + Pepsi |
+----+-------------------------------------------+

It rolls a whole group into one string, with its own ORDER BY and SEPARATOR (the default is a comma), and DISTINCT if you need it. Ideal for an order summary line.

It truncates silently at group_concat_max_len, which defaults to 1024 bytes. No error, no warning — the string is just short. If you are concatenating anything unbounded, raise it for the session or expect to lose the tail.

A few more worth knowing

`UUID()`	a random UUID as a 36-character string
`RAND()`	a random float. `ORDER BY RAND()` sorts the whole table — fine on 14 rows, ruinous on a million
`CAST(x AS type)`	explicit conversion; clearer than relying on MySQL's implicit coercion
`GREATEST` / `LEAST`	max and min across columns, as opposed to across rows
`HEX`, `MD5`, `SHA2`	encoding and hashing. Never `MD5` for passwords — use bcrypt in the application, as the pizza schema does

The performance rule

A function wrapped around a column in WHERE makes any index on that column unusable, because the index stores the column and the query asks about something else:

WHERE UPPER(name) = 'PEPSI'      -- cannot use an index on name
WHERE name = 'Pepsi'             -- can; the collation is case-insensitive anyway

Functions in the SELECT list are free by comparison — they run on the rows that survived. It is WHERE, JOIN ... ON and ORDER BY where it costs. If you genuinely need a computed value indexed, MySQL 8 supports a generated column with an index on it. See indexes.

What to remember

CONCAT returns NULL if any argument is NULL; CONCAT_WS skips them.
String positions are 1-based; CHAR_LENGTH counts characters, LENGTH counts bytes.
ROUND(2.5) is 3 on DECIMAL and 2 on DOUBLE.
GROUP_CONCAT truncates at 1024 bytes without telling you.
Keep functions off the column in WHERE.