CODEWITHSUNDEEP
mysqlsql
Flo19
Flo19

Reputation: 91

Mysql auto-increment after delete

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.

Upvotes: 0

Views: 200

Answers (1)

GMB
GMB

Reputation: 222702

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;

Upvotes: 1

Related Questions