MySQL 内置函数实战指南
在 MySQL 开发中，内置函数是提升 SQL 效率和灵活性的核心工具。无论是日期计算、字符串处理，还是数学运算、数据加密，合理使用内置函数能大幅简化代码，避免重复逻辑。本文基于实战场景，全面拆解 MySQL 常用内置函数，涵盖日期、字符串、数学及其他工具类函数，附带详细案例和避坑要点。
一、日期函数：处理时间相关需求
日期函数是业务开发中最常用的函数类别，用于获取当前时间、日期加减、时间差计算等场景，解决时间相关的数据处理问题。
1.1 核心日期函数一览
| 函数 | 描述 |
|---|---|
CURRENT_DATE() | 获取当前日期（格式：yyyy-mm-dd） |
CURRENT_TIME() | 获取当前时间（格式：hh:mm:ss） |
CURRENT_TIMESTAMP() | 获取当前时间戳（格式：yyyy-mm-dd hh:mm:ss） |
NOW() | 等同于 CURRENT_TIMESTAMP()，获取当前日期时间 |
DATE(datetime) | 提取日期时间中的日期部分 |
DATE_ADD(date, INTERVAL 数值 单位) | 日期/时间加法（单位：YEAR/DAY/HOUR/MINUTE/SECOND） |
DATE_SUB(date, INTERVAL 数值 单位) | 日期/时间减法（单位同上） |
DATEDIFF(date1, date2) | 计算 date1 - date2 的天数差（仅日期部分） |
1.2 实战案例
基础时间获取
-- 获取当前日期
SELECT CURRENT_DATE();
-- 结果：2026-03-10
-- 获取当前时间
SELECT CURRENT_TIME();
-- 结果：12:34:50
-- 获取当前时间戳
SELECT CURRENT_TIMESTAMP();
-- 结果：2026-03-10 12:34:50
-- NOW() 更常用
SELECT NOW();
日期加减运算
-- 日期加 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);
-- 结果：2026-03-10 15:00:00
-- 时间减 1 小时
SELECT DATE_SUB('2026-03-10 14:30:00', INTERVAL 1 HOUR);
-- 结果：2026-03-10 13:30:00
日期差计算与时间提取
-- 计算两个日期的天数差（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();
业务场景：查询近期数据
-- 查询 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 核心字符串函数一览
| 函数名称 | 描述 |
|---|---|
CHARSET(str) | 返回字符串的字符集（如 utf8、gbk） |
CONCAT(str1, str2, ...) | 拼接多个字符串 |
INSTR(str, substr) | 返回 substr 在 str 中首次出现的位置（无则返回 0） |
UCASE(str) / UPPER(str) | 字符串转大写 |
LCASE(str) / LOWER(str) | 字符串转小写 |
LEFT(str, length) | 从 str 左侧截取 length 个字符 |
LENGTH(str) | 返回字符串长度（以字节为单位，受字符集影响） |
REPLACE(str, search_str, replace_str) | 用 replace_str 替换 str 中的 search_str |
SUBSTRING(str, pos [, length]) | 从 str 的 pos 位置（1 开始）截取 length 个字符（length 可选，默认到末尾） |
LTRIM(str) / RTRIM(str) / TRIM(str) | 去除字符串左侧 / 右侧 / 两侧的空格 |
STRCMP(str1, str2) | 逐字符比较 str1 和 str2（相等返回 0，str1 大返回 1，否则返回 -1） |
2.2 实战案例
字符串拼接与格式处理
-- 拼接字符串（业务场景：格式化显示成绩）
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;
字符串截取与替换
-- 截取 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
大小写转换与空格处理
-- 转大写/小写
SELECT UCASE('mysql'), LCASE('MYSQL');
-- 结果：MYSQL、mysql
-- 去除空格
SELECT TRIM(' mysql ');
-- 结果：mysql（去除两侧空格）
SELECT LTRIM(' mysql ');
-- 结果：mysql（仅去除左侧空格）
字符串长度与位置查询
-- 计算字符串长度（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
首字母小写格式化
-- 员工姓名首字母小写，其余不变（如 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 核心数学函数一览
| 函数 | 描述 |
|---|---|
ABS(number) | 绝对值 |
BIN(decimal) | 十进制数转二进制 |
HEX(decimal) | 十进制数转十六进制 |
CONV(number, from_base, to_base) | 进制转换（如二进制转十进制） |
CEILING(number) | 向上取整（大于等于该数的最小整数） |
FLOOR(number) | 向下取整（小于等于该数的最大整数） |
FORMAT(number, decimals) | 格式化数值，保留 decimals 位小数（四舍五入） |
RAND() | 生成随机浮点数（范围：[0.0, 1.0)） |
MOD(number, denominator) | 取模（求余数，等同于 number % denominator） |
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 核心工具函数一览
| 函数 | 描述 |
|---|---|
USER() | 查询当前登录 MySQL 的用户（格式：用户名@主机） |
DATABASE() | 查询当前正在使用的数据库 |
MD5(str) | 对字符串进行 MD5 加密（返回 32 位字符串） |
PASSWORD(str) | MySQL 用户密码加密（仅用于用户认证，不推荐业务加密） |
IFNULL(val1, val2) | 空值处理：val1 为 null 则返回 val2，否则返回 val1 |
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 开发效率。核心要点如下：
- 日期函数解决时间获取、加减、差值计算，是业务系统的核心工具；
- 字符串函数处理文本格式、拼接、截取，满足数据展示和清洗需求；
- 数学函数用于数值运算和格式化，适用于统计、随机数生成等场景；
- 工具类函数提供加密、空值处理等实用功能，简化开发流程。