MySQL全面瓦解13：系统函数相关

最新推荐文章于 2024-06-05 17:05:41 发布

smart哥

最新推荐文章于 2024-06-05 17:05:41 发布

阅读量6

点赞数 26

分类专栏： mysql专题文章标签： mysql 数据库

本文链接：https://blog.csdn.net/smart_an/article/details/138395608

版权

mysql专题专栏收录该内容

29 篇文章 0 订阅

订阅专栏

作者简介：大家好，我是smart哥，前中兴通讯、美团架构师，现某互联网公司CTO

联系qq：184480602，加我进群，大家一起学习，一起进步，一起对抗互联网寒冬

学习必须往深处挖，挖的越深，基础越扎实！

阶段1、深入多线程

 阶段2、深入多线程设计模式

 阶段3、深入juc源码解析

阶段4、深入jdk其余源码解析

阶段5、深入jvm源码解析

码哥源码部分

码哥讲源码-原理源码篇【2024年最新大厂关于线程池使用的场景题】

码哥讲源码【炸雷啦！炸雷啦！黄光头他终于跑路啦！】

码哥讲源码-【jvm课程前置知识及c/c++调试环境搭建】

码哥讲源码-原理源码篇【揭秘join方法的唤醒本质上决定于jvm的底层析构函数】

码哥源码-原理源码篇【Doug Lea为什么要将成员变量赋值给局部变量后再操作？】

码哥讲源码【你水不是你的错,但是你胡说八道就是你不对了！】

码哥讲源码【谁再说Spring不支持多线程事务，你给我抽他！】

终结B站没人能讲清楚红黑树的历史，不服等你来踢馆！

打脸系列【020-3小时讲解MESI协议和volatile之间的关系，那些将x86下的验证结果当作最终结果的水货们请闭嘴】

概述

提到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。

    1 mysql> select abs(-7),abs(8),abs(0);
    2 +---------+--------+--------+
    3 | abs(-7) | abs(8) | abs(0) |
    4 +---------+--------+--------+
    5 |       7 |      8 |      0 |
    6 +---------+--------+--------+
    7 1 row in set

求二次方根(开方)：sqrt

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

    1 mysql> select sqrt(-7),sqrt(9),sqrt(39);
    2 +----------+---------+-------------------+
    3 | sqrt(-7) | sqrt(9) | sqrt(39)          |
    4 +----------+---------+-------------------+
    5 | NULL     |       3 | 6.244997998398398 |
    6 +----------+---------+-------------------+
    7 1 row in set

求模(求余数)：mod

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

    1 mysql> select mod(100,7),mod(100,10),mod(9,4.5),mod(18.3,9);
    2 +------------+-------------+------------+-------------+
    3 | mod(100,7) | mod(100,10) | mod(9,4.5) | mod(18.3,9) |
    4 +------------+-------------+------------+-------------+
    5 |          2 |           0 | 0          | 0.3         |
    6 +------------+-------------+------------+-------------+
    7 1 row in set

向上取整：ceil/ceiling

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

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

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

向下取整：floor

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

    1 mysql> select floor(-7.9),floor(7.5);
    2 +-------------+------------+
    3 | floor(-7.9) | floor(7.5) |
    4 +-------------+------------+
    5 |          -8 |          7 |
    6 +-------------+------------+
    7 1 row in set

随机数：rand

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

    1 mysql> select rand(),rand(),rand(2),rand(),rand(2),rand(),rand(2);
    2 +--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+
    3 | rand()             | rand()             | rand(2)            | rand()             | rand(2)            | rand()             | rand(2)            |
    4 +--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+
    5 | 0.9059044131132815 | 0.9619487030077248 | 0.6555866465490187 | 0.0920303064324244 | 0.6555866465490187 | 0.5743054538725926 | 0.6555866465490187 |
    6 +--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+
    7 1 row in set

四舍五入：round

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

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

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

返回参数符号：sign

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

    1 mysql> select sign(-78),sign(0),sign(78);
    2 +-----------+---------+----------+
    3 | sign(-78) | sign(0) | sign(78) |
    4 +-----------+---------+----------+
    5 |        -1 |       0 |        1 |
    6 +-----------+---------+----------+
    7 1 row in set

