实际上“FOR EACH ROW”意味着更新或删除的每个匹配行。换句话说,我们可以说触发器并不应用于每一行,它只是说对每个受影响的表行执行触发器主体。我们可以通过以下示例来说明这一点 -
在此示例中,我们创建两个表,Sample 和 Sample_rowaffected,如下 -
mysql> Create table Sample(id int, value varchar(20)); Query OK, 0 rows affected (0.47 sec) mysql> Insert into Sample(id, value) values(100, 'same'),(101, 'Different'),(500, 'excellent'),(501, 'temporary'); Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> Select * from Sample; +------+-----------+ | id | value | +------+-----------+ | 100 | same | | 101 | Different | | 500 | excellent | | 501 | temporary | +------+-----------+ 4 rows in set (0.00 sec) mysql> Create table Sample_rowaffected(id int); Query OK, 0 rows affected (0.53 sec) mysql> Select Count(*) as ‘Rows Affected’ from sample_rowaffected; +---------------+ | Rows Affected | +---------------+ | 0 | +---------------+ 1 row in set (0.10 sec)
现在,我们将创建一个触发器,该触发器在删除表“Sample”中的任何值之前触发,如下所示 -
mysql> Delimiter // mysql> Create trigger trigger_before_delete_sample BEFORE DELETE on Sample -> FOR EACH ROW -> BEGIN -> SET @count = if (@count IS NULL, 1, (@count+1)); -> INSERT INTO sample_rowaffected values (@count); -> END ; -> // Query OK, 0 rows affected (0.15 sec) mysql> Delimiter ;
现在,以下查询将从表“Sample”中删除一些值,并且删除的行数将存储在 @count 用户变量中 -
mysql> Delete from Sample WHERE ID >=500; Query OK, 2 rows affected (0.11 sec) mysql> Select @count; +--------+ | @count | +--------+ | 2 | +--------+ 1 row in set (0.03 sec)
借助以下查询,我们可以检查受删除影响的行的值,插入到sample_rowaffected表中,如下 -
mysql> Select Count(*) as 'Rows Affected' from sample_rowaffected; +---------------+ | Rows Affected | +---------------+ | 2 | +---------------+ 1 row in set (0.00 sec) mysql> Select * from Sample; +------+-----------+ | id | value | +------+-----------+ | 100 | same | | 101 | Different | +------+-----------+ 2 rows in set (0.00 sec)
在上面的示例的帮助下,很明显“FOR EACH ROW”意味着更新或删除的每个匹配行。
以上是MySQL 触发器中的'FOR EACH ROW”如何工作?的详细内容。更多信息请关注PHP中文网其他相关文章!