MySQL触发器与事务讲解

码农101号

已于 2025-04-22 14:30:29 修改

阅读量807

点赞数 24

CC 4.0 BY-SA版权

文章标签： mysql 数据库

于 2025-04-22 14:27:55 首次发布

本文链接：https://blog.csdn.net/lsh0411/article/details/147396504

一、DML、DDL、DCL语言讲解

二、触发器讲解

三、MySQL事务讲解

一、DML、DDL、DCL语言讲解

dml 数据操作语言：insert、delete、update

ddl 数据定义语言：drop、alert、create

dcl 数据控制语言：grant、revoke

二、触发器讲解

2.1 什么是触发器

数据库中的触发器（Trigger）是一种特殊的存储程序，它会在指定的表上发生特定事件（如 INSERT、UPDATE、DELETE）时自动执行。触发器可以用于实现复杂的数据逻辑、自动化任务或数据完整性约束。

2.2 触发器的核心概念

1. 触发时机：

BEFORE：在操作（如插入、更新、删除）执行前触发。
AFTER：在操作执行后触发。
INSTEAD OF（仅限视图）：替代原操作，常用于视图触发。

2. 触发事件：

INSERT：数据插入时触发。
UPDATE：数据更新时触发。
DELETE：数据删除时触发。

3. 触发对象：

绑定到某个表（或视图），仅在对应表的操作时激活。

4. 触发粒度：

行级触发（FOR EACH ROW）：逐行处理，适用于逐行操作。
语句级触发：整条 SQL 语句执行一次，无论影响多少行。

2.3 三种触发器

INSERT 触发器

在 INSERT 语句执行之前或之后响应的触发器。

使用 INSERT 触发器需要注意以下几点：

在 INSERT 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问被插入的行。
在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新，即允许更改被插入的值（只要具有对应的操作权限）。
对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含的值是 0，在 INSERT 执行之后将包含新的自动生成值。

UPDATE 触发器

在 UPDATE 语句执行之前或之后响应的触发器。

使用 UPDATE 触发器需要注意以下几点：

在 UPDATE 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问更新的值。
在 UPDATE 触发器代码内，可引用一个名为 OLD（不区分大小写）的虚拟表来访问 UPDATE 语句执行前的值。
在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新，即允许更改将要用于 UPDATE 语句中的值（只要具有对应的操作权限）。
OLD 中的值全部是只读的，不能被更新。

DELETE 触发器

在 DELETE 语句执行之前或之后响应的触发器。

使用 DELETE 触发器需要注意以下几点：

在 DELETE 触发器代码内，可以引用一个名为 OLD（不区分大小写）的虚拟表来访问被删除的行。
OLD 中的值全部是只读的，不能被更新。

总体来说，触发器使用的过程中，MySQL 会按照以下方式来处理错误。

若对于事务性表，如果触发程序失败，以及由此导致的整个语句失败，那么该语句所执行的所有更改将回滚；

对于非事务性表，则不能执行此类回滚，即使语句失败，失败之前所做的任何更改依然有效。

若 BEFORE 触发程序失败，则 MySQL 将不执行相应行上的操作。

若在 BEFORE 或 AFTER 触发程序的执行过程中出现错误，则将导致调用触发程序的整个语句失败。

仅当 BEFORE 触发程序和行操作均已被成功执行，MySQL 才会执行AFTER触发程序。

2.4 触发器的典型示例

1. 数据校验

在插入或更新数据前，检查合法性

-- 示例：禁止插入负数年龄
CREATE TRIGGER check_age_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.age < 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '年龄不能为负数';
    END IF;
END;

2. 审计日志

自动记录数据变更历史

-- 示例：记录用户表的修改日志
CREATE TRIGGER log_user_changes
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit_log (user_id, old_email, new_email, change_time)
    VALUES (OLD.id, OLD.email, NEW.email, NOW());
END;

3. 级联操作

当主表数据变更时，自动同步关联表。

-- 示例：删除用户时，级联删除订单
CREATE TRIGGER delete_user_orders
AFTER DELETE ON users
FOR EACH ROW
BEGIN
    DELETE FROM orders WHERE user_id = OLD.id;
