【MySQL】MySQL（三）儲存過程和函式、觸發器、事務

gonghr發表於2021-10-17
MySql儲存過程函式觸發器

MySQL儲存過程和函式

儲存過程和函式的概念

  • 儲存過程和函式是 事先經過編譯並儲存在資料庫中的一段 SQL 語句的集合

儲存過程和函式的好處

  • 儲存過程和函式可以重複使用，減輕開發人員的工作量。類似於java中方法可以多次呼叫
  • 減少網路流量，儲存過程和函式位於伺服器上，呼叫的時候只需要傳遞名稱和引數即可
  • 減少資料在資料庫和應用伺服器之間的傳輸，可以提高資料處理的效率
  • 將一些業務邏輯在資料庫層面來實現，可以減少程式碼層面的業務處理

儲存過程和函式的區別

  • 函式必須有返回值
  • 儲存過程沒有返回值

建立儲存過程

  • 小知識
/*
	該關鍵字用來宣告sql語句的分隔符，告訴MySQL該段命令已經結束！
	sql語句預設的分隔符是分號，但是有的時候我們需要一條功能sql語句中包含分號，但是並不作為結束標識。
	這個時候就可以使用DELIMITER來指定分隔符了！
*/
-- 標準語法
DELIMITER 分隔符
  • 資料準備
-- 建立db8資料庫
CREATE DATABASE db8;

-- 使用db8資料庫
USE db8;

-- 建立學生表
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 學生id
	NAME VARCHAR(20),					-- 學生姓名
	age INT,							-- 學生年齡
	gender VARCHAR(5),					-- 學生性別
	score INT                           -- 學生成績
);
-- 新增資料
INSERT INTO student VALUES (NULL,'張三',23,'男',95),(NULL,'李四',24,'男',98),
(NULL,'王五',25,'女',100),(NULL,'趙六',26,'女',90);

-- 按照性別進行分組，查詢每組學生的總成績。按照總成績的升序排序
SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
  • 建立儲存過程語法
-- 修改分隔符為$
DELIMITER $

-- 標準語法
CREATE PROCEDURE 儲存過程名稱(引數...)
BEGIN
	sql語句;
END$

-- 修改分隔符為分號
DELIMITER ;
  • 建立儲存過程
-- 修改分隔符為$
DELIMITER $

-- 建立儲存過程，封裝分組查詢學生總成績的sql語句
CREATE PROCEDURE stu_group()
BEGIN
	SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
END$

-- 修改分隔符為分號
DELIMITER ;

呼叫儲存過程

  • 呼叫儲存過程語法
-- 標準語法
CALL 儲存過程名稱(實際引數);

-- 呼叫stu_group儲存過程
CALL stu_group();

檢視儲存過程

  • 檢視儲存過程語法
-- 查詢資料庫中所有的儲存過程 標準語法
SELECT * FROM mysql.proc WHERE db='資料庫名稱';

刪除儲存過程

  • 刪除儲存過程語法
-- 標準語法
DROP PROCEDURE [IF EXISTS] 儲存過程名稱;

-- 刪除stu_group儲存過程
DROP PROCEDURE stu_group;

儲存過程語法

儲存過程語法介紹

  • 儲存過程是可以進行程式設計的。意味著可以使用變數、表示式、條件控制語句、迴圈語句等，來完成比較複雜的功能！

變數的使用

  • 定義變數
-- 標準語法
DECLARE 變數名 資料型別 [DEFAULT 預設值];
-- 注意： DECLARE定義的是區域性變數，只能用在BEGIN END範圍之內

-- 定義一個int型別變數、並賦預設值為10
DELIMITER $

CREATE PROCEDURE pro_test1()
BEGIN
	DECLARE num INT DEFAULT 10;   -- 定義變數
	SELECT num;                   -- 查詢變數
END$

DELIMITER ;

-- 呼叫pro_test1儲存過程
CALL pro_test1();
  • 變數的賦值1
-- 標準語法
SET 變數名 = 變數值;

-- 定義字串型別變數，並賦值
DELIMITER $

CREATE PROCEDURE pro_test2()
BEGIN
	DECLARE NAME VARCHAR(10);   -- 定義變數
	SET NAME = '儲存過程';       -- 為變數賦值
	SELECT NAME;                -- 查詢變數
END$

DELIMITER ;

-- 呼叫pro_test2儲存過程
CALL pro_test2();
  • 變數的賦值2
