MySQL Built-in Functions Reference

MySQL Built-In Functions Reference Overview

MySQL provides built-in functions across five core categories: String, Numeric, Date and Time, Control Flow, and Aggregate functions. Knowing when to transform data in SQL versus application code keeps queries concise while avoiding accidental full-table scans.

Core MySQL Function Categories

Practical SQL Example

This single reporting query combines string formatting, safe NULL handling, rounding, and date calculations on demo_customers and demo_orders:

SELECT
    c.id,
    CONCAT_WS(' ', c.first_name, c.last_name) AS full_name,
    LOWER(TRIM(c.email)) AS clean_email,
    COALESCE(COUNT(o.id), 0) AS order_count,
    ROUND(COALESCE(SUM(o.total_amount), 0.00), 2) AS total_spent,
    DATEDIFF(NOW(), MAX(o.created_at)) AS days_since_last_order
FROM demo_customers AS c
LEFT JOIN demo_orders AS o
    ON o.customer_id = c.id
GROUP BY c.id, c.first_name, c.last_name, c.email;

Common Mistakes and Best Practices

• Keep Functions Out of Indexed WHERE Columns: Calling LOWER(email) or YEAR(created_at) on the left side of a WHERE comparison prevents MySQL from using regular B-tree indexes and forces a full scan. Normalize strings when inserting, or add an indexed generated column.
• Avoid Nondeterministic Functions in Filtered Cache Paths: NOW() and RAND() change output between calls; pass fixed timestamps from your backend when caching query results or writing deterministic unit tests.
• Validate Implicit Type Conversions: Passing strings into numeric math functions triggers silent truncation; cast values cleanly at the application boundary first.
• Check Argument Order on String Helpers: Remember that SUBSTRING(str, pos, len) in MySQL is 1-based rather than 0-based like JavaScript or Python slices.

Frequently Asked Questions

What is the difference between CONCAT() and CONCAT_WS()?

CONCAT() returns NULL if any argument is NULL, whereas CONCAT_WS(' ', a, b) skips NULL arguments cleanly.

Does LENGTH() count characters or bytes in MySQL?

LENGTH() counts bytes (so an emoji takes 4 bytes in utf8mb4), while CHAR_LENGTH() counts actual characters.

Can I build custom functions in MySQL?

Yes, MySQL supports user-defined stored functions via CREATE FUNCTION, though built-in C-level functions execute much faster.