目前mysql的变更闪回一个比较常见的实现基于binlog反转来将误操作反向执行。如果操作的本身涉及到触发器会不会导致触发器变更的重复执行呢？

通常是不会的，只要我们反正后执行的还是BINLOG SQL就不会触发trigger。

我们首先看下普通SQL语句是如何触发trigger的，如下是一个before insert trigger的调用堆栈：

#0  sp_head::execute_trigger (this=0x7fff400fd4f0, thd=0x7fff40000da0, db_name=..., table_name=..., grant_info=0x7fff400fab90) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sp_head.cc:2426

#1  0x000000000340e9f0 in Trigger::execute (this=0x7fff400faaa0, thd=0x7fff40000da0) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/trigger.cc:387

#2  0x0000000003410681 in Trigger_chain::execute_triggers (this=0x7fff400fae80, thd=0x7fff40000da0) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/trigger_chain.cc:144

#3  0x00000000034063f4 in Table_trigger_dispatcher::process_triggers (this=0x7fff4013c178, thd=0x7fff40000da0, event=TRG_EVENT_INSERT, action_time=TRG_ACTION_BEFORE, old_row_is_record1=true)

    at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/table_trigger_dispatcher.cc:560

#4  0x000000000318dffa in call_before_insert_triggers (thd=0x7fff40000da0, table=0x7fff40160c10, event=TRG_EVENT_INSERT, insert_into_fields_bitmap=0x7fffa82f1010)

    at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_base.cc:9752

#5  0x0000000003186d01 in fill_record_n_invoke_before_triggers (thd=0x7fff40000da0, optype_info=0x7fffa82f1230, fields=..., values=..., table=0x7fff40160c10, event=TRG_EVENT_INSERT, num_fields=1,

    raise_autoinc_has_expl_non_null_val=true, is_row_changed=0x0) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_base.cc:9856

#6  0x0000000003950cec in Sql_cmd_insert_values::execute_inner (this=0x7fff400f2350, thd=0x7fff40000da0) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_insert.cc:598

#7  0x00000000032f5a7b in Sql_cmd_dml::execute (this=0x7fff400f2350, thd=0x7fff40000da0) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_select.cc:584

#8  0x00000000032756f1 in mysql_execute_command (thd=0x7fff40000da0, first_level=true) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_parse.cc:3575

#9  0x000000000327a832 in dispatch_sql_command (thd=0x7fff40000da0, parser_state=0x7fffa82f2b10) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_parse.cc:5207

#10 0x0000000003270e2d in dispatch_command (thd=0x7fff40000da0, com_data=0x7fffa82f3c00, command=COM_QUERY) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_parse.cc:1957

#11 0x000000000326ef3b in do_command (thd=0x7fff40000da0) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_parse.cc:1352

#12 0x0000000003450a55 in handle_connection (arg=0xa88b960) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/conn_handler/connection_handler_per_thread.cc:302

#13 0x0000000004fff3f0 in pfs_spawn_thread (arg=0xa880320) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/storage/perfschema/pfs.cc:2942

#14 0x00007ffff7bc16ba in start_thread (arg=0x7fffa82f4700) at pthread_create.c:333

#15 0x00007ffff5e1241d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

可以看出是在Sql_cmd_insert_values::execute_inner函数中调用fill_record_n_invoke_before_triggers触发的，如下是代码示例

 598       if (fill_record_n_invoke_before_triggers(

 599               thd, &info, insert_field_list, *values, insert_table,

 600               TRG_EVENT_INSERT, insert_table->s->fields, true, nullptr)) {

 601         assert(thd->is_error());

 602         

 607         has_error = true;

 608         break;

 609       }

而如果执行的是BINLOG SQL的话，实际上一个insert的binlog event再通过BINLOG SQL的时候走的是SQLCOM_BINLOG_BASE64_EVENT语句类型，而不是一个INSERT语句。

4478     case SQLCOM_BINLOG_BASE64_EVENT: {

4479       mysql_client_binlog_statement(thd);

4480       break;

4481     }

在mysql_client_binlog_statement中实际上是模拟SQL线程进行binlog row event的执行：

159   

162   int err = 0;

163   Relay_log_info *rli = thd->rli_fake;

...

270       ev->thd = thd;

271       

279       err = ev->apply_event(rli);

...

我们知道mysql row format主从复制的从库SQL线程也是调用的row event的apply_event进行应用的；从库应用binlog不会触发trigger，因为在主库trigger执行产生的变更也记录到同一个事务的binlog event中，如果从库应用binlog会触发trigger的话，主从数据就不一致了。

所以只要进行binlog反转后依然执行的是BINLOG SQL，那么就不会重复触发trigger。

但如果是反转成了SQL语句的话，就会触发trigger。

延伸来说，对于基于binlog的增量同步工具，例如otter等，读取binlog后在目标端执行的是普通SQL语句，如果要同步的表是带trigger的话，就会重复触发trigger的执行。