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
