MySQL CREATE TABLE如果不是PHPmyadmin导入中的EXISTS

I have the following code

我有以下代码

CREATE TABLE IF NOT EXISTS `abuses` (
  `abuse_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT '0',
  `abuser_username` varchar(100) NOT NULL DEFAULT '',
  `comment` text NOT NULL,
  `reg_date` int(11) NOT NULL DEFAULT '0',
  `id` int(11) NOT NULL,
  PRIMARY KEY (`abuse_id`),
  KEY `reg_date` (`reg_date`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table with abuse reports' AUTO_INCREMENT=2 ;

this table already exists in the database, but when i import an sql file with phpmyadmin, the following error occurs

此表已存在于数据库中,但是当我使用phpmyadmin导入sql文件时,会发生以下错误

--
-- Dumping data for table `probid_abuses`
--
INSERT INTO  `abuses` (  `abuse_id` ,  `user_id` ,  `abuser_username` ,  `comment` ,  `reg_date` , `auction_id` ) 
VALUES ( 1, 100020,  'artictundra', 'I placed a bid for it more than an hour ago. It is still active. I thought I was supposed to get an email after 15 minutes.', 1338052850, 108625 ) ;

#1062 - Duplicate entry '1' for key 'PRIMARY'

i thought because it already exists it won't attempt to create it, why is it behaving as such?

我认为因为它已经存在它不会尝试创建它,为什么它表现如此?

5 个解决方案

#1

On the CREATE TABLE,

在CREATE TABLE上

The AUTO_INCREMENT of abuse_id is set to 2. MySQL now thinks 1 already exists.

abuse_id的AUTO_INCREMENT设置为2.MySQL现在认为1已经存在。

With the INSERT statement you are trying to insert abuse_id with record 1. Please set AUTO_INCREMENT on CREATE_TABLE to 1 and try again.

使用INSERT语句,您尝试插入带有记录1的abuse_id。请将CREATE_TABLE上的AUTO_INCREMENT设置为1,然后重试。

Otherwise set the abuse_id in the INSERT statement to 'NULL'.

否则,将INSERT语句中的abuse_id设置为“NULL”。

How can i resolve this?

我怎么解决这个问题?

#2

Depending on what you want to accomplish, you might replace INSERT with INSERT IGNORE in your file. This will avoid generating an error for the rows that you are trying to insert and already exist.

根据您要完成的任务,您可以在文件中用INSERT IGNORE替换INSERT。这将避免为您尝试插入并已存在的行生成错误。

See http://dev.mysql.com/doc/refman/5.5/en/insert.html.

#3

it is because you already defined the 'abuse_id' as auto increment, then there is no need to insert its value. it will be inserted automatically. the error comes because you are inserting 1 many times that is duplication of data. the primary key should be unique. should not be repeated.

这是因为您已经将'abuse_id'定义为自动增量,因此无需插入其值。它会自动插入。错误的来源是因为您插入了多次重复数据。主键应该是唯一的。不应重演。

the thing you have to do is to change your insertion query as below

您需要做的是更改插入查询,如下所示

INSERT INTO  `abuses` (  `user_id` ,  `abuser_username` ,  `comment` ,  `reg_date` , `auction_id` ) 
VALUES ( 100020,  'artictundra', 'I placed a bid for it more than an hour ago. It is still active. I     thought I was supposed to get an email after 15 minutes.', 1338052850, 108625 ) ;

#4

If you really want to insert this record, remove the `abuse_id` field and the corresponding value from the INSERTstatement :

如果您确实要插入此记录,请从INSERTstatement中删除`abuse_id`字段和相应的值:

INSERT INTO  `abuses` (  `user_id` ,  `abuser_username` ,  `comment` ,  `reg_date` , `auction_id` ) 
VALUES ( 100020,  'artictundra', 'I placed a bid for it more than an hour ago. It is still active. I thought I was supposed to get an email after 15 minutes.', 1338052850, 108625 ) ;

#5

In your case, the first value to insert must be NULL, because it's AUTO_INCREMENT.

在您的情况下,要插入的第一个值必须为NULL,因为它是AUTO_INCREMENT。

#1