-- 標準語法
SELECT 列名 INTO 變數名 FROM 表名 [WHERE 條件];

-- 定義兩個int變數，用於儲存男女同學的總分數
DELIMITER $

CREATE PROCEDURE pro_test3()
BEGIN
	DECLARE men,women INT;  -- 定義變數
	SELECT SUM(score) INTO men FROM student WHERE gender='男';    -- 計算男同學總分數賦值給men
	SELECT SUM(score) INTO women FROM student WHERE gender='女';  -- 計算女同學總分數賦值給women
	SELECT men,women;           -- 查詢變數
END$

DELIMITER ;

-- 呼叫pro_test3儲存過程
CALL pro_test3();

if語句的使用

  • 標準語法
-- 標準語法
IF 判斷條件1 THEN 執行的sql語句1;
[ELSEIF 判斷條件2 THEN 執行的sql語句2;]
...
[ELSE 執行的sql語句n;]
END IF;
  • 案例演示
/*
	定義一個int變數，用於儲存班級總成績
	定義一個varchar變數，用於儲存分數描述
	根據總成績判斷：
		380分及以上    學習優秀
		320 ~ 380     學習不錯
		320以下       學習一般
*/
DELIMITER $

CREATE PROCEDURE pro_test4()
BEGIN
	-- 定義總分數變數
	DECLARE total INT;
	-- 定義分數描述變數
	DECLARE description VARCHAR(10);
	-- 為總分數變數賦值
	SELECT SUM(score) INTO total FROM student;
	-- 判斷總分數
	IF total >= 380 THEN 
		SET description = '學習優秀';
	ELSEIF total >= 320 AND total < 380 THEN 
		SET description = '學習不錯';
	ELSE 
		SET description = '學習一般';
	END IF;
	
	-- 查詢總成績和描述資訊
	SELECT total,description;
END$

DELIMITER ;

-- 呼叫pro_test4儲存過程
CALL pro_test4();

引數的傳遞

  • 引數傳遞的語法
DELIMITER $

-- 標準語法
CREATE PROCEDURE 儲存過程名稱([IN|OUT|INOUT] 引數名 資料型別)
BEGIN
	執行的sql語句;
END$
/*
	IN:代表輸入引數，需要由呼叫者傳遞實際資料。預設的
	OUT:代表輸出引數，該引數可以作為返回值
	INOUT:代表既可以作為輸入引數，也可以作為輸出引數
*/
DELIMITER ;

  • 輸入引數

    • 標準語法
    DELIMITER $

-- 標準語法
CREATE PROCEDURE 儲存過程名稱(IN 引數名 資料型別)
BEGIN
	執行的sql語句;
END$

DELIMITER ;
    • 案例演示
    /*
	輸入總成績變數，代表學生總成績
	定義一個varchar變數，用於儲存分數描述
	根據總成績判斷：
		380分及以上  學習優秀
		320 ~ 380    學習不錯
		320以下      學習一般
*/
DELIMITER $

CREATE PROCEDURE pro_test5(IN total INT)
BEGIN
	-- 定義分數描述變數
	DECLARE description VARCHAR(10);
	-- 判斷總分數
	IF total >= 380 THEN 
		SET description = '學習優秀';
	ELSEIF total >= 320 AND total < 380 THEN 
		SET description = '學習不錯';
	ELSE 
		SET description = '學習一般';
	END IF;
	
	-- 查詢總成績和描述資訊
	SELECT total,description;
END$

DELIMITER ;

-- 呼叫pro_test5儲存過程
CALL pro_test5(390);
CALL pro_test5((SELECT SUM(score) FROM student));

  • 輸出引數

    • 標準語法
    DELIMITER $

-- 標準語法
CREATE PROCEDURE 儲存過程名稱(OUT 引數名 資料型別)
BEGIN
	執行的sql語句;
END$

DELIMITER ;
    • 案例演示
    /*
	輸入總成績變數，代表學生總成績
	輸出分數描述變數，代表學生總成績的描述
	根據總成績判斷：
		380分及以上  學習優秀
		320 ~ 380    學習不錯
		320以下      學習一般
*/
DELIMITER $

CREATE PROCEDURE pro_test6(IN total INT,OUT description VARCHAR(10))
BEGIN
	-- 判斷總分數
	IF total >= 380 THEN 
		SET description = '學習優秀';
	ELSEIF total >= 320 AND total < 380 THEN 
		SET description = '學習不錯';
	ELSE 
		SET description = '學習一般';
	END IF;
