您当前的位置:首页 > 计算机 > 软件应用 > 数据库 > MySQL

MySQL从零到有13:系统函数相关

时间:10-08来源:作者:点击数:
城东书院 www.cdsy.xyz

概述

提到MySQL的系统函数,我们前面有使用过聚合函数,其实只是其中一小部分。MySQL提供很多功能强大、方便易用的函数,使用这些函数,可以极大地提高用户对于数据库的管理效率,并更加灵活地满足不同用户的需求。

本文将MySQL的函数分类并汇总,以便提供后面使用中的参考。

MySQL 数值类型函数

数值类型函数 说明
abs 返回绝对值
sqrt 返回二次方根
mod 返回余数
ceil/ceiling 两个函数功能一致,返回不小于参数的最小整数,即向上取整
floor 向下取整,返回值转化为bigint
rand 生成一个0~1之间的随机数,传入整数参数是,用来产生重复序列
round 对所传参数进行四舍五入
sign 返回参数的符号(正、负)
pow/power 两个函数的功能相同,返回所传参数的次方的结果值
sin 求正弦值
asin 求反正弦值,与函数 SIN 互为反函数
cos 求余弦值
acos 求反余弦值,与函数 COS 互为反函数
tan 求正切值
atan 求反正切值,与函数 TAN 互为反函数
cot 求余切值
绝对值:abs

使用ABS(number)的目的是返回 number的绝对值,如果是正值则返回是是它本身,负值则返回相反的正值,0则返回0。

mysql> select abs(-7),abs(8),abs(0);
+---------+--------+--------+
| abs(-7) | abs(8) | abs(0) |
+---------+--------+--------+
|       7 |      8 |      0 |
+---------+--------+--------+
1 row in set
求二次方根(开方):sqrt

使用SQRT(number)函数,返回的是number的开方根。这边需要注意,负数无法开方,所以返回的是null,如下所示。

mysql> select sqrt(-7),sqrt(9),sqrt(39);
+----------+---------+-------------------+
| sqrt(-7) | sqrt(9) | sqrt(39)          |
+----------+---------+-------------------+
| NULL     |       3 | 6.244997998398398 |
+----------+---------+-------------------+
1 row in set
求模(求余数):mod

MOD(number1,number2) 返回 number1 除以number2的余数,包含小数的数值同样有效,如下,9%4.5=0,18.3%9=0.3:

mysql> select mod(100,7),mod(100,10),mod(9,4.5),mod(18.3,9);
+------------+-------------+------------+-------------+
| mod(100,7) | mod(100,10) | mod(9,4.5) | mod(18.3,9) |
+------------+-------------+------------+-------------+
|          2 |           0 | 0          | 0.3         |
+------------+-------------+------------+-------------+
1 row in set
向上取整:ceil/ceiling

使用CEIL(number)和CEILING(number)一个意思,返回大于等于number的最小整数值。

mysql> select ceiling(-7.9),ceil(7.5);
+---------------+-----------+
| ceiling(-7.9) | ceil(7.5) |
+---------------+-----------+
|            -7 |         8 |
+---------------+-----------+
1 row in set

这边需要注意,返回的类型是bigint,做存储或计算的时候需要注意数据类型匹配。

向下取整:floor

与上面ceil正好相反,floor(number) 返回的是小于 number 的最大整数值。

mysql> select floor(-7.9),floor(7.5);
+-------------+------------+
| floor(-7.9) | floor(7.5) |
+-------------+------------+
|          -8 |          7 |
+-------------+------------+
1 row in set
随机数:rand

生成0~1之间的随机数。如果传入整数参数,则会产生重复序列,再次调用还是这个随机数,如下图,第3、5、7个是重复序列。

