MySQL 内置函数实战：日期、字符串与数学运算详解

日期差计算与时间提取

-- 计算两个日期的天数差（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();

业务场景：筛选近期记录

-- 查询 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 实战案例

字符串拼接与格式处理

-- 业务场景：格式化显示成绩
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;

字符串截取与替换

-- 截取姓名字段的第二个到第三个字符
SELECT ename, SUBSTRING(ename, 2, 2) FROM exam_result;

-- 替换字符串（将'S'替换为'上海'）
SELECT ename, REPLACE(ename, 'S', '上海') FROM exam_result;

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

大小写转换与空格处理

-- 转大写/小写
SELECT UCASE('mysql'), LOWER('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（第一个逗号的位置）

-- 统计逗号出现次数
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(
    LOWER(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 开发效率。日期函数解决时间获取、加减、差值计算；字符串函数处理文本格式、拼接、截取；数学函数用于数值运算和格式化；工具类函数提供加密、空值处理等实用功能。掌握这些函数后，可应对大部分 SQL 开发场景。