如何完善SQL实现catalog_category_entity_text表数据复制与覆盖新增

场景与需求 #

我们有表catalog_category_entity_text，列结构如下：

value_id - attribute_id - store_id - entity_id - value

示例数据行：

6032 - 47 - 1 - 98 - VALUE TEXT EXAMPLE

需要实现的操作：

• 取出表中store_id=1且attribute_id=47的所有记录
• 将这些记录的store_id改为5，entity_id和value直接复制原数据，value_id用下一个可用值
• 若store_id=5的对应记录（按attribute_id+store_id+entity_id组合匹配）已存在则覆盖，不存在则新增

预期生成的记录行：

6033 - 47 - 5 - 98 - VALUE TEXT EXAMPLE

现有SQL的问题 #

当前编写的SQL逻辑完全倒置，无法实现需求：

INSERT INTO catalog_category_entity_text(value, entity_id, attribute_id, store_id)
SELECT value, entity_id, 47 AS attribute_id, 1 AS store_id
FROM catalog_category_entity_text
WHERE store_id = 5

完善后的解决方案 #

1. 先确保表有唯一约束 #

要实现"存在则覆盖"的逻辑，需要给表添加唯一键，用来判定记录是否存在：

ALTER TABLE catalog_category_entity_text
ADD UNIQUE KEY idx_attr_store_entity (attribute_id, store_id, entity_id);

2. 实现复制/覆盖的核心SQL #

INSERT INTO catalog_category_entity_text (attribute_id, store_id, entity_id, value)
SELECT 47 AS attribute_id, 5 AS store_id, entity_id, value
FROM catalog_category_entity_text
WHERE store_id = 1 AND attribute_id = 47
ON DUPLICATE KEY UPDATE
value = VALUES(value);

逻辑说明 #

• SELECT子句：精准筛选出store_id=1、attribute_id=47的目标记录，同时把store_id替换为5，原样复制entity_id和value
• ON DUPLICATE KEY UPDATE：当插入的记录与唯一键（attribute_id+store_id+entity_id）匹配时，自动执行更新操作，这里更新value字段为新值
• value_id无需手动指定，只要它是自增主键，数据库会自动分配下一个可用值

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