MySQL-Specific Functions

Beyond standard SQL, MySQL ships a large library of its own built-in functions — a handful of them come up constantly in everyday queries.

IFNULL: a default for NULL values

SQL mysql shell

SELECT name, IFNULL(phone, 'Not provided') AS phone
FROM customers;

Output

+-------+----------------+
| name  | phone          |
+-------+----------------+
| Priya | 555-0142       |
| Sam   | Not provided   |
+-------+----------------+
2 rows in set (0.00 sec)

IFNULL(expr, default) returns expr if it isn't NULL, and default if it is — a compact way to avoid showing raw NULL values to a user.

CONCAT: joining strings together

SQL mysql shell

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;

Output

+---------------+
| full_name     |
+---------------+
| Priya Shah    |
| Sam Rivera    |
+---------------+
2 rows in set (0.00 sec)

DATE_FORMAT: formatting a date for display

SQL mysql shell

SELECT order_date, DATE_FORMAT(order_date, '%M %e, %Y') AS pretty_date
FROM orders LIMIT 1;

Output

+------------+------------------+
| order_date | pretty_date      |
+------------+------------------+
| 2024-03-07 | March 7, 2024    |
+------------+------------------+
1 row in set (0.00 sec)

DATE_FORMAT uses its own set of format specifiers (%M for full month name, %e for day without a leading zero, %Y for a four-digit year) — MySQL's own convention, not the same as any particular programming language's date formatting.

CONCAT returns NULL if any argument is NULL: unlike some databases that treat NULL as an empty string inside a concatenation, MySQL's CONCAT() makes the entire result NULL the moment any single argument is NULL — so CONCAT(first_name, ' ', middle_name, ' ', last_name) silently returns nothing at all for anyone without a middle name on file. Wrap the nullable pieces in IFNULL(), or use CONCAT_WS() (which skips NULL arguments instead of failing on them).

PreviousUser Management & Privileges NextStored Procedures