MySQL全面瓦解13：系統函式相關

翁智華發表於2020-11-28

概述

提到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 是第一天
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引數：格式符號，這個可以參考上面那個時間戳格式化的那個表格。