⑩⑥ 【MySQL】详解 触发器TRIGGER,协助 确保数据的完整性,日志记录,数据校验等操作。

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: ⑩⑥ 【MySQL】详解 触发器TRIGGER,协助 确保数据的完整性,日志记录,数据校验等操作。


⑩⑥ 【MySQL】触发器详解


1. 什么是触发器

触发器

  • 触发器是与表有关的数据库对象 ,指在insert / update / delete之前或之后 ,触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性日志记录数据校验等操作。
  • 使用别名OLD和NEW来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在触发器还只支持行级触发不支持语句级触发
  • 行级触发:执行insert / update / delete语句,影响5行数据,触发器会被触发5次。
  • 语句级触发:执行insert / update / delete语句,影响n行数据,触发器只触发1次。


触发器类型

  • INSERT 触发器NEW 表示将要或已经新增的数据。
  • UPDATE 触发器OLD 表示修改之前的数据,NEW 表示将要或已经修改后的数据。
  • DELETE 触发器:OLD表示将要或已经删除的数据。




2. 触发器的使用

①使用规则

使用触发器

  • 🚀创建触发器
-- 语法:
-- {}内的关键字为必选项,任选其一。
-- BEFORE/AFTER表示触发SQL语句的时机,在新增/修改/删除操作之前/之后
CREATE TRIGGER 触发器名
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON 表名 FOR EACH ROW
BEGIN
  -- 触发后执行的SQL语句
END;


  • 🚀查看触发器
SHOW TRIGGERS;


  • 🚀删除触发器
-- 若不指定schema_name,默认当前数据库
DROP TRIGGER [schema_name.]触发器名


②案例

准备工作

  • 通过触发器记录 user 表的数据变更日志(user_logs) | 包含增加, 修改 , 删除
-- 需求: 通过触发器记录 user 表的数据变更日志(user_logs) , 包含增加, 修改 , 删除 ;
-- 准备工作 : 日志表 user_logs
create table user_logs(
  id int(11) not null auto_increment,
  operation varchar(20) not null comment '操作类型, insert/update/delete',
  operate_time datetime not null comment '操作时间',
  operate_id int(11) not null comment '操作的ID',
  operate_params varchar(500) comment '操作参数',
  primary key(`id`)
)engine=innodb default charset=utf8;


INSERT触发器 案例

