MySQL INSERT INTO SELECT 重复插入问题求助：从item表向price表插入不重复数据

首先明确你的表结构和需求：

你有两个MySQL表，结构如下：

CREATE TABLE IF NOT EXISTS `item` ( 
    `item_id` INT NOT NULL AUTO_INCREMENT, 
    `item_code` VARCHAR(45) NOT NULL, 
    `item_name` VARCHAR(80) NOT NULL, 
    `price` DECIMAL(16,2) NOT NULL, 
    PRIMARY KEY (`item_id`), 
    UNIQUE INDEX `item_code_UNIQUE` (`item_code` ASC) 
) ENGINE = InnoDB CHARSET=utf8;
INSERT INTO item VALUES (1, '02', 'item A', '10.00'), (2, '03', 'item B', '20.00'), (3, '04', 'item C', '30.00');

CREATE TABLE IF NOT EXISTS `price` ( 
    `price_id` INT NOT NULL AUTO_INCREMENT, 
    `price` DECIMAL(16,2) NOT NULL, 
    `item_id` INT NOT NULL, 
    PRIMARY KEY (`price_id`), 
    INDEX `fk_item_price_item1_idx` (`item_id` ASC), 
    CONSTRAINT `fk_item_price_item1` FOREIGN KEY (`item_id`) REFERENCES `item` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE 
) ENGINE = InnoDB CHARSET=utf8;

需求是将item表中的price和item_id插入到price表中，且同一item_id对应的price不能重复插入，但你尝试的SQL执行后出现了大量重复记录：

INSERT IGNORE INTO `price` (`price`,`item_id`) 
SELECT DISTINCT `i`.`price`, `i`.`item_id` 
FROM `item` `i` 
INNER JOIN `price` `p` ON `p`.`item_id` = `i`.`item_id`;

为什么你的SQL会产生重复？ #

问题出在两个核心点：

1. INNER JOIN逻辑错误：你用item表和price表做内连接，这会把已经存在于price表中的item_id对应的记录再次查出来，每次执行这个SQL，都会把这些已有的记录重新插入一遍。
2. 缺少唯一约束：price表目前只有price_id作为主键，没有针对(item_id, price)的复合唯一约束，所以INSERT IGNORE无法识别重复——因为只有主键冲突才会被忽略，而你插入的记录主键price_id是自增的，不会重复，所以IGNORE根本起不到作用。

解决方案（两种可选，均无需使用ON DUPLICATE KEY UPDATE） #

方案1：给price表添加唯一约束（推荐）

先给price表添加一个复合唯一键，从根源上确保同一个item_id和price的组合只能出现一次：

ALTER TABLE `price` ADD UNIQUE KEY `unique_item_price` (`item_id`, `price`);

之后可以用两种方式插入数据：

-- 方式1：用INSERT IGNORE，当存在重复的(item_id, price)时自动忽略插入
INSERT IGNORE INTO `price` (`price`, `item_id`)
SELECT `price`, `item_id` FROM `item`;

-- 方式2：用NOT EXISTS筛选不存在的记录，逻辑更直观
INSERT INTO `price` (`price`, `item_id`)
SELECT i.price, i.item_id
FROM `item` i
WHERE NOT EXISTS (
    SELECT 1 FROM `price` p
    WHERE p.item_id = i.item_id AND p.price = i.price
);

添加唯一约束后，不仅这次插入不会重复，后续任何尝试插入重复(item_id, price)的操作都会被数据库阻止，彻底避免重复问题。

方案2：不修改表结构，用NOT EXISTS过滤重复

如果你无法修改表结构，可以直接用NOT EXISTS筛选出还未在price表中出现的(item_id, price)组合：

INSERT INTO `price` (`price`, `item_id`)
SELECT i.price, i.item_id
FROM `item` i
WHERE NOT EXISTS (
    SELECT 1 FROM `price` p
    WHERE p.item_id = i.item_id AND p.price = i.price
);

这个SQL会先检查item表中的每条记录对应的(item_id, price)是否已经存在于price表中，只有不存在的才会被插入，从而避免重复。

验证效果 #

执行上述正确的SQL后，price表只会保留每个item_id对应的一条price记录：

+----------+-------+---------+
| price_id | price | item_id |
+----------+-------+---------+
| 1        | 10.00 | 1       |
| 2        | 20.00 | 2       |
| 3        | 30.00 | 3       |
+----------+-------+---------+

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