END;

4. 自动计算字段

更新时自动计算衍生值

-- 示例：自动更新库存总量
CREATE TRIGGER update_total_stock
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE products 
    SET total_stock = total_stock - NEW.quantity 
    WHERE id = NEW.product_id;
END;

2.5 触发器的优缺点

优点：

自动化：减少手动编写业务逻辑的代码。
数据一致性：强制实施复杂规则（如级联删除）。
审计追踪：隐式记录数据变更历史。

缺点：

性能开销：频繁触发的逻辑可能降低数据库性能。
调试困难：隐式执行可能导致问题难以追踪。
递归风险：触发器调用其他触发器可能引发递归循环。

2.6 实战示例：用户注册日志

-- 创建日志表
CREATE TABLE user_registration_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    username VARCHAR(50),
    action_time DATETIME
);

-- 创建触发器：用户插入后记录日志
DELIMITER $$
CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_registration_log (user_id, username, action_time)
    VALUES (NEW.id, NEW.username, NOW());
END
$$
DELIMITER ;

三、MySQL事务讲解

3.1 什么是事务

事务是一组需要作为一个整体执行的 SQL 语句，要么全部成功（提交），要么全部失败（回滚）。
典型场景：银行转账（A 转给 B 100 元，A 扣款和 B 入账必须同时成功或失败）。

3.2 典型示例

正常情况: 转账这个操作, 需要分为以下这么三步来完成 , 三步完成之后, 张三减少1000, 而李四增加1000, 转账成功 :

异常情况: 转账这个操作, 也是分为以下这么三步来完成 , 在执行第三步是报错了, 这样就导致张三减少1000块钱, 而李四的金额没变, 这样就造成了数据的不一致, 就出现问题了。

为了解决上述的问题，就需要通过数据的事务来完成，我们只需要在业务逻辑执行之前开启事务，执行完毕后提交事务。如果执行过程中报错，则回滚事务，把数据恢复到事务开始之前的状态。

默认MySQL的事务是自动提交的，也就是说，当执行完一条DML语句时，MySQL会立即隐式的提交事务。

3.3 事务四大特性

原子性（Atomicity ）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
一致性（Consistency ）：事务完成时，必须使所有的数据都保持一致状态
隔离性（Isolation ）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
持久性（Durability ）：事务一旦提交或者回滚，它对数据库的改变是永久的

3.4 基本示例

-- 建表语句
drop table if exists account;
create table account(
    id    int primary key AUTO_INCREMENT comment 'ID',
    name  varchar(10) comment '姓名',
    money double(10, 2) comment '余额'
) comment '账户表';
insert into account(name, money) values ('张三', 2000), ('李四', 2000);


-- 设置事务提交方式，1为自动提交，0为手动提交
select @@autocommit = 0;

-- 开启事务
start transaction
-- 1. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 2. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';
-- 如果正常执行完毕, 则提交事务
commit;

3.5 使用 SIGNAL

在 MySQL 中，事务的生命周期由 START TRANSACTION; 开始，直到遇到 COMMIT; 或 ROLLBACK; 结束。
即使发生了异常，只要没有显式回滚事务（ROLLBACK;），事务仍然处于“未完成”状态。手动执行 commit; 会将事务中所有的操作提交到数据库中。
在 MySQL 中，如果发生异常（例如 `SIGNAL` 抛出的错误），事务不会自动回滚。需要开发者显式地调用 `ROLLBACK;` 来回滚事务
MySQL 提供了几种条件处理器，用于响应不同类型的异常、警告或 NOT FOUND 状态：
CONTINUE HANDLER：当触发条件时，执行完处理器中的代码后继续执行后续的 SQL 语句。
EXIT HANDLER：当触发条件时，执行完处理器中的代码后退出当前的 BEGIN...END 块。

3.6 正常示例

-- 删除transfer存储工程
drop procedure if exists transfer;

