MySQL触发器详解保证入土

简介

数据库触发器是一种在特定数据操作（如插入、更新或删除）发生时自动执行的动作。本文将介绍MySQL触发器的基础知识和使用方法。

一、MySQL触发器基础

触发器分类

MySQL触发器可以按照执行时机和数据操作类型进行分类。下面是MySQL触发器的分类：

1. 按执行时机分类：

   • BEFORE触发器：在数据操作之前执行触发器逻辑。
   • AFTER触发器：在数据操作之后执行触发器逻辑。

2. 按数据操作类型分类：

   • INSERT触发器：在插入数据之前或之后执行触发器逻辑。
   • UPDATE触发器：在更新数据之前或之后执行触发器逻辑。
   • DELETE触发器：在删除数据之前或之后执行触发器逻辑。

触发器的分类有助于根据具体需求选择合适的触发器类型。例如，BEFORE INSERT触发器可以用于在插入数据之前验证数据完整性，AFTER UPDATE触发器可以用于记录数据变更日志等。

基础常用关键字

以下是一些MySQL触发器关键字的示例用法：
在MySQL中，触发器常用的关键字包括：

1. BEFORE：表示在数据操作之前执行触发器逻辑。
2. AFTER：表示在数据操作之后执行触发器逻辑。
3. FOR EACH ROW：表示针对每一行数据执行触发器逻辑。
4. BEGIN和END：用于定义触发器的逻辑代码块。
5. NEW：代表要插入、更新或删除的新数据引用。可以通过NEW来访问新值。
6. OLD：代表要更新或删除的旧数据引用。只能在UPDATE和DELETE触发器中使用。
7. SET：用于设置变量的值，可用于对NEW或OLD的字段进行赋值操作。
8. IF和THEN：用于条件判断，控制触发器逻辑的执行流程。
9. INSERT、UPDATE、DELETE：分别表示插入、更新和删除操作的关键字，可用于在相应类型的触发器中执行相应的逻辑。

这些关键字可以根据具体需求在触发器逻辑中使用，用于控制数据操作的时机、访问指定的数据和执行相应的操作。

1. BEFORE:

   CREATE TRIGGER `trigger_before_insert` BEFORE INSERT ON `mytable`
   FOR EACH ROW
   BEGIN
     -- 在插入数据之前执行的逻辑
   END;
   1
   2
   3
   4
   5

2. AFTER:

   CREATE TRIGGER `trigger_after_update` AFTER UPDATE ON `mytable`
   FOR EACH ROW
   BEGIN
     -- 在更新数据之后执行的逻辑
   END;
   1
   2
   3
   4
   5

3. FOR EACH ROW:

   CREATE TRIGGER `trigger_for_each_row` AFTER DELETE ON `mytable`
   FOR EACH ROW
   BEGIN
     -- 针对每一行删除操作执行的逻辑
   END;
   1
   2
   3
   4
   5

4. BEGIN和END：

   CREATE TRIGGER `trigger_multiple_statements` AFTER INSERT ON `mytable`
   FOR EACH ROW
   BEGIN
     DECLARE var INT;
     SET var = NEW.age + 10;
     IF (var > 50) THEN
       -- 逻辑语句1
     ELSE
       -- 逻辑语句2
     END IF;
   END;
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11

5. NEW：

   CREATE TRIGGER `trigger_new_value` BEFORE INSERT ON `mytable`
   FOR EACH ROW
   BEGIN
     SET NEW.name = UPPER(NEW.name);
   END;
   1
   2
   3
   4
   5

6. OLD：

   CREATE TRIGGER `trigger_old_value` BEFORE UPDATE ON `mytable`
   FOR EACH ROW
   BEGIN
     IF (OLD.age < NEW.age) THEN
       -- 逻辑语句
     END IF;
   END;
   1
   2
   3
   4
   5
   6
   7

7. SET：

   CREATE TRIGGER `trigger_set_value` AFTER INSERT ON `mytable`
   FOR EACH ROW
   BEGIN
     SET @total = @total + NEW.quantity;
   END;
   1
   2
   3
   4
   5

