MySQL: A Comprehensive Guide to MySQL Built-in Functions…

Q: What is the result of CONCAT when it contains NULL?

The result is NULL. Use CONCAT_WS or IFNULL to handle it: CONCAT_WS(' ', 'Hello', NULL) → 'Hello'.

Q: Can functions be used in the WHERE clause?

Yes, but it will affect indexing. WHERE DATE(created_at) = CURDATE() cannot use an index; use a range instead: WHERE created_at >= CURDATE() AND created_at < CURDATE() + 1 .

Last updated: 2026-08-26

Functions are the Swiss Army knife of SQL—they handle data transformation, formatting, and calculations.

This lesson provides a systematic overview of the most commonly used built-in functions in MySQL.

graph TB
    A[MySQL Built-in Functions] --> B[Mathematical Functions<br/>ROUND/CEIL/FLOOR/RAND]
    A --> C[String Functions<br/>CONCAT/LENGTH/SUBSTRING]
    A --> D[Date Functions<br/>NOW/DATE_FORMAT/DATEDIFF]
    A --> E[Conditional Functions<br/>IF/IFNULL/CASE]
    A --> F[Aggregate Functions<br/>COUNT/SUM/AVG/MAX/MIN]
    A --> G[Type Conversion<br/>CAST/CONVERT]

1. What You'll Learn

Mathematical Functions (ROUND/CEIL/FLOOR/MOD)
String functions (CONCAT/LENGTH/SUBSTRING/REPLACE)
Date functions (NOW/DATE_FORMAT/DATEDIFF/DATE_ADD)
Type conversion functions (CAST/CONVERT)
Conditional Functions (IF/IFNULL/CASE)

2. Real-Life Stories of Data Cleaning

(1) Pain Point: Inconsistent Data Formats

User Registration Data:

name	phone	created_at
John	138-0000-1111	2026/07/03
Jane	13800002222	July 3, 2026

Phone numbers are formatted inconsistently, and dates are formatted haphazardly.

(2) Solving Functions

SQL

-- Standardize Phone Number Formats
SELECT name, REPLACE(phone, '-', '') AS clean_phone FROM users;

-- Standardize Date Formats
SELECT name, STR_TO_DATE(created_at, '%Y/%m/%d') AS clean_date FROM users;

3. Mathematical Functions

Function	Description	Example
Rounding to specified decimal places.
`ROUND(x, d)`	Round to d decimal places	`ROUND(3.1415, 2)` → 3.14
`CEIL(x)`	Round up	`CEIL(3.1)` → 4
`FLOOR(x)`	Round down	`FLOOR(3.9)` → 3
`ABS(x)`	Absolute value	`ABS(-5)` → 5
`MOD(a, b)`	Modulus	`MOD(10, 3)` → 1
`POWER(x, y)`	Power operation	`POWER(2, 3)` → 8
`SQRT(x)`	square root	`SQRT(16)` → 4
`RAND()`	Random number 0–1	`RAND()` → 0.723...

▶ Example: Applications of Mathematical Functions

TEXT 📖 Display only

-------------+-------+---------+-----------
product_name | price | 85      | sale_price
-------------+-------+---------+-----------
Alice        | 25.00 | value_1 | 25.00     
Bob          | 50.00 | value_2 | 50.00     
Charlie      | 75.00 | value_3 | 75.00     
-------------+-------+---------+-----------
3 rows in set

+-------------+
| total_pages |
+-------------+
| 4           |
+-------------+
1 row in set

---+---------+------------------
id | name    | email            
---+---------+------------------
1  | Alice   | alice@email.com  
2  | Bob     | bob@email.com    
3  | Charlie | charlie@email.com
---+---------+------------------
3 rows in set

SQL

-- Calculate the discounted price and round it off
SELECT 
    product_name,
    price,
    ROUND(price * 0.85, 2) AS sale_price
FROM products;

-- Calculate the number of pages (Round up)
SELECT CEIL(103 / 10) AS total_pages;  -- 11 pages

