原创

MySQL 函数详解：聚合函数、日期函数、字符串函数实战剖析

发布时间：2026-04-04 23:04:32
本文热度：浏览 6 赞 0 评论 0
文章标签： MySQL SQL函数后端开发
全文共1字，阅读约需1分钟

为什么 MySQL 函数必须系统掌握

写 SQL 时，真正决定查询表达力的，往往不是 SELECT、WHERE、JOIN 这些骨架，而是函数。

同样一张表，是否能快速做统计、按时间分组、清洗脏数据、拼接展示字段、处理空值、生成报表字段，核心差异都在函数使用是否熟练。很多查询写得又长又慢，不是因为业务复杂，而是因为函数使用方式不对，或者把函数用错了位置。

MySQL 常用函数可以粗略分为几类：

聚合函数：做统计汇总
日期时间函数：处理时间范围、格式化、差值计算
字符串函数：做文本截取、拼接、替换、清洗
数值函数：做计算、取整、四舍五入
条件与空值函数：做分支判断和兜底处理

这些函数单独看都不复杂，难点在于三个层面：

知道每个函数是干什么的
知道该在什么场景下用
知道哪些写法会让索引失效、结果失真或者出现隐式 bug

下面按实际开发最常见的几类函数展开。

示例表结构

为了让后续示例统一，先准备一张订单表和一张用户表。

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` VARCHAR(64) NOT NULL COMMENT '用户名',
  `nickname` VARCHAR(64) DEFAULT NULL COMMENT '昵称',
  `phone` VARCHAR(20) DEFAULT NULL COMMENT '手机号',
  `city` VARCHAR(32) DEFAULT NULL COMMENT '城市',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  PRIMARY KEY (`id`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_city` (`city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `user_id` BIGINT NOT NULL COMMENT '用户ID',
  `order_no` VARCHAR(64) NOT NULL COMMENT '订单编号',
  `amount` DECIMAL(10,2) NOT NULL COMMENT '订单金额',
  `status` TINYINT NOT NULL COMMENT '订单状态：1待支付 2已支付 3已取消 4已退款',
  `product_name` VARCHAR(128) DEFAULT NULL COMMENT '商品名称',
  `remark` VARCHAR(255) DEFAULT NULL COMMENT '备注',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `paid_at` DATETIME DEFAULT NULL COMMENT '支付时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_paid_at` (`paid_at`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

聚合函数：统计场景的核心工具

聚合函数的作用是把多行数据压缩成一个统计结果。最常见的是 COUNT、SUM、AVG、MAX、MIN。

COUNT：统计数量

SELECT COUNT(*) AS total_orders
FROM orders;

这是最常见的总订单数统计。

再看几个容易混淆的写法：

SELECT 
  COUNT(*) AS c1,
  COUNT(1) AS c2,
  COUNT(id) AS c3,
  COUNT(remark) AS c4
FROM orders;

区别是：

COUNT(*)：统计行数，包含 NULL
COUNT(1)：本质也是统计行数
COUNT(id)：统计 id 非空的行数
COUNT(remark)：只统计 remark 不为 NULL 的行数

在 InnoDB 下，业务开发里优先用 COUNT(*)，语义最清晰。

SUM：求和

SELECT SUM(amount) AS total_amount
FROM orders
WHERE status = 2;

统计已支付订单总金额。

AVG：平均值

SELECT AVG(amount) AS avg_amount
FROM orders
WHERE status = 2;

统计已支付订单平均客单价。

MAX / MIN：最大最小值

SELECT 
  MAX(amount) AS max_amount,
  MIN(amount) AS min_amount
FROM orders
WHERE status = 2;

适合做极值分析，比如最高订单金额、最低订单金额。

GROUP BY：聚合函数几乎总是和分组一起出现

单纯聚合只能得到全表结果，真正的业务统计通常需要分组。

按用户统计订单数和消费金额

SELECT 
  user_id,
  COUNT(*) AS order_count,
  SUM(amount) AS total_amount
FROM orders
WHERE status = 2
GROUP BY user_id;

按订单状态统计数量

SELECT 
  status,
  COUNT(*) AS total
FROM orders
GROUP BY status;

按天统计支付金额

SELECT 
  DATE(paid_at) AS pay_date,
  COUNT(*) AS pay_count,
  SUM(amount) AS pay_amount
FROM orders
WHERE status = 2
  AND paid_at IS NOT NULL
GROUP BY DATE(paid_at)
ORDER BY pay_date;

这类写法很常见，但要注意：GROUP BY DATE(paid_at) 是可以接受的统计写法，但如果把同样的函数写进过滤条件里，就要小心索引问题，后面会展开。

HAVING：过滤聚合后的结果

WHERE 是分组前过滤，HAVING 是分组后过滤。

查询订单数大于 5 的用户

SELECT 
  user_id,
  COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

这里不能写成：

WHERE COUNT(*) > 5

因为 WHERE 执行时聚合还没发生。

WHERE 和 HAVING 的职责边界

应该优先把能提前过滤的数据放进 WHERE，减少参与分组的数据量。

SELECT 
  user_id,
  COUNT(*) AS paid_count,
  SUM(amount) AS paid_amount
FROM orders
WHERE status = 2
GROUP BY user_id
HAVING SUM(amount) > 1000;

这个写法比先全表分组再筛已支付订单更合理。

聚合函数里的常见坑

`COUNT(column)` 会忽略 `NULL`

比如：

SELECT COUNT(remark) FROM orders;

如果很多订单备注为空，结果会明显小于总行数。

`SUM()` 遇到全 `NULL` 结果可能也是 `NULL`

SELECT SUM(remark) FROM orders;

即使语义上这个例子不合理，也说明一件事：聚合结果不一定总是 0，有时会是 NULL。实际开发里常配合 IFNULL 或 COALESCE。

SELECT IFNULL(SUM(amount), 0) AS total_amount
FROM orders
WHERE status = 9;

非法分组字段问题

如果开启严格 SQL 模式，下面写法通常会报错：

SELECT user_id, order_no, COUNT(*)
FROM orders
GROUP BY user_id;

因为 order_no 既不是聚合列，也不在 GROUP BY 中。这不是 MySQL 故意刁难，而是在防止“每组返回哪个 order_no”这种语义不确定的问题。

日期时间函数：时间筛选和报表统计的关键

业务系统几乎绕不开时间。用户注册时间、订单创建时间、支付时间、过期时间、任务执行时间，最终都会进入 SQL。

MySQL 日期函数中，最常用的一批有：

NOW()
CURDATE()
CURTIME()
DATE()
DATE_FORMAT()
YEAR()
MONTH()
DAY()
HOUR()
DATEDIFF()
TIMESTAMPDIFF()
DATE_ADD()
DATE_SUB()

当前时间相关函数

NOW()

返回当前日期时间。

SELECT NOW();

例如结果：

2026-03-16 10:30:15

CURDATE()

只返回当前日期。

SELECT CURDATE();

CURTIME()

只返回当前时间。

SELECT CURTIME();

日期提取函数

DATE()

从 DATETIME 中取出日期部分。

SELECT 
  created_at,
  DATE(created_at) AS created_date
FROM orders;

YEAR() / MONTH() / DAY()

SELECT 
  YEAR(created_at) AS y,
  MONTH(created_at) AS m,
  DAY(created_at) AS d
FROM orders;

适合做按年、按月、按日的拆解统计。

HOUR()

SELECT 
  HOUR(created_at) AS hour_num,
  COUNT(*) AS total
FROM orders
GROUP BY HOUR(created_at)
ORDER BY hour_num;

适合分析一天内哪个时段下单最多。

日期格式化函数

DATE_FORMAT()

这是报表类 SQL 中使用频率非常高的函数。

SELECT 
  DATE_FORMAT(created_at, '%Y-%m-%d') AS day_str
FROM orders;

常见格式符：

%Y：四位年份
%m：两位月份
%d：两位日期
%H：24 小时制小时
%i：分钟
%s：秒

按月统计订单数

SELECT 
  DATE_FORMAT(created_at, '%Y-%m') AS stat_month,
  COUNT(*) AS total
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY stat_month;

这类写法很直观，但它更适合统计展示，不适合高性能过滤条件。

日期计算函数

DATEDIFF()

计算两个日期之间相差多少天，只算日期，不算时分秒。

SELECT DATEDIFF('2026-03-16', '2026-03-10') AS diff_days;

结果是 6。

TIMESTAMPDIFF()

比 DATEDIFF() 更灵活，可以按年、月、日、小时、分钟、秒计算差值。

SELECT 
  TIMESTAMPDIFF(MINUTE, created_at, paid_at) AS pay_minutes
FROM orders
WHERE paid_at IS NOT NULL;

统计支付耗时时，这个函数更实用。

常见单位：

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND

DATE_ADD() / DATE_SUB()

做时间加减。

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 30 DAY);

例如查询最近 30 天订单：

SELECT *
FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

日期函数的性能问题：不要在过滤列上随意套函数

这是 MySQL 开发里非常高频的错误。

低性能写法

SELECT *
FROM orders
WHERE DATE(created_at) = '2026-03-16';

这条 SQL 的问题不是结果错，而是对 created_at 做了函数计算，往往会让索引失去直接利用价值。

更合理的写法

SELECT *
FROM orders
WHERE created_at >= '2026-03-16 00:00:00'
  AND created_at < '2026-03-17 00:00:00';

这类范围查询更容易命中索引。

再看一个常见误区

错误倾向：

WHERE DATE_FORMAT(created_at, '%Y-%m') = '2026-03'

推荐改成：

WHERE created_at >= '2026-03-01 00:00:00'
  AND created_at < '2026-04-01 00:00:00'

原则很简单：

展示时可以格式化，过滤时尽量做范围比较。

字符串函数：数据清洗和字段加工的常用工具

字符串函数是报表展示、模糊处理、数据修正里最常见的一类函数。

高频函数包括：

CONCAT()
CONCAT_WS()
LENGTH()
CHAR_LENGTH()
SUBSTRING()
LEFT()
RIGHT()
TRIM()
REPLACE()
LOWER()
UPPER()
LOCATE()

字符串拼接

CONCAT()

SELECT CONCAT(username, '-', phone) AS user_info
FROM users;

如果参数中某个值是 NULL，CONCAT() 返回结果可能就是 NULL，这点要特别注意。

CONCAT_WS()

WS 是 with separator，表示使用分隔符拼接。

SELECT CONCAT_WS('-', username, nickname, phone) AS user_info
FROM users;

相比 CONCAT()，它对空值处理通常更方便一些，而且语义更清晰。

长度相关函数

LENGTH()

返回字节长度。

SELECT LENGTH('abc');

结果是 3。

对于中文，在 utf8mb4 下，一个汉字通常占多个字节，因此结果不一定等于字符数。

CHAR_LENGTH()

返回字符长度。

SELECT CHAR_LENGTH('中国');

结果是 2。

所以：

统计存储字节大小，关注 LENGTH()
统计字符个数，关注 CHAR_LENGTH()

截取字符串

SUBSTRING()

SELECT SUBSTRING(order_no, 1, 8) AS short_order_no
FROM orders;

表示从第 1 个字符开始，截取 8 个字符。

LEFT() / RIGHT()

SELECT 
  LEFT(order_no, 4) AS left_part,
  RIGHT(order_no, 4) AS right_part
FROM orders;

对于固定前缀、后缀处理很方便。

去空格和替换

TRIM()

SELECT TRIM('  hello  ');

去掉首尾空格。

也可以用 LTRIM()、RTRIM() 分别去左边或右边空格。

REPLACE()

SELECT REPLACE(phone, '-', '') AS pure_phone
FROM users;

适合清洗格式化字符。

比如备注里把某个敏感词替换掉：

SELECT REPLACE(remark, '测试', '***') AS new_remark
FROM orders;

大小写转换

LOWER() / UPPER()

SELECT 
  LOWER(username) AS lower_name,
  UPPER(username) AS upper_name
FROM users;

常用于统一比较口径或导出展示。

查找子串位置

LOCATE()

SELECT LOCATE('Pro', product_name) AS pos
FROM orders;

返回子串第一次出现的位置，找不到返回 0。

字符串函数的典型场景

手机号脱敏

SELECT 
  CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4)) AS masked_phone
