Duplicate entry for primary key on MySQL

问题:

My table just has single column ID (passwords for admin Log-in)

Because this code runs every time that program starts, I can prevent errors occurs on creating database and creating tables by using IF NOT EXIXTS statement.

Since adminLogin table should be initial first time, When user re-run the program, the Duplicate entry for primary key error occurs.

I used IF NOT EXISTS for inserting into table, But there is some another error!

My table:

Error:

回答1:

You are trying to insert same value. PK should be unique.

SET ID as autoincrement.

CREATE TABLE `table_code` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `your_column` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

回答2:

Another possibility

INSERT INTO adminLogin(ID) VALUES(100) ON DUPLICATE KEY UPDATE ID=ID;

回答3:

You can use INSERT IGNORE instead.

INSERT IGNORE INTO ADMINLOGIN VALUES(200);

回答4:

INSERT IGNORE INSERT ... ON DUPLICATE KEY UPDATE ... REPLACE INTO can all work without erroring. Depending on your needs, some may be a better use than others.

Here's a brief pros and cons of each:

INSERT IGNORE: Pros: Easy to write Cons: if data that is being inserted is newer the older data will be preserved

INSERT ... ON DUPLICATE KEY UPDATE ... Pros: Can Insert or update specific columns that could have more recent data Cons: A little harder to write the query for

REPLACE INTO Pros: Can insert and update columns that could have more recent data. Faster than Insert, on duplicate key update. Cons: Overwrites every column even though only some columns may have newer data. IF you are inserting multiple rows replace into could potentially overwrite data for existing rows that you don't really want to overwrite.