2024 Mysql基础与进阶操作系列之MySQL触发器详解（20）作者——LJS[你个小黑子这都还学不会嘛？你是真爱粉嘛？真是的 ~；以后请别侮辱我家鸽鸽]

admin管理员组

文章数量:1794759

2024 Mysql基础与进阶操作系列之MySQL触发器详解（20）作者——LJS[你个小黑子这都还学不会嘛？你是真爱粉嘛？真是的 ~；以后请别侮辱我家鸽鸽]

1.MySQL触发器的使用场景

1.1. 数据完整性约束

插入触发器

代码语言：javascript代码运行次数：0运行复制

-- 创建一个名为 `person` 的表
CREATE TABLE `person` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,  -- 定义 id 列为整型，非空，自增
  `name` VARCHAR(100) NOT NULL,           -- 定义 name 列为可变字符型，最大长度100，非空
  `age` INT(11) NOT NULL,                 -- 定义 age 列为整型，非空
  PRIMARY KEY (`id`)                       -- 设置 id 列为主键
);

-- 更改分隔符，以便定义触发器
DELIMITER //

-- 创建一个名为 `trigger_insert_mytable` 的触发器
CREATE TRIGGER `trigger_insert_mytable` 
BEFORE INSERT ON `person`                  -- 在 `person` 表插入数据之前触发
FOR EACH ROW                                -- 对每一行插入的数据执行触发器逻辑
BEGIN
  -- 触发器逻辑部分，可以在此处编写需要执行的代码
  
  -- 示例：将插入的数据的 name 字段转换为大写
  SET NEW.name = UPPER(NEW.name);          -- 使用 UPPER 函数将新插入的 name 字段转换为全大写
END //                                      -- 结束触发器逻辑
DELIMITER ;                                 -- 恢复默认分隔符

效果图

更新触发器[效果图略]：

代码语言：javascript代码运行次数：0运行复制

-- 创建一个名为 trigger_name 的触发器
CREATE TRIGGER trigger_name
BEFORE UPDATE ON table_name               -- 在 table_name 表进行更新操作之前触发
FOR EACH ROW                               -- 对于每一行被更新的记录都会执行触发器逻辑
BEGIN
    -- 触发器逻辑部分
    -- 这里可以添加你需要执行的操作，例如验证数据、记录日志等
END;

删除触发器[效果图略]：

代码语言：javascript代码运行次数：0运行复制

-- 创建一个名为 trigger_name 的触发器
CREATE TRIGGER trigger_name
BEFORE DELETE ON table_name               -- 在 table_name 表进行删除操作之前触发
FOR EACH ROW                               -- 对于每一行被删除的记录都会执行触发器逻辑
BEGIN
    -- 触发器逻辑部分
    -- 可以在这里添加代码，比如记录日志、检查条件等
END;

2. 数据变更日志的记录与追踪

2.1如何具体创建person的日志表

代码语言：javascript代码运行次数：0运行复制

-- 创建一个名为 person_log 的表，用于记录人员变更日志
CREATE TABLE person_log (
  id INT(11) NOT NULL AUTO_INCREMENT,  -- 日志记录的唯一标识，自动递增
  event_type VARCHAR(50) NOT NULL,      -- 事件类型，例如 INSERT、UPDATE 等
  event_time DATETIME NOT NULL,         -- 事件发生的时间
  person_id INT(11) NOT NULL,           -- 相关人员的 ID
  person_name VARCHAR(100) NOT NULL,    -- 相关人员的姓名
  person_age INT(11) NOT NULL,          -- 相关人员的年龄
  PRIMARY KEY (id)                       -- 将 id 列设置为主键
);

-- 创建一个触发器，当在 person 表中插入新记录后执行
CREATE TRIGGER trigger_insert_person
AFTER INSERT ON person                    -- 在 person 表插入操作后触发
FOR EACH ROW                              -- 每插入一行都会执行触发器
BEGIN
    -- 记录变更日志，将插入事件的信息写入 person_log 表
    INSERT INTO person_log (event_type, event_time, person_id, person_name, person_age)
    VALUES ('INSERT', NOW(), NEW.id, NEW.name, NEW.age);  -- 使用 NEW 关键字获取新插入行的数据
END;

效果图：

3. 触发器与存储过程的对比与选择

触发器与存储过程的对比与选择详解说明表 触发器

• 自动触发：
• 触发器是与数据操作紧密关联的，当满足触发条件时自动执行，无需手动调用
• 数据驱动：
• 触发器通过对数据库中的特定表进行监视，对数据操作作出响应，可以在插入、更新或删除数据时执行相应的操作
• 适用范围：
• 适用于需要在数据操作发生时自动执行的简单逻辑，如验证、约束、日志记录等。触发器通常较轻量级，用于处理特定表上的少量逻辑

存储过程

• 手动调用：
• 存储过程是一段可由应用程序手动调用的代码块，需要显式调用才能执行
• 逻辑复杂性：
• 存储过程适用于较为复杂的业务逻辑，可以包含条件判断、循环、事务控制等复杂逻辑结构
• 参数和返回值：
• 存储过程可以接受参数和返回结果，使其更加灵活和可配置，适合处理多样化的业务需求
• 应用层控制：
• 存储过程通常由应用程序来调用和管理，可以作为一个整体被多个应用程序共享和复用

选择

• 触发器
• 适合在数据库层面上处理与数据操作紧密相关的简单逻辑。对于无需手动干预、并且随着数据操作自动执行的逻辑，触发器是一个好的选择
• 存储过程
• 适合处理较为复杂的业务逻辑，需要手动控制执行时机，并且可能需要参数传递和结果返回的情况

4.触发器的性能和注意事项

触发器的性能和注意事项详解说明表 性能影响

• 触发器会在每次受影响的行上执行操作。如果触发器逻辑复杂或在大量数据插入时使用触发器，可能会对性能产生负面影响。
• 因此，在编写触发器时，应尽量保持逻辑简洁，并评估其对性能的影响

死锁风险

• 触发器可能引发死锁问题，特别是在涉及多个表之间的触发器操作时。
• 确保在触发器中避免无限循环或引发额外的锁定操作，以防止死锁发生

触发器顺序

• 如果数据库中存在多个触发器，触发器的执行顺序可能会影响结果。
• 要确保触发器的执行顺序符合预期，可以使用CREATE TRIGGER语句的BEFORE或AFTER关键字进行调整

触发器的影响范围

• 触发器是与表相关联的，且在表上的特定操作（例如插入、更新和删除）时触发。
• 请确保了解触发器的触发条件和适用范围，以免意外触发或未触发触发器

权限

• 创建和管理触发器需要适当的权限。
• 确保对数据库和表具有足够的权限来创建和执行触发器

调试和测试

• 在编写和使用触发器之前，进行充分的调试和测试是非常重要的。
• 确保触发器的逻辑正确，并验证其与其他操作的交互是否符合预期

本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。 原始发表：2024-10-18，如有侵权请联系 cloudcommunity@tencent 删除前往查看mysql存储过程基础日志数据

本文标签： 2024 Mysql基础与进阶操作系列之MySQL触发器详解（20）作者LJS你个小黑子这都还学不会嘛你是真爱粉嘛真是的 以后请别侮辱我家鸽鸽