【MYSQL】MYSQL学习的一大重点：内置函数

🎬 个人主页：艾莉丝努力练剑

❄专栏传送门：《C语言》《数据结构与算法》《C/C++干货分享&学习过程记录》 《Linux操作系统编程详解》《笔试/面试常见算法：从基础到进阶》《Python干货分享》

⭐️为天地立心，为生民立命，为往圣继绝学，为万世开太平

🎬 艾莉丝的简介：

文章目录

• 0 ~> MySQL 内置函数
• • 0.1 日期类函数
  • 0.2 字符串类函数
  • 0.3 数学类函数
  • 0.4 其他系统函数
• 1 ~> 日期类函数
• • 1.1 基础时间获取函数
  • • 1.1.1 current_date()
    • 1.1.2 current_time()
    • 1.1.3 current_timestamp()
    • 1.1.4 now()
  • 1.2 日期提取与运算函数
  • • 1.2.1 date(datetime)
    • 1.2.2 date_add (date, INTERVAL 值 单位)
    • 1.2.3 date_sub (date, INTERVAL 值 单位)
    • 1.2.4 datediff(date1, date2)
  • 1.3 实战案例
  • • 案例 1：生日表设计与数据插入
    • 案例 2：留言表与时间范围查询
• 2 ~> 字符串类函数
• • 2.1 字符集与拼接函数
  • • 2.1.1 charset(str)
    • 2.1.2 concat(str1, str2, …)
  • 2.2 查找与截取函数
  • • 2.2.1 instr(string, substring)
    • 2.2.2 left(str, length) / right(str, length)
    • 2.2.3 substring(str, position [, length])
  • 2.3 长度与替换函数
  • • 2.3.1 length (str) 【高频考点】
    • 2.3.2 replace(str, search_str, replace_str)
  • 2.4 大小写转换与比较
  • • 2.4.1 ucase(str) / lcase(str)
    • 2.4.2 strcmp(str1, str2)
  • 2.5 空格清洗函数
  • • 2.5.1 ltrim() / rtrim() / trim()
  • 2.6 实战案例
  • • 案例：首字母小写显示员工姓名
• 3 ~> 数学类函数
• • 3.1 基础数值运算
  • • 3.1.1 abs(number)
    • 3.1.2 mod(number, denominator)
  • 3.2 进制转换函数
  • • 3.2.1 bin(decimal_number)
    • 3.2.2 hex(decimalNumber)
    • 3.2.3 conv(number, from_base, to_base)
  • 3.3 取整与格式化
  • • 3.3.1 ceiling(number) / floor(number)
    • 3.3.2 format(number, decimal_places)
  • 3.4 随机数函数
  • • 3.4.1 rand()
• 4 ~> 其他常用函数
• • 4.1 系统信息函数
  • • 4.1.1 user()
    • 4.1.2 database()
  • 4.2 加密摘要函数
  • • 4.2.1 md5(str)
    • 4.2.2 password(str)
  • 4.3 空值处理函数
  • • 4.3.1 ifnull(val1, val2)
    • 4.3.2 补充：isnull (expr)
• 结尾

0 ~> MySQL 内置函数

0.1 日期类函数

0.2 字符串类函数

• 基础属性：charset() / length() / char_length()
• 拼接与截取：concat() / left() / right() / substring()
• 查找与替换：instr() / replace()
• 格式转换：ucase() / lcase()
• 比较与清洗：strcmp() / ltrim() / rtrim() / trim()

0.3 数学类函数

• 数值运算：abs() / mod()
• 进制转换：bin() / hex() / conv()
• 取整格式化：ceiling() / floor() / format()
• 随机数：rand()

0.4 其他系统函数

• 系统信息：user() / database()
• 加密摘要：md5() / password()
• 空值处理：ifnull() / isnull()

1 ~> 日期类函数

1.1 基础时间获取函数

1.1.1 current_date()

• 功能：获取当前系统日期，格式为YYYY-MM-DD，仅包含年月日。
• 示例：

