0

I have a MySQL database with 3 columns:

id (Primary Key, int, auto increment)

X1 (Unique Index, varchar)

X2 (int, default: 1)

I INSERT / UPDATE the table like this:

INSERT INTO table(X1) VALUES(?) ON DUPLICATE KEY UPDATE X2 = X2 +1

Everything works fine but the Primary Key (id) is internally incremented even if there already is a row with X1=value and it is just updated.

So for example: On 1st time insert, it creates a row with id=1. Then the statement is executed again and it just updates the row because of the ON DUPLICATE KEY UPDATE. When I then insert a row with X1=value_different, it inserts a row with id=3.

I expect the id to be 2 in this case as it is the 2nd row in the table.

Am I doing something wrong?

Edit: I'm using InnoDB.

Julius S.
  • 664
  • 9
  • 21
  • You're not doing anything wrong, this is how InnoDB works. – Barmar Jan 01 '17 at 08:59
  • You have to understand that value of primary key is not **sequential** number but **unique** number. Using sequential number increment is the easiest and fastest way to guarantee unique numbers. When a duplicate occurs, that value is spent, counter is incremented and if you accept the fact that you get unique numbers, but not pretty sequential numbers - you have no problem. This is because of concurrent access to the same source of data. If MySQL had to take care of forcing the numbers to be pretty and sequential, it would be several times slower and quite possibly produce duplicates. – N.B. Jan 01 '17 at 10:16
  • @N.B. Thank you for your detailed and helpful answer. – Julius S. Jan 01 '17 at 10:19

0 Answers0