使用基于日志的 mysql 增量更新并关闭 ddl 开关时，对源表添加字段、删除字段、调整字段顺序导致字段值错乱

测试用例

```sql
DROP table source, target;

CREATE TABLE `source` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name1` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `name2` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `target` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name1` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `name2` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 配置、开始增量同步

INSERT INTO source(name1, name2) VALUES('1', '2');
SELECT SLEEP(1) ;
SELECT * FROM target;

-- 调整字段顺序
ALTER TABLE source CHANGE name2 name2 varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL AFTER id;

INSERT INTO source(name1, name2) VALUES('1', '2');
SELECT SLEEP(1) ;
SELECT * FROM target;
```

![增量配置](https://bucket-zhaobs.oss-cn-beijing.aliyuncs.com/image/2024image-20240924143629475.png)

![字段映射](https://bucket-zhaobs.oss-cn-beijing.aliyuncs.com/image/2024image-20240924143647186.png)

第一次插入结果

![第一次插入结果](https://bucket-zhaobs.oss-cn-beijing.aliyuncs.com/image/2024image-20240924143740792.png)

第二次插入结果

![第二次插入结果](https://bucket-zhaobs.oss-cn-beijing.aliyuncs.com/image/2024image-20240924143807469.png)

可以看出第二次插入时，target 表中的 name1、name2 字段值反了。

AE86/DBSyncer

内容风险标识

评论 (0)

AE86/DBSyncer .gitee-modal { width: 500px !important; }

内容风险标识