END$

DELIMITER ;

-- 呼叫pro_test6儲存過程
CALL pro_test6(310,@description);

-- 查詢總成績描述
SELECT @description;
    • 小知識
    @變數名:  這種變數要在變數名稱前面加上“@”符號，叫做使用者會話變數，代表整個會話過程他都是有作用的，這個類似於全域性變數一樣。

@@變數名: 這種在變數前加上 "@@" 符號, 叫做系統變數

case語句的使用

  • 標準語法1
-- 標準語法
CASE 表示式
WHEN 值1 THEN 執行sql語句1;
[WHEN 值2 THEN 執行sql語句2;]
...
[ELSE 執行sql語句n;]
END CASE;
  • 標準語法2
-- 標準語法
CASE
WHEN 判斷條件1 THEN 執行sql語句1;
[WHEN 判斷條件2 THEN 執行sql語句2;]
...
[ELSE 執行sql語句n;]
END CASE;
  • 案例演示
/*
	輸入總成績變數，代表學生總成績
	定義一個varchar變數，用於儲存分數描述
	根據總成績判斷：
		380分及以上  學習優秀
		320 ~ 380    學習不錯
		320以下      學習一般
*/
DELIMITER $

CREATE PROCEDURE pro_test7(IN total INT)
BEGIN
	-- 定義變數
	DECLARE description VARCHAR(10);
	-- 使用case判斷
	CASE
	WHEN total >= 380 THEN
		SET description = '學習優秀';
	WHEN total >= 320 AND total < 380 THEN
		SET description = '學習不錯';
	ELSE 
		SET description = '學習一般';
	END CASE;
	
	-- 查詢分數描述資訊
	SELECT description;
END$

DELIMITER ;

-- 呼叫pro_test7儲存過程
CALL pro_test7(390);
CALL pro_test7((SELECT SUM(score) FROM student));

while迴圈

  • 標準語法
-- 標準語法
初始化語句;
WHILE 條件判斷語句 DO
	迴圈體語句;
	條件控制語句;
END WHILE;
  • 案例演示
/*
	計算1~100之間的偶數和
*/
DELIMITER $

CREATE PROCEDURE pro_test8()
BEGIN
	-- 定義求和變數
	DECLARE result INT DEFAULT 0;
	-- 定義初始化變數
	DECLARE num INT DEFAULT 1;
	-- while迴圈
	WHILE num <= 100 DO
		-- 偶數判斷
		IF num%2=0 THEN
			SET result = result + num; -- 累加
		END IF;
		
		-- 讓num+1
		SET num = num + 1;         
	END WHILE;
	
	-- 查詢求和結果
	SELECT result;
END$

DELIMITER ;

-- 呼叫pro_test8儲存過程
CALL pro_test8();

repeat迴圈

  • 標準語法
-- 標準語法
初始化語句;
REPEAT
	迴圈體語句;
	條件控制語句;
	UNTIL 條件判斷語句
END REPEAT;

-- 注意：repeat迴圈是條件滿足則停止。while迴圈是條件滿足則執行
  • 案例演示
/*
	計算1~10之間的和
*/
DELIMITER $

CREATE PROCEDURE pro_test9()
BEGIN
	-- 定義求和變數
	DECLARE result INT DEFAULT 0;
	-- 定義初始化變數
	DECLARE num INT DEFAULT 1;
	-- repeat迴圈
	REPEAT
		-- 累加
		SET result = result + num;
		-- 讓num+1
		SET num = num + 1;
		
		-- 停止迴圈
		UNTIL num>10
	END REPEAT;
	
	-- 查詢求和結果
	SELECT result;
END$

DELIMITER ;

-- 呼叫pro_test9儲存過程
CALL pro_test9();

loop迴圈

  • 標準語法
-- 標準語法
初始化語句;
[迴圈名稱:] LOOP
	條件判斷語句
		[LEAVE 迴圈名稱;]
	迴圈體語句;
	條件控制語句;
END LOOP 迴圈名稱;

-- 注意：loop可以實現簡單的迴圈，但是退出迴圈需要使用其他的語句來定義。我們可以使用leave語句完成！
--      如果不加退出迴圈的語句，那麼就變成了死迴圈。
  • 案例演示
/*
	計算1~10之間的和
*/
DELIMITER $