mysql> select rand(),rand(),rand(2),rand(),rand(2),rand(),rand(2);
+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+
| rand()             | rand()             | rand(2)            | rand()             | rand(2)            | rand()             | rand(2)            |
+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+
| 0.9059044131132815 | 0.9619487030077248 | 0.6555866465490187 | 0.0920303064324244 | 0.6555866465490187 | 0.5743054538725926 | 0.6555866465490187 |
+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+
1 row in set
四舍五入:round

函数round(number1,number2),指的是对给定的值number1进行四舍五入的取值过程,number2是指定保留小数后的位数,为负数时,则是指定保留小数前的位数。

mysql> select round(78.2),round(78.5),round(-78.78,1),round(78.7819,3),round(78.78,-1),round(78.78,-2);
+-------------+-------------+-----------------+------------------+-----------------+-----------------+
| round(78.2) | round(78.5) | round(-78.78,1) | round(78.7819,3) | round(78.78,-1) | round(78.78,-2) |
+-------------+-------------+-----------------+------------------+-----------------+-----------------+
| 78          | 79          | -78.8           | 78.782           | 80              | 100             |
+-------------+-------------+-----------------+------------------+-----------------+-----------------+
1 row in set

round(78.78,-1)按照个位数取整,为80;round(78.78,-2)按照百位数取整,为100。

返回参数符号:sign

这样需要注意,当你的值为负数时,返回的是-1,当你的值为正数时候,返回的是1,当为0时,返回0。

mysql> select sign(-78),sign(0),sign(78);
+-----------+---------+----------+
| sign(-78) | sign(0) | sign(78) |
+-----------+---------+----------+
|        -1 |       0 |        1 |
+-----------+---------+----------+
1 row in set
n次方函数:pow/power

函数pow/power(number1,number2),用于计算 number1 的 number2 次方,number2可以为负数,为负数时,在次方基础上要再取倒数。

mysql> select pow(10,0),pow(10,1),pow(10,2),pow(10,3),power(10,-2);
+-----------+-----------+-----------+-----------+--------------+
| pow(10,0) | pow(10,1) | pow(10,2) | pow(10,3) | power(10,-2) |
+-----------+-----------+-----------+-----------+--------------+
|         1 |        10 |       100 |      1000 |         0.01 |
+-----------+-----------+-----------+-----------+--------------+
1 row in set

如上,pow(10,-2) = 1 / pow(10,2)=0.01;

三角函数:sin、cos等

sin为正弦值,cos为余弦值,这个我们数学学过了,我们也学过sin(x+y)=sin(x)*cos(y)+ cos(x)*sin(y),一起验证下,如下:

mysql> select sin(1),cos(1),sin(2),sin(1)*cos(1) + cos(1)*sin(1),pi();
+--------------------+--------------------+--------------------+-------------------------------+----------+
| sin(1)             | cos(1)             | sin(2)             | sin(1)*cos(1) + cos(1)*sin(1) | pi()     |
+--------------------+--------------------+--------------------+-------------------------------+----------+
| 0.8414709848078965 | 0.5403023058681398 | 0.9092974268256817 |            0.9092974268256818 | 3.141593 |
+--------------------+--------------------+--------------------+-------------------------------+----------+
1 row in set

其他三角函数可以如法炮制测试下,都是我们学过的数学知识。

MySQL 字符类型函数

字符类型函数 说明
length 计算字符串长度函数,返回字符串的字节长度
concat 合并字符串函数,返回结果为连接参数产生的字符串,参数可以使一个或多个
insert 替换字符串函数
lower 将字符串中的字母转换为小写
upper 将字符串中的字母转换为大写
left 从左侧字截取符串,返回字符串左边的n个字符
right 从右侧字截取符串,返回字符串右边的n个字符
trim 删除字符串左右两侧的空格
replace 字符串替换函数,返回替换后的新字符串
substr/substring 截取字符串,返回从指定位置开始的指定长度的字符换
reverse 字符串反转(逆序)函数,返回与原始字符串顺序相反的字符串
字符串长度:length

统计字符串的字节长度,一这边需要注意,单个数字或者字符是一个字节,汉字(使用utf-8编码格式)是三个字节,字符串中的空格也占据一个字节。

