MySQL CREATE TABLE IF NOT EXISTS in PHPmyadmin import

Question

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

User · Answer

If you really want to insert this record  remove the  abuse id  field and the corresponding value from the INSERTstatement    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

User · Answer

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

User · Answer

On the CREATE TABLE   The AUTO INCREMENT of abuse id is set to 2  MySQL now thinks 1 already exists    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    Otherwise set the abuse id in the INSERT statement to  NULL    How can i resolve this

User · Answer

In your case  the first value to insert must be NULL  because it s AUTO INCREMENT

User · Answer

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   See http   dev mysql com doc refman 5 5 en insert html

[mysql] MySQL CREATE TABLE IF NOT EXISTS in PHPmyadmin import

Examples related to mysql

Examples related to phpmyadmin