CREATE PROCEDURE pro_test10()
BEGIN
	-- 定義求和變數
	DECLARE result INT DEFAULT 0;
	-- 定義初始化變數
	DECLARE num INT DEFAULT 1;
	-- loop迴圈
	l:LOOP
		-- 條件成立，停止迴圈
		IF num > 10 THEN
			LEAVE l;
		END IF;
	
		-- 累加
		SET result = result + num;
		-- 讓num+1
		SET num = num + 1;
	END LOOP l;
	
	-- 查詢求和結果
	SELECT result;
END$

DELIMITER ;

-- 呼叫pro_test10儲存過程
CALL pro_test10();

遊標

  • 遊標的概念

    • 遊標可以遍歷返回的多行結果，每次拿到一整行資料
    • 在儲存過程和函式中可以使用遊標對結果集進行迴圈的處理
    • 簡單來說遊標就類似於集合的迭代器遍歷
    • MySQL中的遊標只能用在儲存過程和函式中

  • 遊標的語法

    • 建立遊標
    -- 標準語法
DECLARE 遊標名稱 CURSOR FOR 查詢sql語句;
    • 開啟遊標
    -- 標準語法
OPEN 遊標名稱;
    • 使用遊標獲取資料
    -- 標準語法
FETCH 遊標名稱 INTO 變數名1,變數名2,...;
    • 關閉遊標
    -- 標準語法
CLOSE 遊標名稱;

  • 遊標的基本使用

-- 建立stu_score表
CREATE TABLE stu_score(
	id INT PRIMARY KEY AUTO_INCREMENT,
	score INT
);

/*
	將student表中所有的成績儲存到stu_score表中
*/
DELIMITER $

CREATE PROCEDURE pro_test11()
BEGIN
	-- 定義成績變數
	DECLARE s_score INT;
	-- 建立遊標,查詢所有學生成績資料
	DECLARE stu_result CURSOR FOR SELECT score FROM student;
	
	-- 開啟遊標
	OPEN stu_result;
	
	-- 使用遊標，遍歷結果,拿到第1行資料
	FETCH stu_result INTO s_score;
	-- 將資料儲存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 使用遊標，遍歷結果,拿到第2行資料
	FETCH stu_result INTO s_score;
	-- 將資料儲存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 使用遊標，遍歷結果,拿到第3行資料
	FETCH stu_result INTO s_score;
	-- 將資料儲存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 使用遊標，遍歷結果,拿到第4行資料
	FETCH stu_result INTO s_score;
	-- 將資料儲存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 關閉遊標
	CLOSE stu_result;
END$

DELIMITER ;

-- 呼叫pro_test11儲存過程
CALL pro_test11();

-- 查詢stu_score表
SELECT * FROM stu_score;


-- ===========================================================
/*
	出現的問題：
		student表中一共有4條資料，我們在遊標遍歷了4次，沒有問題！
		但是在遊標中多遍歷幾次呢？就會出現問題
*/
DELIMITER $

CREATE PROCEDURE pro_test11()
BEGIN
	-- 定義成績變數
	DECLARE s_score INT;
	-- 建立遊標,查詢所有學生成績資料
	DECLARE stu_result CURSOR FOR SELECT score FROM student;
	
	-- 開啟遊標
	OPEN stu_result;
	
	-- 使用遊標，遍歷結果,拿到第1行資料
	FETCH stu_result INTO s_score;
	-- 將資料儲存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 使用遊標，遍歷結果,拿到第2行資料
	FETCH stu_result INTO s_score;
	-- 將資料儲存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 使用遊標，遍歷結果,拿到第3行資料
	FETCH stu_result INTO s_score;
	-- 將資料儲存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 使用遊標，遍歷結果,拿到第4行資料
	FETCH stu_result INTO s_score;
	-- 將資料儲存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 使用遊標，遍歷結果,拿到第5行資料
	FETCH stu_result INTO s_score;
	-- 將資料儲存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 關閉遊標
	CLOSE stu_result;
END$

DELIMITER ;

-- 呼叫pro_test11儲存過程
CALL pro_test11();

-- 查詢stu_score表,雖然資料正確，但是在執行儲存過程時會報錯
SELECT * FROM stu_score;
  • 遊標的優化使用(配合迴圈使用)
/*
	當遊標結束後，會觸發遊標結束事件。我們可以通過這一特性來完成迴圈操作
	加標記思想：
		1.定義一個變數，預設值為0(意味著有資料)
		2.當遊標結束後，將變數值改為1(意味著沒有資料了)
*/
-- 1.定義一個變數，預設值為0(意味著有資料)
DECLARE flag INT DEFAULT 0;
-- 2.當遊標結束後，將變數值改為1(意味著沒有資料了)
DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;