mysql> select length('brand and helen'),length('双十一brand折扣'),length('双十一');
+---------------------------+---------------------------+------------------+
| length('brand and helen') | length('双十一brand折扣') | length('双十一') |
+---------------------------+---------------------------+------------------+
|                        15 |                        20 |                9 |
+---------------------------+---------------------------+------------------+
1 row in set
合并字符串:concat

CONCAT(str1,str1,…) 函数,参数1个或者n个,返回值会将参数合并的结果返回回来,这边需要注意的是,如果有一个值是null的,正整个结果都是null值。

mysql> select concat('brand',' and ','helen'),concat('brand',null,'helen');
+---------------------------------+------------------------------+
| concat('brand',' and ','helen') | concat('brand',null,'helen') |
+---------------------------------+------------------------------+
| brand and helen                 | NULL                         |
+---------------------------------+------------------------------+
1 row in set
替换字符串:insert

INSERT(str1,index,len,str2) 指str1字符串的index位置开始的len长度的字符用str2来替换。

mysql> select insert('Brand is a handsome boy!',7,2,'**');
+---------------------------------------------+
| insert('Brand is a handsome boy!',7,2,'**') |
+---------------------------------------------+
| Brand ** a handsome boy!                    |
+---------------------------------------------+
1 row in set

mysql> select insert('Brand is a handsome boy!',100,2,'**');
+-----------------------------------------------+
| insert('Brand is a handsome boy!',100,2,'**') |
+-----------------------------------------------+
| Brand is a handsome boy!                      |
+-----------------------------------------------+
1 row in set

mysql> select insert('Brand is a handsome boy!',7,100,'**');
+-----------------------------------------------+
| insert('Brand is a handsome boy!',7,100,'**') |
+-----------------------------------------------+
| Brand **                                      |
+-----------------------------------------------+
1 row in set

mysql> select insert('Brand is a handsome boy!',7,100,null);
+-----------------------------------------------+
| insert('Brand is a handsome boy!',7,100,null) |
+-----------------------------------------------+
| NULL                                          |
+-----------------------------------------------+
1 row in set

从上面的语句可以总结以下几点:

1、index指的并非是索引位置而是实际位置,他是从1开始计算的,所以是字符串的索引+1,如第一个。

2、如果index超过字符串最大位置,则返回原值,如第二个。

3、如果len超过字符串长度,是允许的,并且index之后的内容都会被替换。

4、四个参数中只要有一个值为null,系统认为函数调用有问题,同样返回null给你。

大小写转换:upper/lower

upper指的是把字符串转换成大写,lower指的是把字符串转为小写。

mysql> select upper('BRand'),upper('brand'),lower('BRAND'),lower('BRand');
+----------------+----------------+----------------+----------------+
| upper('BRand') | upper('brand') | lower('BRAND') | lower('BRand') |
+----------------+----------------+----------------+----------------+
| BRAND          | BRAND          | brand          | brand          |
+----------------+----------------+----------------+----------------+
1 row in set
左右字符串截取:left、right

LEFT(str,num)、RIGHT(str,num) 函数返回字符串 str 最左边或者最右边的 num 个字符,num小于等于0的时候返回空。

mysql> select left('brand',2),left('brand',100),right('brand',2),left('brand',0),left('brand',-1);
+-----------------+-------------------+------------------+-----------------+------------------+
| left('brand',2) | left('brand',100) | right('brand',2) | left('brand',0) | left('brand',-1) |
+-----------------+-------------------+------------------+-----------------+------------------+
| br              | brand             | nd               |                 |                  |
+-----------------+-------------------+------------------+-----------------+------------------+
1 row in set
字符串移除空格:trim、ltrim、rtrim

TRIM(str):删除str左右空格;LTRIM(str):只删除字符串左边的空格;RTRIM(Str):删除字符串右边的空格。下面的例子简单明了:

