codegy codegy - 16 days ago 5
MySQL Question

MySQL AUTO_INCREMENT does not ROLLBACK

I'm using MySQL's AUTO_INCREMENT field and InnoDB to support transactions. I noticed when I rollback the transaction, the AUTO_INCREMENT field is not rollbacked? I found out that it was designed this way but are there any workarounds to this?

Answer

Let me point out something very important:

You should never depend on the numeric features of autogenerated keys.

That is, other than comparing them for equality (=) or unequality (<>), you should not do anything else. No relational operators (<, >), no sorting by indexes, etc. If you need to sort by "date added", have a "date added" column.

Treat them as apples and oranges: Does it make sense to ask if an apple is the same as an orange? Yes. Does it make sense to ask if an apple is larger than an orange? No. (Actually, it does, but you get my point.)

If you stick to this rule, gaps in the continuity of autogenerated indexes will not cause problems.