-- Random sort
SELECT * FROM users ORDER BY RAND() LIMIT 5;

TEXT 📖 Display only

Output displayed

4. String Functions

Function	Description	Example
`CONCAT(s1, s2, ...)`	Concatenate strings	`CONCAT('Hello', ' ', 'World')`
`CONCAT_WS(sep, s1, ...)`	Concatenate using a separator	`CONCAT_WS('-', '2026', '07', '03')`
`LENGTH(s)`	Byte length	`LENGTH('Hello')` → 5
`CHAR_LENGTH(s)`	Character length	`CHAR_LENGTH('Hello')` → 2
`UPPER(s)`	Convert to uppercase	`UPPER('hello')` → HELLO
`LOWER(s)`	Convert to lowercase	`LOWER('HELLO')` → hello
`TRIM(s)`	Remove leading and trailing spaces	`TRIM(' hi ')` → hi
`LTRIM(s)`	Remove leading spaces	`LTRIM(' hi')` → hi
`RTRIM(s)`	Remove trailing spaces	`RTRIM('hi ')` → hi
`SUBSTRING(s, pos, len)`	Substring	`SUBSTRING('Hello', 2, 3)` → ell
`LEFT(s, n)`	n characters to the left	`LEFT('Hello', 3)` → Hel
`RIGHT(s, n)`	n characters to the right	`RIGHT('Hello', 3)` → llo
`REPLACE(s, old, new)`	Replace	`REPLACE('Hi World', 'Hi', 'Hello')`
`REVERSE(s)`	Reverse	`REVERSE('Hello')` → olleH
`LPAD(s, len, pad)`	Left padding	`LPAD('5', 3, '0')` → 005
`RPAD(s, len, pad)`	Right fill	`RPAD('Hi', 5, '.')` → Hi...

▶ Example: Applications of String Functions

TEXT 📖 Display only

