前言
在 MySQL 开发中,内置函数是提升 SQL 效率和灵活性的核心工具。无论是日期计算、字符串处理,还是数学运算、数据加密,合理使用内置函数能大幅简化代码,避免重复逻辑。本文基于实战场景,全面拆解 MySQL 常用内置函数,涵盖日期、字符串、数学、其他工具类函数,附带详细案例和避坑要点。
MySQL 内置函数涵盖日期、字符串、数学及工具类功能。日期函数支持时间获取、加减运算及差值计算;字符串函数处理拼接、截取、替换及大小写转换;数学函数提供取整、进制转换与随机数生成;工具函数涉及加密与空值处理。文章通过实战案例演示常见用法,并指出 length 按字节计算、substring 索引从 1 开始等避坑要点,帮助开发者提升 SQL 编写效率。

在 MySQL 开发中,内置函数是提升 SQL 效率和灵活性的核心工具。无论是日期计算、字符串处理,还是数学运算、数据加密,合理使用内置函数能大幅简化代码,避免重复逻辑。本文基于实战场景,全面拆解 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(LCASE(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;
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 开发效率。核心要点总结:

微信公众号「极客日志」,在微信中扫描左侧二维码关注。展示文案:极客日志 zeeklog
在线格式化和美化您的 SQL 查询(它支持各种 SQL 方言)。 在线工具,SQL 美化和格式化在线工具,online
解析 INSERT 等受限 SQL,导出为 CSV、JSON、XML、YAML、HTML 表格(见页内语法说明)。 在线工具,SQL转CSV/JSON/XML在线工具,online
CSV 与 JSON/XML/HTML/TSV/SQL 等互转,单页多 Tab。 在线工具,CSV 工具包在线工具,online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具,Base64 字符串编码/解码在线工具,online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具,Base64 文件转换器在线工具,online
将 Markdown(GFM)转为 HTML 片段,浏览器内 marked 解析;与 HTML转Markdown 互为补充。 在线工具,Markdown转HTML在线工具,online