MySQL資料庫的觸發器和事務怎麼應用

一、觸發器

概念

觸發器是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資料庫的觸發器和事務怎麼應用的詳細內容。更多資訊請關注PHP中文網其他相關文章！