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為可選引數,如下面這個表,用於確定週數計算的邏輯。指定本週是從星期一還是星期日開始,返回的週數應在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。


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的系統函式還是比較強大的,一個個驗證寫了快一天,淚崩,如果能熟練使用到我們開發中會事半功倍。這篇分類清晰,可以當作參考工具使用。

相關文章