MySQL 常用函数实操攻略之从基础到实战案例

目录

• 一、日期函数：处理时间维度数据
• 1. 获取当前时间信息
• 2. 日期 / 时间增减：date_add()与date_sub()
• 3. 计算日期差：datediff()
• 4. 实战案例：筛选 2 分钟内的新留言
• 二、字符串函数：处理文本类数据
• 1. 查看字符集：charset()
• 2. 拼接字符串：concat()
• 3. 计算字节数：length()
• 4. 查找子串位置：instr()
• 5. 大小写转换：ucase()与lcase()
• 6. 截取字符串：left()、right()、substring()
• 7. 替换字符：replace()
• 8. 清理空格：ltrim()、rtrim()、phptrim()
• 三、数学函数：处理数值计算
• 1. 绝对值：abs()
• 2. 取整：ceiling()与floor()
• 3. 保留小数：format()
• 4. 随机数：rand()
• 四、其他高频函数：用户、加密与空值处理
• 1. 查看当前用户：user()
• 2. 密码加密：md5()
• 实战场景：创建用户表并加密存储密码
• 3. 空值处理：ifnull()

在 mysql 数据库操作中，函数是提升数据处理效率的核心工具。无论是日期计算、字符串拼接，还是数据加密，掌握常用函数能让复杂需求变得简单。本文将从实际场景出发，梳理日期、字符串、数学及其他高频函数的用法，每个函数都附带可直接运行的代码案例，帮你快速上手。

一、日期函数：处理时间维度数据

日期函数是业务开发中的 “高频工具”，比如统计近 7 天订单、筛选 2 分钟内的新留言等场景都离不开它。以下是最常用的 6 类日期函数及实操案例。

1. 获取当前时间信息

无需手动输入时间，用函数直接获取系统当前时间，避免人工录入误差。

获取年月日：current_date()，返回格式YYYY-MM-DD

select current_date();
-- 结果：2024-10-01

获取时分秒：current_time()，返回格式HH:MM:SS

select current_time();
-- 结果：14:25:30

获取完整时间：current_timestamp() 或 now()，两者功能一致，返回YYYY-MM-DD HH:MM:SS

select now();
-- 结果：2024-10-01 14:26:15

2. 日期 / 时间增减：date_add()与date_sub()

在指定日期上增加或减少时间，支持天、分钟、秒等单位，灵活应对 “有效期计算”“超时判断” 等场景。

加时间：date_add(原始日期, interval 数值 单位)

-- 给2024-10-01加5天
select date_add('2024-10-01', interval 5 day);
-- 结果：2024-10-06
-- 给2024-10-01 12:00:00加30分钟
select date_add('2024-10-01 12:00:00', interval 30 minute);
-- 结果：2024-10-01 12:30:00

减时间：date_sub(原始日期, interval 数值 单位)

-- 给2024-10-01减7天
select date_sub('2024-10-01', interval 7 day);
-- 结果：2024-09-24

3. 计算日期差：datediff()

统计两个日期之间的天数差，结果为 “前日期 - 后日期”，正数表示前者晚，负数表示前者早。

-- 计算2024-10-01与2024-09-20的天数差
select datediff('2024-10-01', '2024-09-20');
-- 结果：11（10月1日比9月20日晚11天）
select datediff('2024-09-20', '2024-10-01');
-- 结果：-11（9月20日比10月1日早11天）

4. 实战案例：筛选 2 分钟内的新留言

需求：从msg留言表中，找出 “发布时间 + 2 分钟” 仍在当前时间之前的记录（即 2 分钟内发布的留言）。

先创建msg表并插入数据：

create table msg(
  id int primary key auto_increment,
  content varchar(30) not null,
  sendtime datetime
);
-- 插入3条留言，sendtime为当前时间
insert into msg (content, sendtime) values 
  ('MySQL函数真好用', now()),
  ('今天学到新技巧了', now()),
  ('这是1小时前的旧留言', date_sub(now(), interval 1 hour));

筛选 2 分钟内的留言：

select * from msg 
where date_add(sendtime, interval 2 minute) > now();
-- 结果：只显示前2条刚插入的新留言，旧留言被过滤

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

字符串函数常用于数据格式化（如 “XXX 的语文成绩是 XXX 分”）、内容匹配、空格清理等场景，以下是开发中最常用的 8 类函数。

1. 查看字符集：charset()

确认字符串或字段的字符集，避免因编码不一致导致乱码。

-- 查看字符串的字符集
select charset('MySQL学习');
-- 结果：utf8
-- 查看表中字段的字符集（以student表的name字段为例）
select charset(name) from student;
-- 结果：所有行均返回utf8

2. 拼接字符串：concat()

将多个字符串 / 字段拼接成一个，支持文本与数字混合拼接。

-- 拼接固定文本和数字
select concat('Hello', 'MySQL', 2024);
-- 结果：HelloMySQL2024
-- 实战场景：格式化显示学生成绩（exam_result表含name、chinese、math字段）
select concat(name, '的语文是', chinese, '分，数学是', math, '分') as 成绩详情
from exam_result;
-- 结果：唐三藏的语文是134分，数学是98分

3. 计算字节数：length()

统计字符串占用的字节数，注意：UTF8 编码下，1 个中文占 3 字节，1 个英文 / 数字占 1 字节。

