How do I stop auto increment on ignored inserts that make no changes in MySQL?

I am i开发者_Go百科nserting say 500 rows using INSERT IGNORE, if the row already exists (based on a unique field) then it simply does nothing, if not it inserts the row.

My problem is that say only 10 do NOT exist the auto increment still increases for every insert so it goes up by 500 so I end up with gaps in my id's. How do I stop this?

I have tried:

SET global innodb_autoinc_lock_mode = 0;

But I get the error:

1238 - Variable 'innodb_autoinc_lock_mode' is a read only variable

How do I change this?

According to MySQL's documentation, the innodb_autoinc_lock_mode is not a dynamic variable and, thus cannot be changed while MySQL is running.

You'll either have to set it in the command line

--innodb_autoinc_lock_mode=0

or in the configuration file (mysql.ini)

innodb_autoinc_lock_mode=0

It should be noted that you should not (read: never) rely on a continuous id sequence. Rollbacked or failed transactions may result in gaps.

I had the same issue and I set the InnoDB auto_inc_lock_mode to 0 and it worked just once but I encountered the same problem again. Luckily I found a way to solve it.

ALTER TABLE `nameOfTable` AUTO_INCREMENT=1;

You can enter this query to reset the jumping indices or better still the GUI of phpMyAdmin has an Operation tab. Go to Operations > Table Options and change the AUTO_INCREMENT value manually.

You can also do this:

SET  @num := 0;

UPDATE your_table SET id = @num := (@num+1);

ALTER TABLE your_table AUTO_INCREMENT =1;

From this source .