MySQL 内置函数实战指南：从日期处理到字符串操作，高效解决业务数据处理需求

        MySQL 内置函数是 “数据处理的瑞士军刀”—— 无需在程序端编写复杂逻辑，直接通过函数完成日期计算、字符串格式化、数学运算等操作，大幅提升 SQL 效率。很多开发者因不熟悉内置函数，导致 SQL 冗长且性能低下（如手动拼接日期、循环计算字符串长度）。本文将系统讲解 MySQL 的四大类核心内置函数（日期、字符串、数学、其他函数），结合 “留言时间筛选”“成绩格式展示” 等实战案例，帮你快速掌握函数用法，写出简洁高效的 SQL。

一、日期函数：精准处理时间相关需求

        日期函数是业务开发中最常用的函数，涵盖 “获取当前时间”“日期增减”“时间差计算” 等场景，解决如 “筛选 3 天内的订单”“计算用户年龄” 等需求。

1.1 核心日期函数速查表

函数	功能	示例
current_date()	获取当前日期（格式：YYYY-MM-DD）	select current_date(); → 2024-05-20
current_time()	获取当前时间（格式：HH:MM:SS）	select current_time(); → 14:30:00
current_timestamp() / now()	获取当前日期时间（格式：YYYY-MM-DD HH:MM:SS）	select now(); → 2024-05-20 14:30:00
date(datetime)	提取日期时间中的 “日期部分”	date('2024-05-20 14:30:00') → 2024-05-20
date_add(date, interval 数值 单位)	日期 / 时间增加（单位：year/day/hour/minute/second）	date_add('2024-05-20', interval 10 day) → 2024-06-09
date_sub(date, interval 数值 单位)	日期 / 时间减少	date_sub('2024-05-20', interval 2 hour) → 2024-05-20 12:00:00
datediff(date1, date2)	计算两个日期的天数差（date1 - date2）	datediff('2024-05-20', '2024-05-10') → 10

1.2 实战案例

案例 1：记录用户生日并插入当前日期

创建 “生日表”，插入当前日期作为默认生日：

-- 1. 创建表
CREATE TABLE user_birthday (
    id INT PRIMARY KEY AUTO_INCREMENT,
    birthday DATE
);

-- 2. 插入当前日期（用current_date()）
INSERT INTO user_birthday (birthday) VALUES (current_date());

-- 3. 查看结果
SELECT * FROM user_birthday;
-- 输出：id=1, birthday=2024-05-20

案例 2：筛选 2 分钟内发布的留言

创建 “留言表”，筛选最近 2 分钟内的留言（避免程序端计算时间戳）：

-- 1. 创建表
CREATE TABLE msg (
    id INT PRIMARY KEY AUTO_INCREMENT,
    content VARCHAR(30) NOT NULL,
    send_time DATETIME
);

-- 2. 插入测试数据（用now()获取当前时间）
INSERT INTO msg (content, send_time) VALUES 
('Hello MySQL', now()),
('内置函数真好用', now() - INTERVAL 1 MINUTE),
('旧留言', now() - INTERVAL 5 MINUTE);

-- 3. 筛选2分钟内的留言（date_add扩展时间后与当前时间比较）
SELECT * FROM msg 
WHERE date_add(send_time, INTERVAL 2 MINUTE) > now();
-- 输出：前2条留言（1分钟内和当前时间的留言）

案例 3：计算用户年龄（基于生日）

已知用户生日（如 1990-01-15），计算当前年龄：

-- 思路：用当前年份 - 生日年份，若未到生日则减1（简化版）
SELECT 
    name,
    birthday,
    YEAR(current_date()) - YEAR(birthday) AS age
FROM user_info;

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

字符串函数用于 “拼接、截取、转换、替换” 文本数据，解决如 “格式化成绩展示”“替换敏感字符”“计算字符串长度” 等需求，避免程序端循环处理字符串。

2.1 核心字符串函数速查表

