✨感谢您阅读本篇文章，文章内容是个人学习笔记的整理，如果哪里有误的话还请您指正噢✨
✨ 个人主页：余辉zmh–CSDN博客
✨ 文章所属专栏：MySQL篇–CSDN博客
文章目录
- 内置函数
- 一.日期函数
- 二.字符串函数
- 三.数学函数
- 四.其他函数
内置函数
一.日期函数
1.相关函数
current_date()：当前日期，年-月-日
mysql> SELECT current_date();
+----------------+
| current_date() |
+----------------+
| 2025-07-31 |
+----------------+
1 row in set (0.00 sec)
current_time()：当前时间，时-分-秒
mysql> SELECT current_time();
+----------------+
| current_time() |
+----------------+
| 17:52:46 |
+----------------+
1 row in set (0.00 sec)
current_timestamp()：当前时间戳，年-月-日 时-分-秒
mysql> SELECT current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2025-07-31 17:53:11 |
+---------------------+
1 row in set (0.00 sec)
date(datetime)：返回datemtime类型参数的日期部分
mysql> SELECT date('1945-10-01 10:00:00');
+-----------------------------+
| date('1945-10-01 10:00:00') |
+-----------------------------+
| 1945-10-01 |
+-----------------------------+
1 row in set (0.00 sec)
now()：当前日期时间
mysql> SELECT now();
+---------------------+
| now() |
+---------------------+
| 2025-07-31 17:56:58 |
+---------------------+
1 row in set (0.00 sec)
date_add(date, interval d_value_type)：
在日期date的基础上添加日期或时间，interval后的数值单位可以是年，日，分，秒；
-- 当前日期加上10天
mysql> SELECT date_add(now(), interval 10 day);
+----------------------------------+
| date_add(now(), interval 10 day) |
+----------------------------------+
| 2025-08-10 17:55:38 |
+----------------------------------+
1 row in set (0.00 sec)-- 当前日期加上10分钟
mysql> SELECT date_add(now(), interval 10 minute);
+-------------------------------------+
| date_add(now(), interval 10 minute) |
+-------------------------------------+
| 2025-07-31 18:06:16 |
+-------------------------------------+
1 row in set (0.00 sec)
date_sub(date, interval d_value_type)：
在日期date的基础上减去日期或时间，interval后的数值单位可以是年，日，分，秒；
-- 当前日期减去1年
mysql> SELECT date_sub(now(), interval 1 year);
+----------------------------------+
| date_sub(now(), interval 1 year) |
+----------------------------------+
| 2024-07-31 17:58:07 |
+----------------------------------+
1 row in set (0.00 sec)-- 当前日期减去10秒
mysql> SELECT date_sub(now(), interval 10 second);
+-------------------------------------+
| date_sub(now(), interval 10 second) |
+-------------------------------------+
| 2025-07-31 17:58:53 |
+-------------------------------------+
1 row in set (0.00 sec)
datediff(date1, date2)：两个日期相差多少天date1-dat2
mysql> SELECT datediff(now(), '1945-10-01');
+-------------------------------+
| datediff(now(), '1945-10-01') |
+-------------------------------+
| 29158 |
+-------------------------------+
1 row in set (0.00 sec)mysql> SELECT datediff('1945-10-01', now());
+-------------------------------+
| datediff('1945-10-01', now()) |
+-------------------------------+
| -29158 |
+-------------------------------+
1 row in set (0.00 sec)
2.示例：
- 创建一个留言表
mysql> CREATE TABLE msg(-> id int unsigned PRIMARY KEY AUTO_INCREMENT,-> message varchar(100) NOT NULL,-> sendtime datetime NOT NULL-> );
Query OK, 0 rows affected (0.05 sec)
- 插入测试数据
mysql> INSERT INTO msg (message, sendtime) values -> ('你好', now()),-> ('你也好', now());
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> INSERT INTO msg(message, sendtime) values ('你是谁', now());
Query OK, 1 row affected (0.01 sec)mysql> SELECT * FROM msg;
+----+-----------+---------------------+
| id | message | sendtime |
+----+-----------+---------------------+
| 1 | 你好 | 2025-07-31 18:19:59 |
| 2 | 你也好 | 2025-07-31 18:19:59 |
| 3 | 你是谁 | 2025-07-31 18:25:43 |
+----+-----------+---------------------+
3 rows in set (0.00 sec)
- 显示所有留言信息，发布日期只显示日期，不用显示时间
mysql> SELECT id, message, date(sendtime) FROM msg;
+----+-----------+----------------+
| id | message | date(sendtime) |
+----+-----------+----------------+
| 1 | 你好 | 2025-07-31 |
| 2 | 你也好 | 2025-07-31 |
| 3 | 你是谁 | 2025-07-31 |
+----+-----------+----------------+
3 rows in set (0.00 sec)
- 查询五分钟内发布的贴子
-- 当前时间减去五分后后要小于发布的时间
mysql> SELECT * FROM msg WHERE date_sub(now(), interval 5 minute) < sendtime;
+----+-----------+---------------------+
| id | message | sendtime |
+----+-----------+---------------------+
| 3 | 你是谁 | 2025-07-31 18:25:43 |
+----+-----------+---------------------+
1 row in set (0.00 sec)-- 当前发布的时间加上五分钟后要大于当前时间
mysql> SELECT * FROM msg WHERE date_add(sendtime, interval 5 minute) > now();
+----+-----------+---------------------+
| id | message | sendtime |
+----+-----------+---------------------+
| 3 | 你是谁 | 2025-07-31 18:25:43 |
+----+-----------+---------------------+
1 row in set (0.00 sec)
二.字符串函数
charset(str)：返回字符串字符集
mysql> SELECT charset('abcd');
+-----------------+
| charset('abcd') |
+-----------------+
| utf8mb4 |
+-----------------+
1 row in set (0.00 sec)
concat(string1, string2, ...)：连接字符集
mysql> SELECT concat('a', 'b', 'c', 1, 2);
+-----------------------------+
| concat('a', 'b', 'c', 1, 2) |
+-----------------------------+
| abc12 |
+-----------------------------+
1 row in set (0.00 sec)instr(string, substring)：返回substring在string中出现的位置，没有返回0
mysql> SELECT instr('abcdefg123', 'def');
+----------------------------+
| instr('abcdefg123', 'def') |
+----------------------------+
| 4 |
+----------------------------+
1 row in set (0.00 sec)mysql> SELECT instr('abcdefg123', 'zmh');
+----------------------------+
| instr('abcdefg123', 'zmh') |
+----------------------------+
| 0 |
+----------------------------+
1 row in set (0.00 sec)
ucase(string)：转为成大写
mysql> SELECT ucase('abcdABCD123');
+----------------------+
| ucase('abcdABCD123') |
+----------------------+
| ABCDABCD123 |
+----------------------+
1 row in set (0.01 sec)
lcase(string)：转换成小写
mysql> SELECT lcase('abcdABCD123');
+----------------------+
| lcase('abcdABCD123') |
+----------------------+
| abcdabcd123 |
+----------------------+
1 row in set (0.01 sec)
left(string, length)：从字符串的左边起取length个字符
mysql> SELECT left('abcdefg', 4);
+--------------------+
| left('abcdefg', 4) |
+--------------------+
| abcd |
+--------------------+
1 row in set (0.00 sec)
length(string)：字符串的长度
mysql> SELECT length('abcdefg');
+-------------------+
| length('abcdefg') |
+-------------------+
| 7 |
+-------------------+
1 row in set (0.00 sec)
replace(str, search_str, replace_str)：在str中用replace_str替换search_str字符
mysql> SELECT replace('abcdabcdabcd', 'a', 'A');
+-----------------------------------+
| replace('abcdabcdabcd', 'a', 'A') |
+-----------------------------------+
| AbcdAbcdAbcd |
+-----------------------------------+
1 row in set (0.00 sec)
strcmp(string1, string2)：逐字符比较两个字符串的大小
mysql> SELECT strcmp('abcd', 'abcd');
+------------------------+
| strcmp('abcd', 'abcd') |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.01 sec)mysql> SELECT strcmp('abcd', 'a');
+---------------------+
| strcmp('abcd', 'a') |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)
substring(str, position, length)：从str的position位置开始取length个字符
mysql> SELECT substring('abcdefg', 2, 3);
+----------------------------+
| substring('abcdefg', 2, 3) |
+----------------------------+
| bcd |
+----------------------------+
1 row in set (0.00 sec)mysql> SELECT substring('abcdefg', 3);
+-------------------------+
| substring('abcdefg', 3) |
+-------------------------+
| cdefg |
+-------------------------+
1 row in set (0.01 sec)
ltrim(string) rtrim(string) trim(string)：去除前空格或后空格
-- 测试字符串 左边3个空格+hello+中间两个空格+world+右边3个空格 = 18
mysql> SELECT ltrim(' hello world ');
+-----------------------------+
| ltrim(' hello world ') |
+-----------------------------+
| hello world |
+-----------------------------+
1 row in set (0.00 sec)
-- 去除左边3个长度变为15
mysql> SELECT length(ltrim(' hello world '));
+-------------------------------------+
| length(ltrim(' hello world ')) |
+-------------------------------------+
| 15 |
+-------------------------------------+
1 row in set (0.00 sec)-- 去除右边的，但是这里效果不明显(可能是因为 MySQL 命令行工具输出表格时自动补齐的，不是字符串本身的内容。)
mysql> SELECT rtrim(' hello world ');
+-----------------------------+
| rtrim(' hello world ') |
+-----------------------------+
| hello world |
+-----------------------------+
1 row in set (0.00 sec)
-- 打印长度，也是变为15
mysql> SELECT length(rtrim(' hello world '));
+-------------------------------------+
| length(rtrim(' hello world ')) |
+-------------------------------------+
| 15 |
+-------------------------------------+
1 row in set (0.00 sec)-- 两边都去除，但效果也不明显
mysql> SELECT trim(' hello world ');
+----------------------------+
| trim(' hello world ') |
+----------------------------+
| hello world |
+----------------------------+
1 row in set (0.00 sec)
-- 打印长度，变为12
mysql> SELECT length(trim(' hello world '));
+------------------------------------+
| length(trim(' hello world ')) |
+------------------------------------+
| 12 |
+------------------------------------+
1 row in set (0.00 sec)
三.数学函数
abs(number)：绝对值函数
mysql> SELECT abs(-100);
+-----------+
| abs(-100) |
+-----------+
| 100 |
+-----------+
1 row in set (0.00 sec)mysql> SELECT abs(100);
+----------+
| abs(100) |
+----------+
| 100 |
+----------+
1 row in set (0.01 sec)
bin(decimal_number)：十进制转换为二进制
mysql> SELECT bin(10);
+---------+
| bin(10) |
+---------+
| 1010 |
+---------+
1 row in set (0.00 sec)
hex(number)：十进制转为十六进制
mysql> SELECT hex(10);
+---------+
| hex(10) |
+---------+
| A |
+---------+
1 row in set (0.01 sec)
conv(number, from_base, to_base)：进制转换
-- 10从十进制转换为二进制
mysql> SELECT conv(10, 10, 2);
+-----------------+
| conv(10, 10, 2) |
+-----------------+
| 1010 |
+-----------------+
1 row in set (0.00 sec)-- 10从十进制转换为十六进制
mysql> SELECT conv(10, 10, 16);
+------------------+
| conv(10, 10, 16) |
+------------------+
| A |
+------------------+
1 row in set (0.00 sec)
ceiling(number)：向上取整
mysql> SELECT ceiling(3.1);
+--------------+
| ceiling(3.1) |
+--------------+
| 4 |
+--------------+
1 row in set (0.01 sec)mysql> SELECT ceiling(3.9);
+--------------+
| ceiling(3.9) |
+--------------+
| 4 |
+--------------+
1 row in set (0.00 sec)mysql> SELECT ceiling(-3.1);
+---------------+
| ceiling(-3.1) |
+---------------+
| -3 |
+---------------+
1 row in set (0.01 sec)mysql> SELECT ceiling(-3.9);
+---------------+
| ceiling(-3.9) |
+---------------+
| -3 |
+---------------+
1 row in set (0.00 sec)
floor(number)：向下取整
mysql> SELECT floor(3.1);
+------------+
| floor(3.1) |
+------------+
| 3 |
+------------+
1 row in set (0.00 sec)mysql> SELECT floor(3.9);
+------------+
| floor(3.9) |
+------------+
| 3 |
+------------+
1 row in set (0.00 sec)mysql> SELECT floor(-3.1);
+-------------+
| floor(-3.1) |
+-------------+
| -4 |
+-------------+
1 row in set (0.00 sec)mysql> SELECT floor(-3.9);
+-------------+
| floor(-3.9) |
+-------------+
| -4 |
+-------------+
1 row in set (0.00 sec)
format(number, decimal_places)：格式化，保留小数位数
mysql> SELECT format(3.1415926, 2);
+----------------------+
| format(3.1415926, 2) |
+----------------------+
| 3.14 |
+----------------------+
1 row in set (0.01 sec)mysql> SELECT format(3.1415926, 3);
+----------------------+
| format(3.1415926, 3) |
+----------------------+
| 3.142 |
+----------------------+
1 row in set (0.00 sec)mysql> SELECT format(3.1415926, 4);
+----------------------+
| format(3.1415926, 4) |
+----------------------+
| 3.1416 |
+----------------------+
1 row in set (0.00 sec)
rand()：返回随机浮点数，范围[0.0-1.0]
mysql> SELECT rand();
+---------------------+
| rand() |
+---------------------+
| 0.03627621851514673 |
+---------------------+
1 row in set (0.01 sec)mysql> SELECT rand()*1000;
+-------------------+
| rand()*1000 |
+-------------------+
| 478.7169121938915 |
+-------------------+
1 row in set (0.00 sec)mysql> SELECT format(rand()*1000, 4);
+------------------------+
| format(rand()*1000, 4) |
+------------------------+
| 284.7559 |
+------------------------+
1 row in set (0.00 sec)
mod(number, denominator)：取模，求余
mysql> SELECT mod(10, 3);
+------------+
| mod(10, 3) |
+------------+
| 1 |
+------------+
1 row in set (0.01 sec)mysql> SELECT mod(10, -3);
+-------------+
| mod(10, -3) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)mysql> SELECT mod(-10, -3);
+--------------+
| mod(-10, -3) |
+--------------+
| -1 |
+--------------+
1 row in set (0.00 sec)mysql> SELECT mod(-10, 3);
+-------------+
| mod(-10, 3) |
+-------------+
| -1 |
+-------------+
1 row in set (0.00 sec)
四.其他函数
user()：查询当前用户
mysql> SELECT user();
+-----------------+
| user() |
+-----------------+
| zmh_1@localhost |
+-----------------+
1 row in set (0.00 sec)
md5(str)：对一个字符串进行md5摘要，摘要后得到一个32位字符串
mysql> SELECT md5('abcdefg');
+----------------------------------+
| md5('abcdefg') |
+----------------------------------+
| 7ac66c0f148de9519b8bd264312c4d64 |
+----------------------------------+
1 row in set (0.00 sec)mysql> SELECT md5('a');
+----------------------------------+
| md5('a') |
+----------------------------------+
| 0cc175b9c0f1b6a831c399e269772661 |
+----------------------------------+
1 row in set (0.00 sec)
database()：显示当前正在使用的是哪个数据库
mysql> SELECT database();
+------------+
| database() |
+------------+
| test1 |
+------------+
1 row in set (0.00 sec)
password()：MySQL数据库使用该函数对用户加密
但是这个函数在新版本 MySQL 已经不能用了，所以这里也没办法做演示，就了解一下吧。
ifnull(val1, val2)：如果val1为空，返回val2，否则返回val1
mysql> SELECT ifnull(NULL, 100);
+-------------------+
| ifnull(NULL, 100) |
+-------------------+
| 100 |
+-------------------+
1 row in set (0.00 sec)mysql> SELECT ifnull(200, 100);
+------------------+
| ifnull(200, 100) |
+------------------+
| 200 |
+------------------+
1 row in set (0.00 sec)-- 只考虑第一个值是否为空，不考虑第二个值
mysql> SELECT ifnull(200, NULL);
+-------------------+
| ifnull(200, NULL) |
+-------------------+
| 200 |
+-------------------+
1 row in set (0.00 sec)
以上就是关于MySQL常用内置函数的讲解，如果哪里有错的话，可以在评论区指正，也欢迎大家一起讨论学习，如果对你的学习有帮助的话，点点赞关注支持一下吧！！！
