当前位置：首页 > news >正文

【MySQL基础篇】：MySQL常用内置函数以及实用示例

news 2025/8/5 10:54:40

✨感谢您阅读本篇文章，文章内容是个人学习笔记的整理，如果哪里有误的话还请您指正噢✨
✨ 个人主页：余辉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)