mysql> select concat('|',trim(' brand '),'|'),concat('|',ltrim(' brand '),'|'),concat('|',rtrim(' brand '),'|');
+---------------------------------+----------------------------------+----------------------------------+
| concat('|',trim(' brand '),'|') | concat('|',ltrim(' brand '),'|') | concat('|',rtrim(' brand '),'|') |
+---------------------------------+----------------------------------+----------------------------------+
| |brand|                         | |brand |                         | | brand|                         |
+---------------------------------+----------------------------------+----------------------------------+
1 row in set
字符串替换:replace

REPLACE(str,a1,a2) ,对于字符串 str ,出现的所有a1都使用a2来替换。

mysql> select replace('brand is a handsome boy!','and','***');
+-------------------------------------------------+
| replace('brand is a handsome boy!','and','***') |
+-------------------------------------------------+
| br*** is a h***some boy!                        |
+-------------------------------------------------+
1 row in set
字符串截断:substr/substring

一种方式是:substr(str1,index,len),截取字符串str1从位置 index 开始的len长度的子字符串。

mysql> select substr('Brand',3);
+-------------------+
| substr('Brand',3) |
+-------------------+
| and               |
+-------------------+
1 row in set

mysql> select substr('Brand',3,2);
+---------------------+
| substr('Brand',3,2) |
+---------------------+
| an                  |
+---------------------+
1 row in set

mysql> select substr('Brand',-2);
+--------------------+
| substr('Brand',-2) |
+--------------------+
| nd                 |
+--------------------+
1 row in set

mysql> select substr('Brand',-4,2);
+----------------------+
| substr('Brand',-4,2) |
+----------------------+
| ra                   |
+----------------------+
1 row in set

从上面的4个语句可以总结以下几点:

1、index指的并非是索引位置而是实际位置,他是从1开始计算的,所以是字符串的索引+1,如第一个。

2、如果是index是负数,则从右开始算,即倒数,如substr('Brand',-4,2),则从右数第四个字符,即r,然后取之后的2个字符,即ra。

另一种方式是:substr(str from index for len),同理,是截取字符串str从位置 index 开始的len长度的子字符串。

mysql> SELECT substring('helenlyn' FROM 3 FOR 3),substring('helenlyn' FROM -3 FOR 3);
+------------------------------------+-------------------------------------+
| substring('helenlyn' FROM 3 FOR 3) | substring('helenlyn' FROM -3 FOR 3) |
+------------------------------------+-------------------------------------+
| len                                | lyn                                 |
+------------------------------------+-------------------------------------+
1 row in set
字符串反转:reverse

REVERSE(str) 指的是将原字符串 str 直接反序显示,比如abc,反序为cba:

mysql> select REVERSE('Brand');
  
+------------------+
| REVERSE('Brand') |
+------------------+
| dnarB            |
+------------------+
1 row in set

MySQL 日期和时间类型函数

日期类型函数 说明
curdate/current_date 两个函数作用相同,返回当前系统的日期值
curtime/current_time 两个函数作用相同,返回当前系统的时间值
now/sysdate 两个函数作用相同,返回当前系统的日期和时间值
unix_timestamp 获取UNIX时间戳函数,返回一个以 UNIX 时间戳为基础的无符号整数
from_unixtime 将 UNIX 时间戳转换为时间格式,与UNIX_TIMESTAMP互为反函数
month 获取指定日期中的月份
monthname 获取指定日期中的月份英文名称
dayname 获取指定曰期对应的星期几的英文名称
dayofweek 获取指定日期是一周中是第几天,返回值范围是1~7,1=周日
week 获取指定日期是一年中的第几周,返回值的范围是否为 0〜52 或 1〜53
dayofyear 获取指定曰期是一年中的第几天,返回值范围是1~366
dayofmonth 获取指定日期是一个月中是第几天,返回值范围是1~31
year 获取年份,返回值范围是 1970〜2069
time_to_sec 将时间参数转换为秒数
sec_to_time 将秒数转换为时间,与TIME_TO_SEC 互为相反
date_add/adddate 两个函数功能相同,都是向日期添加指定的时间间隔
date_sub/subdate 两个函数功能相同,都是向日期减去指定的时间间隔
addtime 时间加法运算,在原始时间上添加指定的时间
subtime 时间减法运算,在原始时间上减去指定的时间
datediff 获取两个日期之间间隔,返回参数 1 减去参数 2 的值
date_format 格式化指定的日期,根据参数返回指定格式的值
weekday 获取指定日期在一周内的对应的工作日索引
返回系统日期:curdate/current_date

