Let's talk about the basics of MySQL triggers and events

This article brings you relevant knowledge about triggers and events in MySQL, including precautions for using triggers, viewing and deleting events, precautions for using events, etc. I hope it will be helpful to everyone.

Trigger

We may have the following requirements when using MySQL:

• Automatically verify the data before inserting or updating data into the t1 table. It is required that the value of the m1 column must be between 1~10 time, the verification rules are as follows:

  • If the value of the m1 column of the inserted record is less than 1, insert it as 1 .
  • If the value of column m1 is greater than 10, insert as 10.

• After inserting a record into the t1 table, this record is automatically inserted into the t2 table.

That is, we may need to have the MySQL server automatically execute some additional statements before and after adding, deleting, or modifying records in the table. This is the application scenario of the so-called trigger.

Creating a trigger

Let’s take a look at the statement that defines a trigger:

CREATE TRIGGER 触发器名
{BEFORE|AFTER}
{INSERT|DELETE|UPDATE}
ON 表名
FOR EACH ROW
BEGIN
    触发器内容
END

Tips:

Statements wrapped by braces `{}` and separated by vertical bars `|` indicate that a value must be selected from the given options, such as `{BEFORE|AFTER}` means that it must be in `BEFORE Choose one between ` and `AFTER`.

{BEFORE|AFTER} represents the timing of trigger content execution. Their meanings are as follows:

{INSERT|DELETE|UPDATE}表示具体的语句，MySQL中目前只支持对INSERT、DELETE、UPDATE这三种类型的语句设置触发器。

FOR EACH ROW BEGIN ... END表示对具体语句影响的每一条记录都执行我们自定义的触发器内容：

• 对于INSERT语句来说，FOR EACH ROW影响的记录就是我们准备插入的那些新记录。

• 对于DELETE语句和UPDATE语句来说，FOR EACH ROW影响的记录就是符合WHERE条件的那些记录（如果语句中没有WHERE条件，那就是代表全部的记录）。

小贴士： 如果触发器内容只包含一条语句，那也可以省略BEGN、END这两个词儿。

因为MySQL服务器会对某条语句影响的所有记录依次调用我们自定义的触发器内容，所以针对每一条受影响的记录，我们需要一种访问该记录中的内容的方式，MySQL提供了NEW和OLD两个单词来分别代表新记录和旧记录，它们在不同语句中的含义不同：

• 对于INSERT语句设置的触发器来说，NEW代表准备插入的记录，OLD无效。
• 对于DELETE语句设置的触发器来说，OLD代表删除前的记录，NEW无效。
• 对于UPDATE语句设置的触发器来说，NEW代表修改后的记录，OLD代表修改前的记录。

现在我们可以正式定义一个触发器了：

mysql> delimiter $
mysql> CREATE TRIGGER bi_t1
    -> BEFORE INSERT ON t1
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.m1 < 1 THEN
    ->         SET NEW.m1 = 1;
    ->     ELSEIF NEW.m1 > 10 THEN
    ->         SET NEW.m1 = 10;
    ->     END IF;
    -> END $
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql>

我们对t1表定义了一个名叫bi_t1的触发器，它的意思就是在对t1表插入新记录之前，对准备插入的每一条记录都会执行BEGIN ... END之间的语句，NEW.列名表示当前待插入记录指定列的值。现在t1表中一共有4条记录：

mysql> SELECT * FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)

mysql>

我们现在执行一下插入语句并再次查看一下t1表的内容：

mysql> INSERT INTO t1(m1, n1) VALUES(5, 'e'), (100, 'z');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|   10 | z    |
+------+------+
6 rows in set (0.00 sec)

mysql>

这个INSERT语句影响的记录有两条，分别是(5, 'e')和(100, 'z')，这两条记录将分别执行我们自定义的触发器内容。很显然(5, 'e')被成功的插入到了t1表中，而(100, 'z')插入到表中后却变成了(10, 'z')，这个就说明我们的bi_t1触发器生效了！

小贴士： 我们上边定义的触发器名`bi_t1`的`bi`是`before insert`的首字母缩写，`t1`是表名。虽然对于触发器的命名并没有什么特殊的要求，但是习惯上还是建议大家把它定义我上边例子中的形式，也就是`bi_表名`、`bd_表名`、`bu_表名`、`ai_表名`、`ad_表名`、`au_表名`的形式。

上边只是举了一个对INSERT语句设置BEFORE触发器的例子，对DELETE和UPDATE操作设置BEFORE或者AFTER触发器的过程是类似的，就不赘述了。

查看和删除触发器

查看当前数据库中定义的所有触发器的语句：

SHOW TRIGGERS;

查看某个具体的触发器的定义：

SHOW CREATE TRIGGER 触发器名;

删除触发器：

DROP TRIGGER 触发器名;

这几个命令太简单了，就不举例子了啊～

触发器使用注意事项

• 触发器内容中不能有输出结果集的语句。

  比方说：

  mysql> delimiter $
  mysql> CREATE TRIGGER ai_t1
      -> AFTER INSERT ON t1
      -> FOR EACH ROW
      -> BEGIN
      ->     SELECT NEW.m1, NEW.n1;
      -> END $
  ERROR 1415 (0A000): Not allowed to return a result set from a trigger
  mysql>

  Copy after login

  显示的ERROR的意思就是不允许在触发器内容中返回结果集！