FROM users;

生成展示名称

SELECT 
  CONCAT_WS(' / ', username, nickname, city) AS display_name
FROM users;

从订单号中提取业务前缀

SELECT 
  LEFT(order_no, 3) AS biz_prefix,
  COUNT(*) AS total
FROM orders
GROUP BY LEFT(order_no, 3);

数值函数：金额、比例、取整都离不开

数值函数不如日期和字符串函数显眼，但在金额计算、分页处理、统计比率里非常常见。

常见函数：

ROUND()
CEIL()
FLOOR()
ABS()
MOD()

ROUND()

四舍五入。

SELECT ROUND(12.3456, 2);

结果是 12.35。

CEIL()

向上取整。

SELECT CEIL(12.01);

结果是 13。

FLOOR()

向下取整。

SELECT FLOOR(12.99);

结果是 12。

ABS()

取绝对值。

SELECT ABS(-100);

MOD()

取余。

SELECT MOD(10, 3);

结果是 1。

示例：统计平均支付耗时并保留两位小数

SELECT 
  ROUND(AVG(TIMESTAMPDIFF(SECOND, created_at, paid_at)) / 60, 2) AS avg_pay_minutes
FROM orders
WHERE paid_at IS NOT NULL
  AND status = 2;

条件函数与空值函数：让 SQL 具备分支能力

