MySQL資料庫的觸發器和事務

2022-08-02 14:07:24

一、觸發器

概念

觸發器是SQL server提供給程式設計師和資料分析員來保證資料完整性的一種方法，它是與表事件相關的特殊的儲存過程，是由事件來觸發。觸發器經常用來加強資料的完整性約束和業務規則。

二、觸發器的操作

建立account和account_log資料表：

CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),			
	money DOUBL
);
INSERT INTO account VALUES (NULL,'張三',1000),(NULL,'李四',1000);
CREATE TABLE account_log(
	id INT PRIMARY KEY AUTO_INCREMENT,
	operation VARCHAR(20),			
	operation_time DATETIME,		
	operation_id INT,			
	operation_params VARCHAR(200)      
);

觸發器的增刪改操作

// 語法格式： BEFORE|AFTER    INSERT: 插入 UPDATE：更新  DELETE：刪除
// DELIMITER $
// CREATE TRIGGER 觸發器名稱
// BEFORE|AFTER INSERT|UPDATE|DELETE
// ON 表名
// FOR EACH ROW
// BEGIN
// 	觸發器要執行的功能;
// END$
// DELIMITER ;


// 建立INSERT型觸發器。用於對account表新增資料進行紀錄檔的記錄
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, '王五', 2000);

// 查詢account表
SELECT * FROM account;

觸發器的檢視刪除操作

// 檢視標準語法： SHOW TRIGGERS;
// 檢視觸發器
SHOW TRIGGERS;


// 刪除標準語法：DROP TRIGGER 觸發器名稱;
// 刪除account_delete觸發器
DROP TRIGGER account_delete;

三、事務

概念

在關聯式資料庫中，一個事務可以是一個SQL語句，一組SQL語句或整個程式。其特點是這個單元要麼同時成功要麼同時失敗，單元中的每條 SQL 語句都相互依賴，形成一個整體。事務應該具有4個屬性：原子性、一致性、隔離性、永續性。這四個屬性通常稱為ACID特性。

四大特性：

原子性：一個事務是一個不可分割的工作單位，事務包含的所有操作要麼全部成功，要麼全部失敗回滾。
一致性：事務必須使資料庫從一個一致性狀態變換到另一個一致性狀態，也就是說一個事務執行之前和執行之後都必須處於一致性狀態。
隔離性： 當多個使用者並行存取資料庫時，比如操作同一張表時，資料庫為每一個使用者開啟的事務，不能被其他事務的操作所幹擾，多個並行事務之間要相互隔離
永續性： 一個事務一旦被提交了，那麼對資料庫中的資料的改變就是永久性的，即便是在資料庫系統遇到故障的情況下也不會丟失提交事務的操作。

四、事務的操作

基本流程

開啟事務：記錄回滾點，並通知伺服器
執行SQL語句：執行具體的一條或多條sql語句
結束事務(提交|回滾)：提交：沒出現問題，資料進行更新；回滾：出現問題，資料恢復到開啟事務時的狀態

基礎操作

-- 開啟事務
START TRANSACTION;

-- 執行SQL語句
UPDATE account SET money=money-500 WHERE NAME='張三';

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

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

事務的提交方式

// 檢視提交方式
SELECT @@AUTOCOMMIT;  -- 1代表自動提交    0代表手動提交
// 修改事務的提交方式
SET @@autocommit=1;

事務的隔離級別

四種隔離級別

讀未提交：read uncommitted
讀已提交：read committed (Oracle/SQL Server 資料庫預設級別)
可重複讀：repeatable read (MySQL 預設級別)
序列化：serializable

可能引發問題：

髒讀：是指在一個事務處理過程中讀取了另一個未提交的事務中的資料 , 導致兩次查詢結果不一致
不可重複讀：是指在一個事務處理過程中讀取了另一個事務中修改並已提交的資料, 導致兩次查詢結果不一致
幻讀：select 某記錄是否存在，不存在，準備插入此記錄，但執行 insert 時發現此記錄已存在，無法插入。或不存在執行delete刪除，卻發現刪除成功

隔離操作

// 查詢資料庫隔離級別
SELECT @@TX_ISOLATION;

// 修改資料庫隔離級別
SET GLOBAL TRANSACTION ISOLATION LEVEL 級別字串;

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

到此這篇關於MySQL資料庫的觸發器和事務的文章就介紹到這了,更多相關MySQL 觸發器和事務內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL資料庫的觸發器和事務

目錄

一、觸發器

概念

二、觸發器的操作

觸發器的增刪改操作

觸發器的檢視刪除操作

三、事務

概念

四、事務的操作

基本流程

基礎操作

事務的提交方式

事務的隔離級別

隔離操作

熱門文章

MySQL資料庫的觸發器和事務

目錄

一、觸發器

概念

二、 觸發器的操作

觸發器的增刪改操作

觸發器的檢視刪除操作

三、事務

概念

四、事務的操作

基本流程

基礎操作

事務的提交方式

事務的隔離級別

隔離操作

熱門文章

二、觸發器的操作