/*
	將student表中所有的成績儲存到stu_score表中
*/
DELIMITER $

CREATE PROCEDURE pro_test12()
BEGIN
	-- 定義成績變數
	DECLARE s_score INT;
	-- 定義標記變數
	DECLARE flag INT DEFAULT 0;
	-- 建立遊標，查詢所有學生成績資料
	DECLARE stu_result CURSOR FOR SELECT score FROM student;
	-- 遊標結束後，將標記變數改為1
	DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;
	
	-- 開啟遊標
	OPEN stu_result;
	
	-- 迴圈使用遊標
	REPEAT
		-- 使用遊標，遍歷結果,拿到資料
		FETCH stu_result INTO s_score;
		-- 將資料儲存到stu_score表中
		INSERT INTO stu_score VALUES (NULL,s_score);
	UNTIL flag=1
	END REPEAT;
	
	-- 關閉遊標
	CLOSE stu_result;
END$

DELIMITER ;

-- 呼叫pro_test12儲存過程
CALL pro_test12();

-- 查詢stu_score表
SELECT * FROM stu_score;

儲存過程的總結

  • 儲存過程是 事先經過編譯並儲存在資料庫中的一段 SQL 語句的集合。可以在資料庫層面做一些業務處理
  • 說白了儲存過程其實就是將sql語句封裝為方法，然後可以呼叫方法執行sql語句而已
  • 儲存過程的好處
    • 安全
    • 高效
    • 複用性強

儲存函式

  • 儲存函式和儲存過程是非常相似的。儲存函式可以做的事情，儲存過程也可以做到！

  • 儲存函式有返回值，儲存過程沒有返回值(引數的out其實也相當於是返回資料了)

  • 標準語法

    • 建立儲存函式
    DELIMITER $

-- 標準語法
CREATE FUNCTION 函式名稱([引數 資料型別])
RETURNS 返回值型別
BEGIN
	執行的sql語句;
	RETURN 結果;
END$

DELIMITER ;
    • 呼叫儲存函式
    -- 標準語法
SELECT 函式名稱(實際引數);
    • 刪除儲存函式
    -- 標準語法
DROP FUNCTION 函式名稱;

  • 案例演示

/*
	定義儲存函式，獲取學生表中成績大於95分的學生數量
*/
DELIMITER $

CREATE FUNCTION fun_test1()
RETURNS INT
BEGIN
	-- 定義統計變數
	DECLARE result INT;
	-- 查詢成績大於95分的學生數量，給統計變數賦值
	SELECT COUNT(*) INTO result FROM student WHERE score > 95;
	-- 返回統計結果
	RETURN result;
END$

DELIMITER ;

-- 呼叫fun_test1儲存函式
SELECT fun_test1();

MySQL觸發器

觸發器的概念

  • 觸發器是與表有關的資料庫物件，可以在 insert/update/delete 之前或之後，觸發並執行觸發器中定義的SQL語句。觸發器的這種特性可以協助應用在資料庫端確保資料的完整性 、日誌記錄 、資料校驗等操作 。
  • 使用別名 NEW 和 OLD 來引用觸發器中發生變化的記錄內容，這與其他的資料庫是相似的。現在觸發器還只支援行級觸發，不支援語句級觸發。
觸發器型別 OLD的含義 NEW的含義
INSERT 型觸發器 無 (因為插入前狀態無資料) NEW 表示將要或者已經新增的資料
UPDATE 型觸發器 OLD 表示修改之前的資料 NEW 表示將要或已經修改後的資料
DELETE 型觸發器 OLD 表示將要或者已經刪除的資料 無 (因為刪除後狀態無資料)

建立觸發器

  • 標準語法
DELIMITER $

CREATE TRIGGER 觸發器名稱
BEFORE|AFTER INSERT|UPDATE|DELETE
ON 表名
[FOR EACH ROW]  -- 行級觸發器
BEGIN
	觸發器要執行的功能;
END$

DELIMITER ;

  • 觸發器演示。通過觸發器記錄賬戶表的資料變更日誌。包含：增加、修改、刪除

    • 建立賬戶表
    -- 建立db9資料庫
CREATE DATABASE db9;

-- 使用db9資料庫
USE db9;