这类函数在业务报表、导出字段、兼容空数据时特别有用。

高频函数包括：

IF()
CASE WHEN
IFNULL()
COALESCE()
NULLIF()

IF()：简单条件判断

SELECT 
  order_no,
  IF(status = 2, '已支付', '非已支付') AS status_text
FROM orders;

适合简单二选一逻辑。

CASE WHEN：更推荐的多分支写法

SELECT 
  order_no,
  CASE status
    WHEN 1 THEN '待支付'
    WHEN 2 THEN '已支付'
    WHEN 3 THEN '已取消'
    WHEN 4 THEN '已退款'
    ELSE '未知状态'
  END AS status_text
FROM orders;

复杂逻辑建议优先使用 CASE WHEN，可读性明显更好。

条件聚合

这是非常实用的一种技巧。

SELECT
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS pending_count,
  SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) AS paid_count,
  SUM(CASE WHEN status = 3 THEN 1 ELSE 0 END) AS canceled_count
FROM orders;

一条 SQL 同时统计多个状态数量，报表中非常常见。

条件金额统计

SELECT
  SUM(CASE WHEN status = 2 THEN amount ELSE 0 END) AS paid_amount,
  SUM(CASE WHEN status = 4 THEN amount ELSE 0 END) AS refund_amount
FROM orders;

