List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:July 18 2001 5:41pm
Subject:Re: How does this work in MySQL?
View as plain text  
In the last episode (Jul 18), Bryan Coon said:
> In this paradigm, my colleague says that there are no guarantees that
> the records will be retrieved in any kind of order, and may in fact
> be random. How does this work?
> 
> He also states that auto_increment does not guarantee consistent
> increments. All it does is guarantee increasing increments.  So in
> fact what he says is that if I insert 5 new records into an empty
> table with id as auto_increment, the corresponding ids may be
> 1,5,7,23,24... or 10,19,21,30,43, etc...
> 
> I had thought that auto_increment incremented by 1, assuming the
> paradigm described above, and no deletes from the table.
> 
> His reasoning for both of these examples is that the data may be
> written to any physical location, and therefore retrieved in any
> order.  I am a bit suspicious of this, as I had thought that the new
> data in a table was appended to the table (again using the paradigm
> above as the case).

You're talking about specific case (empty table, one user), but his
statements are true even if the table has gaps in it or there are
multiple people inserting records to the table.  If there are gaps in
the table due to previously-deleted records, then INSERTed data may be
placed into the middle of the table.  If there are multiple users
inserting records, a single user's view of the auto_increment field
will look like there are "jumps".

-- 
	Dan Nelson
	dnelson@stripped
Thread
How does this work in MySQL?Bryan Coon18 Jul
  • Re: How does this work in MySQL?Dan Nelson18 Jul
  • Re: How does this work in MySQL?Mike Baranski18 Jul