I noticed this...
if u have a table mytable(id, name)
where name is unique index type varchar...
when u make query insert ignore set name='myvalue'
when the value is present and skipped the Autoincrement , increments anyway...
so let's say autoincremente is 5,
if u enter one value successflully
then u have 2 skips
then insert another successfully
(check id with mysql_insert_id() )
the first id inserted is 6 and the second will be 9....
unwanted result? mySQL bug?
While it's not clearly documented in the MySQL manual (as far as I could tell, anyway), I don't think it's a bug or unwanted result, really. By using the 'IGNORE' keyword, you're telling MySQL to treat errors as warnings, thus causing queries that would normally fail to either succeed anyway or be silently IGNORE'd.
Thus, the AUTO_INCREMENT column is incremented just like it would with any other INSERT query... it just so happens that the data (a.k.a. entire row) is silently tossed out because of the UNIQUE constraint failing (except it's not properly failing, since you instructed MySQL to IGNORE errors).
EDIT: Also... does it really matter if the id gets incremented or not? After all, who cares what the value of the id column is for any given row? That's just some arbitrary value used internally by the database and/or your application to identify a particular row and shouldn't be considered a sequential value by any means (in other words, the previous row isn't necessarily 1 less than a given row's id, nor is the next row necessarily 1 more).
EDIT2: Whoa, wait a minute here... I just found this:
MySQL Manual wrote:If you use INSERT IGNORE and the row is ignored, the AUTO_INCREMENT counter is not incremented and LAST_INSERT_ID() returns 0, which reflects that no row was inserted.
That statement can be found even in the 4.1 version of the MySQL manual here.
On the other hand, this user-contributed note for [man]mysql_insert_id/man suggests that what you're saying is correct and that the manual's statement is false.
Out of curiosity, which version of MySQL are you using? Also, which storage engine (MyISAM, InnoDB, ?) are you using?
Just tried it on table types MyISAM and InnoDB, and it didn't do that, meaning, the auto-increment value didn't change when the insert was ignored. However, I did find a couple posts via Google from people talking about the same thing.
