MySQL functions transform values in expressions, filters, grouping, ordering, and projections. Scalar functions return one result per input row; aggregate functions summarize a group. NULL propagation, collation, time zone, and implicit conversion can change apparently simple results.
Reports often need month labels, totals, fallback values, rounded amounts, or combined names. Functions compute those values near the data.
COUNT, SUM, AVG, MIN, and MAX turn many rows into summaries. GROUP BY defines the level of summary.
Functions are useful, but wrapping indexed columns in WHERE can make indexes harder to use. Large tables deserve EXPLAIN review.
Wrapping an indexed column in a function inside WHERE can prevent an ordinary index range lookup. Rewrite date filters as ranges when possible, or create an appropriate functional/generated-column index after measuring the query plan. Compare EXPLAIN output before and after the rewrite, including access type, chosen key, estimated rows, and any temporary sorting. Do not optimize by guesswork.
Aggregate functions ignore NULL except COUNT(*), which counts rows. Use GROUP BY to define the intended groups and HAVING for predicates on aggregate results. Store timestamps and convert zones deliberately rather than relying on a session default.
| Family | Examples | Result Scope |
|---|---|---|
| String | CONCAT, LOWER, TRIM, SUBSTRING | One value per input row. |
| Numeric | ABS, ROUND, CEIL, MOD | One value per input row. |
| Date and time | DATE_ADD, TIMESTAMPDIFF, CONVERT_TZ | Depends on type and session time-zone rules. |
| Conditional and NULL | CASE, COALESCE, NULLIF | Selects or replaces values deliberately. |
| Aggregate | COUNT, SUM, AVG, MIN, MAX | One value per group. |
| Window | ROW_NUMBER, LAG, SUM OVER | Keeps detail rows while calculating across a window. |
Most scalar expressions involving NULL return NULL. COALESCE chooses the first non-NULL expression, while NULLIF returns NULL when two expressions compare equal. Use IS NULL for tests; equality with NULL produces UNKNOWN.
Functions such as NOW depend on statement time, RAND is nondeterministic, and collation affects text comparisons. Those properties matter in generated columns, indexes, replication, caching, and repeatable tests. Keep stored values in a canonical form and format them for display at a deliberate boundary.
GROUP BY collapses rows to one result per group. A window function calculates over related rows without collapsing them, which is useful for ranking, running totals, and comparing with a previous row. Filter grouped results with HAVING; filter ordinary rows with WHERE before aggregation when possible.
SELECT DATE_FORMAT(created_at, "%Y-%m") AS month,
COUNT(*) AS orders,
SUM(total_amount) AS revenue
FROM orders
WHERE created_at >= "2026-01-01"
GROUP BY DATE_FORMAT(created_at, "%Y-%m")
ORDER BY month;A window function retains each row while calculating an aggregate for its region.
SELECT
region,
order_id,
amount,
AVG(amount) OVER (PARTITION BY region) AS regional_average
FROM sales
ORDER BY region, order_id;Each order remains visible beside the average for its region.In SELECT queries, no. They compute result values. UPDATE can store computed values intentionally.
Explore 500+ free tutorials across 20+ languages and frameworks.