IFNULL() 与 COALESCE()：处理空值

IFNULL()

SELECT 
  username,
  IFNULL(nickname, username) AS display_name
FROM users;

如果昵称为空，则用用户名顶上。

COALESCE()

SELECT 
  COALESCE(nickname, username, phone, '未知用户') AS display_name
FROM users;

返回第一个非 NULL 的值。比 IFNULL() 更灵活。

NULLIF()

SELECT NULLIF(status, 2) FROM orders;

如果两个值相等，返回 NULL，否则返回第一个值。实际业务里不如前两个常用，但在某些特殊表达式里能简化写法。

业务开发中最常见的函数组合写法

单个函数并不难，真正高频的是组合使用。

按天统计已支付订单金额

SELECT
  DATE(paid_at) AS pay_date,
  COUNT(*) AS pay_count,
  ROUND(SUM(amount), 2) AS pay_amount
FROM orders
WHERE status = 2
  AND paid_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY DATE(paid_at)
ORDER BY pay_date;

这里组合了：

DATE()
DATE_SUB()
COUNT()
SUM()
ROUND()

用户显示名称兜底

SELECT
  id,
  COALESCE(nickname, username, CONCAT('用户', id)) AS display_name
FROM users;

这里组合了：

COALESCE()
CONCAT()

每月订单状态统计

SELECT
  DATE_FORMAT(created_at, '%Y-%m') AS stat_month,
  SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS pending_count,
  SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) AS paid_count,
  SUM(CASE WHEN status = 3 THEN 1 ELSE 0 END) AS canceled_count,
  SUM(CASE WHEN status = 4 THEN 1 ELSE 0 END) AS refund_count
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY stat_month;

这是典型的后台报表写法。

开发中需要特别警惕的几个问题

1. 不要为了省事在索引列上包函数

比如：

WHERE DATE(created_at) = '2026-03-16'
WHERE YEAR(created_at) = 2026
WHERE LEFT(order_no, 3) = 'ORD'

这些写法语义上没问题，但通常会损害索引利用率。更好的方式，是尽量把条件改写成原字段的范围比较或前缀匹配。

例如：

WHERE created_at >= '2026-03-16 00:00:00'
  AND created_at < '2026-03-17 00:00:00'

2. 聚合时注意 `NULL` 对结果的影响

COUNT(column)、SUM(column)、AVG(column) 都可能受到空值影响。做报表时，不要想当然地认为空值等于 0。

3. `DATE_FORMAT()` 适合展示，不适合高性能过滤

它非常适合报表输出：

SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s')

但不适合写在高频查询条件里作为筛选依据。

4. 字符串长度要分清字节长度和字符长度

对于中英文混合文本，这是一个很容易被忽视的问题。

LENGTH() 看字节
CHAR_LENGTH() 看字符

这在字段校验、导出截断、数据同步时都可能出问题。

5. 金额计算尽量基于 `DECIMAL`，不要依赖浮点

虽然这不是函数本身的问题，但它和函数经常一起出现。金额字段应使用 DECIMAL，再配合 ROUND() 进行显示控制，避免浮点精度误差。