n次方函数：pow/power

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

    1 mysql> select pow(10,0),pow(10,1),pow(10,2),pow(10,3),power(10,-2);
    2 +-----------+-----------+-----------+-----------+--------------+
    3 | pow(10,0) | pow(10,1) | pow(10,2) | pow(10,3) | power(10,-2) |
    4 +-----------+-----------+-----------+-----------+--------------+
    5 |         1 |        10 |       100 |      1000 |         0.01 |
    6 +-----------+-----------+-----------+-----------+--------------+
    7 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),一起验证下，如下：

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

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

MySQL 字符类型函数

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

字符串长度：length

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

    1 mysql> select length('brand and helen'),length('双十一brand折扣'),length('双十一');
    2 +---------------------------+---------------------------+------------------+
    3 | length('brand and helen') | length('双十一brand折扣') | length('双十一') |
    4 +---------------------------+---------------------------+------------------+
    5 |                        15 |                        20 |                9 |
    6 +---------------------------+---------------------------+------------------+
    7 1 row in set

合并字符串：concat

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

    1 mysql> select concat('brand',' and ','helen'),concat('brand',null,'helen');
    2 +---------------------------------+------------------------------+
    3 | concat('brand',' and ','helen') | concat('brand',null,'helen') |
    4 +---------------------------------+------------------------------+
    5 | brand and helen                 | NULL                         |
    6 +---------------------------------+------------------------------+
    7 1 row in set

替换字符串：insert

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

     1 mysql> select insert('Brand is a handsome boy!',7,2,'**');
     2 +---------------------------------------------+
     3 | insert('Brand is a handsome boy!',7,2,'**') |
     4 +---------------------------------------------+
     5 | Brand ** a handsome boy!                    |
     6 +---------------------------------------------+
     7 1 row in set
     8 
     9 mysql> select insert('Brand is a handsome boy!',100,2,'**');
    10 +-----------------------------------------------+
    11 | insert('Brand is a handsome boy!',100,2,'**') |
    12 +-----------------------------------------------+
    13 | Brand is a handsome boy!                      |
    14 +-----------------------------------------------+
    15 1 row in set
    16 
    17 mysql> select insert('Brand is a handsome boy!',7,100,'**');
    18 +-----------------------------------------------+
    19 | insert('Brand is a handsome boy!',7,100,'**') |
    20 +-----------------------------------------------+
    21 | Brand **                                      |
    22 +-----------------------------------------------+
    23 1 row in set
    24 
    25 mysql> select insert('Brand is a handsome boy!',7,100,null);
    26 +-----------------------------------------------+
    27 | insert('Brand is a handsome boy!',7,100,null) |
    28 +-----------------------------------------------+
    29 | NULL                                          |
    30 +-----------------------------------------------+
    31 1 row in set

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

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

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

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

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

大小写转换：upper/lower

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

    1 mysql> select upper('BRand'),upper('brand'),lower('BRAND'),lower('BRand');
    2 +----------------+----------------+----------------+----------------+
    3 | upper('BRand') | upper('brand') | lower('BRAND') | lower('BRand') |
    4 +----------------+----------------+----------------+----------------+
    5 | BRAND          | BRAND          | brand          | brand          |
    6 +----------------+----------------+----------------+----------------+
    7 1 row in set

左右字符串截取：left、right

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

    1 mysql> select left('brand',2),left('brand',100),right('brand',2),left('brand',0),left('brand',-1);
    2 +-----------------+-------------------+------------------+-----------------+------------------+
    3 | left('brand',2) | left('brand',100) | right('brand',2) | left('brand',0) | left('brand',-1) |
    4 +-----------------+-------------------+------------------+-----------------+------------------+
    5 | br              | brand             | nd               |                 |                  |
    6 +-----------------+-------------------+------------------+-----------------+------------------+
    7 1 row in set

字符串移除空格：trim、ltrim、rtrim

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

    1 mysql> select concat('|',trim(' brand '),'|'),concat('|',ltrim(' brand '),'|'),concat('|',rtrim(' brand '),'|');
    2 +---------------------------------+----------------------------------+----------------------------------+
    3 | concat('|',trim(' brand '),'|') | concat('|',ltrim(' brand '),'|') | concat('|',rtrim(' brand '),'|') |
    4 +---------------------------------+----------------------------------+----------------------------------+
    5 | |brand|                         | |brand |                         | | brand|                         |
    6 +---------------------------------+----------------------------------+----------------------------------+
    7 1 row in set

