On 24/01/2011 15:42, Jerry Schwartz wrote:
-----Original Message-----
From: Donovan Brooke [mailto:li...@euca.us]
Sent: Friday, January 21, 2011 7:28 PM
Cc: mysql@lists.mysql.com
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
