mysql ON DUPLICATE KEY UPDATE的替代解决方案

Question

我有两个MySql表。一个呼叫item，另一个呼叫price。

item表数据如下:

+---------+-----------+-----------+-------+
| item_id | item_code | item_name | price |
+---------+-----------+-----------+-------+
|       1 | 02        | item A    | 10.00 |
|       2 | 03        | item B    | 20.00 |
|       3 | 04        | item C    | 30.00 |
+---------+-----------+-----------+-------+ 

价目表结构如下:

CREATE TABLE `price` (
`price_id` int(11) NOT NULL AUTO_INCREMENT,
`price` decimal(16,2) NOT NULL,
`valid_from` date DEFAULT NULL,
`valid_until` date DEFAULT NULL,
`item_id` int(11) NOT NULL,
PRIMARY KEY (`price_id`),
KEY `fk_item_price_item1_idx` (`item_id`),
CONSTRAINT `fk_item_price_item1` FOREIGN KEY (`item_id`) REFERENCES `item` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

我真正需要的是从item表中获取数据，并将其插入或更新到price表中。

如果根据item_id，商品表的价格不等于商品表的价格，则应更新价格表中与该item_id相关的数据。(这里valid_until字段更新。)然后将新的价格插入到价格表中。

我知道这可以用ON DUPLICATE KEY UPDATE查询完成。但是我不能对price表中的item_id / price字段使用唯一索引或约束。因此，我将对更新和插入使用两个单独的查询，如下所示。

UPDATE item i
JOIN price p ON i.item_id = p.item_id
AND p.price <> i.price
AND CURDATE () BETWEEN p.valid_from AND COALESCE (p.valid_until, '9999-12-31')
SET p.valid_until = CURDATE () - INTERVAL 1 DAY
INSERT IGNORE INTO `price` (`price`,`item_id`, `valid_from`)
SELECT DISTINCT `i`.`price`, `i`.`item_id`, CURDATE()
FROM `item` `i`
INNER JOIN `price` `p` ON `p`.`item_id` = `i`.`item_id`;

更新查询工作没有任何问题。但是插入查询不能正常工作。这意味着即使存在相同的价格，也将插入新行。

希望有人能帮帮我。

Answer 1

编写SELECT查询，查找item中与UPDATE查询不匹配的行，并插入它们

INSERT INTO price (`price`,`item_id`, `valid_from`)
SELECT DISTINCT i.price, i.item_id, CURDATE()
FROM item AS i
LEFT JOIN price AS p ON i.item_id = p.item_id AND CURDATE () BETWEEN p.valid_from AND COALESCE (p.valid_until, '9999-12-31')
WHERE p.price_id IS NULL