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

stephenwoo

发布于 2022-7-21 16:42

832浏览

0收藏

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

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

MySQL 数值类型函数
MySQL全面瓦解：系统函数相关-鸿蒙开发者社区
绝对值：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 set1.
2.
3.
4.
5.
6.
7.

求二次方根(开方)：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
 1.
2.
3.
4.
5.
6.
7.
8.

求模(求余数)：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 set1.
2.
3.
4.
5.
6.
7.

向上取整：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 1.
2.
3.
4.
5.
6.
7.

这边需要注意，返回的类型是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 set1.
2.
3.
4.
5.
6.
7.

随机数：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 set1.
2.
3.
4.
5.
6.
7.

四舍五入：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 1.
2.
3.
4.
5.
6.
7.

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 set1.
2.
3.
4.
5.
6.
7.

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 1.
2.
3.
4.
5.
6.
7.

如上，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 set1.
2.
3.
4.
5.
6.
7.

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

MySQL 字符类型函数

MySQL全面瓦解：系统函数相关-鸿蒙开发者社区

字符串长度：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 set1.
2.
3.
4.
5.
6.
7.

合并字符串：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 set1.
2.
3.
4.
5.
6.
7.

替换字符串：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.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.

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

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 set1.
2.
3.
4.
5.
6.
7.

左右字符串截取：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 1.
2.
3.
4.
5.
6.
7.

字符串移除空格：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 set1.
2.
3.
4.
5.
6.
7.

字符串替换：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 set1.
2.
3.
4.
5.
6.
7.

字符串截断：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
 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.

从上面的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 set1.
2.
3.
4.
5.
6.
7.

字符串反转：reverse
REVERSE(str) 指的是将原字符串 str 直接反序显示，比如abc，反序为cba：

mysql> select REVERSE('Brand');
+------------------+
| REVERSE('Brand') |
+------------------+
| dnarB            |
+------------------+
1 row in set1.
2.
3.
4.
5.
6.
7.

MySQL 日期和时间类型函数
MySQL全面瓦解：系统函数相关-鸿蒙开发者社区

MySQL全面瓦解：系统函数相关-鸿蒙开发者社区
返回系统日期：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 set1.
2.
3.
4.
5.
6.
7.

返回系统时间：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 set1.
2.
3.
4.
5.
6.
7.

返回系统日期+时间：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 set1.
2.
3.
4.
5.
6.
7.

返回时间戳：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 set1.
2.
3.
4.
5.
6.
7.

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

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

MySQL全面瓦解：系统函数相关-鸿蒙开发者社区

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 set1.
2.
3.
4.
5.
6.
7.

获取月份：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 set1.
2.
3.
4.
5.
6.
7.

获取月份名称：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 set1.
2.
3.
4.
5.
6.
7.

周名称/数值：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 1.
2.
3.
4.
5.
6.
7.

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

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

MySQL全面瓦解：系统函数相关-鸿蒙开发者社区

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

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

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

MySQL全面瓦解：系统函数相关-鸿蒙开发者社区

因为是可选参数，所以如果默认情况下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 set1.
2.
3.
4.
5.
6.
7.

我们做个测试：

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 set1.
2.
3.
4.
5.
6.
7.

年中的日期位置：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 set1.
2.
3.
4.
5.
6.
7.

月中的日位置：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 set1.
2.
3.
4.
5.
6.
7.

返回年信息：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 set1.
2.
3.
4.
5.
6.
7.

时间和秒的互转：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 set1.
2.
3.
4.
5.
6.
7.

日期加法：date_add/adddate
日期时间加法函数：DATE_ADD(date,INTERVAL expr type)，包含两个参数：

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

type：时间间隔类型，参数如下

MySQL全面瓦解：系统函数相关-鸿蒙开发者社区

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

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 set1.
2.
3.
4.
5.
6.
7.

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

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 set1.
2.
3.
4.
5.
6.
7.

日期加法：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 set1.
2.
3.
4.
5.
6.
7.

时间加减法：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 set1.
2.
3.
4.
5.
6.
7.

日期间隔函数：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 set1.
2.
3.
4.
5.
6.
7.

格式化日期：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 set1.
2.
3.
4.
5.
6.
7.

周的索引：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 set1.
2.
3.
4.
5.
6.
7.

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

MySQL 流程控制函数
后续会有专门的章节进行详解。

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

文章转载自架构与思维

分类

标签

已于2022-7-21 16:42:59修改

51CTO

51CTO博客

51CTO学堂

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

订阅鸿蒙技术特刊，精选内容抢先看