共計(jì) 4410 個(gè)字符，預(yù)計(jì)需要花費(fèi) 12 分鐘才能閱讀完成。
這篇文章主要講解了“MySQL 系統(tǒng)和自定義函數(shù)、觸發(fā)器的使用方法是什么”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著丸趣 TV 小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“MySQL 系統(tǒng)和自定義函數(shù)、觸發(fā)器的使用方法是什么”吧！
一、系統(tǒng)封裝函數(shù)
MySQL 有很多內(nèi)置的函數(shù)，可以快速解決開(kāi)發(fā)中的一些業(yè)務(wù)需求，大概包括流程控制函數(shù)，數(shù)值型函數(shù)、字符串型函數(shù)、日期時(shí)間函數(shù)、聚合函數(shù)等。以下列出了這些分類(lèi)中常用的函數(shù)。
1、控制流程函數(shù)
case…when
根據(jù)值判斷返回值，類(lèi)比編程中的 IF-ELSE 判斷。
-- DEMO 01
SELECT CASE DATE_FORMAT(NOW(), %Y-%m-%d )
WHEN 2019-12-29 THEN today
WHEN 2019-12-28 THEN yesterday
WHEN 2019-12-30 THEN tommor
ELSE Unknow END;
-- DEMO 02
SELECT (CASE WHEN 1 0 THEN true ELSE false END) AS result;if(expr1,expr2,expr3)
如果表達(dá)式 expr1 是 TRUE，則 IF() 的返回值為 expr2; 否則返回值則為 expr3。
SELECT IF(1 2, 1 2 , 1 2) AS result ;
SELECT IF(1 2, yes , no) AS result ;
SELECT IF(STRCMP( test , test), no , yesifnull(expr1,expr2)
如果表達(dá)式 expr1 不為 NULL，則返回值為 expr1; 否則返回值為 expr2。
SELECT IFNULL(NULL, cicada
SELECT IFNULL(1/1, no2、常用字符串函數(shù)
CHAR_LENGTH()
返回值為字符串的長(zhǎng)度。
SELECT CHAR_LENGTH( c i c ) ;-- 包含空格
SELECT LENGTH( S q l ) ;CONCAT(str1…)
拼接串聯(lián)字符串。
SELECT CONCAT( My , S , ql
SELECT CONCAT( My , NULL, QL -- 包含 Null 則返回 Null
SELECT CONCAT(% , Java , % -- mybatis 中拼接模糊查詢(xún) ELT(N,str1,str2,…)
若 N = 1，則返回值為 str1，若 N = 2，則返回值為 str2，以此類(lèi)推，可以用來(lái)轉(zhuǎn)換返回頁(yè)面的狀態(tài)。
SELECT ELT(1, 提交 , 審核中 , 規(guī)則通過(guò)) ;
SELECT ELT(2, 提交 , 審核中 , 規(guī)則通過(guò)) ;FORMAT(X,D)
格式化數(shù)字類(lèi)型。
SELECT FORMAT(3.1455,2) ; -- 四舍五入保留兩位
SELECT TRUNCATE(3.1455,2) ; -- 直接截取兩位 TRIM(str)
清空字符串空格。
SELECT LTRIM( hel l o ) ;-- 清空左邊
SELECT RTRIM( hel l o ) ;-- 清空右邊
SELECT TRIM( hel l o ) ; -- 清空兩邊
SELECT REPLACE(M y S Q L , ,) ; -- 替換掉全部空格 3、數(shù)值函數(shù)
FLOOR(X)
返回不大于 X 的最大整數(shù)值。
SELECT FLOOR(1.23); -- 1
SELECT FLOOR(-1.23); -- -2MOD(N,M)
模操作。返回 N 被 M 除后的余數(shù)。
SELECT MOD(29,9); -- 2
SELECT 29 MOD 9; -- 2RAND() RAND(N)
返回一個(gè)隨機(jī)浮點(diǎn)值，范圍在 0 到 1 之間。若已指定一個(gè)整數(shù)參數(shù) N，則它被用作種子值，用來(lái)產(chǎn)生重復(fù)序列。
SELECT RAND(); -- 0.923
SELECT RAND(20) = RAND(20) ; -- TRUE4、時(shí)間日期函數(shù)
ADDDATE(date,INTERVAL expr type)
給指定日期，以指定類(lèi)型進(jìn)行運(yùn)算。
SELECT DATE_ADD(2019-12-29 , INTERVAL 3 DAY); -- 2020-01-01CURDATE()
將當(dāng)前日期按照’YYYY-MM-DD’或 YYYYMMDD 格式的值返回，具體格式根據(jù)函數(shù)用在字符串或是數(shù)字語(yǔ)境中而定。
SELECT CURDATE(); -- 2019-12-29 字符串
SELECT CURDATE() + 0; -- 20180725 數(shù)字 DATE(expr)
提取日期或時(shí)間日期表達(dá)式 expr 中的日期部分。
SELECT DATE( 2019-12-31 01:02:03 -- 2019-12-31
SELECT DATE(2019-12-31 01:02:03)+0; -- 20191231DATE_FORMAT(date,format)
根據(jù) format 字符串進(jìn)行 date 值的格式化。
SELECT DATE_FORMAT(NOW(), %Y-%m-%d -- 2019-12-29
SELECT DATE_FORMAT(NOW(), %Y 年 %m 月 %d 日 -- 2019 年 12 月 29 日 5、聚合函數(shù)
AVG([distinct] expr) 求平均值
COUNT({*|[distinct] } expr) 統(tǒng)計(jì)行的數(shù)量
MAX([distinct] expr) 求最大值
MIN([distinct] expr) 求最小值
SUM([distinct] expr) 求累加和 二、自定義函數(shù) 1、概念簡(jiǎn)介
函數(shù)存儲(chǔ)著一系列 sql 語(yǔ)句，調(diào)用函數(shù)就是一次性執(zhí)行這些語(yǔ)句。所以函數(shù)可以降低語(yǔ)句重復(fù)。函數(shù)注重返回值，而觸發(fā)器注重執(zhí)行過(guò)程，所以一些語(yǔ)句無(wú)法執(zhí)行。所以函數(shù)并不是單純的 sql 語(yǔ)句集合。
2、使用方式
create function 函數(shù)名 ([參數(shù)列表]) returns 數(shù)據(jù)類(lèi)型
begin
sql 語(yǔ)句;
return 值;
end;參數(shù)列表的格式是： 變量名 數(shù)據(jù)類(lèi)型。
無(wú)參案例
CREATE FUNCTION mysum1 () RETURNS INT RETURN (2+3)*2;
SELECT mysum1 () ;有參函數(shù)
表結(jié)構(gòu)
CREATE TABLE t01_user ( id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 主鍵 ID ,
user_name varchar(20) DEFAULT NULL COMMENT 用戶(hù)名稱(chēng)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 用戶(hù)表 函數(shù)用法
create function get_name(p_id INT) returns VARCHAR(20)
begin
declare userName varchar(20);
select user_name from t01_user where id=p_id into userName;
return userName;
SELECT get_name(1) ;3、函數(shù)查看
show create function get_name ;4、刪除函數(shù)
drop function get_name ;5、函數(shù)注意事項(xiàng)
函數(shù)是事先經(jīng)過(guò)編譯，才能在服務(wù)器環(huán)境調(diào)用，所以 MySQL 集群環(huán)境需要同步編譯；MySQL 是多線程環(huán)境，所以要保證函數(shù)也是線程安全。
三、觸發(fā)器 1、觸發(fā)器簡(jiǎn)介
觸發(fā)器是特殊的存儲(chǔ)過(guò)程，不同的是存儲(chǔ)過(guò)程要用 CALL 來(lái)調(diào)用，而觸發(fā)器不需要使用 CALL。也不需要手工啟動(dòng)，只要當(dāng)一個(gè)預(yù)定義的事件發(fā)生的時(shí)候，就會(huì)被 MYSQL 自動(dòng)觸發(fā)調(diào)用。
2、創(chuàng)建觸發(fā)器
觸發(fā)器語(yǔ)法
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmttrigger_name：觸發(fā)器命名 ;
trigger_time: 觸發(fā)動(dòng)作的時(shí)間 ;
trigger_event: 激活觸發(fā)器的語(yǔ)句類(lèi)型 ;
tbl_name: 觸發(fā)器作用的表明，非臨時(shí)表 ;
trigger_stmt：觸發(fā)程序執(zhí)行的語(yǔ)句 ;
表數(shù)據(jù)同步
當(dāng)向用戶(hù)表
t01_user 寫(xiě)入數(shù)據(jù)時(shí)，同時(shí)向
t02_back 表寫(xiě)入一份備份數(shù)據(jù)。
-- 用戶(hù)備份表
CREATE TABLE t02_back ( id int(11) NOT NULL PRIMARY KEY COMMENT 主鍵 ID ,
user_name varchar(20) DEFAULT NULL COMMENT 用戶(hù)名稱(chēng)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 用戶(hù)備份
-- 觸發(fā)器程序
DROP TRIGGER IF EXISTS user_back_trigger ;
CREATE TRIGGER user_back_trigger AFTER INSERT ON t01_user FOR EACH ROW
BEGIN
INSERT INTO t02_back (id,user_name)
VALUES (new.id,new.user_name);
END ;
-- 測(cè)試案例
INSERT INTO t01_user (user_name) VALUES (smile),(mysql) ;
SELECT * FROM t02_back ;3、查看觸發(fā)器
查看觸發(fā)器是指數(shù)據(jù)庫(kù)中已存在的觸發(fā)器的定義、狀態(tài)、語(yǔ)法信息等。可以在 TRIGGERS 表中查看觸發(fā)器信息。
SELECT * FROM `information_schema`.`TRIGGERS`
WHERE `TRIGGER_NAME`= user_back_trigger4、刪除觸發(fā)器
DROP TRIGGER 語(yǔ)句可以刪除 MYSQL 中已經(jīng)定義的觸發(fā)器，刪除觸發(fā)器的基本語(yǔ)法。
DROP TRIGGER [schema_name.]trigger_name5、觸發(fā)器注意事項(xiàng)
觸發(fā)事件
對(duì)于相同的表，相同的事件只能創(chuàng)建一個(gè)觸發(fā)器，比如對(duì)表 t01_user 創(chuàng)建兩次 AFTER INSERT 觸發(fā)器，就會(huì)報(bào)錯(cuò)。
執(zhí)行效率
觸發(fā)器可以減少應(yīng)用端和數(shù)據(jù)庫(kù)的通信次數(shù)和業(yè)務(wù)邏輯，但是基于行觸發(fā)的邏輯，如果數(shù)據(jù)集非常大，效率會(huì)降低。
事務(wù)問(wèn)題
觸發(fā)器執(zhí)行和原表的執(zhí)行語(yǔ)句是否在同一個(gè)事務(wù)中，取決于觸發(fā)表的存儲(chǔ)引擎是否支持事務(wù)。
感謝各位的閱讀，以上就是“MySQL 系統(tǒng)和自定義函數(shù)、觸發(fā)器的使用方法是什么”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì) MySQL 系統(tǒng)和自定義函數(shù)、觸發(fā)器的使用方法是什么這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！
