如何将MySQL一张表的字段插入另一张表且避免重复数据？

问题背景 #

需要将users表的id（作为user_id）和partner_id字段插入user_favorites表，且避免产生重复数据。尝试过INSERT IGNORE、REPLACE、INSERT ... ON DUPLICATE KEY UPDATE方法均未生效，例如执行以下REPLACE语句仍无法解决重复问题：

REPLACE INTO user_favorites
(user_id, partner_id)
SELECT id, partner_id FROM users

两张表的建表语句如下：

users表 #

create table local.users
(
    id                       int auto_increment,
    name                     varchar(255)             null,
    email                    varchar(255)             null,
    password                 varchar(255)             null,
    partner_id               int                      null,
    
    constraint users_email_unique
        unique (email),
    constraint users_id_uindex
        unique (id)
)
alter table local.users
    add primary key (id);

user_favorites表 #

create table local.user_favorites
(
    id         int auto_increment,
    user_id    int       null,
    partner_id int       null,
    constraint user_favorites_id_uindex
        unique (id),
    constraint user_favorites_partners_id_fk
        foreign key (partner_id) references local.partners (id)
            on update cascade on delete cascade,
    constraint user_favorites_users_id_fk
        foreign key (user_id) references local.users (id)
            on update cascade on delete cascade
); 
alter table local.user_favorites
    add primary key (id);

问题原因 #

之前的去重插入方法失效，核心原因是user_favorites表缺少user_id和partner_id的联合唯一约束。现有的唯一约束仅针对自增主键id，而每次插入新行时id都是自动生成的唯一值，不会触发任何冲突，因此INSERT IGNORE、REPLACE等依赖唯一键冲突判断的方法无法生效。

正确实现步骤 #

1. 添加联合唯一约束 #

首先给user_favorites表添加user_id和partner_id的联合唯一索引，确保同一用户和合作伙伴的组合不会重复：

ALTER TABLE local.user_favorites
ADD UNIQUE KEY unique_user_partner (user_id, partner_id);

2. 选择合适的插入语句 #

添加约束后，即可使用以下方法实现去重插入：

方法一：使用INSERT IGNORE

忽略冲突的行，不会抛出错误，直接跳过重复的(user_id, partner_id)组合：

INSERT IGNORE INTO local.user_favorites (user_id, partner_id)
SELECT id, partner_id FROM local.users
WHERE partner_id IS NOT NULL; -- 可选：过滤partner_id为空的无效数据

方法二：使用INSERT ... ON DUPLICATE KEY UPDATE

遇到冲突时执行自定义的更新操作（此处示例为无操作，仅占位）：

INSERT INTO local.user_favorites (user_id, partner_id)
SELECT id, partner_id FROM local.users
WHERE partner_id IS NOT NULL
ON DUPLICATE KEY UPDATE user_id = user_id; -- 无实际更新，仅触发冲突处理逻辑

如果需要在冲突时更新其他字段（比如更新时间），可以替换为对应的字段赋值，例如：

ON DUPLICATE KEY UPDATE updated_at = NOW();

内容的提问来源于stack exchange，提问作者Ostet