List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 30 2002 2:04am
Subject:Re: next insert id
View as plain text  
At 17:38 -0800 1/29/02, James Montebello wrote:
>Other people do know how to read manuals, Paul.
>
>If the original poster made any assumptions about the NEXT value in the
>auto increment field based on the value of LAST_INSERT_ID, that assumed
>value will be invalid the second another row is written to the table.

Which is why I said that you can't find out the value until you create
it.  Trying to get it ahead of time is a waste of time.  And as you point
out below, whether that is even useful is open to debate.

>
>If I did this:
>
>INSERT INTO foo SET bar='bar';
>SELECT LAST_INSERT_ID() -> 1, so next_value = 2
>...someone else writes one row...
>INSERT INTO foo SET bar='baz';
>SELECT LAST_INSERT_ID() -> 3, hey! is not the same as next_value!
>
>Same as if I used MAX()...
>
>LAST_INSERT_ID is simply the last value YOU inserted.

I don't think that I claimed otherwise.


>   It has no reliable
>relationship with any values in the table itself, and you can make no
>valid assumptions about values in the table unless you hold the lock for
>that table.
>
>And you CAN find out what it WILL be (contrary to your statement below),
>if you lock the table first, and use MAX().

Actually ...
That's not true, unless you can guarantee that the next AUTO_INCREMENT
value will be the maximum value currently in the table + 1.  Which is
not, in general, true.  It will be true for ISAM tables, but can easily
be false for MyISAM tables, for example.  (Specifically, it's true
for table types that reuse deleted values at the top of the sequence.
For table types that guarantee a strictly monotonic sequence, MAX()
bears no relationship to the next AUTO_INCREMENT value.)

>
>Now, as for whether or not it's USEFUL to know the next value, that's
>another matter.  If what you want is to do something like this:
>
>INSERT some row w/o setting the auto_increment column
>SELECT the auto_increment ID for the row I just wrote
>
>Then LAST_INSERT_ID is, indeed, the right way to do that.  No locks
>required.

Right.

My (apparently quite evident) despair at the course of this thread is
not over the finer points of how LAST_INSERT_ID() behaves, but over
the extent to which its basic essential nature seems to go unnoticed.
That's too bad, because it saves many headaches and lots of messing
around that would otherwise be necessary.

Thread
next insert idJoel Wickard30 Jan
  • Re: next insert idJames Montebello30 Jan
    • Re: next insert idNathan30 Jan
      • Re: next insert idChristopher Thompson30 Jan
    • Re: next insert idChristopher Thompson30 Jan
      • Re: next insert idJames Montebello30 Jan
        • Re: next insert idPaul DuBois30 Jan
    • Re: next insert idPaul DuBois30 Jan
      • Re: next insert idJames Montebello30 Jan
        • RE: next insert idRoger Baklund30 Jan
        • Re: next insert idPaul DuBois30 Jan
  • RE: next insert idJohnny Withers30 Jan
    • Re: next insert idlaszlo30 Jan
      • Re: next insert idPaul DuBois30 Jan
  • Re: next insert idPaul DuBois30 Jan
    • Re: next insert idPaul DuBois30 Jan
      • Re: next insert idJames Montebello30 Jan
        • Re: next insert idPaul DuBois30 Jan
        • Re: next insert idDL Neil30 Jan
          • Re: next insert id (slightly OT)William R. Mussatto30 Jan
            • Re: next insert id (slightly OT)DL Neil30 Jan
              • Re: next insert id (slightly OT)William R. Mussatto30 Jan