MySQL 内置函数实战：日期、字符串与数学处理

1.2.2 日期加减运算

-- 日期加 10 天
select date_add('2026-03-10', interval 10 day);
-- 结果：2026-03-20

-- 日期减 2 年
select date_sub('2026-03-10', interval 2 year);
-- 结果：2024-03-10

-- 时间加 30 分钟
select date_add('2026-03-10 14:30:00', interval 30 minute);
-- 注意：原文示例结果有误，此处以实际逻辑为准

-- 时间减 1 小时
select date_sub('2026-03-10 14:30:00', interval 1 hour);

1.2.3 日期差计算与时间提取

-- 计算两个日期的天数差（date1 - date2）
select datediff('2024-05-20','2024-01-01');
-- 结果：140

-- 提取 datetime 中的日期部分
create table msg (
    id int primary key auto_increment,
    content varchar(30) not null,
    sendtime datetime
);
insert into msg(content, sendtime) values('hello mysql', now()), ('hello function', now());

-- 仅显示发布日期，隐藏时间
select content, date(sendtime) as send_date from msg;

请查询在 2 分钟内发布的帖子：

select * from msg where date_add(sendtime, interval 2 minute) > now();

1.2.4 业务场景：查询近期数据

-- 查询 2 分钟内发布的留言
select * from msg where date_add(sendtime, interval 2 minute) > now();

-- 查询 3 天内的生日记录
create table user_birthday (
    id int primary key auto_increment,
    name varchar(20) not null,
    birthday date
);
insert into user_birthday(name, birthday) values('张三','2000-05-18'),('李四','2000-06-01'),('王五','2000-05-22');

-- 计算当前日期与生日的差值，筛选 3 天内的记录
select * from user_birthday where datediff(birthday, current_date()) between 0 and 3;

二、字符串函数：处理文本数据

字符串函数用于字符串的拼接、截取、替换、大小写转换等操作，解决文本格式处理、内容提取等需求。

2.1 核心字符串函数表

2.2 实战案例

2.2.1 字符串拼接与格式处理

-- 拼接字符串（业务场景：格式化显示成绩）
create table exam_result (
    name varchar(20) not null,
    chinese float,
    math float,
    english float
);
insert into exam_result values('张三',85,92,78),('李四',90,88,95);

-- 格式：'XXX 的语文 XXX 分，数学 XXX 分，英语 XXX 分'
select concat(name,'的语文', chinese,'分，数学', math,'分，英语', english,'分') as 成绩详情 from exam_result;

2.2.2 字符串截取与替换

-- 截取 exam_result 表中 ename 字段的第二个到第三个字符
select ename, substring(ename, 2, 2) from exam_result;

-- 替换字符串（将 ename 列中有'S'的替换为'上海'）
select ename, replace(ename,'s','上海') from exam_result;

-- 左侧截取（取前 3 个字符）
select left('hello world', 3);
-- 结果：hel

2.2.3 大小写转换与空格处理

-- 转大写/小写
select ucase('mysql'), lower('MYSQL');
-- 结果：MYSQL、mysql

-- 去除空格
select trim(' mysql ');
-- 结果：mysql（去除两侧空格）
select ltrim(' mysql ');
-- 结果：mysql （仅去除左侧空格）

2.2.4 字符串长度与位置查询

-- 计算字符串长度（utf8 编码下，中文占 3 字节）
select length('mysql'), length('MySQL 函数');
-- 结果：5、11（5+3*2=11）

-- 查询子串位置（逗号在字符串中出现的位置）
select instr('10,A,B',',');
-- 结果：3（第一个逗号的位置）

-- 业务场景：统计逗号出现次数（OJ 真题）
select (length('10,A,B') - length(replace('10,A,B',',',''))) as cnt;
-- 结果：2

2.2.5 首字母小写格式化

-- 员工姓名首字母小写，其余不变（如 SMITH→sMITH）
create table emp (ename varchar(20));
insert into emp values('SMITH'),('SCOTT'),('WARD');
select concat(lcase(substring(ename, 1, 1)), substring(ename, 2)) as format_name from emp;

三、数学函数：数值计算与处理

数学函数用于数值的运算、进制转换、取整、随机数生成等，解决数值处理相关需求。

3.1 核心数学函数表

3.2 实战案例

-- 绝对值
select abs(-100.5), abs(200);
-- 结果：100.5、200

-- 取整操作
select ceiling(23.1), ceiling(23.9);
-- 结果：24、24（向上取整）
select floor(23.1), floor(23.9);
-- 结果：23、23（向下取整）

-- 数值格式化（保留 2 位小数）
select format(123.456, 2), format(78.9, 2);
-- 结果：123.46、78.90

-- 进制转换
select bin(10), hex(10);
-- 结果：1010（二进制）、A（十六进制）
select conv('1010', 2, 10);
-- 结果：10（二进制转十进制）

-- 随机数生成（生成 1-100 的随机整数）
select rand(), floor(rand()*100)+1;

-- 取模运算（求余数）
select mod(10, 3), 10%3;
-- 结果：1、1

四、其他常用函数：工具类功能

这类函数涵盖用户查询、数据库查询、数据加密、空值处理等实用功能，是开发中的'小帮手'。

4.1 核心工具函数表

4.2 实战案例

-- 查询当前用户和数据库
select user();
-- 结果：root@localhost
select database();
-- 结果：当前使用的数据库名（如 test）

-- 数据加密（业务场景：密码存储）
select md5('admin');
-- 结果：21232f297a57a5a743894a0e4a801fc3（MD5 加密）
select password('root');
-- 结果：*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B（MySQL 密码加密）

-- 空值处理（避免 null 影响运算）
create table user_info (
    id int primary key auto_increment,
    name varchar(20) not null,
    age int
);
insert into user_info(name, age) values('张三',20),('李四',null),('王五',25);

-- 年龄为 null 时显示 0
select name, ifnull(age, 0) as format_age from user_info;

五、函数使用避坑指南和总结

在实际使用中，有几个细节容易踩坑，建议重点关注：

• 日期函数的单位大小写不敏感：interval 10 day 和 interval 10 DAY 效果一致，但推荐统一小写；
• length 函数按字节计算长度：utf8 编码下中文占 3 字节，gbk 编码下占 2 字节，需注意字符集影响；
• substring 函数的位置从 1 开始：不同于编程语言的 0 起始索引，如 substring('mysql', 1, 3) 返回 mys；
• rand() 函数每次调用生成新值：如需固定随机数，可结合种子（如 rand(10)），但种子固定后随机序列固定；
• ifnull 仅处理 null 值：空字符串（''）、0 等非 null 值不会触发替换，如需处理空字符串可结合 case when；
• md5 加密不可逆：仅用于密码存储等场景，不可用于需要解密的数据。

总结：MySQL 内置函数覆盖了日期、字符串、数学、工具类等核心场景，合理使用能大幅提升 SQL 开发效率。核心要点如下：

• 日期函数解决时间获取、加减、差值计算，是业务系统的核心工具；
• 字符串函数处理文本格式、拼接、截取，满足数据展示和清洗需求；
• 数学函数用于数值运算和格式化，适用于统计、随机数生成等场景；
• 工具类函数提供加密、空值处理等实用功能，简化开发流程。