字符串替换：replace

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

    1 mysql> select replace('brand is a handsome boy!','and','***');
    2 +-------------------------------------------------+
    3 | replace('brand is a handsome boy!','and','***') |
    4 +-------------------------------------------------+
    5 | br*** is a h***some boy!                        |
    6 +-------------------------------------------------+
    7 1 row in set

字符串截断：substr/substring

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

     1 mysql> select substr('Brand',3);
     2 +-------------------+
     3 | substr('Brand',3) |
     4 +-------------------+
     5 | and               |
     6 +-------------------+
     7 1 row in set
     8 
     9 mysql> select substr('Brand',3,2);
    10 +---------------------+
    11 | substr('Brand',3,2) |
    12 +---------------------+
    13 | an                  |
    14 +---------------------+
    15 1 row in set
    16 
    17 mysql> select substr('Brand',-2);
    18 +--------------------+
    19 | substr('Brand',-2) |
    20 +--------------------+
    21 | nd                 |
    22 +--------------------+
    23 1 row in set
    24 
    25 mysql> select substr('Brand',-4,2);
    26 +----------------------+
    27 | substr('Brand',-4,2) |
    28 +----------------------+
    29 | ra                   |
    30 +----------------------+
    31 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长度的子字符串。

    1 mysql> SELECT substring('helenlyn' FROM 3 FOR 3),substring('helenlyn' FROM -3 FOR 3);
    2 +------------------------------------+-------------------------------------+
    3 | substring('helenlyn' FROM 3 FOR 3) | substring('helenlyn' FROM -3 FOR 3) |
    4 +------------------------------------+-------------------------------------+
    5 | len                                | lyn                                 |
    6 +------------------------------------+-------------------------------------+
    7 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：

    1 mysql> select curdate(),curdate()+0,current_date(),current_date()+0;
    2 +------------+-------------+----------------+------------------+
    3 | curdate()  | curdate()+0 | current_date() | current_date()+0 |
    4 +------------+-------------+----------------+------------------+
    5 | 2020-11-28 |    20201128 | 2020-11-28     |         20201128 |
    6 +------------+-------------+----------------+------------------+
    7 1 row in set

返回系统时间：curtime/current_time

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

    1 mysql> select curtime(),curtime()+0,current_time(),current_time()+0;
    2 +-----------+-------------+----------------+------------------+
    3 | curtime() | curtime()+0 | current_time() | current_time()+0 |
    4 +-----------+-------------+----------------+------------------+
    5 | 10:30:02  |      103002 | 10:30:02       |           103002 |
    6 +-----------+-------------+----------------+------------------+
    7 1 row in set

返回系统日期+时间：now/sysdate

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

    1 mysql> select now(),now()+0,sysdate(),sysdate()+0;
    2 +---------------------+----------------+---------------------+----------------+
    3 | now()               | now()+0        | sysdate()           | sysdate()+0    |
    4 +---------------------+----------------+---------------------+----------------+
    5 | 2020-11-28 10:35:39 | 20201128103539 | 2020-11-28 10:35:39 | 20201128103539 |
    6 +---------------------+----------------+---------------------+----------------+
    7 1 row in set

返回时间戳：unix_timestamp

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

    1 mysql> select unix_timestamp(),unix_timestamp(now()),unix_timestamp('2021-05-01 20:20:20');
    2 +------------------+-----------------------+---------------------------------------+
    3 | unix_timestamp() | unix_timestamp(now()) | unix_timestamp('2021-05-01 20:20:20') |
    4 +------------------+-----------------------+---------------------------------------+
    5 |       1606531656 |            1606531656 |                            1619871620 |
    6 +------------------+-----------------------+---------------------------------------+
    7 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),这里星期一是星期的第一天
%%	输出%

    1 mysql> select from_unixtime(1619871620,'%Y-%m-%d %H:%i:%s'),from_unixtime(1619871620);
    2 +-----------------------------------------------+---------------------------+
    3 | from_unixtime(1619871620,'%Y-%m-%d %H:%i:%s') | from_unixtime(1619871620) |
    4 +-----------------------------------------------+---------------------------+
    5 | 2021-05-01 20:20:20                           | 2021-05-01 20:20:20       |
    6 +-----------------------------------------------+---------------------------+
    7 1 row in set

