时间戳

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

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

问题描述

我有两张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表的价格不等于item表的价格，则需要更新price表中与该item_id相关的数据。（此处valid_until字段已更新。）然后应将新价格插入此价格表中。

我知道这可以通过ON DUPLICATE KEY UPDATE查询来完成。但我不能item_id / price对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`;

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

希望有人可以帮助我。

标签： mysqlsql

解决方案

编写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

推荐阅读