MySQL错误-#1062-密钥2重复输入'' [英] MySQL error - #1062 - Duplicate entry ' ' for key 2

问题描述

我正在尝试向MySQL数据库中插入大量用户，但每次尝试都会出现错误:

I'm trying to insert a huge list of users to a MySQL database but everytime I try I get the error:

#1062 - Duplicate entry '' for key 2

之所以这样，是因为第二列在很多条目上都是空白的，因此在第二列中插入一个空白条目后，就不允许我添加另一个了.但是，当我昨天添加了大多数列表时，即使昨天添加的许多条目在第2列中也有一个空白单元格，但我一次也没有得到此错误.这是怎么回事?

It gives me this because the 2nd column is blank on quite a lot of the entries, so after it's inserted one blank entry in column 2, it won't let me add another. However, when I added most of the list yesterday I didn't get this error once even though a lot of the entries I added yesterday have a blank cell in column 2 as well. Whats going on?

这是插入1个条目的sql代码.其余的遵循相同的格式:

This is the sql code to insert 1 entry. The rest follow the same format:

INSERT INTO users
  (`id`,`title`,`firstname`,`lastname`,`company`,`address`,`city`,`county`
   ,`postcode`,`phone`,`mobile`,`category`,`email`,`password`,`userlevel`) 
VALUES     
  ('','','John','Doe','company','Streeet','city','county'
  ,'postcode','phone','','category','[e-mail]','','');

推荐答案

除了Sabeen的答案:

In addition to Sabeen's answer:

第一列ID是您的主键.
不要在主键中插入''，而应插入null.

The first column id is your primary key.
Don't insert '' into the primary key, but insert null instead.

INSERT INTO users
  (`id`,`title`,`firstname`,`lastname`,`company`,`address`,`city`,`county`
   ,`postcode`,`phone`,`mobile`,`category`,`email`,`password`,`userlevel`) 
VALUES     
  (null,'','John','Doe','company','Streeet','city','county'
  ,'postcode','phone','','category','[e-mail]','','');

如果它是自动增量键，则可以解决您的问题.
如果不是，请使id为自动增量键，并始终将null插入其中以触发自动增量.

If it's an autoincrement key this will fix your problem.
If not make id an autoincrement key, and always insert null into it to trigger an autoincrement.

MySQL的设置为仅在null插入或0和null的两个插入上自动递增密钥.不要指望此设置，因为如果您更改服务器，代码可能会中断.
如果您插入null，则您的代码将始终有效.

MySQL has a setting to autoincrement keys only on null insert or on both inserts of 0 and null. Don't count on this setting, because your code may break if you change server.
If you insert null your code will always work.

请参阅: http://dev.mysql.com/doc/refman /5.0/zh-CN/example-auto-increment.html

这篇关于MySQL错误-#1062-密钥2重复输入''的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！