【MySQL】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語句要麼同時成功，要麼同時失敗。就需要被事務所管理！
管理事務演示
- 操作事務的三個步驟
- 開啟事務：記錄回滾點，並通知服務器，將要執行一組操作，要麼同時成功、要麼同時失敗
- 執行sql語句：執行具體的一條或多條sql語句
- 結束事務(提交|回滾)
- 提交：沒出現問題，數據進行更新
- 回滾：出現問題，數據恢復到開啟事務時的狀態
- 開啟事務
-- 標準語法
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 (串行化)