SELECT current_date();
— 输出示例：2023-06-07

1.1.2 current_time()

• 功能：获取当前系统时间，格式为HH:MM:SS，仅包含时分秒。
• 示例：

SELECT current_time();
— 输出示例：14:23:47

1.1.3 current_timestamp()

• 功能：获取当前时间戳，格式为YYYY-MM-DD HH:MM:SS，包含完整日期时间，随系统时间实时递增。
• 示例：

SELECT current_timestamp();
— 输出示例：2023-06-07 14:24:55

1.1.4 now()

• 功能：获取当前完整日期时间，返回格式与current_timestamp()一致，为 SQL 中最常用的时间获取函数。
• 示例：

SELECT now();
— 输出示例：2023-06-07 14:25:42

1.2 日期提取与运算函数

1.2.1 date(datetime)

• 功能：提取 datetime 类型参数中的日期部分，丢弃时分秒。
• 常用场景：从完整时间字段中提取日期做分组统计。
• 示例：

— 从指定时间中提取日期
SELECT date('1949-10-01 00:00:00');
— 输出：1949-10-01

— 嵌套使用，提取当前日期
SELECT date(now());
— 效果等价于 current_date()

1.2.2 date_add (date, INTERVAL 值 单位)

• 功能：在指定日期 / 时间基础上，增加指定时长的时间量。
• 支持单位：year、month、day、hour、minute、second。
• 示例：

— 日期加10天
SELECT date_add('2017-10-28', INTERVAL 10 day);
— 输出：2017-11-07

— 当前时间加10分钟
SELECT date_add(now(), INTERVAL 10 minute);

1.2.3 date_sub (date, INTERVAL 值 单位)

• 功能：在指定日期 / 时间基础上，减去指定时长的时间量，单位与date_add一致。
• 示例：

— 日期减10天
SELECT date_sub('2050-01-01', INTERVAL 10 day);
— 输出：2049-12-22

— 当前时间减2分钟（常用作“近N分钟数据查询”）
SELECT date_sub(now(), INTERVAL 2 minute);

1.2.4 datediff(date1, date2)

• 功能：计算两个日期的差值，返回单位为天，计算规则为 date1 – date2。
• 注意：仅计算日期部分的差值，忽略时分秒。
• 示例：

SELECT datediff('2010-10-10', '2016-09-01');
— 输出：-2153（前者小于后者，结果为负）

— 计算建国至今天数
SELECT datediff(date(now()), '1949-10-01');

1.3 实战案例

案例 1：生日表设计与数据插入

— 建表
CREATE TABLE tmp (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
birthday DATE NOT NULL
);

— 规范插入：使用日期函数/date()包裹，避免隐式转换warning
INSERT INTO tmp(birthday) VALUES(current_date());
INSERT INTO tmp(birthday) VALUES(date(current_timestamp()));
INSERT INTO tmp(birthday) VALUES('1980-01-01');

案例 2：留言表与时间范围查询

— 建表
CREATE TABLE msg (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
content VARCHAR(100) NOT NULL,
sendtime DATETIME
);

— 插入数据
INSERT INTO msg(content, sendtime) VALUES('纸上得来终觉浅', now());

— 需求1：只显示发布日期，不显示时间
SELECT content, date(sendtime) FROM msg;

— 需求2：查询2分钟内发布的帖子
— 推荐写法：字段不参与运算，可命中索引
SELECT * FROM msg WHERE sendtime > date_sub(now(), INTERVAL 2 minute);
— 不推荐写法：字段参与运算，无法命中索引
SELECT * FROM msg WHERE date_add(sendtime, INTERVAL 2 minute) > now();

2 ~> 字符串类函数

2.1 字符集与拼接函数

2.1.1 charset(str)

• 功能：返回字符串的字符集编码。
• 常用场景：排查乱码问题，确认字段的字符集。
• 示例：

SELECT charset('abcd');
— 输出：utf8

— 查询表字段的字符集
SELECT charset(ename) FROM emp;

2.1.2 concat(str1, str2, …)

