List:General Discussion« Previous MessageNext Message »
From:Mark Goodge Date:January 24 2011 3:59pm
Subject:Re: CURRENT insert ID
View as plain text  
On 24/01/2011 15:42, Jerry Schwartz wrote:
>> -----Original Message-----
>> From: Donovan Brooke [mailto:lists@stripped]
>> Sent: Friday, January 21, 2011 7:28 PM
>> Cc: mysql@stripped
>> Subject: Re: CURRENT insert ID
>>
>> Just an idear..
>>
>> Don't auto_increment the main table.. create a unique Id table,
>> auto_increment that, and grab that value first for use with both fields
>> in your main table.
>>
> [JS] I've thought of that, but it creates another problem.
>
> Let's say I add a record to the ID table, thereby auto-incrementing its key.
> Now I need to retrieve that key value. How do I do that while retaining some
> semblance of data integrity? I'd have to do something like "SELECT MAX()",
> which fails to retrieve "my" value if someone else has inserted a record in
> the meantime.

That's what LAST_INSERT_ID() is for:

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

This is on a per-connection basis, so even if another connection inserts 
a line in the meantime your query will return the auto-increment value 
of the line you inserted.

Most programming languages with an interface to MySQL, either built-in 
or via a module, implement this natively. For example, in PHP:

mysql_query("insert into mytable set name = 'foo'");
$id = mysql_insert_id();

the value of $id will be the auto-increment number from the line you 
just inserted.

Mark
-- 
http://mark.goodge.co.uk
http://www.ratemysupermarket.com
Thread
CURRENT insert IDJerry Schwartz21 Jan
  • Re: CURRENT insert IDjoao21 Jan
    • Re: CURRENT insert IDDarryle Steplight21 Jan
  • Re: CURRENT insert IDjoao21 Jan
    • RE: CURRENT insert IDJerry Schwartz21 Jan
      • Re: CURRENT insert IDDonovan Brooke22 Jan
        • Re: CURRENT insert IDJesper Wisborg Krogh22 Jan
        • RE: CURRENT insert IDJerry Schwartz24 Jan
          • Re: CURRENT insert IDMark Goodge24 Jan
      • Re: CURRENT insert IDJaime Crespo Rinc√≥n24 Jan
  • Re: CURRENT insert IDCarsten Pedersen23 Jan
    • Re: CURRENT insert IDCarsten Pedersen23 Jan
    • RE: CURRENT insert IDJerry Schwartz24 Jan
Re: CURRENT insert IDMichael Dykman21 Jan
  • RE: CURRENT insert IDJerry Schwartz21 Jan
    • RE: CURRENT insert IDJerry Schwartz21 Jan
      • RE: CURRENT insert IDJerry Schwartz21 Jan
      • Re: CURRENT insert IDMichael Dykman21 Jan
        • RE: CURRENT insert IDJerry Schwartz21 Jan