一次 MySQL 老数据同步

先说下业务背景，项目中白名单功能重构。这就涉及到老数据同步问题

表结构

CREATE TABLE `nw_white_list` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`sub_user_id` int(11) DEFAULT NULL,
`platform` tinyint(4) NOT NULL COMMENT '10: 淘宝/天猫 20: 京东',
`channel` tinyint(4) NOT NULL COMMENT '10:哇聊 20: 手动录入 30:批量导入',
`buyer_nick` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '旺旺或咚咚',
`status` tinyint(4) NOT NULL COMMENT '10: 正常，-1 删除',
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_id_platform` (`user_id`,`platform`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

同步问题

• 批量插入
• 需要保持user_id、platform、buyer_nick 唯一
• status 状态标示（软删）
• 如果存在则更新，如果不存在则新增

解决方案

1. 方案一：REPLACE

   REPLACE INTO nw_white_list (user_id, sub_user_id, platform, channel, status, buyer_nick, created_at, updated_at)
VALUES {$insertStr}

这样的话就需要创建user_id、platform、buyer_nick、status 四个字段的唯一索引

2. 方案二：INSERT … ON DUPLICATE KEY UPDATE

	INSERT INTO nw_white_list (user_id, sub_user_id, platform, channel, status, buyer_nick, created_at, updated_at)
VALUES {$insertStr} ON DUPLICATE KEY UPDATE updated_at = now()

这种方法也需要创建user_id、platform、buyer_nick、status 四个字段的唯一索引

3. 方法三：先通过user_id、platform、buyer_nick批量查询，删除已经存在的记录，然后批量插入

选择

因为设计到软删问题，如果设置user_id、platform、buyer_nick、status四个字段的唯一索引，那每次删除就要检查之前是否有删除的记录。最后选用了第三种方案。