8. IF和THEN：

   CREATE TRIGGER `trigger_if_then` BEFORE DELETE ON `mytable`
   FOR EACH ROW
   BEGIN
     IF (OLD.age > 30) THEN
       -- 逻辑语句1
     ELSE
       -- 逻辑语句2
     END IF;
   END;
   1
   2
   3
   4
   5
   6
   7
   8
   9

这些示例演示了触发器关键字的一些常用用法。你可以根据自己的需求进行调整和扩展以满足特定的业务逻辑。

1. 定义触发器

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
END;
1
2
3
4
5
6

其中关键部分包括：

• trigger_name：触发器的名称，你可以根据需要自定义。

• {BEFORE | AFTER}：指定触发器在数据操作之前（BEFORE）或之后（AFTER）执行。

• {INSERT | UPDATE | DELETE}：指定触发器在哪种类型的数据操作时触发，可以是插入数据（INSERT）、更新数据（UPDATE）或删除数据（DELETE）。

• table_name：指定触发器所属的表名。

• FOR EACH ROW：确保触发器逻辑应用于每一行受影响的数据。

• BEGIN 和 END：定义了触发器逻辑的开始和结束位置，你可以在其中编写相应的代码来实现需要的业务逻辑。

这个语法模板可以帮助你创建符合需求的触发器，并根据实际情况编写适当的触发器逻辑。

2. 创建和删除触发器

• 创建触发器：

CREATE TRIGGER trigger_name ...
1

• 删除触发器：

DROP TRIGGER IF EXISTS trigger_name;
1

3. 执行时机和条件

• BEFORE触发器：在数据操作之前执行。
• AFTER触发器：在数据操作之后执行。
• FOR EACH ROW：对每一行数据执行触发器逻辑。

二、MySQL触发器的使用场景

1. 数据完整性约束

插入触发器

-- 创建插入触发器
CREATE TABLE `person` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `age` INT(11) NOT NULL,
  PRIMARY KEY (`id`)
);
DELIMITER //
CREATE TRIGGER `trigger_insert_mytable` BEFORE INSERT ON `person`
FOR EACH ROW
BEGIN
  -- 触发器逻辑，可以在此处编写需要执行的代码
  
  -- 示例：将插入的数据的name字段转换为大写
  SET NEW.name = UPPER(NEW.name);
END //
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

更新触发器

CREATE TRIGGER trigger_name
BEFORE UPDATE ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
END;
1
2
3
4
5
6

删除触发器

CREATE TRIGGER trigger_name
BEFORE DELETE ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
END;
1
2
3
4
5
6

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

-- 创建person的日志表
CREATE TABLE person_log (
  id INT(11) NOT NULL AUTO_INCREMENT,
  event_type VARCHAR(50) NOT NULL,
  event_time DATETIME NOT NULL,
  person_id INT(11) NOT NULL,
  person_name VARCHAR(100) NOT NULL,
  person_age INT(11) NOT NULL,
  PRIMARY KEY (id)
);
-- 创建person的触发器
CREATE TRIGGER trigger_insert_person
AFTER INSERT ON person
FOR EACH ROW
BEGIN
    -- 记录变更日志
    INSERT INTO person_log (event_type, event_time, person_id, person_name, person_age)
    VALUES ('INSERT', NOW(), NEW.id, NEW.name, NEW.age);
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

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

触发器和存储过程是在数据库中实现复杂业务逻辑的两种不同方式。下面是它们的对比和选择方面的考虑：

触发器：

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

存储过程：

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

选择：

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

三、触发器的性能和注意事项

在使用触发器时，有几个注意点需要考虑：

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

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

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

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

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

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

四、总结

MySQL触发器是数据库中重要且有用的功能。通过本文的学习，你应该掌握了MySQL触发器的基础知识和使用方法，并了解了触发器对数据库性能的影响以及使用触发器的技巧和注意事项。