获取月份：month

MONTH(date) 函数：data为必填参数，返回date对应的月份，范围为 1～12。

    1 mysql> select month(now()),month('2020-05-15');
    2 +--------------+---------------------+
    3 | month(now()) | month('2020-05-15') |
    4 +--------------+---------------------+
    5 |           11 |                   5 |
    6 +--------------+---------------------+
    7 1 row in set

获取月份名称：monthname

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

    1 mysql> select monthname(now()),monthname('2020-05-15');
    2 +------------------+-------------------------+
    3 | monthname(now()) | monthname('2020-05-15') |
    4 +------------------+-------------------------+
    5 | November         | May                     |
    6 +------------------+-------------------------+
    7 1 row in set

周名称/数值：dayname/dayofweek

    1 mysql> select now(),dayname(now()),dayofweek(now());
    2 +---------------------+----------------+------------------+
    3 | now()               | dayname(now()) | dayofweek(now()) |
    4 +---------------------+----------------+------------------+
    5 | 2020-11-28 11:13:03 | Saturday       |                7 |
    6 +---------------------+----------------+------------------+
    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为可选参数，如下面这个表，用于确定周数计算的逻辑。指定本周是从星期一还是星期日开始，返回的周数应在0到52之间或0到53之间。

模式	星期的第一天	范围	星期1是第一天
模式	星期的第一天	范围	星期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。

    
    1 mysql>  SHOW VARIABLES LIKE 'default_week_format';
    2 +---------------------+-------+
    3 | Variable_name       | Value |
    4 +---------------------+-------+
    5 | default_week_format | 0     |
    6 +---------------------+-------+
    7 1 row in set

我们做个测试：

    1 mysql> select WEEK('2021-1-1'),WEEK('2021-1-1',0),WEEK('2021-1-1',2);
    2 +------------------+--------------------+--------------------+
    3 | WEEK('2021-1-1') | WEEK('2021-1-1',0) | WEEK('2021-1-1',2) |
    4 +------------------+--------------------+--------------------+
    5 |                0 |                  0 |                 52 |
    6 +------------------+--------------------+--------------------+
    7 1 row in set

年中的日期位置：dayofyear

    1 mysql> select now(),dayofyear(now()),dayofyear('2020-12-31');
    2 +---------------------+------------------+-------------------------+
    3 | now()               | dayofyear(now()) | dayofyear('2020-12-31') |
    4 +---------------------+------------------+-------------------------+
    5 | 2020-11-28 12:27:25 |              333 |                     366 |
    6 +---------------------+------------------+-------------------------+
    7 1 row in set

月中的日位置：dayofmonth

    1 select now(),dayofmonth(now()),dayofmonth('2020-12-31');
    2 +---------------------+-------------------+--------------------------+
    3 | now()               | dayofmonth(now()) | dayofmonth('2020-12-31') |
    4 +---------------------+-------------------+--------------------------+
    5 | 2020-11-28 12:29:03 |                28 |                       31 |
    6 +---------------------+-------------------+--------------------------+
    7 1 row in set

返回年信息：year

    1 mysql> select now(),year(now()),year('2020-08-08');
    2 +---------------------+-------------+--------------------+
    3 | now()               | year(now()) | year('2020-08-08') |
    4 +---------------------+-------------+--------------------+
    5 | 2020-11-28 12:34:31 |        2020 |               2020 |
    6 +---------------------+-------------+--------------------+
    7 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 转换为时、分、秒时间值。

    1 mysql> select now(),time_to_sec(now()),sec_to_time(23*3600 + 59*60 + 59);
    2 +---------------------+--------------------+-----------------------------------+
    3 | now()               | time_to_sec(now()) | sec_to_time(23*3600 + 59*60 + 59) |
    4 +---------------------+--------------------+-----------------------------------+
    5 | 2020-11-28 12:43:02 |              45782 | 23:59:59                          |
    6 +---------------------+--------------------+-----------------------------------+
    7 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

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

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