函数	功能	示例
concat(str1, str2, ...)	拼接多个字符串	concat('张三', '的语文成绩是', 90, '分') → 张三的语文成绩是 90 分
length(str)	计算字符串长度（单位：字节，UTF8 中中文占 3 字节）	length('MySQL') → 5；length('数据库') → 9
instr(str, substr)	查找子串在字符串中的位置（从 1 开始，无则返回 0）	instr('MySQL函数', '函数') → 6
ucase(str) / upper(str)	转换为大写	ucase('mysql') → MYSQL
lcase(str) / lower(str)	转换为小写	lcase('MYSQL') → mysql
substring(str, pos, len)	截取字符串（pos：起始位置，len：长度）	substring('MySQL函数', 1, 5) → MySQL
replace(str, old, new)	替换字符串中的子串	replace('MySQL5.7', '5.7', '8.0') → MySQL8.0
trim(str) / ltrim(str) / rtrim(str)	去除前后空格 / 左空格 / 右空格	trim(' MySQL ') → MySQL

2.2 实战案例

案例 1：格式化成绩展示

将 “学生成绩表” 的语文、数学成绩拼接为 “XXX 的语文是 XXX 分，数学是 XXX 分” 格式：

-- 表结构：exam_result(name, chinese, math)
SELECT 
    concat(name, '的语文是', chinese, '分，数学是', math, '分') AS score_info
FROM exam_result;

-- 输出示例：张三的语文是85分，数学是92分

案例 2：替换敏感字符

将 “员工表” 中姓名含 “S” 的字符替换为 “上海”（如用于数据脱敏）：

-- 表结构：emp(ename)
SELECT 
    ename AS 原姓名,
    replace(ename, 'S', '上海') AS 替换后姓名
FROM emp;

-- 输出示例：原姓名=SMITH → 替换后姓名=上海MITH

案例 3：首字母小写显示姓名

将 “员工表” 的姓名首字母改为小写，其余不变（如统一展示格式）：

SELECT 
    ename AS 原姓名,
    concat(
        lower(substring(ename, 1, 1)),  -- 首字母小写
        substring(ename, 2)             -- 截取剩余字符
    ) AS 格式化姓名
FROM emp;

-- 输出示例：原姓名=SMITH → 格式化姓名=sMITH

案例 4：计算字符串中逗号出现次数（OJ 实战）

查找字符串'10,A,B'中逗号的出现次数（核心：总长度 - 去除逗号后的长度）：

SELECT 
    length('10,A,B') - length(replace('10,A,B', ',', '')) AS cnt;
-- 计算逻辑：原长度5 - 去除逗号后长度3 = 2（逗号出现2次）

三、数学函数：高效处理数值运算

数学函数用于 “绝对值、取整、进制转换、随机数” 等数值操作，解决如 “计算利润绝对值”“生成随机验证码”“格式化小数” 等需求，无需程序端调用数学库。

3.1 核心数学函数速查表

函数	功能	示例
abs(num)	计算绝对值	abs(-10.5) → 10.5
ceiling(num)	向上取整（返回大于等于 num 的最小整数）	ceiling(23.1) → 24；ceiling(-23.9) → -23
floor(num)	向下取整（返回小于等于 num 的最大整数）	floor(23.9) → 23；floor(-23.1) → -24
format(num, dec)	格式化小数（保留 dec 位小数，四舍五入）	format(123.456, 2) → 123.46
rand()	生成 0~1 之间的随机浮点数	rand() → 0.789（每次结果不同）
mod(num, den)	取模（求余数）	mod(10, 3) → 1；mod(-10, 3) → -1
conv(num, from_base, to_base)	进制转换（from_base：原进制，to_base：目标进制）	conv(10, 10, 2) → 1010（十进制 10 转二进制）

3.2 实战案例

案例 1：格式化金额（保留 2 位小数）

将 “订单表” 的金额（如 199.5）格式化为保留 2 位小数的字符串（如 199.50）：