一套更实用的函数学习顺序

如果是为了业务开发，不建议按函数手册一页页背。更高效的顺序是：

第一阶段：先掌握最常用函数

优先熟悉这批：

聚合：COUNT、SUM、AVG
日期：NOW、DATE、DATE_FORMAT、DATE_SUB
字符串：CONCAT、SUBSTRING、TRIM、REPLACE
条件：CASE WHEN、IFNULL、COALESCE

这批已经能覆盖大多数后台开发场景。

第二阶段：学习组合写法

比如：

时间分组 + 聚合统计
CASE WHEN + SUM 做条件汇总
COALESCE + CONCAT 做展示字段兜底
TIMESTAMPDIFF + AVG 做耗时统计

第三阶段：学习性能边界

重点盯住：

函数是否导致索引失效
空值是否导致统计失真
日期格式化是否被误用成过滤条件
字符串处理是否引发隐式类型转换

真正拉开差距的，往往不是“知道多少函数”，而是“知道函数该放在哪”。

一条经验：函数是表达能力，不是炫技工具

SQL 写复杂并不代表高级。真正好的 SQL，应该同时满足三件事：

结果准确
可读性好
性能可接受

函数的价值，是让 SQL 更贴近业务表达，而不是把一个简单查询写成难以维护的谜题。

在 MySQL 中，聚合函数解决统计问题，日期函数解决时间问题，字符串函数解决加工问题，条件函数解决分支问题。把这几类函数掌握扎实，日常开发中的绝大多数查询、报表和数据处理工作，基本都能覆盖。

函数不是 SQL 的边角料，而是业务查询的主力工具。真正熟练之后，你会发现很多以前要靠应用层二次处理的逻辑，其实在 SQL 层就能稳定完成。

正文到此结束

所属分类：Mysql

本文链接： https://refblogs.com/article/1233
版权声明： 本文由老牛原创发布，转载或复制请以超链接形式转载,并注明出处搬砖的码农。

MySQL 函数详解：聚合函数、日期函数、字符串函数实战剖析

为什么 MySQL 函数必须系统掌握

示例表结构

聚合函数：统计场景的核心工具

COUNT：统计数量

SUM：求和

AVG：平均值

MAX / MIN：最大最小值

GROUP BY：聚合函数几乎总是和分组一起出现

按用户统计订单数和消费金额

按订单状态统计数量

按天统计支付金额

HAVING：过滤聚合后的结果

查询订单数大于 5 的用户

WHERE 和 HAVING 的职责边界

聚合函数里的常见坑

COUNT(column) 会忽略 NULL

SUM() 遇到全 NULL 结果可能也是 NULL

非法分组字段问题

日期时间函数：时间筛选和报表统计的关键

当前时间相关函数

NOW()

CURDATE()

CURTIME()

日期提取函数

DATE()

YEAR() / MONTH() / DAY()

HOUR()

日期格式化函数

DATE_FORMAT()

按月统计订单数

日期计算函数

DATEDIFF()

TIMESTAMPDIFF()

DATE_ADD() / DATE_SUB()

日期函数的性能问题：不要在过滤列上随意套函数

低性能写法

更合理的写法

再看一个常见误区

字符串函数：数据清洗和字段加工的常用工具

字符串拼接

CONCAT()

CONCAT_WS()

长度相关函数

LENGTH()

CHAR_LENGTH()

截取字符串

SUBSTRING()

LEFT() / RIGHT()

去空格和替换

TRIM()

REPLACE()

大小写转换

LOWER() / UPPER()

查找子串位置

LOCATE()

字符串函数的典型场景

手机号脱敏

生成展示名称

从订单号中提取业务前缀

数值函数：金额、比例、取整都离不开

ROUND()

CEIL()

FLOOR()

ABS()

MOD()

示例：统计平均支付耗时并保留两位小数

条件函数与空值函数：让 SQL 具备分支能力

IF()：简单条件判断

CASE WHEN：更推荐的多分支写法

条件聚合

条件金额统计

IFNULL() 与 COALESCE()：处理空值

IFNULL()

COALESCE()

NULLIF()

业务开发中最常见的函数组合写法

按天统计已支付订单金额

用户显示名称兜底

每月订单状态统计

`COUNT(column)` 会忽略 `NULL`

`SUM()` 遇到全 `NULL` 结果可能也是 `NULL`

2. 聚合时注意 `NULL` 对结果的影响

3. `DATE_FORMAT()` 适合展示，不适合高性能过滤

5. 金额计算尽量基于 `DECIMAL`，不要依赖浮点