• 功能：拼接多个字符串，数字、浮点数会自动转为字符串后拼接。
• 注意：任意参数为NULL时，返回结果整体为NULL。
• 示例：

— 基础拼接
SELECT concat('a', 'b', 'c');
— 输出：abc

— 业务场景：格式化成绩展示
SELECT concat('考生姓名:', name, ',总分:', chinese+math+english) AS msg
FROM exam_result;

2.2 查找与截取函数

2.2.1 instr(string, substring)

• 功能：返回子串在主串中首次出现的位置，位置从 1 开始计数；未找到返回 0。
• 示例：

SELECT instr('abcd1234efg', '1234');
— 输出：5

2.2.2 left(str, length) / right(str, length)

• 功能：从字符串左侧 / 右侧截取指定长度的字符。
• 示例：

SELECT left('abcd1234', 3);
— 输出：abc

SELECT right('abcd1234', 3);
— 输出：234

2.2.3 substring(str, position [, length])

• 功能：从指定位置开始截取字符串，可指定截取长度；不指定长度则截取到末尾。
• 注意：位置从 1 开始计数。
• 示例：

— 从第2个字符开始，截取2个字符
SELECT substring('SMITH', 2, 2);
— 输出：MI

— 从第2个字符截取到末尾
SELECT substring('SMITH', 2);
— 输出：MITH

2.3 长度与替换函数

2.3.1 length (str) 【高频考点】

• 功能：返回字符串的字节长度，单位为字节。
• 核心区分：
  • length()：字节长度，UTF-8 下 1 个汉字占 3 字节，1 个英文字母占 1 字节。
  • char_length()：字符长度，无论中英文，1 个字符算 1 个。
• 示例：

SELECT length('abc');
— 输出：3

SELECT length('你好');
— UTF-8下输出：6

SELECT char_length('你好');
— 输出：2

2.3.2 replace(str, search_str, replace_str)

• 功能：将字符串中所有的search_str替换为replace_str。
• 注意：仅作用于查询结果，不修改数据库原数据。
• 示例：

— 将员工姓名中的S替换为“上海”
SELECT replace(ename, 'S', '上海'), ename FROM emp;

2.4 大小写转换与比较

2.4.1 ucase(str) / lcase(str)

• 功能：将字符串全部转为大写 / 小写，非字母字符保持不变。
• 示例：

SELECT ucase('abcd1234ABCD');
— 输出：ABCD1234ABCD

SELECT lcase('abcd1234ABCD');
— 输出：abcd1234abcd

2.4.2 strcmp(str1, str2)

• 功能：逐字符比较两个字符串大小。
• 返回规则：
  • str1 > str2 → 返回 1
  • str1 = str2 → 返回 0
  • str1 < str2 → 返回 – 1

2.5 空格清洗函数

2.5.1 ltrim() / rtrim() / trim()

• 功能：
  • ltrim(str)：去除字符串左侧空格
  • rtrim(str)：去除字符串右侧空格
  • trim(str)：去除字符串左右两侧空格
• 注意：均不会去除字符串中间的空格。
• 示例：

SELECT trim(' 你好 hello ');
— 输出：你好 hello

2.6 实战案例

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

SELECT
concat(
lcase(substring(ename, 1, 1)), — 首字母转小写
substring(ename, 2) — 拼接剩余字符
) AS lower_first_ename,
ename
FROM emp;

3 ~> 数学类函数

3.1 基础数值运算

3.1.1 abs(number)

• 功能：返回数值的绝对值。
• 示例：

SELECT abs(–12.3);
— 输出：12.3

3.1.2 mod(number, denominator)

• 功能：取模（求余）运算，结果符号与被除数一致。
• 示例：

SELECT mod(–10, 3);
— 输出：-1

SELECT mod(10, –3);
— 输出：1

3.2 进制转换函数

3.2.1 bin(decimal_number)

• 功能：将十进制整数转换为二进制字符串。
• 注意：传入浮点数时，会先取整再转换。
• 示例：

SELECT bin(10);
— 输出：1010

