复制父子表数据时如何保留照片与条目的映射关系？

问题根因 #

你现在用的SQL直接复用了原条目的document_id，但这个字段是唯一的——新条目不能重复使用这个值，而照片肯定是绑定在原document_id上的，所以新插入的条目自然关联不上照片。

分步解决办法 #

假设你有个存储照片的表（比如叫item_photo），里面用document_id和item表关联，按以下两步操作：

1. 复制item数据，同时留存新旧document_id的对应关系 #

先插入新的item条目，把原document_id和新生成的document_id存到临时表里：

-- 创建临时表存储映射关系
CREATE TEMP TABLE item_map (
    old_doc_id INT,
    new_doc_id INT
);

-- 插入新item并记录映射
INSERT INTO item (storage_id, price, quantity, description)
SELECT 10, price, quantity, description
FROM item
WHERE quantity >= 10 AND price <= 100
RETURNING document_id AS new_doc_id, (SELECT document_id FROM item WHERE id = inserted.id) AS old_doc_id
INTO item_map;

注：如果item表的主键不是id而是其他字段（比如item_id），把上面的id换成对应的主键字段即可，核心是找到原条目的document_id。

2. 复制照片并关联到新的document_id #

利用临时表的映射关系，把原照片复制一份绑定到新的item条目上：

INSERT INTO item_photo (document_id, photo_url, photo_desc)
SELECT im.new_doc_id, ip.photo_url, ip.photo_desc
FROM item_photo ip
JOIN item_map im ON ip.document_id = im.old_doc_id;

更简洁的写法（支持CTE的数据库可用） #

如果你的数据库是PostgreSQL、MySQL 8.0+这类支持WITH子句的，无需临时表，一步完成操作：

WITH inserted_items AS (
    INSERT INTO item (storage_id, price, quantity, description)
    SELECT 10, price, quantity, description
    FROM item
    WHERE quantity >= 10 AND price <= 100
    RETURNING document_id AS new_doc_id, (SELECT document_id FROM item WHERE id = inserted.id) AS old_doc_id
)
INSERT INTO item_photo (document_id, photo_url, photo_desc)
SELECT ii.new_doc_id, ip.photo_url, ip.photo_desc
FROM item_photo ip
JOIN inserted_items ii ON ip.document_id = ii.old_doc_id;

关键注意事项 #

• 新插入item时，document_id必须由数据库自动生成（比如自增、序列），不要手动赋值，否则会触发唯一性约束报错。
• 如果照片表是通过item的主键（比如item_id）关联的，只需把映射关系换成新旧主键的对应即可，逻辑完全一致。

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