返回当前所在服务器的系统日期,当以字符串方式返回的时候,格式为"YYYY-MM-DD",当以数值方式返回的时候,格式为"YYYYMMDD",如下面+0后得到 20201128:

mysql> select curdate(),curdate()+0,current_date(),current_date()+0;
+------------+-------------+----------------+------------------+
| curdate()  | curdate()+0 | current_date() | current_date()+0 |
+------------+-------------+----------------+------------------+
| 2020-11-28 |    20201128 | 2020-11-28     |         20201128 |
+------------+-------------+----------------+------------------+
1 row in set
返回系统时间:curtime/current_time

返回当前所在服务器的系统时间,当以字符串方式返回的时候,格式为"HH:MM:SS",当以数值方式返回的时候,格式为"HHMMSS",如下面+0后得到 103002:

mysql> select curtime(),curtime()+0,current_time(),current_time()+0;
+-----------+-------------+----------------+------------------+
| curtime() | curtime()+0 | current_time() | current_time()+0 |
+-----------+-------------+----------------+------------------+
| 10:30:02  |      103002 | 10:30:02       |           103002 |
+-----------+-------------+----------------+------------------+
1 row in set
返回系统日期+时间:now/sysdate

同理返回系统日期+时间,格式为"YYYY-MM-DD HH:MM:SS" 或者 "YYYYMMDDHHMMSS",根据不同场景返回对应格式。

mysql> select now(),now()+0,sysdate(),sysdate()+0;
+---------------------+----------------+---------------------+----------------+
| now()               | now()+0        | sysdate()           | sysdate()+0    |
+---------------------+----------------+---------------------+----------------+
| 2020-11-28 10:35:39 | 20201128103539 | 2020-11-28 10:35:39 | 20201128103539 |
+---------------------+----------------+---------------------+----------------+
1 row in set
返回时间戳:unix_timestamp

unix_timestamp(date),里面的date是可选参数,无参的时候等同于获得当前系统时间的时间戳:

mysql> select unix_timestamp(),unix_timestamp(now()),unix_timestamp('2021-05-01 20:20:20');
+------------------+-----------------------+---------------------------------------+
| unix_timestamp() | unix_timestamp(now()) | unix_timestamp('2021-05-01 20:20:20') |
+------------------+-----------------------+---------------------------------------+
|       1606531656 |            1606531656 |                            1619871620 |
+------------------+-----------------------+---------------------------------------+
1 row in set
时间戳转日期:from_unixtime

FROM_UNIXTIME(timestamp[,format])  与上面正好相反,把时间戳数据进行处理,并返回日期时间的格式,

参数timestamp是时间戳,参数format是格式,有%Y %m  %d %H之类分别来代表年月日时分秒等,如下

