MySQL | 各大常用函数详解与实战示例

关注：CodingTechWork

引言

  MySQL 提供了丰富的函数，用于处理各种数据操作和转换。这些函数可以帮助开发者高效地完成复杂的查询和数据处理任务。本文将详细介绍 MySQL 中常用的函数，包括函数语法、示例、推荐用法以及适用场景。

1. 字符串函数

1.1 CONCAT()

语法：

CONCAT(str1, str2, ...)

功能：将多个字符串连接成一个字符串。

示例：

SELECT CONCAT('Hello', ' ', 'World') AS result;
-- 输出：Hello World

推荐用法：用于拼接字符串，适合在生成动态字段时使用。

使用场景：生成完整的地址、姓名等字段。

1.2 SUBSTRING()

语法：

SUBSTRING(str, pos, len)

功能：从字符串 str 的第 pos 个字符开始，截取长度为 len 的子字符串。

示例：

SELECT SUBSTRING('Hello World', 7, 5) AS result;
-- 输出：World

推荐用法：用于提取字符串中的特定部分。

使用场景：解析日志文件、提取特定格式的数据。

1.3 LENGTH()

语法：

LENGTH(str)

功能：返回字符串 str 的字节长度。

示例：

SELECT LENGTH('Hello World') AS result;
-- 输出：11

推荐用法：用于检查字符串长度，适合在数据校验时使用。

使用场景：验证用户输入的字符串长度是否符合要求。

1.4 UPPER() 和 LOWER()

语法：

UPPER(str)
LOWER(str)

功能：将字符串 str 转换为大写或小写。

示例：

SELECT UPPER('hello world') AS upper_result, LOWER('HELLO WORLD') AS lower_result;
-- 输出：HELLO WORLD, hello world

推荐用法：用于统一字符串格式，便于比较和排序。

使用场景：在用户输入或数据导入时统一格式。

2. 数值函数

2.1 ABS()

语法：

ABS(num)

功能：返回数值 num 的绝对值。

示例：

SELECT ABS(-10) AS result;
-- 输出：10

推荐用法：用于处理负数，适合在计算距离或差值时使用。

使用场景：计算两个数值之间的绝对差值。

2.2 ROUND()

语法：

ROUND(num, decimals)

功能：将数值 num 四舍五入到指定的小数位数 decimals。

示例：

SELECT ROUND(123.456, 2) AS result;
-- 输出：123.46

推荐用法：用于处理浮点数，适合在财务计算中使用。

使用场景：计算金额、平均值等需要保留小数位的场景。

2.3 CEIL() 和 FLOOR()

语法：

CEIL(num)
FLOOR(num)

功能：CEIL 返回大于或等于 num 的最小整数，FLOOR 返回小于或等于 num 的最大整数。

示例：

SELECT CEIL(123.456) AS ceil_result, FLOOR(123.456) AS floor_result;
-- 输出：124, 123

推荐用法：用于向上或向下取整，适合在分页计算或资源分配时使用。

使用场景：计算需要向上或向下取整的场景，如分页总数。

3. 日期和时间函数

3.1 NOW()

语法：

NOW()

功能：返回当前日期和时间。

示例：

SELECT NOW() AS current_time;
-- 输出：2025-04-16 14:30:00

推荐用法：用于记录操作时间，适合在日志表或用户操作记录中使用。

使用场景：记录用户登录时间、操作时间等。

3.2 DATE_FORMAT()

语法：

DATE_FORMAT(date, format)

功能：将日期 date 格式化为指定的格式 format。

示例：

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS formatted_date;
-- 输出：2025-04-16 14:30:00

推荐用法：用于格式化日期和时间，适合在显示或导出数据时使用。

使用场景：将日期时间格式化为用户友好的格式。

3.3 DATEDIFF()

语法：

DATEDIFF(date1, date2)

功能：返回两个日期 date1 和 date2 之间的天数差。

示例：

SELECT DATEDIFF('2025-04-20', '2025-04-16') AS days_diff;
-- 输出：4

推荐用法：用于计算日期差，适合在统计分析中使用。

使用场景：计算两个日期之间的天数差，如用户注册天数。

4. 聚合函数

4.1 COUNT()

语法：

COUNT(expression)

功能：返回满足条件的行数。

示例：

SELECT COUNT(*) AS total_count FROM users;
-- 输出：100

推荐用法：用于统计行数，适合在数据统计和报表中使用。

使用场景：统计用户数量、订单数量等。

4.2 SUM()

语法：

SUM(expression)

功能：返回数值列的总和。

示例：

SELECT SUM(amount) AS total_amount FROM orders;
-- 输出：10000

推荐用法：用于计算总和，适合在财务报表中使用。

使用场景：计算订单总金额、销售额等。

4.3 AVG()

语法：

AVG(expression)

功能：返回数值列的平均值。

示例：

SELECT AVG(score) AS average_score FROM students;
-- 输出：85.5

推荐用法：用于计算平均值，适合在数据分析中使用。

使用场景：计算平均成绩、平均销售额等。

5. 条件函数

5.1 CASE

语法：

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE result3
END

功能：根据条件返回不同的结果。

示例：

SELECT name,
       CASE
           WHEN age < 18 THEN 'Minor'
           WHEN age >= 18 AND age < 60 THEN 'Adult'
           ELSE 'Senior'
       END AS age_group
