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

MySQL 内置函数实战：日期、字符串与数学运算 | 极客日志

函数	描述
`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 的天数差（仅日期部分）

-- 获取当前日期
SELECT current_date();
-- 结果：2026-03-10

-- 获取当前时间
SELECT current_time();
-- 结果：12:34:50

-- 获取当前时间戳
SELECT current_timestamp();
-- 结果：2026-03-10 12:34:50
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;

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;

函数名称	描述
`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）

-- 拼接字符串（业务场景：格式化显示成绩）
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'), 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（第一个逗号的位置）

-- 业务场景：统计逗号出现次数（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(LOWER(SUBSTRING(ename, 1, 1)), SUBSTRING(ename, 2)) AS format_name FROM emp;

函数	描述
`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）

-- 绝对值
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

函数	描述
`user()`	查询当前登录 MySQL 的用户（格式：用户名@主机）
`database()`	查询当前正在使用的数据库
`md5(str)`	对字符串进行 MD5 加密（返回 32 位字符串）
`password(str)`	MySQL 用户密码加密（仅用于用户认证，不推荐业务加密）
`ifnull(val1, val2)`	空值处理：val1 为 null 则返回 val2，否则返回 val1

-- 查询当前用户和数据库
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;

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

前言：

一。日期函数：处理时间相关需求

1.1 核心日期函数表

1.2 实战案例

1.2.1 基础时间获取

1.2.2 日期加减运算

1.2.3 日期差计算与时间提取

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

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

2.1 核心字符串函数表

2.2 实战案例

2.2.1 字符串拼接与格式处理

2.2.2 字符串截取与替换

2.2.3 大小写转换与空格处理

2.2.4 字符串长度与位置查询

2.2.5 首字母小写格式化

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

3.1 核心数学函数表

3.2 实战案例

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

4.1 核心工具函数表

4.2 实战案例

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

更多推荐文章

相关免费在线工具

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

前言：

一。日期函数：处理时间相关需求

1.1 核心日期函数表

1.2 实战案例

1.2.1 基础时间获取

1.2.2 日期加减运算

1.2.3 日期差计算与时间提取

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

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

2.1 核心字符串函数表

2.2 实战案例

2.2.1 字符串拼接与格式处理

2.2.2 字符串截取与替换

2.2.3 大小写转换与空格处理

2.2.4 字符串长度与位置查询

2.2.5 首字母小写格式化

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

3.1 核心数学函数表

3.2 实战案例

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

4.1 核心工具函数表

4.2 实战案例

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

微信扫一扫，关注极客日志

更多推荐文章

相关免费在线工具