也可以为负数，负数则为相反的意思：

    1 mysql> select now(),date_add(now(),interval -1 day),adddate(now(),interval -1 HOUR),adddate(now(),interval -1 MINUTE);
    2 +---------------------+---------------------------------+---------------------------------+-----------------------------------+
    3 | now()               | date_add(now(),interval -1 day) | adddate(now(),interval -1 HOUR) | adddate(now(),interval -1 MINUTE) |
    4 +---------------------+---------------------------------+---------------------------------+-----------------------------------+
    5 | 2020-11-28 14:28:34 | 2020-11-27 14:28:34             | 2020-11-28 13:28:34             | 2020-11-28 14:27:34               |
    6 +---------------------+---------------------------------+---------------------------------+-----------------------------------+
    7 1 row in set

日期加法：date_sub/subdate

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

    1 mysql> select now(),date_sub(now(),interval 1 day),subdate(now(),interval 1 HOUR),subdate(now(),interval 1 MINUTE);
    2 +---------------------+--------------------------------+--------------------------------+----------------------------------+
    3 | now()               | date_sub(now(),interval 1 day) | subdate(now(),interval 1 HOUR) | subdate(now(),interval 1 MINUTE) |
    4 +---------------------+--------------------------------+--------------------------------+----------------------------------+
    5 | 2020-11-28 14:31:49 | 2020-11-27 14:31:49            | 2020-11-28 13:31:49            | 2020-11-28 14:30:49              |
    6 +---------------------+--------------------------------+--------------------------------+----------------------------------+
    7 1 row in set

时间加减法：addtime/subtime

ADDTIME(time,expr)、SUBTIME(time,expr) 函数用于执行时间的加减法运算。
参数time：是一个时间或日期时间表达式
参数expr：是一个时间表达式

测试一下：

    1 mysql> select now(),addtime(now(),'1:1:1'),subtime(now(),'1:1:1');
    2 +---------------------+------------------------+------------------------+
    3 | now()               | addtime(now(),'1:1:1') | subtime(now(),'1:1:1') |
    4 +---------------------+------------------------+------------------------+
    5 | 2020-11-28 14:40:53 | 2020-11-28 15:41:54    | 2020-11-28 13:39:52    |
    6 +---------------------+------------------------+------------------------+
    7 1 row in set

日期间隔函数：datediff

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

    1 mysql> select now(),datediff(now(),adddate(now(),interval 15 day)),datediff(now(),subdate(now(),interval 1 month));
    2 +---------------------+------------------------------------------------+-------------------------------------------------+
    3 | now()               | datediff(now(),adddate(now(),interval 15 day)) | datediff(now(),subdate(now(),interval 1 month)) |
    4 +---------------------+------------------------------------------------+-------------------------------------------------+
    5 | 2020-11-28 14:45:49 |                                            -15 |                                              31 |
    6 +---------------------+------------------------------------------------+-------------------------------------------------+
    7 1 row in set

格式化日期：date_format

DATE_FORMAT(date，format) 函数：将我们的日期进行格式化显示。
包含两个参数：
date参数：要进行格式化的日期值
format参数：格式符号，这个可以参考上面那个时间戳格式化的那个表格。

测试一下：

    1 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');
    2 +----------------------------------------+-------------------------------+-------------------------------------+
    3 | DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') | DATE_FORMAT(NOW(),'%d %b %y') | DATE_FORMAT(NOW(),'%d %b %Y %T:%f') |
    4 +----------------------------------------+-------------------------------+-------------------------------------+
    5 | 2020-11-28 14:59:05                    | 28 Nov 20                     | 28 Nov 2020 14:59:05:000000         |
    6 +----------------------------------------+-------------------------------+-------------------------------------+
    7 1 row in set

周的索引：weekday

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

今天是周六，测试一下：

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

MySQL 聚合函数

这个在分组函数那一章学习过了，大家可以参考下：MySQL全面瓦解10：分组查询和聚合函数

MySQL 流程控制函数

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

总结

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

smart哥

关注

26
点赞
踩
21

收藏

觉得还不错? 一键收藏
0
评论
MySQL全面瓦解13：系统函数相关

提到MySQL的系统函数，我们前面有使用过聚合函数，其实只是其中一小部分。MySQL提供很多功能强大、方便易用的函数，使用这些函数，可以极大地提高用户对于数据库的管理效率，并更加灵活地满足不同用户的需求。本文将MySQL的函数分类并汇总，以便提供后面使用中的参考。mysql的系统函数还是比较强大的，一个个验证写了快一天，泪崩，如果能熟练使用到我们开发中会事半功倍。这篇分类清晰，可以当作参考工具使用。
复制链接

扫一扫