-- 建立賬戶表account
CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 賬戶id
	NAME VARCHAR(20),					-- 姓名
	money DOUBLE						-- 餘額
);
-- 新增資料
INSERT INTO account VALUES (NULL,'張三',1000),(NULL,'李四',2000);
    • 建立日誌表
    -- 建立日誌表account_log
CREATE TABLE account_log(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 日誌id
	operation VARCHAR(20),				-- 操作型別 (insert update delete)
	operation_time DATETIME,			-- 操作時間
	operation_id INT,					-- 操作表的id
	operation_params VARCHAR(200)       -- 操作引數
);
    • 建立INSERT觸發器
    -- 建立INSERT觸發器
DELIMITER $

CREATE TRIGGER account_insert
AFTER INSERT
ON account
FOR EACH ROW
BEGIN
	INSERT INTO account_log VALUES (NULL,'INSERT',NOW(),new.id,CONCAT('插入後{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END$

DELIMITER ;

-- 向account表新增記錄
INSERT INTO account VALUES (NULL,'王五',3000);

-- 查詢account表
SELECT * FROM account;

-- 查詢日誌表
SELECT * FROM account_log;
    • 建立UPDATE觸發器
    -- 建立UPDATE觸發器
DELIMITER $

CREATE TRIGGER account_update
AFTER UPDATE
ON account
FOR EACH ROW
BEGIN
	INSERT INTO account_log VALUES (NULL,'UPDATE',NOW(),new.id,CONCAT('修改前{id=',old.id,',name=',old.name,',money=',old.money,'}','修改後{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END$

DELIMITER ;

-- 修改account表
UPDATE account SET money=3500 WHERE id=3;

-- 查詢account表
SELECT * FROM account;

-- 查詢日誌表
SELECT * FROM account_log;
    • 建立DELETE觸發器
    -- 建立DELETE觸發器
DELIMITER $

CREATE TRIGGER account_delete
AFTER DELETE
ON account
FOR EACH ROW
BEGIN
	INSERT INTO account_log VALUES (NULL,'DELETE',NOW(),old.id,CONCAT('刪除前{id=',old.id,',name=',old.name,',money=',old.money,'}'));
END$

DELIMITER ;

-- 刪除account表資料
DELETE FROM account WHERE id=3;

-- 查詢account表
SELECT * FROM account;

-- 查詢日誌表
SELECT * FROM account_log;

檢視觸發器

-- 標準語法
SHOW TRIGGERS;

-- 檢視觸發器
SHOW TRIGGERS;

刪除觸發器

-- 標準語法
DROP TRIGGER 觸發器名稱;

-- 刪除DELETE觸發器
DROP TRIGGER account_delete;

觸發器的總結

  • 觸發器是與表有關的資料庫物件
  • 可以在 insert/update/delete 之前或之後，觸發並執行觸發器中定義的SQL語句
  • 觸發器的這種特性可以協助應用在資料庫端確保資料的完整性 、日誌記錄 、資料校驗等操作
  • 使用別名 NEW 和 OLD 來引用觸發器中發生變化的記錄內容

MySQL事務

事務的概念

  • 一條或多條 SQL 語句組成一個執行單元，其特點是這個單元要麼同時成功要麼同時失敗，單元中的每條 SQL 語句都相互依賴，形成一個整體，如果某條 SQL 語句執行失敗或者出現錯誤，那麼整個單元就會回滾，撤回到事務最初的狀態，如果單元中所有的 SQL 語句都執行成功，則事務就順利執行。

事務的資料準備

-- 建立db10資料庫
CREATE DATABASE db10;

-- 使用db10資料庫
USE db10;

-- 建立賬戶表
CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 賬戶id
	NAME VARCHAR(20),			-- 賬戶名稱
	money DOUBLE				-- 賬戶餘額
);
-- 新增資料
INSERT INTO account VALUES (NULL,'張三',1000),(NULL,'李四',1000);

未管理事務演示

-- 張三給李四轉賬500元
-- 1.張三賬戶-500
UPDATE account SET money=money-500 WHERE NAME='張三';
-- 2.李四賬戶+500
出錯了...
UPDATE account SET money=money+500 WHERE NAME='李四';

-- 該場景下，這兩條sql語句要麼同時成功，要麼同時失敗。就需要被事務所管理！

管理事務演示

  • 操作事務的三個步驟
    1. 開啟事務：記錄回滾點，並通知伺服器，將要執行一組操作，要麼同時成功、要麼同時失敗
    2. 執行sql語句：執行具體的一條或多條sql語句
    3. 結束事務(提交|回滾)
      • 提交：沒出現問題，資料進行更新
      • 回滾：出現問題，資料恢復到開啟事務時的狀態
  • 開啟事務
-- 標準語法
START TRANSACTION;
  • 回滾事務
-- 標準語法
ROLLBACK;
  • 提交事務
-- 標準語法
COMMIT;
  • 管理事務演示
-- 開啟事務
START TRANSACTION;

-- 張三給李四轉賬500元
-- 1.張三賬戶-500
UPDATE account SET money=money-500 WHERE NAME='張三';
-- 2.李四賬戶+500
-- 出錯了...
UPDATE account SET money=money+500 WHERE NAME='李四';

-- 回滾事務(出現問題)
ROLLBACK;

-- 提交事務(沒出現問題)
COMMIT;

事務的提交方式

  • 提交方式

    • 自動提交(MySQL預設為自動提交)
    • 手動提交

  • 修改提交方式

    • 檢視提交方式
    -- 標準語法
SELECT @@AUTOCOMMIT;  -- 1代表自動提交    0代表手動提交
    • 修改提交方式
    -- 標準語法
SET @@AUTOCOMMIT=數字;

-- 修改為手動提交
SET @@AUTOCOMMIT=0;

-- 檢視提交方式
SELECT @@AUTOCOMMIT;

事務的四大特徵(ACID)

  • 原子性(atomicity)
    • 原子性是指事務包含的所有操作要麼全部成功，要麼全部失敗回滾，因此事務的操作如果成功就必須要完全應用到資料庫，如果操作失敗則不能對資料庫有任何影響
  • 一致性(consistency)
    • 一致性是指事務必須使資料庫從一個一致性狀態變換到另一個一致性狀態，也就是說一個事務執行之前和執行之後都必須處於一致性狀態
    • 拿轉賬來說，假設張三和李四兩者的錢加起來一共是2000，那麼不管A和B之間如何轉賬，轉幾次賬，事務結束後兩個使用者的錢相加起來應該還得是2000，這就是事務的一致性
  • 隔離性(isolcation)
    • 隔離性是當多個使用者併發訪問資料庫時，比如操作同一張表時，資料庫為每一個使用者開啟的事務，不能被其他事務的操作所干擾，多個併發事務之間要相互隔離
  • 永續性(durability)
    • 永續性是指一個事務一旦被提交了，那麼對資料庫中的資料的改變就是永久性的，即便是在資料庫系統遇到故障的情況下也不會丟失提交事務的操作

事務的隔離級別

  • 隔離級別的概念
    • 多個客戶端操作時 ,各個客戶端的事務之間應該是隔離的，相互獨立的 , 不受影響的。
    • 而如果多個事務操作同一批資料時，則需要設定不同的隔離級別 , 否則就會產生問題 。
    • 我們先來了解一下四種隔離級別的名稱 , 再來看看可能出現的問題
  • 四種隔離級別
1 讀未提交 read uncommitted
2 讀已提交 read committed
3 可重複讀 repeatable read
4 序列化 serializable
  • 可能引發的問題
問題 現象
髒讀 是指在一個事務處理過程中讀取了另一個未提交的事務中的資料 , 導致兩次查詢結果不一致
不可重複讀 是指在一個事務處理過程中讀取了另一個事務中修改並已提交的資料, 導致兩次查詢結果不一致
幻讀 select 某記錄是否存在，不存在，準備插入此記錄，但執行 insert 時發現此記錄已存在，無法插入。或不存在執行delete刪除，卻發現刪除成功
  • 查詢資料庫隔離級別
-- 標準語法
SELECT @@TX_ISOLATION;
  • 修改資料庫隔離級別
-- 標準語法
SET GLOBAL TRANSACTION ISOLATION LEVEL 級別字串;

-- 修改資料庫隔離級別為read uncommitted
SET GLOBAL TRANSACTION ISOLATION LEVEL read uncommitted;

-- 檢視隔離級別
SELECT @@TX_ISOLATION;   -- 修改後需要斷開連線重新開

事務隔離級別演示

  • 髒讀的問題

    • 視窗1
    -- 查詢賬戶表
select * from account;

-- 設定隔離級別為read uncommitted
set global transaction isolation level read uncommitted;

-- 開啟事務
start transaction;

-- 轉賬
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;

-- 視窗2查詢轉賬結果 ,出現髒讀(查詢到其他事務未提交的資料)

-- 視窗2檢視轉賬結果後，執行回滾
rollback;
    • 視窗2
    -- 查詢隔離級別
select @@tx_isolation;

-- 開啟事務
start transaction;

-- 查詢賬戶表
select * from account;

  • 解決髒讀的問題和演示不可重複讀的問題

    • 視窗1
    -- 設定隔離級別為read committed
set global transaction isolation level read committed;

-- 開啟事務
start transaction;

-- 轉賬
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;

-- 視窗2檢視轉賬結果，並沒有發生變化(髒讀問題被解決了)

-- 執行提交事務。
commit;

-- 視窗2檢視轉賬結果，資料發生了變化(出現了不可重複讀的問題，讀取到其他事務已提交的資料)
    • 視窗2
    -- 查詢隔離級別
select @@tx_isolation;

-- 開啟事務
start transaction;

-- 查詢賬戶表
select * from account;

  • 解決不可重複讀的問題

    • 視窗1
    -- 設定隔離級別為repeatable read
set global transaction isolation level repeatable read;

-- 開啟事務
start transaction;

-- 轉賬
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;

-- 視窗2檢視轉賬結果，並沒有發生變化

-- 執行提交事務
commit;

-- 這個時候視窗2只要還在上次事務中，看到的結果都是相同的。只有視窗2結束事務，才能看到變化(不可重複讀的問題被解決)
    • 視窗2
    -- 查詢隔離級別
select @@tx_isolation;

-- 開啟事務
start transaction;

-- 查詢賬戶表
select * from account;

-- 提交事務
commit;

-- 查詢賬戶表
select * from account;

  • 幻讀的問題和解決

    • 視窗1
    -- 設定隔離級別為repeatable read
set global transaction isolation level repeatable read;

-- 開啟事務
start transaction;

-- 新增一條記錄
INSERT INTO account VALUES (3,'王五',1500);

-- 查詢賬戶表，本視窗可以檢視到id為3的結果
SELECT * FROM account;

-- 提交事務
COMMIT;
    • 視窗2
    -- 查詢隔離級別
select @@tx_isolation;

-- 開啟事務
start transaction;

-- 查詢賬戶表，查詢不到新新增的id為3的記錄
select * from account;

-- 新增id為3的一條資料，發現新增失敗。出現了幻讀
INSERT INTO account VALUES (3,'測試',200);

-- 提交事務
COMMIT;

-- 查詢賬戶表，查詢到了新新增的id為3的記錄
select * from account;
    • 解決幻讀的問題
    /*
	視窗1
*/
-- 設定隔離級別為serializable
set global transaction isolation level serializable;

-- 開啟事務
start transaction;

-- 新增一條記錄
INSERT INTO account VALUES (4,'趙六',1600);

-- 查詢賬戶表，本視窗可以檢視到id為4的結果
SELECT * FROM account;

-- 提交事務
COMMIT;



/*
	視窗2
*/
-- 查詢隔離級別
select @@tx_isolation;

-- 開啟事務
start transaction;

-- 查詢賬戶表，發現查詢語句無法執行，資料表被鎖住！只有視窗1提交事務後，才可以繼續操作
select * from account;

-- 新增id為4的一條資料，發現已經存在了，就不會再新增了！幻讀的問題被解決
INSERT INTO account VALUES (4,'測試',200);

-- 提交事務
COMMIT;

隔離級別總結

隔離級別 名稱 出現髒讀 出現不可重複讀 出現幻讀 資料庫預設隔離級別
1 read uncommitted 讀未提交 是 是 是
2 read committed 讀已提交 否 是 是 Oracle / SQL Server
3 repeatable read 可重複讀 否 否 是 MySQL
4 **serializable ** 序列化 否 否 否

注意：隔離級別從小到大安全性越來越高，但是效率越來越低 , 所以不建議使用READ UNCOMMITTED 和 SERIALIZABLE 隔離級別.

事務的總結

  • 一條或多條 SQL 語句組成一個執行單元，其特點是這個單元要麼同時成功要麼同時失敗。例如轉賬操作
  • 開啟事務：start transaction;
  • 回滾事務：rollback;
  • 提交事務：commit;
  • 事務四大特徵
    • 原子性
    • 永續性
    • 隔離性
    • 一致性
  • 事務的隔離級別
    • read uncommitted(讀未提交)
    • read committed (讀已提交)
    • repeatable read (可重複讀)
    • serializable (序列化)

相關文章