0

I have a table with a primary auto-incrementing id, a unique key (fruit), and a property string (color)

+-------------+-----------+--------+
| id          | fruit     | color  |
+-------------+-----------+--------+
| 1           | apple     | green  | 
| 2           | banana    | yellow |
+-------------+-----------+--------+

Now, I want to add items to my color database. I want to update the existing color if the fruit is in my database, or add a new row if it's not. The default approach would be to use INSERT ... ON DUPLICATE KEY UPDATE ... but this yields an undesired side effect with the ID column:

INSERT into table(fruit,color) VALUES ('apple','red') ON DUPLICATE KEY UPDATE color='red' INSERT into table(fruit,color) VALUES ('kiwi','pink') ON DUPLICATE KEY UPDATE color='pink'

This results in a table 

+-------------+-----------+--------+
| id          | fruit     | color  |
+-------------+-----------+--------+
| 1           | apple     | red    | 
| 2           | banana    | yellow |
| 4           | kiwi      | pink   |
+-------------+-----------+--------+

Thus with a gap in the auto_increment. I know why this is (insert triggers first, raises the counter, then calls update when it fails) but my question is what can I use instead so that my ids stay consecutive?

I've tried updating my counter after every statement with SET @m=(SELECT MAX(id)+1 FROM table);SET @s=CONCAT('ALTER TABLE table AUTO_INCREMENT=',@m);PREPARE st FROM @s;EXECUTE st;DEALLOCATE PREPARE st; but while this has the desired effect, doing it after every statement is really detrimental to the performance.

I've found a rather nice solution for INSERT IGNORE here: https://www.percona.com/blog/2011/11/29/avoiding-auto-increment-holes-on-innodb-with-insert-ignore/ so I'm hoping that something similar for INSERT ... ON DUPLICATE KEY UPDATE exists.

user1111929
  • 6,050
  • 9
  • 43
  • 73
  • 4
    why do you care that the IDs are consecutive? They're just IDs. They should be entirely meaningless other than simply a handy way to identify the row. Eventually when you delete something you'll end up with a gap anyway, so who cares? Clearly you do care, but I just wondered why? It's not something which people are generally concerned about as far as I know. – ADyson May 08 '18 at 12:51
  • Already answered here: https://stackoverflow.com/questions/23516958/on-duplicate-key-auto-increment-issue-mysql?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – LDMdev May 08 '18 at 12:56
  • 1st run a select query for primary keys if it return 0 rows then perform insert query else run update query – Mustahsan May 08 '18 at 13:03
  • It's the same question, but they accepted an answer that doesn't actually answer the question (only explains why, which I know). I read this nice article https://www.percona.com/blog/2011/11/29/avoiding-auto-increment-holes-on-innodb-with-insert-ignore/ and hoped a similar solution for my case would exist. – user1111929 May 08 '18 at 13:03
  • 1
    If you care about the value of the id, then you're not really understanding relational databases. – Strawberry May 08 '18 at 13:05

0 Answers0