Resetting Auto-Increment ID in MySQL without Dropping Columns

If you've ever faced the challenge of deleting a large amount of data in a table, only to find that the auto-increment ID column has large gaps between values, here's a simple solution to reset the ID column without dropping or adding any columns.

Below are the steps to achieve this: ??

Step 1: ?? Check the total count of records in the table:

SELECT COUNT(*) FROM world.tbl2;        

Step 2: ?? Find the next auto-increment value:

SELECT AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'world' AND TABLE_NAME = 'tbl2';        

You may notice a significant difference between the total count of records and the next auto-increment value.

Step 3: ?? Review the table data:

SELECT * FROM world.tbl2 ORDER BY id DESC;        

Step 4: ??? Delete unnecessary data:

DELETE FROM world.tbl2 WHERE created_at='2024-07-24';        

Step 5: ?? Reset ID column values:

SET @counter=0;
UPDATE world.tbl2 SET id = (@counter := @counter+1) WHERE id > 0;        

Step 6: ?? Update the auto-increment value:

SET @max_id = (SELECT MAX(id) + 1 FROM world.tbl2);
SET @sql = CONCAT('ALTER TABLE world.tbl2 AUTO_INCREMENT = ', @max_id);
PREPARE st FROM @sql;
EXECUTE st;        

Step 7: ? Verify the updated auto-increment value:

SELECT AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'world' AND TABLE_NAME = 'tbl2';        

Now, the total count and the maximum ID value in the table should match. If you find this useful, please share it with your connections! ??