------------------+---------+----------
CONCAT(first_name | ' '     | full_name
------------------+---------+----------
Alice             | value_1 | Alice    
Bob               | value_2 | Bob      
Charlie           | value_3 | Charlie  
------------------+---------+----------
3 rows in set

----------------------+---------+---------+---------+------------
REPLACE(REPLACE(phone | '-'     | '')     | ' '     | clean_phone
----------------------+---------+---------+---------+------------
555-0101              | value_1 | value_1 | value_1 | 555-0101   
555-0101              | value_2 | value_2 | value_2 | 555-0101   
555-0101              | value_3 | value_3 | value_3 | 555-0101   
----------------------+---------+---------+---------+------------
3 rows in set

Query OK, 0 rows affected

-------------+---------+---------+---------
CONCAT('ORD' | LPAD(id | 6       | order_no
-------------+---------+---------+---------
value_1      | value_1 | value_1 | value_1 
value_2      | value_2 | value_2 | value_2 
value_3      | value_3 | value_3 | value_3 
-------------+---------+---------+---------
3 rows in set

SQL

-- Full name (combined)
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

-- Clean phone numbers
SELECT REPLACE(REPLACE(phone, '-', ''), ' ', '') AS clean_phone FROM users;

-- Extract the email domainSELECT 
    email,
    SUBSTRING(email, LOCATE('@', email) + 1) AS domain
FROM users;

-- Generate order number
SELECT CONCAT('ORD', LPAD(id, 6, '0')) AS order_no FROM orders;

TEXT 📖 Display only

+-------------+------------+
| email       | domain     |
+-------------+------------+
| a@gmail.com | gmail.com  |
| b@163.com   | 163.com    |
+-------------+------------+

TEXT 📖 Display only

Output displayed

5. Date Functions

Function	Description	Example
`NOW()`	Current Date and Time	`2026-07-03 10:30:00`
`CURDATE()`	Current Date	`2026-07-03`
`CURTIME()`	Current Time	`10:30:00`
`DATE(dt)`	Extract the date portion	`DATE('2026-07-03 10:30:00')`
`YEAR(dt)`	Extraction Year	`YEAR('2026-07-03')` → 2026
`MONTH(dt)`	Extract Month	`MONTH('2026-07-03')` → 7
`DAY(dt)`	Extraction date	`DAY('2026-07-03')` → 3
`HOUR(dt)`	Extract hours	`HOUR('10:30:00')` → 10
`DATE_FORMAT(dt, fmt)`	Date Format	`DATE_FORMAT(NOW(), '%Y-%m-%d')`
`STR_TO_DATE(s, fmt)`	Convert String to Date	`STR_TO_DATE('2026-07-03', '%Y-%m-%d')`
`DATEDIFF(d1, d2)`	Date Difference (Days)	`DATEDIFF('2026-07-10', '2026-07-03')` → 7
`DATE_ADD(dt, INTERVAL)`	Add Date	`DATE_ADD(NOW(), INTERVAL 7 DAY)`
`DATE_SUB(dt, INTERVAL)`	Date Subtract	`DATE_SUB(NOW(), INTERVAL 1 MONTH)`
`TIMESTAMPDIFF(unit, d1, d2)`	Time Difference	`TIMESTAMPDIFF(YEAR, birth, CURDATE())`

▶ Example: Using Date Functions

TEXT 📖 Display only

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

+------------------------+-------+-------------+--------------+
| DATE_FORMAT(order_date | month | order_count | total_amount |
+------------------------+-------+-------------+--------------+
| 5                      | 5     | 5           | 5            |
+------------------------+-------+-------------+--------------+
1 row in set

--------+---------------------+---------------------+---------------------+----
name    | birth_date          | TIMESTAMPDIFF(YEAR  | birth_date          | age
--------+---------------------+---------------------+---------------------+----
Alice   | 2024-01-15 10:30:00 | 2024-01-15 10:30:00 | 2024-01-15 10:30:00 | 25 
Bob     | 2024-01-15 10:30:00 | 2024-01-15 10:30:00 | 2024-01-15 10:30:00 | 26 
Charlie | 2024-01-15 10:30:00 | 2024-01-15 10:30:00 | 2024-01-15 10:30:00 | 27 
--------+---------------------+---------------------+---------------------+----
3 rows in set

SQL

-- Check today's data
SELECT * FROM orders WHERE DATE(created_at) = CURDATE();

-- View recent 7 days
SELECT * FROM orders WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);

-- Monthly Statistics
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY month
ORDER BY month;

-- Calculate Age
SELECT 
    name,
    birth_date,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM users;

TEXT 📖 Display only

Output displayed

TEXT 📖 Display only

+---------+-------------+--------------+
| month   | order_count | total_amount |
+---------+-------------+--------------+
| 2026-01 |         120 |     45000.00 |
| 2026-02 |         135 |     52000.00 |
| 2026-03 |         148 |     58000.00 |
+---------+-------------+--------------+

▶ Example: Date Formatting

SQL

-- Common Formats
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');           -- 2026-07-03
SELECT DATE_FORMAT(NOW(), '%Y/%m/%d');           -- 2026/07/03
SELECT DATE_FORMAT(NOW(), '%H:%i:%s');           -- 10:30:00
SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y');     -- Friday, July 03, 2026

▶ Try it Yourself

TEXT 📖 Display only

Output displayed

6. Conditional Functions

Function	Description	Example
`IF(expr, t, f)`	Conditional Statements	`IF(score >= 60, 'Pass', 'Fail')`
`IFNULL(expr, alt)`	NULL replacement	`IFNULL(email, 'N/A')`
`NULLIF(a, b)`	Equal returns NULL	`NULLIF(1, 1)` → NULL
`CASE WHEN ... THEN ... END`	Multiple-Condition Evaluation	See Example

▶ Example: Conditional Functions

TEXT 📖 Display only

--------+-------+----------------+--------+--------
name    | score | IF(score >= 60 | 'Pass' | result 
--------+-------+----------------+--------+--------
Alice   | 85.0  | 85.0           | ***    | value_1
Bob     | 86.0  | 86.0           | ***    | value_2
Charlie | 87.0  | 87.0           | ***    | value_3
--------+-------+----------------+--------+--------
3 rows in set

--------+--------------+---------
name    | IFNULL(phone | phone   
--------+--------------+---------
Alice   | 555-0101     | 555-0101
Bob     | 555-0101     | 555-0101
Charlie | 555-0101     | 555-0101
--------+--------------+---------
3 rows in set

--------+-------+------
name    | score | grade
--------+-------+------
Alice   | 85.0  | 85.0 
Bob     | 86.0  | 86.0 
Charlie | 87.0  | 87.0 
--------+-------+------
3 rows in set

SQL

-- IF Decision
SELECT 
    name,
    score,
    IF(score >= 60, 'Pass', 'Fail') AS result
FROM students;

-- IFNULL Processing NULL
SELECT 
    name,
    IFNULL(phone, 'No phone') AS phone
FROM users;

-- CASE Multiple conditions
SELECT 
    name,
    score,
    CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END AS grade
FROM students;

TEXT 📖 Display only

Output displayed

TEXT 📖 Display only

+-------+-------+-------+
| name  | score | grade |
+-------+-------+-------+
| Alice |    95 | A     |
| Bob   |    82 | B     |
| Carol |    67 | D     |
| Dave  |    45 | F     |
+-------+-------+-------+

7. Aggregate Function Preview

Function	Description	Example
`COUNT(*)`	count	`SELECT COUNT(*) FROM users`
`SUM(col)`	Sum	`SELECT SUM(amount) FROM orders`
`AVG(col)`	Average	`SELECT AVG(salary) FROM employees`
`MAX(col)`	Maximum value	`SELECT MAX(price) FROM products`
`MIN(col)`	minimum value	`SELECT MIN(age) FROM users`

❓ FAQ

Q What is the result of CONCAT when it contains NULL?

A The result is NULL. Use CONCAT_WS or IFNULL to handle it: CONCAT_WS(' ', 'Hello', NULL) → 'Hello'.

Q What is the difference between NOW() and CURDATE()?

A NOW() returns the date and time, while CURDATE() returns only the date.

Q Where can I find date formatting specifiers?

A Search for "MySQL DATE_FORMAT specifiers." Common ones include: %Y (year), %m (month), %d (day), %H (hour), %i (minute), and %s (second).

Q Can functions be used in the WHERE clause?

A Yes, but it will affect indexing. WHERE DATE(created_at) = CURDATE() cannot use an index; use a range instead: WHERE created_at >= CURDATE() AND created_at < CURDATE() + 1.

📖 Summary

Math functions: ROUND/CEIL/FLOOR/MOD/RAND
String functions: CONCAT/LENGTH/UPPER/LOWER/TRIM/SUBSTRING/REPLACE
Date Functions: NOW/CURDATE/DATE_FORMAT/DATEDIFF/DATE_ADD/DATE_SUB
Conditional functions: IF/IFNULL/CASE WHEN
Aggregate Functions: COUNT/SUM/AVG/MAX/MIN

📝 Exercises

Basic Problem (Difficulty ⭐): Use CONCAT and LPAD to generate an order number in the format 'ORD000001'.
Advanced Problem (Difficulty: ⭐⭐): Query the number of orders for each day over the past 30 days, using DATE_FORMAT to format the dates.
Challenge Question (Difficulty: ⭐⭐⭐): Calculate each user's age and use a CASE WHEN statement to classify them into four categories: 'teen,' 'young adult,' 'middle-aged,' and 'elderly.'

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

MySQL: A Comprehensive Guide to MySQL Built-in Functions…

1. What You'll Learn

2. Real-Life Stories of Data Cleaning

(1) Pain Point: Inconsistent Data Formats

(2) Solving Functions

3. Mathematical Functions

▶ Example: Applications of Mathematical Functions

4. String Functions

▶ Example: Applications of String Functions

5. Date Functions

▶ Example: Using Date Functions

▶ Example: Date Formatting

6. Conditional Functions

▶ Example: Conditional Functions

7. Aggregate Function Preview

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好