如果 PHPmyadmin 导入中不存在 MySQL 创建表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18794580/
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
MySQL CREATE TABLE IF NOT EXISTS in PHPmyadmin import
提问by Frank Ly
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?
我想因为它已经存在它不会尝试创建它，为什么它会这样？
回答by user6494166
On the CREATE TABLE,
在创建表上，
The AUTO_INCREMENT of abuse_id is set to 2. MySQL now thinks 1 already exists.
Abus_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?
我该如何解决这个问题？
回答by rafee_que_
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' 定义为自动增量，则无需插入其值。它会自动插入。出现错误是因为您多次插入 1 次重复数据。主键应该是唯一的。不应重复。
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 ) ;
回答by Marc Delisle
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。这将避免为您尝试插入且已存在的行生成错误。
回答by Diego Celdrn Only Quality Soft
In your case, the first value to insert must be NULL, because it's AUTO_INCREMENT.
在您的情况下，要插入的第一个值必须为 NULL，因为它是 AUTO_INCREMENT。
回答by Fabien TheSolution
If you really want to insert this record, remove the `abuse_id`field and the corresponding value from the INSERTstatement :
如果确实要插入此记录，请`abuse_id`从INSERT语句中删除字段和相应的值：
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 ) ;
