1

I have a database table on which I am using INSERT ... ON DUPLICATE KEY UPDATE, where the unique check has been applied on a non-primary key field.

Initially I did not notice, but as the database has grown and has almost 500k records, I notice that the primary key value has gone too high.

Currently I have 507 811 records in the table, but the highest primary key right now is: 4 841 123, which is way higher than it should be.

As quoted here:

"INSERT ... ON DUPLICATE KEY UPDATE works similarly to REPLACE in that when a row with an existing (duplicate) key is found, it's actually removed (1 row affected) and then a new row created in its place (1 more row affected), giving the illusion of updating/replacing a row.",

so should I really use primary key field, because eventually it will go out range one day as the data keeps increasing.

I know it is not a bug, but I really need some more knowledge into this. I have been searching a lot about this. And I realise, either I should just keep the column and remove primary key check and auto-increment from it or increase the size for the column, currently it is int(11).

Community
  • 1
  • 1
Deepanshu Goyal
  • 2,738
  • 3
  • 34
  • 61

1 Answers1

1

Well that answer you have referred to is plain wrong. This is what the manual says:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have similar effect:

It clearly says that the row is updated and not replaced. A row is replaced only if you use the REPLACE INTO syntax which is a mysql extension.

What's actually happening in your table is the common 'gaps in sequences' problem which is not really a problem at all. This occurs when you delete a row in your table that had say id=100. You might expect a new row with a fresh insert to have id=100 but it will have 101. This in practice is never a problem.

The maximum value that can be stored in an int field is 2147483647 and you are a long way from hitting that limit. You can fill roughly 250 times as much data in that table as you have at the moment.

e4c5
  • 52,766
  • 11
  • 101
  • 134