List:General Discussion« Previous MessageNext Message »
From:Thimble Smith Date:July 22 1999 8:17am
Subject:Re: Unique IDs in multiuser environment
View as plain text  
At 11:02, 19990722, Christos J. Pontikis wrote:
>I have a problem when last row in a table is deleted and another
>row takes the same ID.

The manual has a discussion of this, under the documentation for

      The last ID that was generated is maintained in the server
      on a per-connection basis. It will not be changed by
      another client. It will not even be changed if you update
      another AUTO_INCREMENT column with a non-magic value (that
      is, a value that is not NULL and not 0). If expr is given
      as an argument to LAST_INSERT_ID() in an UPDATE clause,
      then the value of the argument is returned as a
      LAST_INSERT_ID() value. This can be used to simulate
      sequences: First create the table: 

      mysql> create table sequence (id int not null);
      mysql> insert into sequence values (0);

      Then the table can be used to generate sequence numbers
      like this: 

      mysql> update sequence set id=LAST_INSERT_ID(id+1);

      You can generate sequences without calling
      LAST_INSERT_ID(), but the utility of using the function
      this way is that the ID value is maintained in the server
      as the last automatically-generated value. You can retrieve
      the new ID as you would read any normal AUTO_INCREMENT
      value in MySQL. For example, LAST_INSERT_ID() (without an
      argument) will return the new ID. The C API function
      mysql_insert_id() can also be used to get the value. 

This is one way to handle it.  Also, it's worth noting that the
behavior of auto_increment keys is going to change in the future,
so that the problem you're having does not exist.  When in the
future, I'm not sure.

Unique IDs in multiuser environmentChristos J. Pontikis22 Jul
  • Re: Unique IDs in multiuser environmentThimble Smith22 Jul
  • Re: Unique IDs in multiuser environmentMiso Pach22 Jul
  • Re: Using SQL variables(Pete Harlan)23 Sep
Re: Unique IDs in multiuser environmentChristos J. Pontikis23 Jul
  • Re: Unique IDs in multiuser environmentBenjamin Pflugmann23 Jul
    • Re: Unique IDs in multiuser environmentThimble Smith23 Jul
Re: Using SQL variablesDirector General: NEFACOMP23 Sep
Re: Using SQL variablesDirector General: NEFACOMP24 Sep
Re: Using SQL variablesDirector General: NEFACOMP24 Sep
Re: Using SQL variablesDirector General: NEFACOMP25 Sep