Auto Increment value according to previous

0
votes

When i delete one record from mysql table and insert new one on that place that time it is setting the auto increment column value to next i had inserted lastly which is not expected

I googled it on many sites but not getting exact result that i want I also tried by setting last_inserted_id()+1 as follows:

Alter table t1 auto_increment = last_insert_id() + 1;

But it is showing error

I want to set that value next to previous record's auto increment value,

Please Help, Thanks.

1
mysql

1 Answers

1
votes

Why do you want to do this? The contract of an auto increment column does not say that the id will always follow the next sequential value. It only says that it will always be unique, and generally increasing.

If you want to generate a sequence, use something like ROW_NUMBER (in MySQL versions 8+):

SELECT ROW_NUMBER() OVER (ORDER BY auto_increment) AS rn
FROM yourTable;

Or, in earlier versions of MySQL, use user variables:

SET @row_number = 0;

SELECT 
    (@row_number:=@row_number + 1) AS rn
FROM yourTable
ORDER BY auto_increment;