php - Restrict autoincrement on MYSQL when insert duplicate record -

when go insert duplicate value , ignore value insert in column 'tagname' id increasing automaitcally not need jump id's here have table 2 columns -- innodb - id(autoincrement), tagname(unique varchar)

when doing insert ignore tablename set tagname="something" autoincrement id value increases if insert ignored.

for example i'm displaying 3 rows bad ids.

id   tagname 1    test 8    test111 29   test2222 

i not want use sub queries

thanks

if i'm reading question right, you're asking if can stop autoincrement incrementing if tried insert row , insertion failed.

the behaviour you're observing deliberate design choice major databases (mysql, oracle, postgresql, sqlite, etc) do, reason autoincrementing columns used give new row unique id, , if attempt insert row next available value assigned autoincrement increments itself.

if insertion failed 1 occurs @ same time succeeds new row's id fixed. it's considered bad practice change row's id once it's been established, in database makes use of foreign keys, therefore allowing gap in numbering exist better approach attempt update rows may have been inserted in time since insert failure occurred, yet more new rows may in process of being inserted while you're updating rows. there's going window database in inconsistent state.

in short, there's no reason rolling autoincrement value after failed insert (other ocd) , plenty of reasons not rolling back.