MySQL触发器中的“ FOR EACH ROW”如何工作？
实际上，“FOREACHROW”意味着每个匹配的行都将被更新或删除。换句话说，我们可以说触发器没有应用于每一行，它只是说要为每个受影响的表行执行触发器主体。我们可以通过以下示例来说明这一点-
示例
在此示例中，我们将创建两个表Sample和Sample_rowaffected，如下所示-
mysql> Create table Sample(id int, value varchar(20)); mysql> Insert into Sample(id, value) values(100, 'same'),(101, 'Different'),(500, 'excellent'),(501, 'temporary'); 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); 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 ;
-> //
mysql> Delimiter ;现在，以下查询将从表“Sample”中删除一些值，并且已删除行数的计数将存储在@count用户变量中-
mysql> Delete from Sample WHERE ID >=500; 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)
在上面的示例的帮助下，很明显，“FOREACHROW”对于每个匹配的行都意味着被更新或删除。