Search code examples
phpdatabasecakephpincrement

CakePHP: How to make auto-increment values contiguous again after deleting rows

I have a blog with 4 remaining posts. The posts' ids are: 0, 1, 8, and 10. This is because I've deleted posts 2-7 and post 9. Is there any what I can "fix" these posts' ids so that in my database table they go from 1 - 3 and are contiguous?

Solution

  • You can drop the MySQL column manually, then re-add it with the same properties. The rows will be reordered.

    Alternatively, use these commands:

    SET @count = 0;
UPDATE `users` SET `users`.`id` = @count:= @count + 1;

    Replace users and id appropriately.

    You can reset your auto incrementing id column also with:

    ALTER TABLE `users` AUTO_INCREMENT = 1;

    Where users is replaced with the name of your table.