-- 表结构：orders(order_id, amount)
SELECT 
    order_id,
    format(amount, 2) AS formatted_amount
FROM orders;

-- 输出示例：amount=199.5 → formatted_amount=199.50

案例 2：生成随机验证码（6 位数字）

生成 6 位随机验证码（思路：rand () 生成 0~1 的随机数→乘以 1000000→向下取整→补前导 0）：

SELECT 
    lpad(floor(rand() * 1000000), 6, '0') AS verify_code;
-- 输出示例：012345、987654（确保6位，不足补0）

案例 3：计算商品折扣价（向下取整）

商品原价 199 元，打 8 折后向下取整（避免小数金额）：

SELECT 
    product_name,
    price,
    floor(price * 0.8) AS discount_price
FROM products;

-- 输出示例：price=199 → discount_price=159

四、其他常用函数：解决特殊业务需求

除上述三类函数外，MySQL 还提供 “用户查询”“数据加密”“空值处理” 等函数，覆盖权限管理、数据安全等场景。

4.1 核心其他函数速查表

函数	功能	示例
user()	获取当前登录 MySQL 的用户（格式：用户名 @主机）	select user(); → root@localhost
database()	获取当前使用的数据库	select database(); → test_db
md5(str)	对字符串进行 MD5 加密（返回 32 位十六进制字符串）	md5('admin') → 21232f297a57a5a743894a0e4a801fc3
password(str)	对字符串进行 MySQL 加密（用于用户密码存储，已过时，推荐用 MD5 或 SHA2）	password('root') → *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
ifnull(val1, val2)	空值处理：val1 为 NULL 则返回 val2，否则返回 val1	ifnull(null, '默认值') → 默认值；ifnull('实际值', '默认值') → 实际值

4.2 实战案例

案例 1：处理空值（替换 NULL 为默认值）

“用户表” 中qq字段可能为 NULL，查询时替换为 “未填写”：

-- 表结构：user(name, qq)
SELECT 
    name,
    ifnull(qq, '未填写') AS qq_info
FROM user;

-- 输出示例：qq=NULL → qq_info=未填写；qq=123456 → qq_info=123456

案例 2：加密用户密码（MD5）

创建 “用户表” 时，用 MD5 加密密码（避免明文存储）：

-- 1. 创建表
CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL UNIQUE,
    password VARCHAR(32) NOT NULL  -- 存储MD5加密后的密码
);

-- 2. 插入用户（密码admin加密）
INSERT INTO user (username, password) 
VALUES ('zhangsan', md5('admin'));

-- 3. 登录验证（对比输入密码的MD5与存储值）
SELECT * FROM user 
WHERE username = 'zhangsan' AND password = md5('admin');

五、函数使用注意事项

1. 避免过度使用函数：WHERE 条件中使用函数（如where date(send_time) = '2024-05-20'）会导致索引失效，需优化为where send_time BETWEEN '2024-05-20 00:00:00' AND '2024-05-20 23:59:59'；
2. 注意字符集影响：length()按字节计算长度，UTF8 中中文占 3 字节，若需按字符数计算，用char_length()（如char_length('数据库') → 3）；
3. 日期函数的时区问题：now()获取的是 MySQL 服务器时区的时间，需确保服务器时区与业务时区一致（可通过set time_zone = '+8:00'设置东八区）；
4. 加密函数的安全性：password()函数已过时，敏感场景推荐用SHA2(str, 256)或第三方加密（如 bcrypt），MD5 仅适用于非核心数据加密。

六、总结

MySQL 内置函数覆盖了 “日期、字符串、数学、安全” 等大部分数据处理场景，合理使用能：

1. 简化 SQL 逻辑：用concat()替代手动字符串拼接，用date_add()避免程序端时间计算；
2. 提升性能：减少程序端与数据库的交互（如直接在 SQL 中完成数据格式化，无需拉取数据后处理）；
3. 保证数据一致性：统一数据处理逻辑（如所有日期计算用 MySQL 函数，避免程序端不同模块处理方式不一致）。