概述
提到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 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
統計字元串的位元組長度,一這邊需要注意,單個數字或者字元是一個位元組,漢字(使用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
傳回目前所在伺服器的系統日期,當以字元串方式傳回的時候,格式為"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之類分别來代表年月日時分秒等,如下
mysql> select from_unixtime(1619871620,'%Y-%m-%d %H:%i:%s'),from_unixtime(1619871620);
+-----------------------------------------------+---------------------------+
| from_unixtime(1619871620,'%Y-%m-%d %H:%i:%s') | from_unixtime(1619871620) |
+-----------------------------------------------+---------------------------+
| 2021-05-01 20:20:20 | 2021-05-01 20:20:20 |
+-----------------------------------------------+---------------------------+
1 row in set
擷取月份:month
MONTH(date) 函數:data為必填參數,傳回date對應的月份,範圍為 1~12。
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。如下圖:
擷取全年中的第n周:week
WEEK(date[,mode]) 函數:傳回給定date 屬于一年中的第幾周。它包含兩個參數:
data是指定時間,在它所在年的第幾周。
mode為可選參數,如下面這個表,用于确定周數計算的邏輯。指定本周是從星期一還是星期日開始,傳回的周數應在0到52之間或0到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:時間間隔類型,參數如下
測試一下:分别輸出間隔一天、一小時、一分鐘的時間:
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的系統函數還是比較強大的,一個個驗證寫了快一天,淚崩,如果能熟練使用到我們開發中會事半功倍。這篇分類清晰,可以當作參考工具使用。
為幫助開發者們提升面試技能、有機會入職BATJ等大廠公司,特别制作了這個專輯——這一次整體放出。
大緻内容包括了: Java 集合、JVM、多線程、并發程式設計、設計模式、Spring全家桶、Java、MyBatis、ZooKeeper、Dubbo、Elasticsearch、Memcached、MongoDB、Redis、MySQL、RabbitMQ、Kafka、Linux、Netty、Tomcat等大廠面試題等、等技術棧!
歡迎大家關注公衆号【Java爛豬皮】,回複【666】,擷取以上最新Java後端架構VIP學習資料以及視訊學習教程,然後一起學習,一文在手,面試我有。
每一個專欄都是大家非常關心,和非常有價值的話題,如果我的文章對你有所幫助,還請幫忙點贊、好評、轉發一下,你的支援會激勵我輸出更高品質的文章,非常感謝!