CODEWITHSUNDEEP
mysql
tyler5819
tyler5819

Reputation: 58

mySQL auto increment doesn't adjust to changes in database

So I have auto increment on my primary key. I was under the impression that if I have:

1. 2. 3. 4.

And remove two, I won't end up with this:

1. 3. 4.

But rather:

1. 2. 3.

Is this not the function of auto increment? If so, what is this I'm trying to achieve, and how can I do it?

Upvotes: 0

Views: 43

Answers (1)

Eugen Rieck
Eugen Rieck

Reputation: 65274

If you remove 2 from a set of 1,2,3,4 you end up with 1,3,4 - this keeps the identifying feature of each record, i.e. its primary key, intact, If you think about it, this is a good thing: Imagine a user clicking "delete 3" while you just deleted 2, so changing the meaning of "delete 3" while it is under way.

If you want to select a consecutive number together with some rows, use something like

SELECT
  -- fieldlist
  @num:=@num+1 AS rownum
FROM 
  -- table
  , (SELECT @num:=0) AS init
WHERE
 -- ...

Upvotes: 1

Related Questions