定时炸弹—MySQL触发器
一、触发器概述
触发器是与表有关的数据库对象,在满足指定条件时触发,并执行触发器中定义的语句集合,这样可以令某些操作之间的一致性得到协调。
触发器的特性
1、什么条件触发: insert delete update;
2、什么时候触发: 在增删改前(before)或者后(after);
3、触发频率:针对每一行的执行
4、触发器定义在表上,附着在表上。
也就是由事件来触发某个操作,事件包括insert语句,delete语句和update语句;
注意点:尽量少使用触发器,不建议使用
假设触发器触发每次执行1s,insert table 500条数据,那么就需要触发500次触发器,光是触发器执行的时间就花费了500s,而insert 500条数据一共是1s,那么这个insert的效率就非常低了。因此我们特别需要注意的一点是触发器的begin end;之间的语句的执行效率一定要高,资源消耗要小。
触发器尽量少的使用,因为不管如何,它还是很消耗资源,如果使用的话要谨慎的使用,确定它是非常高效的:触发器是针对每一行的;对增删改非常频繁的表上切记不要使用触发器,因为它会非常消耗资源。
二、创建触发器
create trigger 触发器名 before | after 触发事件
on 表名 for each row 执行语句
当为bofore时,表示在增删改之前先执行触发器中定义的代码,然后在执行增删改操作。
1、创建只有一个执行语句的触发器
mysql> create table timelog(
-> id int(11) primary key auto_increment,
-> savetime varchar(50) not null
-> );
Query OK, 0 rows affected (0.17 sec)
mysql> create table studentinfo(
-> id int(11) primary key auto_increment,
-> name varchar(50) not null
-> );
Query OK, 0 rows affected (0.12 sec)
mysql> create trigger now_time before insert
-> on studentinfo for each row
-> insert into timelog(savetime) values(now());
Query OK, 0 rows affected (0.24 sec)
mysql> insert into studentinfo(name) values('AFeng');
Query OK, 1 row affected (0.10 sec)
mysql> select * from timelog;
+----+---------------------+
| id | savetime |
+----+---------------------+
| 1 | 2018-03-21 12:46:16 |
+----+---------------------+
1 row in set (0.00 sec)
触发器的这种操作相当于on后面所跟的表上定义了一个事件,当该事件发生时,执行所定义的SQL语句。
2、创建具有多个执行语句的触发器
create trigger 触发器名 before | after 触发事件
on 表名 for each row
begin
执行语句列表
end
mysql> create table timelog(
-> id int(11) primary key auto_increment,
-> now_time varchar(50) not null
-> );
Query OK, 0 rows affected (0.15 sec)
mysql> create table studentinfo(
-> id int(11) primary key auto_increment,
-> name varchar(50) not null
-> );
Query OK, 0 rows affected (0.18 sec)
mysql> create table timeinfo(
-> id int(11) primary key auto_increment,
-> info varchar(50) not null
-> );
Query OK, 0 rows affected (0.14 sec)
mysql> delimiter //
mysql> create trigger delete_time_info before delete
-> on studentinfo for each row
-> begin
-> insert into timelog(now_time) values(now());
-> insert into timeinfo(info) values('deleteach');
-> end
-> //
Query OK, 0 rows affected (0.20 sec)
mysql> delimiter ;
mysql> insert into studentinfo(name) values('AFeng');
Query OK, 1 row affected (0.08 sec)
mysql> delete from studentinfo where id = 1;
Query OK, 1 row affected (0.10 sec)
mysql> select * from timelog;
+----+---------------------+
| id | now_time |
+----+---------------------+
| 1 | 2018-03-21 13:34:04 |
+----+---------------------+
1 row in set (0.00 sec)
mysql> select * from timeinfo;
+----+-----------+
| id | info |
+----+-----------+
| 1 | deleteach |
+----+-----------+
1 row in set (0.00 sec)
三、查看触发器
1、show triggers
mysql> show triggers\G;
*************************** 1. row ***************************
Trigger: delete_time_info
Event: DELETE
Table: studentinfo
Statement: begin
insert into timelog(now_time) values(now());
insert into timeinfo(info) values('deleteach');
end
Timing: BEFORE
Created: NULL
sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.01 sec)
2、在information_schema.triggers表中查看触发器信息。
mysql> select * from information_schema.triggers
-> where trigger_name = 'delete_time_info'\G;
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: learnmysql
TRIGGER_NAME: delete_time_info
EVENT_MANIPULATION: DELETE
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: learnmysql
EVENT_OBJECT_TABLE: studentinfo
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: begin
insert into timelog(now_time) values(now());
insert into timeinfo(info) values('deleteach');
end
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
SQL_MODE: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
1 row in set (0.06 sec)
四、删除触发器
1、drop trigger [if exists] trigger_name
mysql> drop trigger delete_time_info;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from information_schema.triggers
-> where trigger_name = 'delete_time_info'\G;
Empty set (0.06 sec)
如果不需要某个触发器时一定要将这个触发器删除,以免造成不必要的麻烦。
五、触发器的执行顺序(before触发器,表操作,after触发器)
mysql> CREATE TRIGGER before_in BEFORE INSERT ON
-> studentinfo FOR EACH ROW
-> INSERT INTO timeinfo(info) VALUES("before");
-> //
Query OK, 0 rows affected (0.17 sec)
mysql> CREATE TRIGGER after_in AFTER INSERT ON
-> studentinfo FOR EACH ROW
-> INSERT INTO timeinfo(info) VALUES("after");
-> //
Query OK, 0 rows affected (0.20 sec)
mysql> INSERT INTO studentinfo(name) VALUES("AFeng")//
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM timeinfo//
+----+-----------+
| id | info |
+----+-----------+
| 1 | deleteact |
| 2 | before |
| 3 | after |
+----+-----------+
3 rows in set (0.02 sec)
查询结果表明:before触发器首先被激活,然后才是after触发器被激活。
六、触发器中的new和old
mysql中定义了new和old,用来表示与触发器所关联的表(也是相当于事件源)中触发了触发器的那一行数据,也就是新增加,删除或者更新的那一行数据,来引用触发器中发生变化的记录内容。
①在INSERT型触发器中,NEW用来表示将要(BEFORE)或已经(AFTER)插入的新数据;
②在UPDATE型触发器中,OLD用来表示将要或已经被修改的原数据,NEW用来表示将要或已经修改为的新数据;
③在DELETE型触发器中,OLD用来表示将要或已经被删除的原数据;
mysql> select * from user;
+----+-----+
| id | age |
+----+-----+
| 1 | 11 |
| 2 | 22 |
| 3 | 33 |
+----+-----+
3 rows in set (0.00 sec)
mysql> delimiter //
mysql> create trigger young before update on user
-> for each row
-> begin
-> if new.age < 10 then set new.age = 18;
-> elseif new.age > 60 then set new.age = 28;
-> end if;
-> end;//
Query OK, 0 rows affected (0.20 sec)
mysql> update user set age = 8 where id = 1 //
Query OK, 1 row affected (0.13 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user//
+----+-----+
| id | age |
+----+-----+
| 1 | 18 |
| 2 | 22 |
| 3 | 33 |
+----+-----+
3 rows in set (0.00 sec)
参考链接
http://www.cnblogs.com/geaozhang/p/6819648.html
https://www.chenyudong.com/archives/database-trigger-new-old-value-understand.html