MySQL插入/重复键失败 [英] MySQL Insert / On duplicate key failure

问题描述

我有一些代码正在尝试使用2列的新值更新表的现有行.该行将始终存在.它具有一个主键以及另一个唯一字段，并且更新指定了唯一字段.我正在使用INSERT/ON DUPLICATE KEY UPDATE进行此操作.

I have some code which is trying to update existing rows of a table with new values for 2 columns. The row will always exist. It has a primary key and also a further unique field, and the update specifies the unique field. I am doing this using INSERT / ON DUPLICATE KEY UPDATE.

但是，这并没有更新行，而是更新了主键为0的行.如果不存在主键为0的行，那么它将完美地工作.

However this was not updating the row, rather it updates a row that happens to exist with a primary key of 0. If no row exists with a primary key of 0 then it works perfectly.

我认为正在发生的事情是，由于我没有指定主键，因此MySQL正在某处为此选择一个值0，然后它用主键0而不是我指定其唯一索引的实际行来更新该行.

I think what is happening is that as I do not specify a primary key MySQL is choosing a value of 0 for this somewhere, then it updates the row with a primary key of 0 rather than the actual row whose unique index I specify.

我确实注意到手册页说:-

I do note that the manual pages say :-

通常，您应该尝试避免在具有多个唯一索引的表上使用ON DUPLICATE KEY UPDATE子句.

该问题可以证明如下:-

The issue can be demonstrated as follows:-

CREATE TABLE fred
(
    id int(11) NOT NULL,    
    code varchar(32) NOT NULL,
    stock int(5) NOT NULL,
    is_active tinyint(1) NOT NULL DEFAULT '1',
    PRIMARY KEY (id),
    UNIQUE KEY code (code)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO fred VALUES
(0, 'a', 123, 1),
(2, 'b', 234, 1),
(3, 'c', 345, 1);

使用此数据，下面的插入操作应更新第二行(主键2， b 代码上的唯一索引)，但相反，它使用零主键和唯一键更新第一行 a 的代码索引.

With this data the following insert should update the 2nd row (primary key 2, unique index on code of b ), but instead it updates the 1st row with the primary key of zero and the unique index on code of a .

INSERT  INTO fred (code,stock,is_active) VALUES ('b',46,68) ON DUPLICATE KEY UPDATE stock = VALUES(stock), is_active = VALUES(is_active);

切换到为此指定主键(或者只是删除主键为0的记录的捷径)，而不是唯一键，我不知道如何彻底解决此问题.

Short of switching to specifying the primary key for this (or a bodge of just removing the record with a primary key of 0), rather than the unique key I am at a loss as to how to cleanly fix this.

推荐答案

以下是您要执行的操作的工作版本:

Here is a working version of what you want to do:

CREATE TABLE fred (
   id int(11) NOT NULL,
   `code` varchar(32) NOT NULL,
   stock int(5) NOT NULL,
   is_active tinyint(1) NOT NULL,
   PRIMARY KEY (`code`),
   UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO fred (id, code, stock, is_active) VALUES
(1, 'a', 123, 1),
(2, 'b', 234, 1),
(3, 'c', 345, 1);

然后要进行更新，您还需要发送ID，但是唯一的主键是代码.

Then to update you need to send the ID as well, but the primary unique key is code.

INSERT  INTO fred (id, code, stock, is_active) VALUES (98, 'b',46,68) ON DUPLICATE KEY UPDATE stock = VALUES(stock), is_active = VALUES(is_active), id = VALUES(id);

这篇关于MySQL插入/重复键失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！