MySQL触发器简介

备注:测试数据库版本为MySQL 8.0

这个blog我们来聊聊MySQL触发器。
触发器是在表中数据发生更改时自动触发执行的，特殊的存储过程。
存储过程是手工进行调用的，触发器是自动的。
当然，对于频繁更新或数据量比较大的表，慎用触发器，在一定程度上，会影响性能。

一.语法相关

触发器语法:

CREATE
    [DEFINER = user]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

BEFORE – 在触发时间之前执行操作
AFTER – 在触发时间之后执行操作

FOR EACH ROW – 操作影响的每一行都会做执行一次触发程序

NEW – 标记新纪录，例如NEW.ID 表示表中新数据行的ID
OLD – 标记旧纪录，例如OLD.ID 表示表中旧数据行的ID

二.案例相关

需求: 对于用户表，进行删除的数据，都把历史数据保留到历史表中

测试数据:

create table user_info(id int not null auto_increment,name varchar(200),id_number varchar(50),primary key(id));
insert into user_info values (1,'张三','420123199001011234');
insert into user_info values (2,'李四','420123199001011235');
insert into user_info values (3,'王五','420123199001011236');

-- 用户历史数据表
create table user_info_history(id int not null auto_increment,type varchar(50),user_id int,name varchar(200),id_number varchar(50),primary key(id));

触发器代码:

delimiter //

create trigger trg_del_userinfo before delete on user_info
  for each row
begin
  
  insert into user_info_history(user_id,name,id_number,create_time)
  select old.id,old.name,old.id_number,now();

end;

//

delimiter ;

执行结果:

mysql> delimiter //
mysql>
mysql> create trigger trg_del_userinfo before delete on user_info
    ->   for each row
    -> begin
    ->
    ->   insert into user_info_history(user_id,name,id_number,create_time)
    ->   select old.id,old.name,old.id_number,now();
    ->
    -> end;
    ->
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> delimiter ;
mysql>
mysql>
mysql> select * from user_info;
+----+--------+--------------------+
| id | name   | id_number          |
+----+--------+--------------------+
|  1 | 张三   | 420123199001011234 |
|  2 | 李四   | 420123199001011235 |
|  3 | 王五   | 420123199001011236 |
+----+--------+--------------------+
3 rows in set (0.00 sec)

mysql> select * from user_info_history;
Empty set (0.00 sec)

-- 执行删除操作
mysql> delete from user_info where id = 1;
Query OK, 1 row affected (0.00 sec)
-- 查询user_info表，发现数据被删除
mysql> select * from user_info;
+----+--------+--------------------+
| id | name   | id_number          |
+----+--------+--------------------+
|  2 | 李四   | 420123199001011235 |
|  3 | 王五   | 420123199001011236 |
+----+--------+--------------------+
2 rows in set (0.00 sec)
-- 查询历史表，已经保留了历史数据，触发器是自动触发的，无需手工调用
mysql> select * from user_info_history;
+----+------+---------+--------+--------------------+---------------------+
| id | type | user_id | name   | id_number          | create_time         |
+----+------+---------+--------+--------------------+---------------------+
|  1 | NULL |       1 | 张三   | 420123199001011234 | 2020-05-25 22:26:54 |
+----+------+---------+--------+--------------------+---------------------+
1 row in set (0.00 sec)

mysql>