0

I have a query that goes as follows:

insert into TABLE(COLUMNS) values(VALUES) on duplicate key update id=last_insert_id(id)

I use this to get an ID of inserted or existing row of data. 

$id=$connection->lastinsertid();

Well now I have a issue with my auto_increment jumping with each duplicate key update.

enter image description here

I am fairly certain on duplicate key update part is causing these jumps. Is this the source of the problem? Is there a way to fix this within the query without reseting auto_increment entirely every time?

Maciek Semik
  • 1,872
  • 23
  • 43
  • What is a value of [innodb_autoinc_lock_mode](https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_mode) settings on your MySql server ? – krokodilko Aug 20 '17 at 08:21
  • 1
    1.) The autoincrement is not designed to be consecutive, but to be unique. It doesn't reset on failure (even without `on duplicate key`) or rollbacks. If you need consecutive numbers, you have to provide them (e.g. 2nd table which stores ids) 2.) `last_insert_id()` is not specific to that table, but returns the last autoincrement used in ANY table in your whole system. 3.) It's not clear what you are trying to do. I guess you want to get a correct id if someone tries to insert with an existing id. But this will update the *existing* row with a new id, not insert a new row with a corrected id. – Solarflare Aug 20 '17 at 09:07
  • 4.) If your intention is to get the id no matter if it exists or not, you should do it another way: first check if the row already exists: `select id from table ... ` (but with prepares statements, this is just pseudo code). If it finds a row, you have your id, otherwise insert and use `lastinsertid`. This will actually even reduce the number of failed attempts/on duplicate key-updates, so give you more consecutive numbers (apart from rollbacks). – Solarflare Aug 20 '17 at 09:14
  • What did you learn in your research? – philipxy Aug 27 '17 at 06:30
  • I didn't look much into it. I reset my auto_increment before my insert to prevent the keys from jumping – Maciek Semik Aug 27 '17 at 06:52
  • @MatthewSemik I wonder what would be the performance trade-off if you reset the auto_increment just before every insert? – Coder Absolute Oct 02 '19 at 03:36

0 Answers0