MySQL中函式的使用
基本概念
字串函式
char_length（）
length（）
concat（）
concat_ws（）
insert（）
upper（）
lower（）
left（）
right（）
lpad（）
rpad（）
ltrim（）
rtrim（）
trim（）
trim（from）
repeat（）
space（）
replace（）
strcmp（）
substring（）
mid（）
locate（）
position（in）
instr（）
reverse（）
elt（）
export_set（）
field（）
find_in_set（）
make_set（）
substring_index（）
load_file（）
條件函式
if（expr，v1，v2）
ifnull（v1，v2）
case
數學函式
abs（）
ceil（）
floor（）
rand（）
sign（）
pi（）
truncate（）
round（）
pow（）
sqrt（）
exp（）
mod（）
log（）
log10（）
radians（）
degrees（）
sin（）
asin（）
cos（）
acos（）
tan（）
atan（）
cot（）
日期時間函式
curdate（）
curtime（）
now（）
unix_timestamp（）
from_unixtime（）
utc_date（）
utc_time（）
month（）
monthname（）
dayname（）
dayofweek（）
weekday（）
week（）
dayofyear（）
dayofmonth（）
quarter（）
hour（）
minute（）
second（）
extract（）
time_to_sec（）
sec_to_time（）
to_days（）
from_days（）
datediff（）
adddate（）
date_add（）
subdate（）
addtime（）
subtime（）
date_format（）
time_format（）
get_format（）
系統資訊函式
version（）
connection_id（）
database（）
user（）
charset（）
collation（）
last_insert_id（）
加密函式
password（）
md5
擴充套件函式
格式化函式-fomat（x，n）
不同進位制的數字之間進行轉換的函式
IP地址與數字相互轉換函式
加鎖函式和解鎖函式
重複執行指定操作的函式
修改指定的字符集函式
轉換資料型別函式
基本概念
MySQL資料庫中包含以下七類函式：
字串函式
條件函式
數學函式
日期時間函式
系統資訊函式
加密函式
擴充套件函式
字串函式
字串函式用於處理MySQL中的字串資料
char_length（）
返回字串中字元的個數
select char_length（‘Chova’）
5
length（）
返回字串的長度
select length（‘我是Chova’）
—— 中文字元長度為2，英文字元長度為1 ——9
concat（）
將多個字串合併為一個字串
select concat（‘Chova’，‘Vea’）
ChovaVea
concat_ws（）
將多個字串合併為一個字串，並以指定的字串連線
select concat_ws（‘-’，‘Chova’，‘Vea’）
Chova-Vea
insert（）
將字串的指定位置開始的指定長度的位置替換為給定的字串
select insert（‘chova’，1，1，‘C’）
Chova
upper（）
將字串中的所有字元變為大寫
select upper（‘Chova’）
CHOVA
lower（）
將字串中的所有字元變為小寫字母
select lower（‘Chova’）
chova
left（）
獲取字串前面的指定個數的字元
select left（‘Chova’，1）
C
right（）
獲取字串後面的指定個數的字元
select right（‘Chova’，2）
va
lpad（）
使得字串達到指定的長度在字串前面填充進指定的字串
select lpad（‘Vea’，8，‘Chova’）
ChovaVea
rpad（）
使得字串達到指定的長度在字串的後面填充進指定的字串
select rpad（‘Chova’，8，‘Vea’）
ChovaVea
ltrim（）
去掉字串前面的空格
rtrim（）
去掉字串後面的空格
trim（）
去掉字串前面和後面的空格
trim（from）
去掉前面和後面的指定字串從指定的字串中
select trim（‘Vea’ from ‘ChovaVea’）
Chova
repeat（）
將指定的字串重複指定的次數
select repeat（‘Vea’，3）
VeaVeaVea
space（）
獲取指定個數的空格
replace（）
替換指定字串中的指定字串為指定的字串
select replace（‘ChovaV’，‘V’，‘Vea’）
ChovaVea
strcmp（）
比較兩個字串
substring（）
獲取指定字串中指定位置開始的指定長度的字串
mid（）
獲取指定字串中指定位置開始的指定長度的字串
locate（）
獲取指定字串在指定字串中的位置
select locate（‘V’，‘ChovaVea’）
6
position（in）
獲取指定字串在指定字串中的位置
select position（‘V’ in ‘ChovaVea’）
6
instr（）
獲取指定字串中的指定字串開始的位置
select instr（‘Chova’，‘v’）
4
reverse（）
獲取指定字串的反轉字串
select reverse（‘aev’）
vea
elt（）
獲取指定位置的字串
select elt（2，‘Chova’，‘Vea’）
Vea
export_set（）
export_set（bits， on， off， bits， on， off， separator， number of bits， number of bits）： 返回一個指定處理方式的字串
bits： 將第一個引數的值轉換為二進位制的值。然後從右向左檢查每個位上的值是1還是0
on： 如果檢查位數是1， 則返回這裡on指定的字串
off： 如果檢查位數是0， 則返回這裡off指定的字串
separator： 指定返回的字串的分隔符
number of bits： 檢查位數的長度。即第一個引數轉換為二進位制的長度，超過位數在前面用0補全
select export_set（5，‘Y’，‘N’，‘，’6）
—— 5的二進位制： 101 —— 檢查的位數為6， 二進位制數表示為： 000101——‘Y’，‘N’，‘Y’，‘N’，‘N’，‘N’
field（）
返回指定的字串在後面的字串中匹配的位置
select field（‘Vea’，‘Chova’，‘Vea’）
2
find_in_set（）
find_in_set（’’，’’）： 返回指定字串在指定字串中匹配的位置
make_set（）
MAKE_SET（bits，str1，str2，…）： 返回一個指定處理方式獲取的字串
bits： 將第一個引數的值轉換為二進位制，可能包含邏輯運算。然後從右向左檢查每個位上的值是1還是0
str： 每檢查一個位後則順延到下一的字串
select make_set（1|4，‘Chova’，‘Vea’）
Chova
substring_index（）
substring_index（‘s’，‘x’，count）： 返回從指定字串中按照指定分隔符分隔的指定位置分隔的字串
count大於0， 返回指定位置分隔符左邊的字串
count小於0， 返回指定位置分隔符右邊的字串
select substring_index（‘Chova-Vea’，‘-’， 1）
Chova
load_file（）
讀入檔案並以字串的形式返回檔案內容
檔案必須在伺服器上
必須指定到檔案的完整路徑名
人員必須擁有檔案file許可權
檔案必須所有內容都是可讀取的並且小於max_allowed_packet
如果檔案不存在或者由於上述原因無法讀出則返回null
條件函式
條件函式用於MySQL中的條件判斷
if（expr，v1，v2）
如果expr條件成立，返回結果v1
如果expr條件不成立，返回結果v2
select if（1>0，‘Y’，‘N’）
Y
ifnull（v1，v2）
如果v1的值不為null， 則返回v1
如果v1的值為null， 則返回v2
select ifnull（null，‘Chova’）
Chova
case
case表示函式的開始 ，end表示函式的結束
如果e1成立，則返回v1
如果e2成立，則返回v2
如果全部都不成立，則返回v
當有一個成立之後，後面的語句就不再執行
case
when e1 then v1 when e2 then v2 。。。
else vend
case表示函式的開始 ，end表示函式的結束
如果表示式的值等於e1， 則返回v1
如果表示式的值等於e2， 則返回v2
當有一個成立之後，後面的語句就不再執行
case expr when e1 then v1 when e2 then v2 。。。
else vend
數學函式
數學函式主要用來處理MySQL中的數值處理，包括整數和浮點數等
abs（）
獲取數值的絕對值
select abs（-6）
6
ceil（）
獲取數值的向上取整
select ceil（5。6）
6
floor（）
獲取數值的向下取證
select floor（6。6）
6
rand（）
獲取一個0和1之間的隨機數
rand（x）： 獲取一個0和1之間的隨機數，同一個模組下 ，x值相同時獲取到的隨機數相同
select rand（）
0。93099315644334
select rand（6）select rand（6）
0。7137596899542470。713759689954247
sign（）
獲取數值的符號
如果數值是負數，則返回**-1**
如果數值是0， 則返回0
如果數值是正數，則返回1
select sign（6）
1
pi（）
獲取一個圓周率的值
select pi（）
3。141593
truncate（）
獲取指定數值保留小數點後面的指定位數。直接獲取，不進行四捨五入操作
select truncate（3。141593，2）
3。14
round（）
獲取數值的整數。按照四捨五入進行獲取
select round（6。36）
6
round（x，n）： 獲取指定數值保留小數點後面的指定位數。按照四捨五入進行獲取
select round（6。686， 2）
6。69
pow（）
獲取指定數值的指定冪次方的值
select pow（2，3）
8
sqrt（）
獲取指定數值的平方根
select sqrt（36）
6
exp（）
獲取e值的指定冪次方的值
select exp（3）
20。085536923188
mod（）
獲取指定的兩個數相除後的餘數
select mod（6，5）
1
log（）
獲取指定數值的自然對數，即以e為底
select log（20。085536923188）
3
log10（）
獲取指定數值以10為底的對數
select log10（100）
2
radians（）
獲取指定角度的弧度
select radians（180）
3。1415926535898
degrees（）
獲取指定弧度的角度
select degrees（3。1415926535898）
180
sin（）
獲取指定弧度的正弦值
select sin（radians（30））
0。5
asin（）
獲取指定弧度的反正弦值
cos（）
獲取指定弧度的餘弦值
acos（）
獲取指定弧度的反餘弦值
tan（）
獲取指定弧度的正切值
atan（）
獲取指定弧度的反正切值
cot（）
獲取指定弧度的餘切值
日期時間函式
日期時間函式主要用於處理MySQL中的日期時間
curdate（）
獲取當前的日期
select curdate（）
2021-04-02
curtime（）
獲取當前的時間
select curtime（）
16：16：16
now（）
獲取當前的日期和時間
select now（）
2021-04-02 16：16：16
unix_timestamp（）
獲取當前時間的時間戳格式
select unix_timestamp（）
1617351376
unix_timestamp（d）： 獲取指定日期時間的時間戳格式
select unix_timestamp（‘2021-04-02 16：16：16’）
1617351376
from_unixtime（）
獲取指定時間戳的日期時間格式
select from_unixtime（1617351376）
2021-04-02 16：16：16
utc_date（）
獲取UTC日期
select utc_date（）
2021-04-02
utc_time（）
獲取UTC時間
select utc_time（）
08：16：16
month（）
獲取指定日期時間所屬的月份，值在1和12之間
select month（‘2021-04-02 16：16：16’）
4
monthname（）
獲取指定日期時間所屬的月份名稱
select monthname（‘2021-04-02 16：16：16’）
April
dayname（）
獲取指定日期時間所屬的星期的名稱
select dayname（‘2021-04-02 16：16：16’）
Friday
dayofweek（）
獲取指定日期時間是一週中的第幾天 。1和7表示星期日到星期六
select dayofweek（‘2021-04-02 16：16：16’）
6
weekday（）
獲取指定日期時間是一週中的第幾天 。0和6表示星期一到星期日
select weekday（‘2021-04-02 16：16：16’）
4
week（）
獲取指定日期時間是當年的第幾個星期。值在0和53之間
select week（‘2021-04-02 16：16：16’）
13
dayofyear（）
獲取指定日期時間是當年的第幾天
select dayofyear（‘2021-04-02 16：16：16’）
92
dayofmonth（）
獲取指定日期時間是當月的第幾天
select dayofmonth（‘2021-04-02 16：16：16’）
2
quarter（）
獲取指定日期時間是當年的第幾個季度
select quarter（‘2021-04-02 16：16：16’）
2
hour（）
獲取指定日期或時間中的小時值
select hour（‘16：16：16’）
16
minute（）
獲取指定日期或時間中的分鐘值
select minute（‘16：16：16’）
16
second（）
獲取指定日期或之間中的秒鐘值
select second（‘16：16：16’）
16
extract（）
extract（type from d）： 獲取指定型別在指定日期時間中的值
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
type的值：
time_to_sec（）
獲取指定時間的秒值
select time_to_sec（‘16：16：16’）
58576
sec_to_time（）
獲取指定的秒值轉換的時間的值
select sec_to_time（58576）
16：16：16
to_days（）
獲取距離0000年01月01日的天數
select to_days（‘2021-04-02 16：16：16’）
738247
from_days（）
獲取距離0000年01月01日後指定天數的日期
select from_days（738247）
2021-04-02
datediff（）
獲取指定的兩個日期之間相隔的天數。 前面的日期減去後面日期的天數
select datediff（‘2021-05-02’， ‘2021-04-02’）
30
adddate（）
獲取指定日期時間之後指定天數的日期時間
select adddate（‘2021-04-02’， 30）
2021-05-02
adddate（d， interval expr 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
type的值：
date_add（）
date_add（d， interval expr 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
type的值：
subdate（）
獲取指定日期時間之前指定天數的時間
select subdate（‘2021-05-02’，30）
2021-04-02
subdate（d， interval expr 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
type的值：
addtime（）
獲取指定日期時間之後指定秒數的日期時間
select addtime（‘2021-04-02 16：16：16’，20）
2021-04-02 16：16：36
subtime（）
獲取指定日期時間之前指定秒數的日期時間
select subtime（‘2021-04-02 16：16：16’，10）
2021-04-02 16：16：06
date_format（）
獲取指定日期時間的指定格式輸出的日期時間
select date_format（‘2021-04-02 16：16：16’，‘%Y-%m-%d %r’）
2021-04-02 04：16：16 PM
time_format（）
獲取指定時間的指定格式輸出的時間
select time_format（‘06：16：16’，‘%r’）
06：16：16 AM
get_format（）
get_format（type，s）： 獲取指定時間在指定國家地區的輸出格式
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
type的值：
select get_format（date， ‘usa’）
%m。%d。%Y
系統資訊函式
系統資訊函式用來查詢MySQL資料庫相關的系統資訊
version（）
獲取資料庫的版本號
connection_id（）
返回伺服器的連線數
database（）
database（）
schema
獲取當前的資料庫名稱
user（）
user（）
system_user（）
session_user（）
current_user（）
current_user
獲取資料庫的當前使用者
charset（）
獲取指定字串的字符集
collation（）
獲取指定字串的字元排列方式
last_insert_id（）
獲取最新生成的AUTO_INCREMENT的值
加密函式
加密函式用來對MySQL中的資料進行加密
password（）
獲取指定字串的加密後的值
select password（‘Chova’）
*8C4103E52796CCF4A350218583E396B47CB15311
md5
md5（str）： 可以對字串進行雜湊，可以用於對一些不需要進行解密的資料進行加密
select md5（‘Chova’）
8c94d8f085f29ee2785e796e60e13c07
擴充套件函式
格式化函式-fomat（x，n）
獲取指定數字的小數點後面的指定位數
select format（3。141592643，5）
3。14159
不同進位制的數字之間進行轉換的函式
ASCII（s）： 返回字串第一個字元的ASCII碼值
BIN（x）： 返回x的二進位制編碼
HEX（x）： 返回x的十六進位制編碼
OCT（x）： 返回x的八進位制編碼
CONV（x，f1，f2）： 返回f1進位制數變為f2進位制數
IP地址與數字相互轉換函式
inet_aton（ip）： 獲取指定IP字串的數值表示
select inet_aton（‘192。168。0。1’）
3232235521
inet_ntoa（n）： 獲取指定數值的IP字串表示
select inet_ntoa（3232235521）
192。168。0。1
加鎖函式和解鎖函式
get_lock（name，time）： 定義一個名稱為name， 持續時間長度為time的鎖
如果鎖定成功，則返回1
如果嘗試超時，則返回0
如果發生錯誤，則返回null
select get_lock（‘mysql’，10）
1
release_lock（name）： 釋放函式名為name的鎖
如果釋放成功，則返回1
如果嘗試超時，則返回0
如果發生錯誤，則返回null
select release_lock（‘mysql’）
1
is_free_lock（name）： 判斷是否已經使用過名稱為name的鎖
如果已經使用過，則返回1
如果未使用過，則返回0
select is_free_lock（‘mysql’）
0
重複執行指定操作的函式
benchmark（count，expr）： 將指定的表示式expr操作重複執行count次，然後返回執行時間。 可以用來判斷MySQL處理表達式的速度
select benchmark（10000，now（））
0
修改指定的字符集函式
convert（s using charset）： 將指定字串s的字符集變為charset字元
select charset（convert（‘Chova’ using utf-8））
utf-8
轉換資料型別函式
cast（x as type）
convert（x as type）
binary
char
date
datetime
time
signed integer
unsigned integer
轉換資料型別只對以下資料型別生效：
select cast（‘565’ as unsigned integer） + 1
566
—— MySQL可以預設將字串型別轉換為數字型別 ——select ‘565’+1
566
select convert（now（），date）
2021-04-02