MySQL數據庫的觸發器和事務

Posted on by

一、觸發器

概念

觸發器是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 觸發器和事務內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：