MySQL UPDATE ON DUPLICATE KEY仅插入 [英] Mysql UPDATE ON DUPLICATE KEY only inserting

问题描述

我正在尝试运行查询，以便它将重复插入或更新.我正在为副本使用唯一索引，但似乎无法使它正常工作.它仍然添加新记录.希望能有一些新的观点可以指出我的问题.谢谢.

I am trying to run a query so that it will insert or update on duplicate. I am using a unique index for the duplicate but I cannot seem to get it to work. It still adds new records. Hoping some fresh eyes can point out my issue. Thanks.

这是我的模式

CREATE TABLE IF NOT EXISTS `pricing_puchasing` (
  `custno` varchar(6) DEFAULT NULL COMMENT 'customer code',
  `recipe` varchar(15) DEFAULT NULL,
  `item` varchar(120) NOT NULL COMMENT 'Item Code from dProduce',
  `unit_weight` double(12,4) DEFAULT NULL,
  `case_cost` double(12,4) DEFAULT NULL COMMENT 'Projected cost of item',
  `cost_per_lb` double(12,4) NOT NULL,
  `projected_price` float(12,3) DEFAULT NULL COMMENT 'projected Price',
  `projected_margin` float(12,3) DEFAULT NULL COMMENT 'Projected Margin',
  `trend` tinyint(1) DEFAULT NULL COMMENT 'Trend status 1=up, 0=down',
  `note` varchar(255) DEFAULT NULL COMMENT 'Note about the data',
  `week_of` date NOT NULL COMMENT 'Week of this data',
  `approved` tinyint(1) DEFAULT NULL COMMENT 'approval flag',
  `last_edited_from` varchar(20) NOT NULL COMMENT 'identifier to know where it was last saved from',
  `saved_at` datetime NOT NULL COMMENT 'Date time of save/update',
  `saved_by` int(11) NOT NULL COMMENT 'Created by user ID'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Stores pricing purchasing data';


ALTER TABLE `pricing_puchasing`
 ADD UNIQUE KEY `custno_recipe_item_week_of` (`custno`,`recipe`,`item`,`week_of`), ADD KEY `custno` (`custno`);

这是我正在测试的查询:

This is the query I am testing:

INSERT INTO pricing_puchasing 
    (recipe,item,unit_weight,case_cost,cost_per_lb,trend,note,week_of,saved_at,saved_by,approved,last_edited_from) 
VALUES 
    ('APPLEGRRAW','APPLEGRRAW',40.00,100.01,2.50,0,'','2014-07-28',NOW(),1,NULL,'purchasing') 
ON DUPLICATE KEY UPDATE 
    unit_weight=40.00,case_cost=100.01,cost_per_lb=2.50,trend=0,note='',saved_at=NOW(),saved_by=1,approved=NULL,last_edited_from='purchasing'

编辑 我解决了custno丢失的问题，但是插入重复项仍然有问题.我创建了一个 sqlfiddle .这次我正在测试2个NULL值，但还是不行...这是我的架构还是我的查询问题?

EDIT I fixed the issue with custno missing but I still am having issue with it inserting duplicates. I created a sqlfiddle. This time I am testing with 2 NULL values and still a no go... Is it my schema or is this still an issue with my query?

推荐答案

NULL值不被视为唯一".唯一索引的第一列是custno，您没有在INSERT中提供该列的值，因此将默认值NULL用作该列的值.

A NULL value is not consider to be "unique". The first column in the unique index is custno, you aren't providing a value for that column in the INSERT, so the default value of NULL is used as the value for that column.

由于NULL值不被认为是唯一的，因此INSERT不会引发重复的键异常.

Because of the NULL value is not considered to be unique, the INSERT will not throw a duplicate key exception.

使用custno列的非NULL值进行尝试.

Try it with a non-NULL value for the custno column.

此外，您可以在语句的UPDATE部分中使用特殊的VALUES()函数，以引用INSERT中提供的值.

Also, you can use the special VALUES() function in the UPDATE portion of the statement, to reference the value that was supplied in the INSERT.

ON DUPLICATE KEY UPDATE 
    unit_weight=VALUES(unit_weight),case_cost=VALUES(case_cost)

http://dev.mysql.com/doc/refman/5.5/zh-CN/miscellaneous-functions.html#function_values

这篇关于MySQL UPDATE ON DUPLICATE KEY仅插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！