即使没有重复条目，MySQL重复条目错误 [英] MySQL duplicate entry error even though there is no duplicate entry

问题描述

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

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

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;

它包含这两行:

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);

而且 MySQL 在告诉我这个时不会插入它:

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

#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?

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

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.

我是否遗漏了什么或者这是 MySQL 或 MyISAM 的错误?谢谢.

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

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

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.

推荐答案

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

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

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

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

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

备份该表中的数据，删除并重新创建.

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

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

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.

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

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

DESCRIBE my_table;

<小时>

编辑 - 稍后添加:

---

Edit - added later:

尝试运行:

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;

这篇关于即使没有重复条目，MySQL重复条目错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！