-- 插入数据触发器
DELIMITER $$
CREATE TRIGGER tb_user_insert_trigger
AFTER INSERT 
ON tb_user FOR EACH ROW
BEGIN
    INSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
    (NULL, 'insert', NOW(), new.id, CONCAT('插入的数据内容为: id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
END$$
DELIMITER ;
-- 查看触发器
SHOW TRIGGERS ;
-- 插入数据到tb_user
INSERT INTO tb_user(id, NAME, phone, email, profession, age, gender, STATUS, createtime) VALUES 
(26,'三皇子','18809091212','erhuangzi@163.com','软件工程',23,'1','1',NOW());
-- 查看触发器触发后的日志表
SELECT * FROM user_logs;
-- 删除触发器
drop trigger tb_user_insert_trigger;


UPDATE触发器 案例

-- 修改数据触发器
DELIMITER $$
CREATE TRIGGER tb_user_update_trigger
AFTER UPDATE 
ON tb_user FOR EACH ROW
BEGIN
    INSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
    (NULL, 'update', NOW(), new.id,
        CONCAT('更新之前的数据: id=',old.id,',name=',old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession,
            ' | 更新之后的数据: id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
END$$
DELIMITER ;
-- 查看触发器
SHOW TRIGGERS ;
-- 执行更新操作
UPDATE tb_user SET profession = '会计' WHERE id = 23;
UPDATE tb_user SET profession = '会计' WHERE id <= 5;
-- 查看触发器触发后的日志表
SELECT * FROM user_logs;


DELETE触发器 案例

-- 删除数据触发器
DELIMITER $$
CREATE TRIGGER tb_user_delete_trigger
AFTER DELETE 
ON tb_user FOR EACH ROW
BEGIN
    INSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
    (NULL, 'delete', NOW(), old.id,
        CONCAT('删除之前的数据: id=',old.id,',name=',old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession));
END$$
DELIMITER ;
-- 查看触发器
SHOW TRIGGERS ;




相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
目录
相关文章
|
9天前
|
存储 缓存 关系型数据库
图解MySQL【日志】——Redo Log
Redo Log(重做日志)是数据库中用于记录数据页修改的物理日志,确保事务的持久性和一致性。其主要作用包括崩溃恢复、提高性能和保证事务一致性。Redo Log 通过先写日志的方式,在内存中缓存修改操作,并在适当时候刷入磁盘,减少随机写入带来的性能损耗。WAL(Write-Ahead Logging)技术的核心思想是先将修改操作记录到日志文件中,再择机写入磁盘,从而实现高效且安全的数据持久化。Redo Log 的持久化过程涉及 Redo Log Buffer 和不同刷盘时机的控制参数(如 `innodb_flush_log_at_trx_commit`),以平衡性能与数据安全性。
25 5
图解MySQL【日志】——Redo Log
|
8天前
|
关系型数据库 MySQL 数据库
图解MySQL【日志】——两阶段提交
两阶段提交是为了解决Redo Log和Binlog日志在事务提交时可能出现的半成功状态,确保两者的一致性。它分为准备阶段和提交阶段,通过协调者和参与者协作完成。准备阶段中,协调者向所有参与者发送准备请求,参与者执行事务并回复是否同意提交;提交阶段中,若所有参与者同意,则协调者发送提交请求,否则发送回滚请求。MySQL通过这种方式保证了分布式事务的一致性,并引入组提交机制减少磁盘I/O次数,提升性能。
24 4
图解MySQL【日志】——两阶段提交
|
10天前
|
监控 关系型数据库 MySQL
MySQL和SQLSugar百万条数据查询分页优化
在面对百万条数据的查询时,优化MySQL和SQLSugar的分页性能是非常重要的。通过合理使用索引、调整查询语句、使用缓存以及采用高效的分页策略,可以显著提高查询效率。本文介绍的技巧和方法,可以为开发人员在数据处理和查询优化中提供有效的指导,提升系统的性能和用户体验。掌握这些技巧后,您可以在处理海量数据时更加游刃有余。
40 9
|
4天前
|
SQL 关系型数据库 MySQL
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
16 2
|
6天前
|
关系型数据库 MySQL 数据库
MySQL日志
本文介绍了MySQL中三个重要的日志:binlog、redolog和undolog。binlog记录数据库更改操作,支持数据恢复、复制和审计;redolog保证事务的原子性和持久性,实现crash-safe;undolog用于事务回滚及MVCC的实现。每个日志都有其独特的作用和应用场景,确保数据库的稳定性和数据一致性。
|
8天前
|
关系型数据库 MySQL
图解MySQL【日志】——磁盘 I/O 次数过高时优化的办法
当 MySQL 磁盘 I/O 次数过高时,可通过调整参数优化。控制刷盘时机以降低频率:组提交参数 `binlog_group_commit_sync_delay` 和 `binlog_group_commit_sync_no_delay_count` 调整等待时间和事务数量;`sync_binlog=N` 设置 write 和 fsync 频率,`innodb_flush_log_at_trx_commit=2` 使提交时只写入 Redo Log 文件,由 OS 择机持久化,但两者在 OS 崩溃时有丢失数据风险。
21 3
|
11天前
|
存储 关系型数据库 MySQL
MySQL进阶突击系列(09)数据磁盘存储模型 | 一行数据怎么存?
文中详细介绍了MySQL数据库中一行数据在磁盘上的存储机制,包括表空间、段、区、页和行的具体结构,以及如何设计和优化行数据存储以提高性能。
|
11天前
|
缓存 关系型数据库 MySQL
图解MySQL【日志】——Buffer Pool
Buffer Pool 是数据库管理系统(DBMS)中用于缓存磁盘数据页的内存区域,主要包含数据页、索引页、undo 页等。它通过减少磁盘 I/O 提升性能,特别是在处理大型数据库时效果显著。查询时,整个数据页而非单条记录会被加载到 Buffer Pool 中,以提高访问效率。
17 0
图解MySQL【日志】——Buffer Pool
|
10天前
|
存储 SQL 关系型数据库
【YashanDB 知识库】MySQL 迁移至崖山 char 类型数据自动补空格问题
问题分类】功能使用 【关键字】char,char(1) 【问题描述】MySQL 迁移至崖山环境,字段类型源端和目标端都为 char(2),但应用存储的数据为'0'、'1',此时崖山查询该表字段时会自动补充空格 【问题原因分析】mysql 有 sql_mode 控制,检查是否启用了 PAD_CHAR_TO_FULL_LENGTH SQL 模式。如果启用了这个模式,MySQL 才会保留 CHAR 类型字段的尾随空格,默认没有启动。 #查看sql_mode mysql> SHOW VARIABLES LIKE 'sql_mode'; 【解决/规避方法】与应用确认存储的数据,正确定义数据
|
11天前
|
存储 关系型数据库 MySQL
图解MySQL【日志】——Undo Log
Undo Log(回滚日志)是 MySQL 中用于实现事务原子性和一致性的关键机制。在默认的自动提交模式下,MySQL 隐式开启事务,每条增删改语句都会记录到 Undo Log 中。其主要作用包括:
25 0