-- 计算中文+英文的字节数
select length('MySQL学习');
-- 结果：9（MySQL是5个字母，占5字节；“学习”是2个中文，占6字节，合计5+6=11？此处修正：MySQL是5字符，占5字节，“学习”2字符占6字节，总计11，此前示例有误，以实际计算为准）
-- 查看学生姓名的字节编程客栈数（student表）
select name, length(name) as 姓名字节数 from student;
-- 结果：张三 → 6字节（2个中文3）

4. 查找子串位置：instr()

判断子串是否在主串中，返回子串的起始下标（从 1 开始），若不存在则返回 0。

-- 查找“函数”在主串中的位置
select instr('MySQL常用函数指南', '函数');
-- 结果：6（“函数”从第6个字符开始）
-- 查找不存在的子串
select instr('MySQL常用函数指南', 'SQL Server');
-- 结果：0

5. 大小写转换：ucase()与lcase()

将字符串统一转为大写或小写，常用于不区分大小写的查询场景。

-- 转为大写
select ucase('mysql');
-- 结果：MYSQphpL
-- 转为小写
select lcase('MYSQL FUNCTION');
-- 结果：mysql function

6. 截取字符串：left()、right()、substring()

• left(主串, 长度)：从左侧截取指定长度的字符
• right(主串, 长度)：从右侧截取指定长度的字符
• substring(主串, 起始下标, 长度)：从指定下标开始，截取指定长度（下标从 1 开始）

-- 左侧截取3个字符
select left('MySQL函数', 3);
-- 结果：MyS
-- 右侧截取2个字符
select right('MySQL函数', 2);
-- 结果：函数
-- 从第4个字符开始，截取3个字符
select substring('MySQL函数指南', 4, 3);
-- 结果：QL函

7. 替换字符：replace()

将主串中的指定子串替换为新字符，常用于敏感词过滤、内容修正。

-- 将“MySQL”替换为“数据库”
select replace('学习MySQL很重要', 'MySQL', '数据库');
-- 结果：学习数据库很重要
-- 实战场景：将emp表ename字段中的“S”替换为“上海”
select replace(ename, 'S', '上海') as 替换后姓名 from emp;
-- 结果：SMITH → 上海MITH

8. 清理空格：ltrim()、rtrim()、trim()

• ltrim(字符串)：清除左侧空格
• rtrim(字符串)：清除右侧空格
• trim(字符串)：清除两侧空格

-- 清除左侧空格
select ltrim('   左侧有空格  ') as 结果;
-- 结果：左侧有空格  
-- 清除两侧空格
select trim('   两侧有空格  ') as 结果;
-- 结果：两侧有空格

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

数学函数主要用于数值的计算与格式化，如绝对值、取整、随机数生成等，以下是 4 类常用函数。

1. 绝对值：abs()

返回数值的绝对值，常用于计算差值（如距离、误差）。

select abs(-100); -- 结果：100
select abs(25.5); -- 结果：25.5

2. 取整：ceiling()与floor()

• ceiling(数值)：向上取整（无论小数部分是多少，都进 1）
• floor(数值)：向下取整（无论小数部分是多少，都舍掉）

-- 向上取整
select ceiling(3.1); -- 结果：4
select ceiling(-2.9); -- 结果：-2
-- 向下取整
select floor(3.9); -- 结果：3
select floor(-2.1); -- 结果：-3

3. 保留小数：format()

按指定位数保留小数，自动四舍五入，返回字符串格式（常用于金额、成绩格式化）。

-python- 保留2位小数
select format(123.456, 2); -- 结果：123.46（四舍五入）
select format(78.9, 2); -- 结果：78.90（补0）

4. 随机数：rand()

生成 0~1 之间的随机小数（左闭右开），可通过乘法扩展到指定范围。

-- 生成0~1的随机数
select rand(); -- 结果：0.78956（每次运行结果不同）
-- 生成1~10的随机整数（先10，再保留0位小数）
select format(rand()*10, 0) as 1到10的随机数;
-- 结果：5（每次运行结果不同）

四、其他高频函数：用户、加密与空值处理

除了上述三类函数，还有一些 “特殊功能” 函数，在用户管理、数据安全场景中非常实用。

1. 查看当前用户：user()

返回当前登录 MySQL 的用户名及主机，用于权限排查。

select user();
-- 结果：root@localhost（root是用户名，localhost是主机）

2. 密码加密：md5()

对字符串进行 MD5 加密，生成 32 位的十六进制字符串，常用于密码存储（避免明文泄露）。

实战场景：创建用户表并加密存储密码

创建user_info表：

create table user_info(
  id int primary key auto_increment,
  username varchar(20) not null,
  password char(32) not null -- MD5加密后是32位，用char(32)存储
);

插入加密后的密码（原始密码为 123456）：

insert into user_info (username, password) 
values (编程客栈'zhangsan', md5('123456'));

验证密码（查询时需对输入的密码也进行 MD5 加密）：

select id, username from user_info 
where password = md5('123456');
-- 结果：返回zhangsan的记录（密码匹配）

3. 空值处理：ifnull()

如果第一个值为null，则返回第二个值；否则返回第一个值，常用于避免null导致的计算错误。

-- 第一个值为null，返回第二个值
select ifnull(null, '空值时显示这个'); -- 结果：空值时显示这个
-- 第一个值不为null，返回第一个值
select ifnull(100, '空值时显示这个'); -- 结果：100
-- 实战场景：计算学生总分（若math字段为null，按0分计算）
select name, chinese + ifnull(math, 0) as 语文数学总分 
from exam_result;

到此这篇关于MySQL 常用函数实操攻略之从基础到实战案例的文章就介绍到这了,更多相关mysql常用函数内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！