mysql数据库，外键关联删除主表数据，binlog只记主表binlog

环境：8.0.32

场景一、开启外键约束关联删除时，删除主表记录，binlog中内容解析

1、写入测试数据，并删除主键 id=1，子表id=1一并删除

mysql> create table t1(id int primary key ,name varchar(200));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 select 1,'zs';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t1 select 2,'ls';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t1 select 3,'ww';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> create table t2(id int,mx varchar(200) ,constraint wj foreign key(id) references t1(id) ON UPDATE NO ACTION ON DELETE CASCADE);
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | zs   |
|  2 | ls   |
|  3 | ww   |
+----+------+
3 rows in set (0.00 sec)

mysql> insert into t2 select 4,4;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ceshi`.`t2`, CONSTRAINT `wj` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE CASCADE)
mysql> insert into t2 select 1,1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t2 select 1,2;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t2 select 1,3;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> delete from t1 where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from t2;
Empty set (0.00 sec)

 

 

2、分析binlog

BEGIN
/*!*/;
# at 354
#240909 10:41:26 server id 3306  end_log_pos 411 CRC32 0x4d8ebc49     Table_map: `ceshi`.`t1` mapped to number 131
# has_generated_invisible_primary_key=0
# at 411
#240909 10:41:26 server id 3306  end_log_pos 455 CRC32 0xaa89f9cc     Delete_rows: table id 131 flags: STMT_END_F

BINLOG '
VmDeZhPqDAAAOQAAAJsBAAAAAIMAAAAAAAMABWNlc2hpAAJ0MQACAw8CIAMCAQEAAgP8/wBJvI5N
VmDeZiDqDAAALAAAAMcBAAAAAIMAAAAAAAEAAgAC/wABAAAAAgB6c8z5iao=
'/*!*/;
### DELETE FROM `ceshi`.`t1`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='zs' /* VARSTRING(800) meta=800 nullable=1 is_null=0 */
# at 455
#240909 10:41:26 server id 3306  end_log_pos 486 CRC32 0x9ce4b572     Xid = 77
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

 

 

场景二、关闭外键约束，关联删除时，删除主表记录，binlog中内容解析

1、测试数据如下，关闭外键约束后，删除主表记录，子表数据并没有跟着删除

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  2 | ls   |
|  3 | ww   |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from t2;
Empty set (0.00 sec)

mysql> insert into t2 select 2,2;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t2 select 2,3;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t2 select 2,4;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t1 where id=2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  3 | ww   |
+----+------+
1 row in set (0.00 sec)

mysql> select * from t2;
+------+------+
| id   | mx   |
+------+------+
|    2 | 2    |
|    2 | 3    |
|    2 | 4    |
+------+------+
3 rows in set (0.00 sec)

 

2、分析 binlog

#240909 10:53:51 server id 3306  end_log_pos 2661 CRC32 0xe5698ce8     Query    thread_id=12    exec_time=0    error_code=0
SET TIMESTAMP=1725850431/*!*/;
BEGIN
/*!*/;
# at 2661
#240909 10:53:51 server id 3306  end_log_pos 2718 CRC32 0xcbe15e87     Table_map: `ceshi`.`t1` mapped to number 131
# has_generated_invisible_primary_key=0
# at 2718
#240909 10:53:51 server id 3306  end_log_pos 2762 CRC32 0xab449127     Delete_rows: table id 131 flags: STMT_END_F

BINLOG '
P2PeZhPqDAAAOQAAAJ4KAAAAAIMAAAAAAAMABWNlc2hpAAJ0MQACAw8CIAMCAQEAAgP8/wCHXuHL
P2PeZiDqDAAALAAAAMoKAAAAAIMAAAAAAAMAAgAC/wACAAAAAgBscyeRRKs=
'/*!*/;
### DELETE FROM `ceshi`.`t1`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ls' /* VARSTRING(800) meta=800 nullable=1 is_null=0 */
# at 2762
#240909 10:53:51 server id 3306  end_log_pos 2793 CRC32 0x11115280     Xid = 93
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;