• 触发器内容中NEW代表记录的列的值可以被更改，OLD代表记录的列的值无法更改。

  NEW代表新插入或着即将修改后的记录，修改它的列的值将影响INSERT和UPDATE语句执行后的结果，而OLD代表修改或删除之前的值，我们无法修改它。比方说如果我们非要这么写那就会报错的：

  mysql> delimiter $
  mysql> CREATE TRIGGER bu_t1
      -> BEFORE UPDATE ON t1
      -> FOR EACH ROW
      -> BEGIN
      ->     SET OLD.m1 = 1;
      -> END $
  ERROR 1362 (HY000): Updating of OLD row is not allowed in trigger
  mysql>

  Copy after login

  可以看到提示的错误中显示在触发器中OLD代表的记录是不可被更改的。

• 在BEFORE触发器中，我们可以使用SET NEW.列名 = 某个值的形式来更改待插入记录或者待更新记录的某个列的值，但是这种操作不能在AFTER触发器中使用，因为在执行AFTER触发器的内容时记录已经被插入完成或者更新完成了。

  比方说如果我们非要这么写那就会报错的：

  mysql> delimiter $
  mysql>     CREATE TRIGGER ai_t1
      ->     AFTER INSERT ON t1
      ->     FOR EACH ROW
      ->     BEGIN
      ->         SET NEW.m1 = 1;
      ->     END $
  ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger
  mysql>

  Copy after login

  可以看到提示的错误中显示在AFTER触发器中是不允许更改NEW代表的记录的。

• 如果我们的BEFORE触发器内容执行过程中遇到了错误，那这个触发器对应的具体语句将无法执行；如果具体的操作语句执行过程中遇到了错误，那与它对应的AFTER触发器的内容将无法执行。

  小贴士： 对于支持事务的表，不论是执行触发器内容还是具体操作语句过程中出现了错误，会把这个过程中所有的语句都回滚。当然，作为小白的我们并不知道啥是个事务，啥是个回滚，这些进阶内容都在《MySQL是怎样运行的：从根儿上理解MySQL》中呢～

事件

有时候我们想让MySQL服务器在某个时间点或者每隔一段时间自动地执行一些语句，这时候就需要去创建一个事件。

创建事件

创建事件的语法如下：

CREATE EVENT 事件名
ON SCHEDULE
{
    AT 某个确定的时间点| 
    EVERY 期望的时间间隔 [STARTS datetime][END datetime]
}
DO
BEGIN
    具体的语句
END

事件支持两种类型的自动执行方式：

1. 在某个确定的时间点执行。

   比方说：

   CREATE EVENT insert_t1_event
   ON SCHEDULE
   AT '2019-09-04 15:48:54'
   DO
   BEGIN
       INSERT INTO t1(m1, n1) VALUES(6, 'f');
   END

   Copy after login

   我们在这个事件中指定了执行时间是'2019-09-04 15:48:54'，除了直接填某个时间常量，我们也可以填写一些表达式：

   CREATE EVENT insert_t1
   ON SCHEDULE
   AT DATE_ADD(NOW(), INTERVAL 2 DAY)
   DO
   BEGIN
       INSERT INTO t1(m1, n1) VALUES(6, 'f');
   END

   Copy after login

   其中的DATE_ADD(NOW(), INTERVAL 2 DAY)表示该事件将在当前时间的两天后执行。

2. 每隔一段时间执行一次。

   比方说：

   CREATE EVENT insert_t1
   ON SCHEDULE
   EVERY 1 HOUR
   DO
   BEGIN
       INSERT INTO t1(m1, n1) VALUES(6, 'f');
   END

   Copy after login

   其中的EVERY 1 HOUR表示该事件将每隔1个小时执行一次。默认情况下，采用这种每隔一段时间执行一次的方式将从创建事件的事件开始，无限制的执行下去。我们也可以指定该事件开始执行时间和截止时间：

   CREATE EVENT insert_t1
   ON SCHEDULE
   EVERY 1 HOUR STARTS '2019-09-04 15:48:54' ENDS '2019-09-16 15:48:54'
   DO
   BEGIN
       INSERT INTO t1(m1, n1) VALUES(6, 'f');
   END

   Copy after login

   如上所示，该事件将从'2019-09-04 15:48:54'开始直到'2019-09-16 15:48:54'为止，中间每隔1个小时执行一次。

   小贴士： 表示事件间隔的单位除了HOUR，还可以用YEAR、QUARTER、MONTH、DAY、HOUR、 MINUTE、WEEK、SECOND、YEAR_MONTH、DAY_HOUR、DAY_MINUTE、DAY_SECOND、HOUR_MINUTE、HOUR_SECOND、MINUTE_SECOND这些单位，根据具体需求选用我们需要的时间间隔单位。

在创建好事件之后我们就不用管了，到了指定时间，MySQL服务器会帮我们自动执行的。

查看和删除事件

查看当前数据库中定义的所有事件的语句：

SHOW EVENTS;

查看某个具体的事件的定义：

SHOW CREATE EVENT 事件名;

删除事件：

DROP EVENT 事件名;

这几个命令太简单了，就不举例子了啊～

事件使用注意事项

默认情况下，MySQL服务器并不会帮助我们执行事件，除非我们使用下边的语句手动开启该功能：

mysql> SET GLOBAL event_scheduler = ON;
Query OK, 0 rows affected (0.00 sec)

mysql>

小贴士： event_scheduler其实是一个系统变量，它的值也可以在MySQL服务器启动的时候通过启动参数或者通过配置文件来设置event_scheduler的值。这些所谓的系统变量、启动参数、配置文件的各种东东并不是我们小白现在需要掌握的，大家忽略它们就好了～

推荐学习：mysql视频教程

The above is the detailed content of Let's talk about the basics of MySQL triggers and events. For more information, please follow other related articles on the PHP Chinese website!