texelate texelate - 1 year ago 63
SQL Question

MySQL Auto increment: Is there any way to reassign IDs starting from 1?

I have a table with an auto increment ID that runs a lot of INSERTs and DELETEs resulting in big gaps in the numbers. I'm just wondering for the sake of neatness, is there a query that will start with the lowest ID, assign it 1 and work up +1 from there? The ID is not in a relation with any other column in the database.

So it would go from, e.g:




I don't want to reset the auto increment, I know how to do that. I was to actually change the IDs. Or is this considered bad practice?


Answer Source

Although it is not a good practice you could use a query like this:

Let's assume we have a table with two fiels id and value where id is of type INT.

insert into table (id, value)
select max(id)+1, 'value' from table

Be aware that with a lot of insert/update there could be problems if more than one queries runs simultaneously.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download