我正在尝试运行一个查询,以便它将插入或更新重复项。我正在为重复项使用唯一索引,但我似乎无法让它工作。它还增加了新记录。希望一些新的目光能够指出我的问题。谢谢。
这是我的架构
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`);
这是我正在测试的查询:
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'
EDIT我解决了 custno 丢失的问题,但插入重复项时仍然遇到问题。我创建了一个sqlfiddle http://sqlfiddle.com/#!2/d92b79/1。这次我用 2 个 NULL 值进行测试,但仍然不行...这是我的架构还是我的查询仍然存在问题?