格式 说明
%M 月名字(January~December)
%W 星期名字(Sunday~Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等)
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun~Sat)
%d 月份中的天数, 数字(00~31)
%e 月份中的天数, 数字(0~31)
%m 月, 数字(01~12)
%c 月, 数字(1~12)
%b 缩写的月份名字(Jan~Dec)
%j 一年中的天数(001~366)
%H 小时(00~23)
%k 小时(0~23)
%h 小时(01~12)
%I 小时(01~12)
%l(小写的L) 小时(1~12)
%i 分钟, 数字(00~59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00~59)
%s 秒(00~59)
%p AM或PM
%W 一个星期中的天数英文名称(Sunday~Saturday)
%w 一个星期中的天数(0=Sunday ~6=Saturday)
%U 星期(0~52), 这里星期天是星期的第一天
%u 星期(0~52), 这里星期一是星期的第一天
%% 输出%
mysql> select from_unixtime(1619871620,'%Y-%m-%d %H:%i:%s'),from_unixtime(1619871620);
+-----------------------------------------------+---------------------------+
| from_unixtime(1619871620,'%Y-%m-%d %H:%i:%s') | from_unixtime(1619871620) |
+-----------------------------------------------+---------------------------+
| 2021-05-01 20:20:20                           | 2021-05-01 20:20:20       |
+-----------------------------------------------+---------------------------+
1 row in set
获取月份:month

MONTH(date) 函数:data为必填参数,返回date对应的月份,范围为 1~12。

mysql> select month(now()),month('2020-05-15');
+--------------+---------------------+
| month(now()) | month('2020-05-15') |
+--------------+---------------------+
|           11 |                   5 |
+--------------+---------------------+
1 row in set
获取月份名称:monthname

MONTHNAME(date) 函数:date为必填参数,返回对应的月份名称。

mysql> select monthname(now()),monthname('2020-05-15');
+------------------+-------------------------+
| monthname(now()) | monthname('2020-05-15') |
+------------------+-------------------------+
| November         | May                     |
+------------------+-------------------------+
1 row in set
周名称/数值:dayname/dayofweek
mysql> select now(),dayname(now()),dayofweek(now());
+---------------------+----------------+------------------+
| now()               | dayname(now()) | dayofweek(now()) |
+---------------------+----------------+------------------+
| 2020-11-28 11:13:03 | Saturday       |                7 |
+---------------------+----------------+------------------+
1 row in set

DAYNAME(date):返回的是指定日期的对应星期名称,比如今天周六就是Saturday.

DAYWEEK(date):返回date对应的数值,这边可以看到周六返回的是7,这个是正确的,因为是从周日开始算的,周日是1,周一是2,...  ,周六是7。如下图:

星期几? 对应数值
星期日 0
星期一 1
星期二 2
星期三 3
星期四 4
星期五 5
星期六 6
星期日 7
获取全年中的第n周:week

WEEK(date[,mode]) 函数:返回给定date 属于一年中的第几周。它包含两个参数:

data是指定时间,在它所在年的第几周。

mode为可选参数,如下面这个表,用于确定周数计算的逻辑。指定本周是从星期一还是星期日开始,返回的周数应在052之间或053之间。 

模式 星期的第一天 范围 星期 1 是第一天
0 Sunday 0-53 一年中多一个星期天
1 Monday 0-53 一年多3天
2 Sunday 1-53 一年中多一个星期天
3 Monday 1-53 一年多3天
4 Sunday 0-53 一年多3天
5 Monday 0-53 一年中多一个星期一
6 Sunday 1-53 一年多3天
7 Monday 1-53 一年中多一个星期一

因为是可选参数,所以如果默认情况下WEEK函数将使用default_week_format系统变量的值。不同人的系统参数配置可能不一样,可以看看自己的配置是什么:这边查出是0,则代表从星期的第一天为sunday。

mysql>  SHOW VARIABLES LIKE 'default_week_format';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| default_week_format | 0     |
+---------------------+-------+
1 row in set

我们做个测试:

mysql> select WEEK('2021-1-1'),WEEK('2021-1-1',0),WEEK('2021-1-1',2);
+------------------+--------------------+--------------------+
| WEEK('2021-1-1') | WEEK('2021-1-1',0) | WEEK('2021-1-1',2) |
+------------------+--------------------+--------------------+
|                0 |                  0 |                 52 |
+------------------+--------------------+--------------------+
1 row in set
年中的日期位置:dayofyear
mysql> select now(),dayofyear(now()),dayofyear('2020-12-31');
+---------------------+------------------+-------------------------+
| now()               | dayofyear(now()) | dayofyear('2020-12-31') |
+---------------------+------------------+-------------------------+
| 2020-11-28 12:27:25 |              333 |                     366 |
+---------------------+------------------+-------------------------+
1 row in set
月中的日位置:dayofmonth
select now(),dayofmonth(now()),dayofmonth('2020-12-31');
+---------------------+-------------------+--------------------------+
| now()               | dayofmonth(now()) | dayofmonth('2020-12-31') |
+---------------------+-------------------+--------------------------+
| 2020-11-28 12:29:03 |                28 |                       31 |
+---------------------+-------------------+--------------------------+
1 row in set
返回年信息:year
mysql> select now(),year(now()),year('2020-08-08');
+---------------------+-------------+--------------------+
| now()               | year(now()) | year('2020-08-08') |
+---------------------+-------------+--------------------+
| 2020-11-28 12:34:31 |        2020 |               2020 |
+---------------------+-------------+--------------------+
1 row in set
时间和秒的互转:time_to_sec/sec_to_time

TIME_TO_SEC(time) 函数将参数 time 转换为秒数的时间值,公式:" h×3600+ m ×60+ s"。

SEC_TO_TIME(seconds) 函数返回将参数 seconds 转换为时、分、秒时间值。

mysql> select now(),time_to_sec(now()),sec_to_time(23*3600 + 59*60 + 59);
+---------------------+--------------------+-----------------------------------+
| now()               | time_to_sec(now()) | sec_to_time(23*3600 + 59*60 + 59) |
+---------------------+--------------------+-----------------------------------+
| 2020-11-28 12:43:02 |              45782 | 23:59:59                          |
+---------------------+--------------------+-----------------------------------+
1 row in set
日期加法:date_add/adddate

日期时间加法函数:DATE_ADD(date,INTERVAL expr type),包含两个参数:

date:参数是日期格式。expr 参数是时间间隔。

type:时间间隔类型,参数如下

type字段的类型
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

测试一下:分别输出间隔一天、一小时、一分钟的时间:

mysql> select now(),date_add(now(),interval 1 day),adddate(now(),interval 1 HOUR),adddate(now(),interval 1 MINUTE);
+---------------------+--------------------------------+--------------------------------+----------------------------------+
| now()               | date_add(now(),interval 1 day) | adddate(now(),interval 1 HOUR) | adddate(now(),interval 1 MINUTE) |
+---------------------+--------------------------------+--------------------------------+----------------------------------+
| 2020-11-28 14:26:24 | 2020-11-29 14:26:24            | 2020-11-28 15:26:24            | 2020-11-28 14:27:24              |
+---------------------+--------------------------------+--------------------------------+----------------------------------+
1 row in set

也可以为负数,负数则为相反的意思:

mysql> select now(),date_add(now(),interval -1 day),adddate(now(),interval -1 HOUR),adddate(now(),interval -1 MINUTE);
+---------------------+---------------------------------+---------------------------------+-----------------------------------+
| now()               | date_add(now(),interval -1 day) | adddate(now(),interval -1 HOUR) | adddate(now(),interval -1 MINUTE) |
+---------------------+---------------------------------+---------------------------------+-----------------------------------+
| 2020-11-28 14:28:34 | 2020-11-27 14:28:34             | 2020-11-28 13:28:34             | 2020-11-28 14:27:34               |
+---------------------+---------------------------------+---------------------------------+-----------------------------------+
1 row in set
日期加法:date_sub/subdate

DATE_SUB(date,INTERVAL expr type),参数与上面日期加法一致,测试一下,分别减去1年、1时、1分:

