欢迎拜访：雾里看山-CSDN博客
本篇主题：【MySQL】内置函数
发布时间：2025.2.20
隶属专栏：MySQL
日期函数
函数介绍
| 函数名称 | 描述 |
|---|---|
| current_date() | 当前日期 |
| current_time() | 当前时间 |
| current_timestamp() | 当前时间戳 |
| date(datetime) | 返回 datetime 参数的日期部分 |
| date_add(date, interval d_value_type) | 在 date 中添加日期或者时间 interval 后的数值单位可以是：year minute second day |
| date_sub(date, interval d_value_type) | 在 date 中减去日期或者时间 interval 后的数值单位可以是：year minute second day |
| datediff(date1,date2) | 两个日期的差，单位是天 |
| now() | 当前日期时间 |
函数使用
current_date() 获得年月日
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2025-02-18 |
+----------------+
1 row in set (0.02 sec)
current_time() 获得时分秒
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 15:33:32 |
+----------------+
1 row in set (0.00 sec)
current_timestamp() 获得时间戳
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2025-02-18 15:33:37 |
+---------------------+
1 row in set (0.00 sec)
now() 获取日期时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2025-02-18 15:36:23 |
+---------------------+
1 row in set (0.00 sec)
date 截取日期
参数是具体时间
mysql> select date('1949-10-01 00:00:00');
+-----------------------------+
| date('1949-10-01 00:00:00') |
+-----------------------------+
| 1949-10-01 |
+-----------------------------+
1 row in set (0.00 sec)
参数是时间函数
mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2025-02-18 |
+-------------+
1 row in set (0.00 sec)
date_add 日期加
具体日期加时间
mysql> select date_add('2050-01-01', interval 10 day);
+-----------------------------------------+
| date_add('2050-01-01', interval 10 day) |
+-----------------------------------------+
| 2050-01-11 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2050-01-01', interval 10 month);
+-------------------------------------------+
| date_add('2050-01-01', interval 10 month) |
+-------------------------------------------+
| 2050-11-01 |
+-------------------------------------------+
1 row in set (0.00 sec)
函数日期加时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2025-02-18 15:47:57 |
+---------------------+
1 row in set (0.00 sec)
mysql> select date_add(now(), interval 10 month);
+------------------------------------+
| date_add(now(), interval 10 month) |
+------------------------------------+
| 2025-12-18 15:47:59 |
+------------------------------------+
1 row in set (0.00 sec)
date_sub 日期减
具体日期减时间
mysql> select date_sub('2050-01-01', interval 10 day);
+-----------------------------------------+
| date_sub('2050-01-01', interval 10 day) |
+-----------------------------------------+
| 2049-12-22 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> select date_sub('2050-01-01', interval 10 month);
+-------------------------------------------+
| date_sub('2050-01-01', interval 10 month) |
+-------------------------------------------+
| 2049-03-01 |
+-------------------------------------------+
1 row in set (0.00 sec)
函数日期加时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2025-02-18 15:49:50 |
+---------------------+
1 row in set (0.00 sec)
mysql> select date_sub(now(), interval 10 month);
+------------------------------------+
| date_sub(now(), interval 10 month) |
+------------------------------------+
| 2024-04-18 15:50:00 |
+------------------------------------+
1 row in set (0.00 sec)
datediff 日期差
mysql> select datediff('2020-10-23', '2013-04-13');
+--------------------------------------+
| datediff('2020-10-23', '2013-04-13') |
+--------------------------------------+
| 2750 |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> select datediff(date(now()), '1949-10-01');
+-------------------------------------+
| datediff(date(now()), '1949-10-01') |
+-------------------------------------+
| 27534 |
+-------------------------------------+
1 row in set (0.00 sec)
案例
案例1 ：创建一张表，记录生日
mysql> create table tmp(
-> id bigint primary key auto_increment,
-> birthday date
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc tmp;
+----------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| birthday | date | YES | | NULL | |
+----------+------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
插入数据
mysql> insert into tmp (brithday) values ('1990-02-24');
ERROR 1054 (42S22): Unknown column 'brithday' in 'field list'
mysql> insert into tmp (birthday) values ('1990-02-24');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tmp (birthday) values ('1980-03-05');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tmp (birthday) values (current_date());
Query OK, 1 row affected (0.00 sec)
mysql> select * from tmp;
+----+------------+
| id | birthday |
+----+------------+
| 1 | 1990-02-24 |
| 2 | 1980-03-05 |
| 3 | 2025-02-18 |
+----+------------+
3 rows in set (0.00 sec)
案例二：创建一个留言表
mysql> create table msg(
-> id bigint primary key auto_increment,
-> content varchar(30) not null,
-> sendtime datetime
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc msg;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| content | varchar(30) | NO | | NULL | |
| sendtime | datetime | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
插入数据
mysql> insert into msg (content, sendtime) values ('纸上得来终觉浅，绝知此事要躬行', now());
Query OK, 1 row affected (0.00 sec)
mysql> insert into msg (content, sendtime) values ('不敢高声语，恐惊天上人', now());
Query OK, 1 row affected (0.00 sec)
mysql> select * from msg;
+----+-----------------------------------------------+---------------------+
| id | content | sendtime |
+----+-----------------------------------------------+---------------------+
| 1 | 纸上得来终觉浅，绝知此事要躬行 | 2025-02-18 16:27:09 |
| 2 | 不敢高声语，恐惊天上人 | 2025-02-18 16:28:51 |
+----+-----------------------------------------------+---------------------+
2 rows in set (0.00 sec)
请查询在2分钟内发布的帖子
select * from msg where date_add(sendtime, interval 2 minute) > now();
或者
select * from msg where sendtime > date_sub(now(), interval 2 minute);
mysql> insert into msg (content, sendtime) values ('举头望明月，低头思故乡', now());
Query OK, 1 row affected (0.00 sec)
mysql> select * from msg where date_add(sendtime, interval 2 minute) > now();
+----+-----------------------------------+---------------------+
| id | content | sendtime |
+----+-----------------------------------+---------------------+
| 3 | 举头望明月，低头思故乡 | 2025-02-18 16:44:15 |
+----+-----------------------------------+---------------------+
1 row in set (0.00 sec)
mysql> select * from msg where sendtime > date_sub(now(), interval 2 minute);
+----+-----------------------------------+---------------------+
| id | content | sendtime |
+----+-----------------------------------+---------------------+
| 3 | 举头望明月，低头思故乡 | 2025-02-18 16:44:15 |
+----+-----------------------------------+---------------------+
1 row in set (0.00 sec)
字符串函数
函数介绍
| 函数 | 描述 |
|---|---|
| charset(str) | 返回字符串字符集 |
| concat(string2 [, …]) | 连接字符串 |
| instr(string,sunstring) | 返回substring在string中出现的位置 |
| ucase(string2) | 转换成大写 |
| lcase(string2) | 转换成小写 |
| left(string2,length) | 从string2的左边取length个字符 |
| length(string) | string的长度 |
| replace(str, search_str,replace_str) | 在str中用replace_str代替search_str |
| strcmp(string1,string2) | 逐字符比较两字符串大小 |
| substring(str, position [, length]) | 从str的position开始，取length个字符 |
| ltrim(string) rtrim(string) trim(string) | 去除前空格或者后空格 |
函数使用
charset 查字符集
应用场景：当插入时有的地方乱码了，可以查看字符集是否有问题。
mysql> select charset('1234');
+-----------------+
| charset('1234') |
+-----------------+
| utf8 |
+-----------------+
1 row in set (0.00 sec)
mysql> select charset(sendtime) from msg;
+-------------------+
| charset(sendtime) |
+-------------------+
| binary |
| binary |
| binary |
+-------------------+
3 rows in set (0.00 sec)
mysql> select charset(content) from msg;
+------------------+
| charset(content) |
+------------------+
| utf8 |
| utf8 |
| utf8 |
+------------------+
3 rows in set (0.00 sec)
concat 拼接字符串
mysql> select concat('a', 'b', 'c', 123, 3.14) as res;
+------------+
| res |
+------------+
| abc1233.14 |
+------------+
1 row in set (0.00 sec)
instr 查找子串
mysql> select instr('abcd1234efg','1234');
+-----------------------------+
| instr('abcd1234efg','1234') |
+-----------------------------+
| 5 |
+-----------------------------+
1 row in set (0.00 sec)
mysql> select instr('abcd1234efg','1234a');
+------------------------------+
| instr('abcd1234efg','1234a') |
+------------------------------+
| 0 |
+------------------------------+
1 row in set (0.00 sec)
ucase 转大写
mysql> select ucase('abcd1234ABCD');
+-----------------------+
| ucase('abcd1234ABCD') |
+-----------------------+
| ABCD1234ABCD |
+-----------------------+
1 row in set (0.00 sec)
lcase 转小写
mysql> select lcase('abcd1234ABCD');
+-----------------------+
| lcase('abcd1234ABCD') |
+-----------------------+
| abcd1234abcd |
+-----------------------+
1 row in set (0.00 sec)
left 从左取length个字符
mysql> select left('abcd1234',5);
+--------------------+
| left('abcd1234',5) |
+--------------------+
| abcd1 |
+--------------------+
1 row in set (0.00 sec)
right 从右取length个字符
mysql> select right('abcd1234',5);
+---------------------+
| right('abcd1234',5) |
+---------------------+
| d1234 |
+---------------------+
1 row in set (0.00 sec)
length 字符串占的字节数
注意：length函数返回字符串长度，以字节为单位。如果是多字节字符则计算多个字节数；
如果是单字节字符则算作一个字节。比如：字母，数字算作一个字节，中文表示多个字节数（与字符集编码有关）
mysql> select length('abcd1234');
+--------------------+
| length('abcd1234') |
+--------------------+
| 8 |
+--------------------+
1 row in set (0.00 sec)
ltrim , rtrim, trim
mysql> select ltrim(' 你好 ') as res;
+---------------------+
| res |
+---------------------+
| 你好 |
+---------------------+
1 row in set (0.00 sec)
mysql> select rtrim(' 你好 ') as res;
+----------------+
| res |
+----------------+
| 你好 |
+----------------+
1 row in set (0.01 sec)
mysql> select trim(' 你好 ') as res;
+--------+
| res |
+--------+
| 你好 |
+--------+
1 row in set (0.00 sec)
案例
- 获取emp表的ename列的字符集
mysql> select charset(ename) from emp;
+----------------+
| charset(ename) |
+----------------+
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
+----------------+
14 rows in set (0.00 sec)
- 要求显示exam_result表中的信息，显示格式：“考生姓名：XXX,总分：XXX,语文成绩：XXX,数学成绩XXX,英语成绩XXX”
mysql> select concat('考生姓名：', name, ',总分：', chinese+math+english , ',语文成绩：',chinese,',数学成绩',math,',英语成绩', english) msg from exam_result;
+----------------------------------------------------------------------------------------+
| msg |
+----------------------------------------------------------------------------------------+
| 考生姓名：唐三藏,总分：288,语文成绩：134,数学成绩98,英语成绩56 |
| 考生姓名：猪悟能,总分：364,语文成绩：176,数学成绩98,英语成绩90 |
| 考生姓名：曹孟德,总分：297,语文成绩：140,数学成绩90,英语成绩67 |
| 考生姓名：孙权,总分：291,语文成绩：140,数学成绩73,英语成绩78 |
| 考生姓名：宋公明,总分：275,语文成绩：150,数学成绩95,英语成绩30 |
+----------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
- 求学生表中学生姓名占用的字节数
mysql> select name, length(name) from exam_result;
+-----------+--------------+
| name | length(name) |
+-----------+--------------+
| 唐三藏 | 9 |
| 猪悟能 | 9 |
| 曹孟德 | 9 |
| 孙权 | 6 |
| 宋公明 | 9 |
+-----------+--------------+
5 rows in set (0.00 sec)
- 将EMP表中所有名字中有S的替换成’上海’
mysql> select ename, replace(ename, 'S', '上海') from emp;
+--------+-------------------------------+
| ename | replace(ename, 'S', '上海') |
+--------+-------------------------------+
| SMITH | 上海MITH |
| ALLEN | ALLEN |
| WARD | WARD |
| JONES | JONE上海 |
| MARTIN | MARTIN |
| BLAKE | BLAKE |
| CLARK | CLARK |
| SCOTT | 上海COTT |
| KING | KING |
| TURNER | TURNER |
| ADAMS | ADAM上海 |
| JAMES | JAME上海 |
| FORD | FORD |
| MILLER | MILLER |
+--------+-------------------------------+
14 rows in set (0.00 sec)
- 截取EMP表中ename字段的第二个到第三个字符
mysql> select substring(ename, 2, 2) ename from emp;
+-------+
| ename |
+-------+
| MI |
| LL |
| AR |
| ON |
| AR |
| LA |
| LA |
| CO |
| IN |
| UR |
| DA |
| AM |
| OR |
| IL |
+-------+
14 rows in set (0.00 sec)
- 以首字母小写的方式显示所有员工的姓名
mysql> select ename, concat(lcase(substring(ename, 1, 1)),substring(ename,2)) from emp;
+--------+----------------------------------------------------------+
| ename | concat(lcase(substring(ename, 1, 1)),substring(ename,2)) |
+--------+----------------------------------------------------------+
| SMITH | sMITH |
| ALLEN | aLLEN |
| WARD | wARD |
| JONES | jONES |
| MARTIN | mARTIN |
| BLAKE | bLAKE |
| CLARK | cLARK |
| SCOTT | sCOTT |
| KING | kING |
| TURNER | tURNER |
| ADAMS | aDAMS |
| JAMES | jAMES |
| FORD | fORD |
| MILLER | mILLER |
+--------+----------------------------------------------------------+
14 rows in set (0.00 sec)
数学函数
函数介绍
| 函数 | 描述 |
|---|---|
| abs(number) | 绝对值函数 |
| bin(decimal_number) | 十进制转换二进制 |
| hex(decimalNumber) | 转换成十六进制 |
| conv(number, from_base, to_base) | 进制转换 |
| ceiling(number) | 向上取整 |
| floor(number) | 向下取整 |
| format(number, decimal_places) | 格式化，保留小数位 |
| rand() | 返回随机浮点数，范围[0.0, 1.0) |
| mod（number, denominator） | 取模，求余 |
函数使用
绝对值
mysql> select abs(-10);
+----------+
| abs(-10) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
mysql> select abs(10);
+---------+
| abs(10) |
+---------+
| 10 |
+---------+
1 row in set (0.00 sec)
mysql> select abs(-10.23);
+-------------+
| abs(-10.23) |
+-------------+
| 10.23 |
+-------------+
1 row in set (0.00 sec)
十进制转换二进制
mysql> select bin(5);
+--------+
| bin(5) |
+--------+
| 101 |
+--------+
1 row in set (0.00 sec)
十进制转换十六进制
mysql> select hex(16);
+---------+
| hex(16) |
+---------+
| 10 |
+---------+
1 row in set (0.00 sec)
进制转换
mysql> select conv(20, 10 ,16);
+------------------+
| conv(20, 10 ,16) |
+------------------+
| 14 |
+------------------+
1 row in set (0.00 sec)
向上取整
mysql> select ceiling(2.001);
+----------------+
| ceiling(2.001) |
+----------------+
| 3 |
+----------------+
1 row in set (0.00 sec)
mysql> select ceiling(-2.99);
+----------------+
| ceiling(-2.99) |
+----------------+
| -2 |
+----------------+
1 row in set (0.00 sec)
向下取整
mysql> select floor(-2.55);
+--------------+
| floor(-2.55) |
+--------------+
| -3 |
+--------------+
1 row in set (0.00 sec)
mysql> select floor(2.55);
+-------------+
| floor(2.55) |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
格式化，保留小数位数
会四舍五入
mysql> select format(3.1415926, 3);
+----------------------+
| format(3.1415926, 3) |
+----------------------+
| 3.142 |
+----------------------+
1 row in set (0.00 sec)
生成随机数
mysql> select format(rand()*100, 0);
+-----------------------+
| format(rand()*100, 0) |
+-----------------------+
| 77 |
+-----------------------+
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)
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 user();
+--------+
| user() |
+--------+
| root@ |
+--------+
1 row in set (0.00 sec)
查看所在数据库
mysql> select database();
+------------+
| database() |
+------------+
| scott |
+------------+
1 row in set (0.00 sec)
md5(str)
对一个字符串进行md5摘要，摘要后得到一个32位字符串
mysql> select md5('afe152');
+----------------------------------+
| md5('afe152') |
+----------------------------------+
| cf3c4ad0dc7525587fc64a9f60d6f8e0 |
+----------------------------------+
1 row in set (0.01 sec)
password()
MySQL数据库使用该函数对用户加密
mysql> select password('afe152');
+-------------------------------------------+
| password('afe152') |
+-------------------------------------------+
| *B9CDC4309CB123945F0951B63E13118A9235A4E0 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
ifnull（val1， val2）
如果val1为null，返回val2，否则返回val1的值
mysql> select ifnull(20, 10);
+----------------+
| ifnull(20, 10) |
+----------------+
| 20 |
+----------------+
1 row in set (0.00 sec)
mysql> select ifnull(null, 10);
+------------------+
| ifnull(null, 10) |
+------------------+
| 10 |
+------------------+
1 row in set (0.00 sec)
mysql> select ifnull(20, null);
+------------------+
| ifnull(20, null) |
+------------------+
| 20 |
+------------------+
1 row in set (0.00 sec)
mysql> select ifnull(null, null);
+--------------------+
| ifnull(null, null) |
+--------------------+
| NULL |
+--------------------+
1 row in set (0.00 sec)
⚠️ 写在最后：以上内容是我在学习以后得一些总结和概括，如有错误或者需要补充的地方欢迎各位大佬评论或者私信我交流！！！