含外键约束的解决方案_数据传输服务(DTS)-阿里云帮助中心

本文为您介绍如何规避源库存在外键约束FOREIGN KEY REFERENCES ON DELETE CASCADE或FOREIGN KEY REFERENCES ON UPDATE CASCADE所引发的数据不一致问题。

背景信息

在使用了外键约束FOREIGN KEY REFERENCES ON DELETE CASCADE或FOREIGN KEY REFERENCES ON UPDATE CASCADE的MySQL数据库中，若删除或更新父表的数据，则子表中关联的数据将会自动被删除或更新。在MySQL数据库中，这些自动删除或更新子表数据的操作不会被记录到二进制日志（Binlog）中。

影响

在使用数据传输服务DTS（Data Transmission Service）进行含增量任务的数据迁移、数据同步、数据订阅时，DTS会因无法从Binlog中获取到这些删除或更新操作，而无法迁移、同步、订阅该删除或更新操作，从而可能会导致数据不一致或数据丢失的问题（例如，目标数据库中子表的数据量大于父表）。

涉及的链路

源库的数据库类型为MySQL、PolarDB for MySQL、Mariadb、PolarDB-X 1.0、PolarDB-X 2.0或OceanBase（MySQL）。

解决方案

本文为您提供手动管理删除或更新操作和使用触发器两种方案，以规避该外键约束所引发的数据不一致问题。

重要

解决方案中提供的SQL语句为在数据管理DMS中执行的命令，在其他MySQL客户端中执行的SQL命令可能会有所差异。

示例数据

父表

-- 创建父表
CREATE TABLE parent (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 插入一些数据
INSERT INTO parent (id, name) VALUES (1, 'Parent 1'), (2, 'Parent 2');

子表

-- 创建含外键约束FOREIGN KEY REFERENCES ON DELETE CASCADE的子表
CREATE TABLE child (
    id INT PRIMARY KEY,
    parent_id INT,
    name VARCHAR(50),
    FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- 插入一些数据
INSERT INTO child (id, parent_id, name) VALUES (1, 1, 'Child 1'), (2, 1, 'Child 2'), (3, 2, 'Child 3');

操作步骤

手动管理删除或更新操作

请先手动删除或更新相关子表的数据，然后再删除或更新父表的数据，以确保所有删除或更新操作均被记录在Binlog中。示例如下：

-- 删除父表记录时手动删除子表记录
DELETE FROM child WHERE parent_id = 1;
DELETE FROM parent WHERE id = 1;

-- 更新父表记录时手动更新子表记录
UPDATE parent SET id = 3 WHERE id = 2;
UPDATE child SET parent_id = 3 WHERE parent_id = 2;

使用触发器

使用MySQL的触发器，在删除或更新父表数据时自动触发对相应子表数据的删除或更新操作，以确保所有删除或更新操作均被记录在Binlog中。示例如下：

-- 创建触发器
DELIMITER //

CREATE TRIGGER delete_parent_trigger
BEFORE DELETE ON parent
FOR EACH ROW
BEGIN
    DELETE FROM child WHERE parent_id = OLD.id;
END //

CREATE TRIGGER update_parent_trigger
AFTER UPDATE ON parent
FOR EACH ROW
BEGIN
    UPDATE child SET parent_id = NEW.id WHERE parent_id = OLD.id;
END //

DELIMITER ;

-- 插入一些数据
INSERT INTO parent (id, name) VALUES (3, 'Parent 3');
INSERT INTO child (id, parent_id, name) VALUES (4, 3, 'Child 4');

-- 删除父表数据
DELETE FROM parent WHERE id = 3;

-- 插入一些数据并进行更新
INSERT INTO parent (id, name) VALUES (4, 'Parent 4');
INSERT INTO child (id, parent_id, name) VALUES (5, 4, 'Child 5');
UPDATE parent SET id = 5 WHERE id = 4;

总结

通过手动管理删除或更新操作，或者使用数据库的触发器，确保在DTS实例运行过程中，MySQL中的外键约束FOREIGN KEY REFERENCES ON DELETE CASCADE或FOREIGN KEY REFERENCES ON UPDATE CASCADE所需执行的删除或更新操作可以被准确地记录到Binlog中。