MySQL 即使没有重复条目，MySQL重复条目错误

MySQL duplicate entry error even though there is no duplicate entry

提问by user1763581

I am using MySQL 5.1.56, MyISAM. My table looks like this:

我使用的是 MySQL 5.1.56，MyISAM。我的桌子看起来像这样：

CREATE TABLE IF NOT EXISTS `my_table` (
  `number` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `money` int(11) NOT NULL,
  PRIMARY KEY (`number`,`name`)
) ENGINE=MyISAM;

It contains these two rows:

它包含这两行：

INSERT INTO `my_table` (`number`, `name`, `money`) VALUES
(1, 'S. Name', 150), (2, 'Another Name', 284);

Now I am trying to insert another row:

现在我试图插入另一行：

INSERT INTO `my_table` (`number`, `name`, `money`) VALUES
(2, 'S. Name', 240);

And MySQL just won't insert it while telling me this:

而 MySQL 只是不会在告诉我这个时插入它：

#1062 - Duplicate entry '2-S. Name' for key 'PRIMARY'

I really don't understand it. The primary key is on the first two columns (both of them), so the row I am trying to insert HAS a unique primary key, doesn't it?

我真的不明白。主键在前两列（两列）上，所以我试图插入的行有一个唯一的主键，不是吗？

I tried to repair the table, I tried to optimize the table, all to no avail. Also please note that I cannot change from MyISAM to InnoDB.

我试图修复表，我试图优化表，都无济于事。另请注意，我无法从 MyISAM 更改为 InnoDB。

Am I missing something or is this a bug of MySQL or MyISAM? Thanks.

我是否遗漏了什么，或者这是 MySQL 或 MyISAM 的错误？谢谢。

