Mysql中的存储程序——触发器、事件

触发器和时间都是mysql服务器会进行自动调用的存储例程，因此我这里放在一起讲了，原理非常简单，但是好像蛮少用的。

触发器

触发器的应用场景应该是相对广泛的，触发器，顾名思义就是在做某一个操作的时候就它就会工作。而mysql的对记录的操作无非就是增删改，因此，触发器会在增删改操作前后自动执行一些额外的语句。这就是触发器的应用场景

 

创建触发器(格式)

create trigger 触发器名
{before | affter}  //在操作 之前 或者 之后
{insert | delete | update}  //只支持增、删、改
on 表名
for each row
begin
    触发器内容  //如果触发器内容只有一条语句，那么前后的begin、end可以省略
end

还有一个定义就是，由于增删改其中的某一个操作会将一条记录划分为两个状态，即新记录和旧记录（NEW、OLD）。因此使用NEW和LOD来代表新纪录和旧记录。

• 在insert语句中的触发器来说，new代表准备插入的记录，old无效
• 在delete语句中的触发器来说，old代表准备删除的记录，new无效
• 在update语句中的触发器来说，new代表修改后的记录，old代表修改前的记录

 

查看所有触发器（当前库）

mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: bf_users
               Event: INSERT
               Table: users
           Statement: begin
if new.id>100 then 
set new.id = 10;
end if;
end
              Timing: BEFORE
             Created: NULL
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_unicode_ci
1 row in set (0.00 sec)

 

查看某触发器定义

mysql> show create trigger bf_users\G
*************************** 1. row ***************************
               Trigger: bf_users
              sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` trigger bf_users
before insert on users
for each row
begin
if new.id>100 then 
set new.id = 10;
end if;
end
  character_set_client: utf8
  collation_connection: utf8_general_ci
    Database Collation: utf8_unicode_ci
1 row in set (0.00 sec)

 

删除触发器

mysql> drop trigger bf_users#
Query OK, 0 rows affected (0.00 sec)

 

 

 

案例（插入前校验ID值）

mysql> delimiter #
mysql> create trigger bf_users  //定义触发器
    -> before insert on users   //定义触发器内容执行的时间
    -> for each row
    -> begin
    -> if new.id>100 then 
    -> set new.id = 10;
    -> end if;
    -> end #
Query OK, 0 rows affected (0.01 sec)
mysql> insert into users(id,userName,passWord,realName) values(110,'zzzz','zzzyyll', 'zyl888')#   //执行插入的语句中，id是大于100的，触发器就会自动运行，将110改成10
Query OK, 1 row affected (0.01 sec)
mysql> select * from users#
+----+----------+-------------+----------+
| id | userName | passWord    | realName |
+----+----------+-------------+----------+
|  1 | root     | rootpw      | rootn    |
|  2 | csop     | !Linkdo#365 | csman    |
|  3 | yz       | zyl0724     | zyl      |
| 10 | zzzz     | zzzyyll     | zyl888   |
+----+----------+-------------+----------+
4 rows in set (0.00 sec)

 

注意：

• 触发器内容不可含有查询语句，否则就会报错
• 触发器内容中，new值是可以被beffer语句更改的，影响的是insert或update的执行结果；而old值是无法修改的，否则就会报错
• 同理，当记录已经修改之后，affter就无法对记录再做出更改了，如果做了就会报错

 

事件

触发器是操作触发的，而事件则是时间触发的。它是按照指定的时间或者时间间隔执行的

创建事件（语法）

create event 事件名
on schedule
{
  at 某时间点 | 
  every 时间间隔[ starts 开始日期 时间 ] [ ends 结束日期 时间]        
}
do
begin
    具体的语句
end

 

查看事件

mysql> show events\G
*************************** 1. row ***************************
                  Db: test
                Name: event_users
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MINUTE
              Starts: 2021-12-25 15:48:24
                Ends: NULL
              Status: ENABLED
          Originator: 0
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_unicode_ci
1 row in set (0.01 sec)

 

查看某事件的定义

mysql> show create event event_users\G
*************************** 1. row ***************************
               Event: event_users
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`root`@`localhost` EVENT `event_users` ON SCHEDULE EVERY 1 MINUTE STARTS '2021-12-25 15:48:24' ON COMPLETION NOT PRESERVE ENABLE DO begin
insert into users(id,userName,passWord,realName) values(5,'1234','234', now());
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_unicode_ci
1 row in set (0.00 sec)

 

删除事件

mysql> drop event event_users#
Query OK, 0 rows affected (0.00 sec)

 

案例（在某个时间点执行）

mysql> create event insert_users
    -> on schedule
    -> at '2021-12-25 15:42:00'   //在该时间点执行下面的插入语句
    -> do
    -> begin
    -> insert into users(id,userName,passWord,realName) values(4,'qwqwqw','wewe', 'ererer');
    -> end #
Query OK, 0 rows affected (0.00 sec)

mysql> select  * from users#
+----+----------+-------------+----------+
| id | userName | passWord    | realName |
+----+----------+-------------+----------+
|  1 | root     | rootpw      | rootn    |
|  2 | csop     | !Linkdo#365 | csman    |
|  3 | yz       | zyl0724     | zyl      |
| 10 | zzzz     | zzzyyll     | zyl888   |
+----+----------+-------------+----------+
4 rows in set (0.00 sec)

mysql> select now()\G
*************************** 1. row ***************************
now(): 2021-12-25 15:41:57  //上面是42分之前表中的记录，最大是3（10不算），3秒钟之后自动执行了事件，在表中增加了一条记录（id为4）
1 row in set (0.00 sec)

mysql> select * from users#
+----+----------+-------------+----------+
| id | userName | passWord    | realName |
+----+----------+-------------+----------+
|  1 | root     | rootpw      | rootn    |
|  2 | csop     | !Linkdo#365 | csman    |
|  3 | yz       | zyl0724     | zyl      |
|  4 | qwqwqw   | wewe        | ererer   |
| 10 | zzzz     | zzzyyll     | zyl888   |
+----+----------+-------------+----------+
5 rows in set (0.00 sec)

 

案例（指定时间频度执行）

create event event_users
on schedule
every 1 minute starts '2021-12-25 16:00:00' ends '2022-1-1 00:00:00'  //在这个时间范围内，每分钟执行一次语句
do
begin
    insert into users(id,userName,passWord,realName) values(5,'1234','234', now());
end #