0

I am using mysql database. Now I am in the testing phase and I delete a lot of datasets. But even I deleted them the id is still incremented. So actually my last dataset Id would be 156 but if I insert a new dataset it starts at 200. Of course I would like my database to continue after the highest id.

GMB
  • 216,147
  • 25
  • 84
  • 135
Flo19
  • 81
  • 1
  • 5
  • Check here https://stackoverflow.com/questions/751522/how-to-change-identity-column-values-programmatically – Talha Shafique Sep 17 '19 at 11:06
  • May I ask _why_ you'd like there to be no gaps? It would be quite some hard work for the database to keep track of unused IDs to always use "holes" instead of just incrementing a simple counter to get the next available id. In other words, the tradeoff is that you get a much faster insert against that you get removed IDs that are never reused. – Joachim Isaksson Sep 17 '19 at 11:10
  • ok. That sounds plausible. – Flo19 Sep 17 '19 at 13:02

1 Answers1

1

You may change the value of the auto increment of a column using:

ALTER TABLE tablename AUTO_INCREMENT = <new_value>;

It is also possible to dynamically compute the maximum value of the counter in the table, and then assign it to the counter, using a prepared statement:

SELECT @last_id := MAX(ID) + 1 FROM mytable; 
PREPARE stmt FROM 'ALTER TABLE mytable AUTO_INCREMENT = ?';
EXECUTE stmt USING @max;
DEALLOCATE PREPARE stmt;
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Good answer for a quiet database. But `MAX(id)` is unreliable if other processes are inserting rows into the table due to race conditions. – O. Jones Sep 17 '19 at 11:58