迁移account表中brand类型数据后，如何重建brand与contacts的关联关系？

1. 创建brands_contacts中间表 #

首先执行SQL创建中间表，设置组合主键避免重复关联：

CREATE TABLE brands_contacts (
    brand_id INT NOT NULL,
    contact_id INT NOT NULL,
    PRIMARY KEY (brand_id, contact_id)
);

2. 迁移原关联数据到新表 #

根据你迁移brand记录的方式，选择对应SQL语句：

情况一：brand表id直接沿用原accounts表中type=brand记录的id #

INSERT INTO brands_contacts (brand_id, contact_id)
SELECT ac.account_id, ac.contact_id
FROM accounts_contacts ac
JOIN accounts a ON ac.account_id = a.id
WHERE a.type = 'brand';

情况二：brand表使用新id，通过original_account_id字段关联原accounts记录 #

INSERT INTO brands_contacts (brand_id, contact_id)
SELECT b.id, ac.contact_id
FROM accounts_contacts ac
JOIN accounts a ON ac.account_id = a.id
JOIN brands b ON a.id = b.original_account_id
WHERE a.type = 'brand';

执行后可验证数据一致性：

-- 对比迁移前后的关联数量
SELECT COUNT(*) FROM brands_contacts;
SELECT COUNT(*) FROM accounts_contacts ac JOIN accounts a ON ac.account_id = a.id WHERE a.type = 'brand';

3. 添加外键约束（推荐） #

为保证数据完整性，给中间表添加外键约束：

ALTER TABLE brands_contacts
ADD CONSTRAINT fk_brand_contact_brand FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_brand_contact_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE;

4. 原数据清理（可选） #

若不再需要原accounts表中type=brand的关联数据，可删除：

DELETE ac FROM accounts_contacts ac
JOIN accounts a ON ac.account_id = a.id
WHERE a.type = 'brand';

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