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.

总结并指出我认为的问题所在(尽管不应该有)：TABLE在两列上有主键。我试图在这两列中插入一个具有新的值组合的行，但是第一列中的值已经在某一行中，而第二列中的值已经在另一行中。但它们并不是在任何地方结合在一起的，所以我相信这应该是可行的，但看到它没有奏效，我感到非常困惑。

优秀答案推荐

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.

如果创建表不存在，可能您试图运行该表。它不是创建的，您有旧版本，但没有错误，因为如果不存在。

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;

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.

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

I had a similar issue, but in my case it turned out that I used case insensitive collation - utf8_general_ci.

我也遇到过类似的问题，但在我的例子中，我使用了不区分大小写的排序规则-utf8_General_ci。

Thus, when I tried to insert two strings which were different in a case-sensitive comparison, but the same in the case-insensitive one, MySQL fired the error and I couldn't understand what a problem, because I used a case-sensitive search.

因此，当我尝试插入在区分大小写的比较中不同但在不区分大小写的比较中相同的两个字符串时，MySQL触发了错误，我无法理解是什么问题，因为我使用了区分大小写的搜索。

The solution is to change the collation of a table, e.g. I used utf8_bin which is case-sensitive (or utf8_general_cs should be appropriate one too).

解决方案是更改表的排序规则，例如，我使用了区分大小写的utf8_bin(或者utf8_General_cs也应该是合适的)。

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突然出错。

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.

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)，但我试图导入的转储是从模式的修改版本创建的，该模式对该列(以及该字段的一些条目，其中使用了超过50个字符)具有varchar(70)。

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!".

在导入过程中，某些键被截断，截断后的版本不再唯一。花了一段时间才弄明白，我就像“但这个所谓的复制键甚至不存在！“.

Try with auto increment:

尝试使用自动递增：

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

Your code is work well on this demo:

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

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.

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

i have just tried, and if you have data and table recreation wouldnt work, just alter table to InnoDB and try again, it would fix the problem

我刚刚试过了，如果您的数据和表重建不起作用，只需将TABLE更改为InnoDB并重试，问题就会解决

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”解决了这个问题。

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'.

根据您的代码，您的“number”和“name”是主关键字，并且您在两行都插入了S.NAME，因此会造成冲突。我们使用PrimiyKey来访问完整的数据。在这里，您不能使用主键‘name’访问数据。

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

我是个初学者，我想这可能是错误。

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:

在我的案例中，这个错误非常具有误导性。问题是，当您单击“Make Unique”按钮时，PHPMyAdmin使用的是“ALTER TABLE”，而不是“ALTER IGNORE TABLE”，所以我不得不手动执行此操作，如下所示：

ALTER TABLE mytbl ADD UNIQUE (columnName);

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.

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

For me a noop on table has been enough (was already InnoDB):

对我来说，点一下桌子就够了(已经是InnoDB了)：

ALTER TABLE $tbl ENGINE=InnoDB;

tl;dr: my view showed my table was empty but the view excluded existing rows.

TL；DR：我的视图显示我的表是空的，但该视图排除了现有的行。

I had the same problem but mine was because I was inserting the same test rows I had used before. When I checked to see if my table was empty, I used a view that excluded different tenants so the search came back empty. When I checked the actual table, the previous records were still there.

我也遇到了同样的问题，但我的问题是因为我插入的是我以前使用过的相同测试行。当我检查我的表是否为空时，我使用了一个排除不同租户的视图，因此搜索结果为空。当我检查实际的表格时，以前的记录仍然在那里。

Once I had deleted the existing records, the insert worked. Only half a day of frustration lost to this one...

一旦我删除了现有的记录，插入就起作用了。只有半天的挫败感输给了这一位。

Had this error, when adding a composite primary key that is ADD PRIMARY KEY (column1, column2, ...) The value of all the columns in that row must not be duplicated.

在添加复合主键时出现此错误，该复合主键为添加主键(Column1，Column2，...)该行中所有列的值不得重复。

For Example:
You do ADD PRIMARY KEY (name, country, number)

例如：添加主键(名称、国家/地区、号码)

This will throw an error #1062 - Duplicate entry 'collin-UK-5' for key 'PRIMARY' because the columns combined have duplicate

这将引发错误#1062-键‘PRIMARY’的重复条目‘Collin-UK-5’，因为组合的列有重复项

So if you see this format of error just check and ensure that the columns you want to add a composite primary key to combined don't have duplicates.

因此，如果您看到这种格式的错误，只需检查并确保要向组合添加复合主键的列没有重复项。

Another reason you may be getting this error is because the same restriction exists in another related table, and they Keyname on the related table has the exact same name. I've had this happen once and it was quite difficult to identify.

您可能会收到此错误的另一个原因是，在另一个相关表中存在相同的限制，并且相关表上的Keyname具有完全相同的名称。我有过一次这样的经历，很难辨认。

i.e. if you have a trigger that inserts data to a different table (the "related" table) with the same restriction and same Keyname, MySQL will not include the name of the table throwing the error, only the Keyname.

也就是说，如果您有一个触发器将数据插入到具有相同限制和相同Keyname的不同表(“相关”表)，MySQL将不包括抛出错误的表的名称，而只包括Keyname。

In my case, it was caused by special characters. For example, I got

在我的案例中，这是由特殊人物造成的。例如，我得到了

foó@dummy.com
and
[email protected]

Foó@ummy.com和[电子邮件受保护]

When I tried to search with LIKE email = '%[email protected]%' the results were empty.

当我尝试用Like Email=‘%[Email in Protected]%’搜索时，结果是空的。

I had to apply regex to fix this special case.

我不得不应用正则表达式来解决这个特殊的情况。

As looking on your error #1062 - Duplicate entry '2-S. Name' for key 'PRIMARY' it is saying that you use primary key in your number field that's why it is showing duplicate Error on Number Field.
So Remove this primary Key then it inset duplicate also.

就像你的错误#1062-DUPLICATE ENTRY‘2-S NAME’对于键‘PRIMARY’，它是说你在你的数字域中使用了主键，这就是为什么它在数字域上显示重复错误的原因。所以删除这个主键，然后再插入副本。