FROM users;

推荐用法：用于根据条件分类，适合在数据分类和报表中使用。

使用场景：根据年龄分类用户、根据销售额分类客户等。

5.2 IF()

语法：

IF(condition, true_result, false_result)

功能：如果条件为真，返回 true_result，否则返回 false_result。

示例：

SELECT name, IF(age >= 18, 'Adult', 'Minor') AS age_group
FROM users;

推荐用法：用于简单的条件判断，适合在查询中快速分类。

使用场景：判断用户是否成年、订单是否完成等。

6. 转换函数

6.1 CAST()

语法：

CAST(expression AS data_type)

功能：将表达式转换为指定的数据类型。

示例：

SELECT CAST('123' AS SIGNED) AS number;
-- 输出：123

推荐用法：用于数据类型转换，适合在数据导入或计算时使用。

使用场景：将字符串转换为数字、日期等。

6.2 CONVERT()

语法：

CONVERT(data_type, expression)

功能：与 CAST 类似，用于数据类型转换。

示例：

SELECT CONVERT(SIGNED, '123') AS number;
-- 输出：123

推荐用法：用于将字符串转换为数值类型，或者将日期时间格式转换为其他格式。CONVERT() 在处理日期时间格式化时特别有用。

使用场景：

• 将字符串转换为数值进行计算。
• 将日期时间格式化为特定格式，例如：

  SELECT CONVERT(DATE, '2025-04-16 14:30:00') AS date_only;
  -- 输出：2025-04-16
  

7. 其他常用函数

7.1 COALESCE()

语法：

COALESCE(expression1, expression2, ...)

功能：返回参数列表中第一个非 NULL 的值。如果所有参数都是 NULL，则返回 NULL。

示例：

SELECT COALESCE(NULL, 'default', 'another') AS result;
-- 输出：default

推荐用法：用于处理可能为 NULL 的字段，提供默认值。

使用场景：

• 在查询结果中为 NULL 字段提供默认值。
• 防止因 NULL 值导致的错误。

7.2 NULLIF()

语法：

NULLIF(expression1, expression2)

功能：如果 expression1 和 expression2 相等，则返回 NULL，否则返回 expression1。

示例：

SELECT NULLIF(10, 10) AS result1, NULLIF(10, 20) AS result2;
-- 输出：NULL, 10

推荐用法：用于避免除以零或处理重复值。

使用场景：

• 在计算中避免除以零错误：

  SELECT a / NULLIF(b, 0) AS result FROM table_name;
  

7.3 REPLACE()

语法：

REPLACE(str, from_str, to_str)

功能：将字符串 str 中的所有 from_str 替换为 to_str。

示例：

SELECT REPLACE('Hello World', 'World', 'MySQL') AS result;
-- 输出：Hello MySQL

推荐用法：用于批量替换字符串中的特定内容。

使用场景：

• 清理数据中的特定格式。
• 替换用户输入中的敏感词汇。

7.4 TRIM()

语法：

TRIM([BOTH | LEADING | TRAILING] [remstr] FROM str)

功能：删除字符串 str 两端的空格或指定字符 remstr。

示例：

SELECT TRIM('   Hello World   ') AS result;
-- 输出：Hello World

推荐用法：用于清理用户输入或数据导入时的多余空格。

使用场景：

• 清理用户输入的多余空格。
• 处理从外部系统导入的数据。

7.5 IFNULL()

语法：

IFNULL(expression, alt_value)

功能：检查第一个参数 expression 是否为 NULL。如果为 NULL，则返回第二个参数 alt_value；否则返回第一个参数的值。

示例：

• 简单替换 NULL 值：

  SELECT IFNULL(NULL, 'Default Value') AS result;
  -- 输出：Default Value
  

  SELECT IFNULL('Hello', 'Default Value') AS result;
  -- 输出：Hello
  

  在第一个例子中，expression 是 NULL，因此返回了 alt_value。

• 避免计算结果为 NULL：

  SELECT SUM(IFNULL(salary, 0)) AS total_salary FROM employees;
  

  在这个例子中，IFNULL 将 salary 中的 NULL 值替换为 0，从而避免了计算结果为 NULL。

• 处理可能为空的字段：

  SELECT name, IFNULL(phone, 'No Phone') AS phone_number FROM contacts;
  

  如果 phone 字段为 NULL，则显示 'No Phone'。

推荐用法：

• 用于处理可能为 NULL 的字段，提供默认值。
• 在计算中避免因 NULL 值导致的错误。

使用场景：

• 替换查询结果中的 NULL 值为友好的提示或默认值。
• 在数学计算中，将 NULL 替换为 0。
• 在联合查询中，处理可能为空的字段。

注意事项：

• IFNULL 只接受两个参数，而 COALESCE 可以接受多个参数。
• 如果两个参数的类型不同，MySQL 会尝试进行隐式类型转换。
• IFNULL 仅在第一个参数为 NULL 时返回第二个参数。

总结

  MySQL 提供了丰富的函数，用于处理各种数据操作和转换。这些函数可以帮助开发者高效地完成复杂的查询和数据处理任务。通过合理使用这些函数，可以提高代码的可读性和维护性，同时避免重复造轮子。