MySQL中如何批量更新oc_product与oc_seo_url关联数据？

前置准备 #

1. 备份数据：操作主键更新风险极高，先备份两张表避免数据丢失：

   -- 备份oc_product
   CREATE TABLE oc_product_backup LIKE oc_product;
   INSERT INTO oc_product_backup SELECT * FROM oc_product;
   
   -- 备份oc_seo_url
   CREATE TABLE oc_seo_url_backup LIKE oc_seo_url;
   INSERT INTO oc_seo_url_backup SELECT * FROM oc_seo_url;
   

2. 检查model唯一性：因为product_id是主键，必须确保model字段无重复值，否则更新会触发主键冲突：

   SELECT model, COUNT(*) FROM oc_product GROUP BY model HAVING COUNT(*) > 1;
   

   如果查询返回结果，先修改重复的model值，保证每个model唯一后再继续。

批量更新步骤 #

用事务包裹两个更新操作，确保数据一致性：

START TRANSACTION;

-- 第一步：同步更新oc_seo_url的query字段
UPDATE oc_seo_url su
JOIN oc_product p ON SUBSTRING_INDEX(su.query, '=', -1) = p.product_id
SET su.query = CONCAT('product_id=', p.model)
WHERE su.query LIKE 'product_id=%';

-- 第二步：更新oc_product的product_id为model值
UPDATE oc_product SET product_id = model WHERE model != product_id;

COMMIT;

适配复杂query场景（如带其他参数） #

如果oc_seo_url的query字段包含多个参数（例如product_id=1&category_id=5），用正则表达式替换更可靠（MySQL 8.0+支持）：

START TRANSACTION;

UPDATE oc_seo_url su
JOIN oc_product p ON REGEXP_SUBSTR(su.query, 'product_id=(\\d+)', 1, 1, '', 1) = p.product_id
SET su.query = REGEXP_REPLACE(su.query, 'product_id=\\d+', CONCAT('product_id=', p.model))
WHERE su.query REGEXP 'product_id=\\d+';

UPDATE oc_product SET product_id = model WHERE model != product_id;

COMMIT;

为什么不用循环存储过程？ #

循环逐行更新效率低，且容易出现部分更新失败的情况。用JOIN批量更新是更高效、更安全的方式，一次操作即可完成所有数据同步，同时事务能保证两张表的更新要么全部成功，要么全部回滚。

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