How to arrange ID in a mysql database after deleting a row?

Question

I have a table in mysql,i delete some rows but now the id's become disordered like 8,10,11,13,14,16 etc(this is just an example).its almost huge data,so how can i ordered this by using a query?

Answer 1

Short answer: You don't.

Long answer: The row id either already has semantic meaning, in which case you would most certainly not want to be haphazardly "renumbering" 8 to 1 and 10 to 2 and thus changing the data it represents; or, it is a meaningless pseudo-key in which case it does not matter what the values are - and you can still ORDER BY it, gaps or no gaps.

XY answer: If you want a "row number" for some reason in a query, you can use the following trick, which increments a variable over the result set. Note here I am not ordering by a meaningless id, but by some column with meaning, such as dateInserted.

SET @rn = 0;
SELECT @rn := @rn + 1 as rn, *
FROM myTable
ORDER BY dateInserted

Answer 2

Use code same as this. I hope this will help you.

SET @count = 0;# MySQL returned an empty result set (i.e. zero rows).
UPDATE `ourtheme_options` SET `ourtheme_options`.`id` = @count:= @count + 1;

Answer 3

If you are using an auto incrementing value don't bother because the next inserted row will be where your increment value was at the last insert.. To use your example, 8,10,11,13,14,16 will become 1,2,3,4,5,6 but next time you will insert a row it will still be as 17 unless you re-set the increment value to 7

Also, i'm sure there a valid reason why you should NOT do that but i don't know it..

regardless To answer your question, you can use something like:

SET @n=0;
UPDATE `table_name` SET `id` = @n := @n + 1 ORDER BY `id`

Further more, you could use MAX to insert next row with an +1 value, but then again you should not use that either. see MySQL auto_increment vs max

See my question : Mysql Incrementing variable performance with a reference to MySQL update a field with an incrementing variable