Re: Pragmatically changing a "Record Number"

On Aug 29, 2007, at 2:27 PM, Jim Lucas wrote:

Jason Pruim wrote:

3. Ask yourself, "Is it important to keep the auto-increment field contiguous?"

The main reason for changing it is I do currently have an option to sort by record number, although, if I add a Record number in php, but still have it sort based off of the record number stored in the database, it would still sort right, but also look right...

Are you thinking mysql reuses auto-increment number? If so, your mistaken.

No, I am aware that it doesn't reuse the auto increment numbers.

MySQL will not reuse an auto-increment unique value. say you have an empty table with an auto-increment column. You add 5 row your next id to be used is 6. if you delete id # 2 and 3 the next id to be used will still be 6 not 2

And what I'm looking for is away to take rows 4 and 5 and move them to rows 2 and 3 so the next record inserted would be row 4 :)

it does not go back and fill in the holes/gaps --Jim Lucas "Some men are born to greatness, some achieve greatness,

       and some have greatness thrust upon them."

Twelfth Night, Act II, Scene V

    by William Shakespeare

-- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com japruim@raoset.com