On DUPLICATE KEY UPDATE上的Mysql重复输入错误

I'm using a memory table. It has several ids and counter all data is integers. My code updates the counter by 1 if the data exists or creates a line with counter=1 if not.

我正在使用内存表。它有几个id和计数器,所有数据都是整数。如果数据存在,我的代码将计数器更新为1,否则创建一个counter = 1的行。

The query I use is:

我使用的查询是:

INSERT INTO linked_mem
        ( id1, id2, id31, id4 cnt)
        VALUES (31316, 0, 557158967, 261470594, 1)
        ON DUPLICATE KEY UPDATE cnt= cnt+1

Occasionally (about 5% of inserts) I get " Duplicate entry '[key numbers]' for key 1

偶尔(大约5%的插入)我得到键1的“重复输入'[键号]”

What could be the problem? Isn't the ON DUPLICATE KEY UPDATE part supposed to handle the duplicate key?

可能是什么问题呢?是不是ON DUPLICATE KEY UPDATE部分应该处理重复键?

Update: adding create table of the real table

更新:添加真实表的创建表

CREATE TABLE `linked_mem` (
  `li_sid` int(10) unsigned NOT NULL default '0',
  `li_id1` int(10) unsigned NOT NULL default '0',
  `li_cid1` int(10) unsigned NOT NULL default '0',
  `li_id2` int(10) unsigned NOT NULL default '0',
  `li_cid2` int(10) unsigned NOT NULL default '0',
  `cnt` int(10) unsigned NOT NULL default '1',
  `li_filter` int(10) unsigned NOT NULL default '0',
  `li_group` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`li_id1`,`li_sid`,`li_cid1`,`li_cid2`,`li_group`,`cnt`,`li_id2`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1

5 个解决方案

#1

This can happen if you update a field that is marked UNIQUE and the second key violation occurs on UPDATE.

如果更新标记为UNIQUE的字段并且在UPDATE上发生第二次密钥冲突,则会发生这种情况。

Update:

From your table structure I can see that it's exactly your case.

从你的表结构我可以看出它正是你的情况。

That's what happens:

这就是发生的事情:

INSERT INTO linked_mem
        ( id1, id2, id31, id4 cnt)
        VALUES (31316, 0, 557158967, 261470594, 1)

-- inserts

INSERT INTO linked_mem
        ( id1, id2, id31, id4 cnt)
        VALUES (31316, 0, 557158967, 261470594, 1)

-- updates `cnt` to 2

INSERT INTO linked_mem
        ( id1, id2, id31, id4 cnt)
        VALUES (31316, 0, 557158967, 261470594, 1)

-- tries again to update `cnt` to 2 and fails

Remove cnt from your PRIMARY KEY definition.

从PRIMARY KEY定义中删除cnt。

#2

cnt is in the primary key, maybe that's what causing the error/inability to UPDATE it.

cnt在主键中,可能是导致错误/无法更新它的原因。

#3

Are you sure that the primary key is correct? Using this primary key identifies a line also by the value of cnt, which is supposed to be a counter.

你确定主键是否正确?使用此主键也通过cnt的值标识一行,cnt应该是一个计数器。

I've not tested this, but I think the following query will give the error if you start with an empty table.

我没有测试过这个,但我认为如果你从一个空表开始,下面的查询将给出错误。

INSERT INTO linked_mem
    ( id1, id2, id31, id4, cnt)
VALUES 
    (1, 1, 1, 1, 1),                  // (1, 1, 1, 1, 1) added
    (1, 1, 1, 1, 1),                  // (1, 1, 1, 1, 1) -> (1, 1, 1, 1, 2)
    (1, 1, 1, 1, 1),                  // (1, 1, 1, 1, 1) added (now two rows)
    (1, 1, 1, 1, 1)                   // error
ON DUPLICATE KEY UPDATE cnt = cnt+1

at the fourth row, the (1, 1, 1, 1, 1) would be updated to (1, 1, 1, 1, 2), but this already exists.

在第四行,(1,1,1,1,1)将更新为(1,1,1,1,2),但这已经存在。

#4

It's hard to tell with your uninformative column names, but that primary key is so wide that it looks pretty darn useless. What is it actually doing, and why was that set of columns chosen? Is there a better choice? I'd guess the other posters are right, and your update is violating the implicit uniqueness constrant of the PK; with the count being one of your PK columns, that's pretty much to be expected.

你的无名列名很难说,但主键太宽了,看起来很无用。它实际上在做什么,为什么选择这组列?有更好的选择吗?我猜其他海报是对的,你的更新违反了PK的隐含唯一性;计数是你的PK列之一,这几乎是预料之中的。

#5

I had the same problem.

我有同样的问题。

Using ON DUPLICATE KEY UPDATE and still having the error : Duplicate entry

使用ON DUPLICATE KEY UPDATE并仍然有错误:重复输入

It was caused by another column having a unique key index and one row having an empty value in it.

它是由另一列具有唯一键索引并且其中一行具有空值引起的。

#1