-- 创建存储过程
delimiter $$
create procedure transfer
begin
    -- 定义变量
    declare m double default 0.0;
    -- 捕捉异常
    declare exit handler for sqlexception

    begin 
		-- 回滚事务
		rollback;
		-- 输出异常信息
		select '出现异常';
	end;
    
    -- 打开事务
    start stansaction;
    
    -- 查询张三当前余额多少
    select money into m from account where name = '张三';

    if m < 1000 then
        singger sqlstate '54000'
        set message_text = '余额不足';
    end if; 
    
    -- 设置张三减钱，李四加钱
    update account set money = money - 1000 where name = '张三';
	update account set money = money + 1000 where name = '李四';

    -- 提交事务
    commit;
    
end$$
delimiter ;

注释：
当张三的余额小于1000，来进行转账时，会出现异常，

3.7 使用 ROW_COUNT()

MySQL 提供了一个内置函数 ROW_COUNT()，用于返回上一条 SQL 语句影响的行数。

这个函数可以判断是否有数据被修改。

对于INSERT，UPDATE，DELETE语句，返回受影响的行数。如果没有行被修改，则返回 0。

drop procedure if exists transfer;
delimiter $$
create procedure transfer(a int, b int, c double)
my: begin
	-- 定义变量
	declare m double default 0.0;
	
	-- 打开事务
	start transaction;
	select ifnull(money,0) into m from account where id = a;
	
	if m < c then 
		rollback;
		leave my;
	end if;
	
	update account set money = money - c where id = a;
	
	update account set money = money + c where id = b;
	
	-- 判断是否成功
	if row_count() = 0 then
		rollback;
	end if;
	
	-- 提交事务
	commit;
	
end$$
delimiter ;

3.8 存储过程与触发器

3.8.1 存储过程

存储过程是 MySQL 中一种封装 SQL 逻辑的方式，支持显式地开启、提交和回滚事务。

特点

存储过程可以通过 START TRANSACTION; 开启事务，并通过 COMMIT; 提交事务或通过 ROLLBACK; 回滚事务。
可以结合异常处理机制（如 DECLARE EXIT HANDLER FOR SQLEXCEPTION）来实现自动回滚。
如果存储过程中没有显式开启事务，默认情况下每条 SQL 语句会自动提交（Auto-commit 模式）。
在存储过程中，事务的生命周期由 START TRANSACTION; 开始，直到遇到 COMMIT; 或 ROLLBACK; 结束。

3.8.2 触发器

触发器是一种特殊的数据库对象，它会在特定事件（如 INSERT、UPDATE 或 DELETE）发生时自动执行。

特点

触发器本身不能显式地开启事务（即不能使用 START TRANSACTION;），因为触发器运行在一个已经存在的事务上下文中。
如果触发器中发生了错误，整个事务会回滚（包括触发器外部的操作）。
触发器不能显式地调用 COMMIT; 或 ROLLBACK;，因为它依赖于外部事务的上下文。
如果触发器中抛出了异常（例如通过 SIGNAL），整个事务会被回滚。
触发器的行为会影响事务的完整性，因此需要谨慎编写触发器逻辑。

示例：

DROP TRIGGER IF EXISTS CHANGE_MONEY;

CREATE TRIGGER CHANGE_MONEY
    AFTER UPDATE
    ON ACCOUNT
    FOR EACH ROW
BEGIN
    DECLARE M DOUBLE(10, 2) DEFAULT 0;
    IF NEW.MONEY < OLD.MONEY THEN
        SIGNAL SQLSTATE '54000' SET MESSAGE_TEXT = '只能增加不能减少';
    END IF;
    SELECT NEW.MONEY - OLD.MONEY INTO M;
    UPDATE COMPANY SET MONEY = MONEY - M WHERE DEPT = 'YW';
END;




DROP TRIGGER IF EXISTS CHANGE_MONEY2;

CREATE TRIGGER CHANGE_MONEY2
    AFTER UPDATE
    ON COMPANY
    FOR EACH ROW
BEGIN
    IF OLD.MONEY <= 0 THEN
        SIGNAL SQLSTATE '54000' SET MESSAGE_TEXT = '部门没钱了';
    END IF;
END;

UPDATE ACCOUNT SET MONEY = MONEY + 5000 WHERE ID = 1;