mysql> select now(),date_sub(now(),interval 1 day),subdate(now(),interval 1 HOUR),subdate(now(),interval 1 MINUTE);
+---------------------+--------------------------------+--------------------------------+----------------------------------+
| now()               | date_sub(now(),interval 1 day) | subdate(now(),interval 1 HOUR) | subdate(now(),interval 1 MINUTE) |
+---------------------+--------------------------------+--------------------------------+----------------------------------+
| 2020-11-28 14:31:49 | 2020-11-27 14:31:49            | 2020-11-28 13:31:49            | 2020-11-28 14:30:49              |
+---------------------+--------------------------------+--------------------------------+----------------------------------+
1 row in set
时间加减法:addtime/subtime

ADDTIME(time,expr)、SUBTIME(time,expr) 函数用于执行时间的加减法运算。

参数time: 是一个时间或日期时间表达式

参数expr: 是一个时间表达式

测试一下:

mysql> select now(),addtime(now(),'1:1:1'),subtime(now(),'1:1:1');
+---------------------+------------------------+------------------------+
| now()               | addtime(now(),'1:1:1') | subtime(now(),'1:1:1') |
+---------------------+------------------------+------------------------+
| 2020-11-28 14:40:53 | 2020-11-28 15:41:54    | 2020-11-28 13:39:52    |
+---------------------+------------------------+------------------------+
1 row in set
日期间隔函数:datediff

获取两个日期的间隔,因为只计算日期部分,所以实际是第一个日期减去第二个日期的差额天数,测试一下:

mysql> select now(),datediff(now(),adddate(now(),interval 15 day)),datediff(now(),subdate(now(),interval 1 month));
+---------------------+------------------------------------------------+-------------------------------------------------+
| now()               | datediff(now(),adddate(now(),interval 15 day)) | datediff(now(),subdate(now(),interval 1 month)) |
+---------------------+------------------------------------------------+-------------------------------------------------+
| 2020-11-28 14:45:49 |                                            -15 |                                              31 |
+---------------------+------------------------------------------------+-------------------------------------------------+
1 row in set
格式化日期:date_format

DATE_FORMAT(date,format) 函数:将我们的日期进行格式化显示。

包含两个参数:

date参数:要进行格式化的日期值

format参数:格式符号,这个可以参考上面那个时间戳格式化的那个表格。

测试一下:

mysql> select DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'),DATE_FORMAT(NOW(),'%d %b %y'),DATE_FORMAT(NOW(),'%d %b %Y %T:%f');
+----------------------------------------+-------------------------------+-------------------------------------+
| DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') | DATE_FORMAT(NOW(),'%d %b %y') | DATE_FORMAT(NOW(),'%d %b %Y %T:%f') |
+----------------------------------------+-------------------------------+-------------------------------------+
| 2020-11-28 14:59:05                    | 28 Nov 20                     | 28 Nov 2020 14:59:05:000000         |
+----------------------------------------+-------------------------------+-------------------------------------+
1 row in set
周的索引:weekday

注意与dayofweek的区别,dayofweek是周天为1,周一到周六为2~7。而WEEKDAY(date) 返回date的周索引(0=周一,1=周二, ……6= 周天)。

今天是周六,测试一下:

mysql> select now(),dayofweek(now()),weekday(now());
+---------------------+------------------+----------------+
| now()               | dayofweek(now()) | weekday(now()) |
+---------------------+------------------+----------------+
| 2020-11-28 15:03:52 |                7 |              5 |
+---------------------+------------------+----------------+
1 row in set

MySQL 聚合函数

这个在分组函数那一章学习过了,大家可以参考下:MySQL从零到有10:分组查询和聚合函数

MySQL 流程控制函数

后续会有专门的章节进行详解。

总结

mysql的系统函数还是比较强大的,一个个验证写了快一天,泪崩,如果能熟练使用到我们开发中会事半功倍。这篇分类清晰,可以当作参考工具使用。

城东书院 www.cdsy.xyz
方便获取更多学习、工作、生活信息请关注本站微信公众号城东书院 微信服务号城东书院 微信订阅号
推荐内容
相关内容
栏目更新
栏目热门
本栏推荐