Auto_increment fun!

Discovered some fun things in MySQL today..

Set the variable @id to 0:
SET @id = 0;

Update the column id to be id + 1 – this will re-index your id column in one fell swoop. Very nice:
UPDATE tblname SET id = (@id := @id + 1);

Set the first value for an auto_increment column:
(this won’t work if there are rows with a higher value already in the table)

All in all, a few nice bits and pieces for cleaning up your tables.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.