Make MySQL auto-increment id (re) start from 1

Question

"BIG" UPDATE:

Ok I was getting the whole auto-increment point wrong. I though this would be an easier way to target the first, second, third and so row, but it is just the wrong approach.

You should instead care about that the auto_increments are unique and well... that they increment. You should use the for that.

I wont delete this question because I think it might be helpful for someone else with the same wrong idea, BUT BE WARNED! :)

---

I have a very simple MySQL table which went like this:

id    comment    user

1     hello      name1
2     bye        name2
3     hola       name3

Then I deleted the two first comments, the result:

id    comment    user

3     hola      name3

So now when I add comments:

id    comment    user

3     hola      name3
5     chau      name4
6     xxx       name5

My problem is that I would need that whenever a row gets deleted it should "start over" and look like this.

id    comment    user

1     hola      name3
2     chau      name4
3     xxx       name5

I would like to know how is it possible to some how "restart" the table so that it is "always" indexed 1, 2, 3 and so on.

Thanks in advance!!

---

I hope I have explained myself clear enough, I'm sorry for all my "plain english", feel free to edit if you think a word might be confusing :) and please ask for any clarification needed!

BTW: I did not add any of my code because this is a simplified situation and I though it be more confusing and less helpful to others, but I you think it would help (or is necessary) tell me about it!

Answer 1

Assuming there are no foreign key issues to deal with, this code will do it:

set @id:=0;
update mytable
set id = (@id := @id + 1)
order by id;

If there are foreign key issues, make sure your constraints are defined like this before you execute the update:

ALTER CHILD_TABLE ADD CONSTRAINT
FOREIGN KEY MYTABLE_ID REFERENCES MYTABLE
ON UPDATE CASCADE; -- This is the important bit

When it's all done, execute this to fix up the auto_increment value:

SELECT MAX(ID) + 1 FROM MYTABLE; -- note the output
ALTER TABLE MYTABLE AUTO_INCREMENT = <result from above>;

Answer 2

Disclaimer: I can't think of one valid reason to do this, and it can break stuff very bad. However, I'm adding this for the sake of completeness and demonstration purposes.

You could use this really ugly solution, please only do this if you're at gunpoint or your dog is held hostage!

-- Create a new veriable.
SET @newId:=0;

-- Set all id's in the table to a new one and
-- also increment the counter in the same step.
-- It's basically just setting id to ++id.
UPDATE
    yourTableHere
SET
    id=@newId:=@newId+1;

-- Now prepare and execute an ALTER TABLE statement
-- which sets the next auto-increment value.
SET @query:=CONCAT("ALTER TABLE yourTableHere AUTO_INCREMENT=", @newId+1);
PREPARE sttmnt FROM @query;            
EXECUTE sttmnt;
DEALLOCATE PREPARE sttmnt;  

This will reset all of the Ids to the position of the row in the table. Please be aware that this will reorder the rows to how MySQL gets them from the storage engine, so there's no guarantee on the order in any way.

If you have a system which is based on the Ids (like relationships between tables) then you'll be...well, let's say I hope you have a backup.