MySQL如何实现跨库将表数据插入到另一个库的同结构同名表中

前置要求 #

• 两个数据库部署在同一MySQL实例内，执行SQL的账号同时拥有store1库商品表的读权限、store2库商品表的写权限
• 两个库的商品表（含所有关联附属表）结构、字段顺序、字段类型完全一致
• 提前确认商品表主键/唯一键规则，避免同步时出现约束冲突

核心同步SQL写法 #

假设商品主表表名为goods，主键为goods_id，同步单款指定ID的商品直接使用如下语句：

-- 将123替换为实际需要同步的商品ID
INSERT INTO store2.goods
SELECT * FROM store1.goods
WHERE goods_id = 123;

如果是批量选中多款商品同步，直接修改WHERE条件匹配多个ID即可：

INSERT INTO store2.goods
SELECT * FROM store1.goods
WHERE goods_id IN (123,456,789); -- 括号内填入所有选中的商品ID

常见冲突场景处理 #

同步时如果store2已经存在相同主键/唯一键的商品，直接执行插入会抛出约束冲突错误，可根据业务需求选择对应写法：

• 需求1：已存在的商品跳过不处理，只插入store2中不存在的商品

INSERT IGNORE INTO store2.goods
SELECT * FROM store1.goods
WHERE goods_id IN (/* 待同步商品ID列表 */);

• 需求2：已存在的商品用store1的最新数据覆盖更新，不存在的则正常插入
  使用ON DUPLICATE KEY UPDATE语法，除主键外所有需要同步的字段逐一赋值即可：

INSERT INTO store2.goods
SELECT * FROM store1.goods
WHERE goods_id IN (/* 待同步商品ID列表 */)
ON DUPLICATE KEY UPDATE
  goods_name = VALUES(goods_name),
  price = VALUES(price),
  stock = VALUES(stock),
  goods_desc = VALUES(goods_desc),
  update_time = NOW();
-- 按表实际字段补全剩余需要同步更新的非主键字段即可

注意事项 #

• 商品如果有关联附属表（比如商品规格表goods_sku、商品图片表goods_images、商品分类关联表goods_category_relation），同步主表后需要按相同逻辑同步关联表数据，过滤条件保持和主表一致即可
• 正式执行插入前，建议先执行查询语句校验待同步数据是否符合预期，避免误操作：

SELECT * FROM store1.goods WHERE goods_id IN (/* 待同步商品ID列表 */);

• 如果两个数据库部署在不同MySQL实例，需要先在store1所在实例通过FEDERATED引擎建立store2对应表的映射，映射后写法和同实例完全一致
• 所有同步语句必须携带商品ID过滤条件，禁止无条件全表同步，避免造成非预期的数据覆盖

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