mysql 中键“PRIMARY”的重复条目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12075445/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Duplicate entry for key 'PRIMARY' in mysql
提问by mickeymoon
I have a table called tbl_jobsthat stores the meta data of some background jobs running in the application. The schema is like :
我有一个名为的表tbl_jobs，用于存储应用程序中运行的某些后台作业的元数据。架构是这样的：
CREATE TABLE `tbl_jobs` (
`type` varchar(30) NOT NULL DEFAULT '',
`last_run_on` datetime NOT NULL,
`records_updated` text,
PRIMARY KEY (`type`,`last_run_on`),
UNIQUE KEY `index2` (`type`,`last_run_on`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$
Whenever a job runs it makes an entry in the table with the typewhich is a unique identifier for different jobs, run timeand the records updatedin that run.
每当作业运行时，它都会在表中创建一个条目，type其中 是不同作业的唯一标识符，run time并且records updated在该运行中。
There are two different jobs that run at same time with types : MAILER_UNLOCKED_REWARDSand MAILER_ALMOST_UNLOCKED.
有两个不同的作业同时运行，类型为 :MAILER_UNLOCKED_REWARDS和MAILER_ALMOST_UNLOCKED。
When these jobs try to insert their entries with the same timestamp only one of them gets inserted and the other throws a Duplicate Entry for keyerror.
当这些作业尝试插入具有相同时间戳的条目时，只有其中一个会被插入，而另一个会因键错误而引发重复条目。
For instance the two jobs ran the following :
例如，这两个作业运行如下：
INSERT INTO tbl_jobs
(type,
last_run_on,
records_updated)
VALUES ('MAILER_ALMOST_UNLOCKED',
'2012-08-22 19:10:00',
'f8a35230fb214989ac75bf11c085aa28:b591426df4f340ecbce5a63c2a5a0174')
that ran successfully but when the second job ran the insert command
成功运行但是当第二个作业运行插入命令时
INSERT INTO tbl_jobs
(type,
last_run_on,
records_updated)
VALUES ('MAILER_UNLOCKED_REWARDS',
'2012-08-22 19:10:00',
'8a003e8934c07f040134c30959c40009:59bcc21b33a0466e8e5dc50443beb945')
It threw the error
它抛出了错误
Duplicate entry 'M-2012-08-22 19:10:00' for key 'PRIMARY'
The primary key is combination of typeand last_run_oncolumns.
主键是type和last_run_on列的组合。
If I delete the entry for the first job the insertion succeeds, i.e it is asking for timestampalone to be unique.
如果我删除第一个工作的条目插入成功，即它要求timestamp单独是唯一的。
However the conflict for the same timestampoccurs only between these two jobs.There are other jobs that get inserted for the same timestamp.
然而，相同的冲突timestamp只发生在这两个作业之间。还有其他作业被插入相同的timestamp.
Any ideas on what could be the issue?
关于可能是什么问题的任何想法？
采纳答案by LSerni
Are you using the whole "type" field in your index? Or only the first character? Because the key MySQL is complaining about is
您是否在索引中使用整个“类型”字段？还是只有第一个字符？因为 MySQL 抱怨的关键是
M-2012-08-22 19:10:00
instead of MAILER_...
而不是 MAILER_...
Try running:
尝试运行：
SHOW INDEXES FROM tbl_jobs;
It should give something like:
它应该给出类似的东西：
+----------+------------+----------+--------------+-------------+-----------+-------------+ ----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl_jobs | 0 | PRIMARY | 1 | type | A | 0 | NULL | NULL | | BTREE | | |
| tbl_jobs | 0 | PRIMARY | 2 | last_run_on | A | 0 | NULL | NULL | | BTREE | | |
...
...
and I suspect it will show instead "1" in the Sub_part column of the PRIMARY index:
我怀疑它会在 PRIMARY 索引的 Sub_part 列中显示“1”：
+----------+------------+----------+--------------+-------------+-----------+-------------+ ----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl_jobs | 0 | PRIMARY | 1 | type | A | 0 | 1 | NULL | | BTREE | | |
| tbl_jobs | 0 | PRIMARY | 2 | last_run_on | A | 0 | NULL | NULL | | BTREE | | |
...
...
BTW, the primary key is always unique, so the second index index2you declare there is redundant.
顺便说一句，主键总是唯一的，所以index2你声明的第二个索引是多余的。
回答by ?oàn Nh?t Du?n
The first thing: you have to make sure you PRIMARY KEY was seted AUTO_INCREMENT. The second thing: you just enable Auto increment by : ALTER TABLE [table name] AUTO_INCREMENT = 1The third thing: when you execute the insert command you have to skip this key.
第一件事：您必须确保将 PRIMARY KEY 设置为 AUTO_INCREMENT。第二件事：您只需通过以下方式启用自动增量： ALTER TABLE [表名] AUTO_INCREMENT = 1第三件事：当您执行插入命令时，您必须跳过此键。
回答by Casey
I have seen this error if I have a system shutdown or network problem. You really don't have a duplicate in your db. That is a MySQL db error. All you need to do is: if you do your insertion and it is not true, just alter one of the columns of your table you want to insert into either from varcharto textor bigintand then redo the insertion. That solves the problem.
如果我有系统关闭或网络问题，我就会看到这个错误。您的数据库中确实没有重复项。这是一个 MySQL 数据库错误。您需要做的就是：如果您进行了插入而它不是true，只需更改您要插入的表的其中一列 from varchartotext或bigint然后重新插入。这解决了问题。
If(!$insert)
{
$alter=Mysql_query("alter table
`table_name` change `table_name`
`table_name` bigint(255) not null");
If($alter){
//you then redo your insertion.
}
}