To summarize and point out where I think is the problem (even though there shouldn't be):Table has primary key on two columns. I am trying to insert a row with a new combination of values in these two columns, but value in column one is already in some row and value in column two is already in another row. But they are not anywhere combined, so I believe this is supposed to work and I am very confused to see that it doesn't.

总结并指出我认为问题出在哪里（即使不应该存在）：表在两列上有主键。我正在尝试在这两列中插入具有新值组合的行，但第一列中的值已经在某行中，而第二列中的值已经在另一行中。但是它们并没有在任何地方组合在一起，所以我相信这应该有效，我很困惑看到它没有。

采纳答案by Kamil

Your code and schema are OK. You probably trying on previous version of table.

您的代码和架构没问题。您可能正在尝试使用以前版本的表格。

http://sqlfiddle.com/#!2/9dc64/1/0

http://sqlfiddle.com/#!2/9dc64/1/0

Your table even has no UNIQUE, so that error is impossible on that table.

您的表甚至没有 UNIQUE，因此该表上不可能出现错误。

Backup data from that table, drop it and re-create.

从该表备份数据，删除它并重新创建。

Maybe you tried to run that CREATE TABLE IF NOT EXIST. It was not created, you have old version, but there was no error because of IF NOT EXIST.

也许你试图运行那个CREATE TABLE IF NOT EXIST。它不是创建的，你有旧版本，但没有错误，因为IF NOT EXIST.

You may run SQL like this to see current table structure:

你可以像这样运行 SQL 来查看当前的表结构：

DESCRIBE my_table;

---

---

Edit - added later:

编辑 - 稍后添加：

Try to run this:

尝试运行这个：

DROP TABLE `my_table`; --make backup - it deletes table

CREATE TABLE `my_table` (
  `number` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `money` int(11) NOT NULL,
  PRIMARY KEY (`number`,`name`),
  UNIQUE (`number`, `name`) --added unique on 2 rows
) ENGINE=MyISAM;

回答by carla

I know this wasn't the problem in this case, but I had a similar issue of "Duplicate Entry" when creating a composite primary key:

我知道在这种情况下这不是问题，但是在创建复合主键时我遇到了类似的“重复条目”问题：

ALTER TABLE table ADD PRIMARY KEY(fieldA,fieldB); 

The error was something like:

错误是这样的：

#1062 Duplicate entry 'valueA-valueB' for key 'PRIMARY'

So I searched:

所以我搜索：

select * from table where fieldA='valueA' and fieldB='valueB'

And the output showed just 1 row, no duplicate!

并且输出仅显示 1 行，没有重复！

After some time I found out that if you have NULL values in these field you receive these errors. In the end the error message was kind of misleading me.

一段时间后，我发现如果这些字段中有 NULL 值，则会收到这些错误。最后，错误消息有点误导我。

回答by Xeoncross

Less common cases, but keep in mind that according to DOC https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-limitations.html

不太常见的情况，但请记住，根据 DOC https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-limitations.html

When running an online ALTER TABLE operation, the thread that runs the ALTER TABLE operation will apply an “online log” of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the “online log”. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction.

运行联机 ALTER TABLE 操作时，运行 ALTER TABLE 操作的线程将应用 DML 操作的“联机日志”，这些操作是从其他连接线程在同一表上并发运行的。应用 DML 操作时，可能会遇到重复键条目错误（ERROR 1062 (23000): Duplicate entry），即使重复条目只是临时的，并且会被“在线日志”中的后续条目还原. 这类似于 InnoDB 中的外键约束检查的想法，其中约束必须在事务期间保持。

回答by flagg19

In my case the error was caused by the outdated schema, one column was originally varchar(50)but the dump I was trying to import was created from a modified version of the schema that has varchar(70)for that column (and some of the entries of that field where using more than 50 chars).

在我的情况下，错误是由过时的架构引起的，一列最初是varchar(50)但我尝试导入的转储是从varchar(70)该列的架构的修改版本创建的（以及该字段的一些条目使用更多超过 50 个字符）。

During the import some keys were truncated and the truncated version was not unique anymore. Took a while to figure that out, I was like "but this supposedly duplicated key doesn't even exist!".

在导入过程中，一些键被截断，被截断的版本不再是唯一的。花了一段时间才弄清楚，我就像“但这个所谓的重复密钥甚至不存在！”。

回答by Ben Wilson

In case anyone else finds this thread with my problem -- I was using an "integer" column type in MySQL. The row I was attempting to insert had a primary key with a value larger than allowed by integer. Switching to "bigint" fixed the problem.

万一其他人发现这个线程与我的问题有关——我在 MySQL 中使用了“整数”列类型。我试图插入的行有一个主键，其值大于整数所允许的值。切换到“bigint”解决了这个问题。

回答by Timo

In case this helps anyone besides the OP, I had a similar problem using InnoDB.

如果这对 OP 以外的任何人都有帮助，我在使用InnoDB 时也遇到了类似的问题。

For me, what was really going on was a foreign key constraint failure. I was referencing a foreign key that did not exist.

对我来说，真正发生的是外键约束失败。我引用了一个不存在的外键。

In other words, the error was completely off. The primary key was fine, and inserting the foreign key first fixed the problem. No idea why MySQL got this wrong suddenly.

换句话说，错误完全消失了。主键没问题，先插入外键解决问题。不知道为什么 MySQL 会突然出错。

回答by Megamind

As per your code your "number" and "Name" are primarykey and you are inserting S.NAME in both row so it will make a conflict. we are using primarykey for accessing complete data. here you cant access the data using the primarykey 'name'.

根据您的代码，您的“号码”和“名称”是主键，并且您在两行中都插入了 S.NAME，因此会产生冲突。我们使用主键来访问完整的数据。在这里，您无法使用主键“名称”访问数据。

im a beginner and i think it might be the error.

我是初学者，我认为这可能是错误。

回答by Ivan Castellanos

In my case the error was very misleading. The problem was that PHPMyAdmin uses "ALTER TABLE" when you click on the "make unique" button instead of "ALTER IGNORE TABLE", so I had to do it manually, like in:

就我而言，该错误非常具有误导性。问题是当您单击“制作唯一”按钮而不是“ALTER IGNORE TABLE”时，PHPMyAdmin 使用“ALTER TABLE”，因此我必须手动执行此操作，例如：

ALTER TABLE mytbl ADD UNIQUE (columnName);

回答by Robert Mauro

This problem is often created when adding a column or using an existing column as a primary key. It is not created due to a primary key existing that was never actually created or due to damage to the table.

在添加列或使用现有列作为主键时，通常会产生此问题。它不是由于存在从未实际创建的主键或由于表损坏而创建的。

What the error actually denotes is that a pending key value is blank.

错误实际上表示待处理的键值为空。

The solution is to populate the column with unique values and then try to create the primary key again. There can be no blank, null or duplicate values, or this misleading error will appear.

解决方案是用唯一值填充列，然后再次尝试创建主键。不能有空白、空值或重复值，否则会出现这种误导性错误。

回答by Habibillah

Your code is work well on this demo:

您的代码在此演示中运行良好：

http://sqlfiddle.com/#!8/87e10/1/0

http://sqlfiddle.com/#!8/87e10/1/0

I think you are doing second query (insert...) twice. Try

我认为您正在执行第二次查询（插入...）两次。尝试

select * from my_table

before insert new row and you will get that your data already exist or not.

在插入新行之前，您将知道您的数据是否已经存在。