SELECT bin(3.14);
— 先取整为3，输出：11

3.2.2 hex(decimalNumber)

• 功能：将十进制整数转换为十六进制字符串。
• 示例：

SELECT hex(15);
— 输出：F

3.2.3 conv(number, from_base, to_base)

• 功能：通用进制转换，支持 2~36 进制之间的任意转换。
• 示例：

— 十进制10转二进制
SELECT conv(10, 10, 2);
— 输出：1010

— 十进制10转十六进制
SELECT conv(10, 10, 16);
— 输出：A

3.3 取整与格式化

3.3.1 ceiling(number) / floor(number)

• 功能：
  • ceiling()：向上取整（向数值更大的方向取整）
  • floor()：向下取整（向数值更小的方向取整）
• 示例：

SELECT ceiling(3.1);
— 输出：4

SELECT ceiling(–3.9);
— 输出：-3

SELECT floor(4.9);
— 输出：4

SELECT floor(–4.1);
— 输出：-5

3.3.2 format(number, decimal_places)

• 功能：格式化数字，保留指定小数位数，遵循四舍五入规则。
• 示例：

SELECT format(3.1415926, 2);
— 输出：3.14

SELECT format(3.1415926, 3);
— 输出：3.142

3.4 随机数函数

3.4.1 rand()

• 功能：返回[0.0, 1.0)范围内的随机浮点数。
• 常用技巧：
  • 生成 0~100 随机数：rand() * 100
  • 生成 0~100 整数：floor(rand() * 100)
• 示例：

— 生成0~100的随机整数
SELECT floor(rand() * 100);

4 ~> 其他常用函数

4.1 系统信息函数

4.1.1 user()

• 功能：查询当前登录的 MySQL 用户。
• 示例：

SELECT user();

4.1.2 database()

• 功能：查询当前正在使用的数据库。
• 示例：

SELECT database();

4.2 加密摘要函数

4.2.1 md5(str)

• 功能：对字符串进行 MD5 哈希摘要，返回 32 位小写十六进制字符串。
• 常用场景：用户密码非明文存储（注：MD5 为哈希算法，不可逆，不属于加密）。
• 示例：

SELECT md5('admin');
— 输出：21232f297a57a5a743894a0e4a801fc3

— 用户登录校验
SELECT name FROM user
WHERE name='李四' AND password = md5('hellobit');

4.2.2 password(str)

• 功能：MySQL 内置的账号密码哈希函数，用于 MySQL 内部用户密码加密。
• 重要警示：
  • MySQL 8.0 版本已正式移除该函数，仅 5.7 及更早版本可用。
  • 仅用于 MySQL 内部账号管理，严禁在业务表中使用该函数存储用户密码。
  • 业务场景推荐使用 MD5、SHA2 等通用哈希算法。

4.3 空值处理函数

4.3.1 ifnull(val1, val2)

• 功能：如果val1为NULL，则返回val2；否则返回val1。
• 常用场景：将 NULL 值替换为默认值，避免数值运算结果为 NULL。
• 示例：

SELECT ifnull(null, 10);
— 输出：10

SELECT ifnull('abc', '123');
— 输出：abc

4.3.2 补充：isnull (expr)

• 功能：判断表达式是否为 NULL，是则返回 1，否则返回 0。
• 与 ifnull 的核心区别：isnull是判断函数，返回 0/1 布尔值；ifnull是替换函数，返回具体业务值。
• 示例：

SELECT isnull(null);
— 输出：1

SELECT isnull('abc');
— 输出：0

结尾

uu们，本文的内容到这里就全部结束了，艾莉丝在这里再次感谢您的阅读！

结语：希望对学习Linux相关内容的uu有所帮助，不要忘记给博主“一键四连”哦！

往期回顾：

【MYSQL】MYSQL学习的一大重点：基本查询（下）

🗡博主在这里放了一只小狗，大家看完了摸摸小狗放松一下吧！🗡

૮₍ ˶ ˊ ᴥ ˋ˶₎ა