[thelist] MySQL generated id

Andrew Chadwick andrew.chadwick at prnewswire.co.uk
Fri Sep 21 04:12:21 CDT 2001

On Thu, Sep 20, 2001 at 11:47:05AM -0700, Joe Crawford wrote:
> noah wrote:
> > When I insert a new record into a MySQL table (via PHP), I use MySQL's
> > auto-increment to create a unique id number for that record. What I'd like
> > to do is immediately have access to the id number in PHP. I could use a
> > separate select statement, but since I don't know the unique id, I run the
> > risk of returning multiple records, rather than just the one I just inserted.
> > 
> > Is there anyway to immediately have access to the id number generated when
> > the record is inserted?
> I believe mysql_insert_id() is a php function to do what you want --
> documentation here:
> http://www.php.net/manual/en/function.mysql-insert-id.php
> I'm not sure if there's a native mysql way to do what you want though.
> :-\

You can almost do this in native MySQL by locking the table for writes
while you add the new record. You can use LAST_INSERT_ID() if your
field is auto_incrementing, but the general solution is:

  LOCK TABLES Animals READ; /* this assumes we're creating a complete
                             * record with a single
                             * insert statement */
  SELECT MAX(Id)+1 FROM Animals;
      /* ... and store it in scripting var $__id__ */
     SET Id=$__id__,


You have to store the ID in a scripting language variable, then
interpolate it into the INSERT statement. How that happens is
dependent on the scripting language you use, so I've left that bit

Have a look at a MySQL manual for the difference between a READ and a
WRITE lock, and consider whether it's OK for other client processes to
be able to read a partial record.

Andrew Chadwick, UNIX/Internet Programmer, PR Newswire Europe, Oxford
The views or opinions above are solely mine and are not necessarily those
of PR Newswire Europe. The message may contain privileged or confidential
information; if you are not a named recipient, notify me, and do not copy,
use, or disclose this message. <andrew.chadwick at prnewswire.co.uk>.

More information about the thelist mailing list