Re: How to Find Most Recent Autoincrement Index Assigned???
From 'Section 13.5.3 SET Syntax' of the 5.0.18 ref manual: IDENTITY = value The variable is a synonym for the LAST_INSERT_ID variable. It exists for compatibility with other database systems. You can read its value with SELECT @@IDENTITY, and set it using SET IDENTITY. INSERT_ID = value Set the value to be used by the following INSERT or ALTER TABLE statement when inserting an AUTO_INCREMENT value. This is mainly used with the binary log. LAST_INSERT_ID = value Set the value to be returned from LAST_INSERT_ID(). This is stored in the binary log when you use LAST_INSERT_ID() in a statement that updates a table. Setting this variable does not update the value returned by the mysql_insert_id() C API function. Eg. mysql select @@last_insert_id; +--+ | @@last_insert_id | +--+ |0 | +--+ 1 row in set (0.04 sec) mysql select @@insert_id; +-+ | @@insert_id | +-+ | 0 | +-+ 1 row in set (0.00 sec) mysql select @@identity; ++ | @@identity | ++ | 0 | ++ 1 row in set (0.00 sec) mysql set @@last_insert_id = 5; Query OK, 0 rows affected (0.00 sec) mysql select @@last_insert_id; +--+ | @@last_insert_id | +--+ |5 | +--+ 1 row in set (0.00 sec) mysql select @@insert_id; +-+ | @@insert_id | +-+ | 5 | +-+ 1 row in set (0.00 sec) mysql select @@identity; ++ | @@identity | ++ | 5 | ++ 1 row in set (0.00 sec) So it appears you can use either of the three variables above to achieve the same effect. Regards Keith On Sat, 22 Apr 2006, Michael Stassen wrote: To: David T. Ashley [EMAIL PROTECTED] From: Michael Stassen [EMAIL PROTECTED] Subject: Re: How to Find Most Recent Autoincrement Index Assigned??? David T. Ashley wrote: I'm using PHP, and I sometimes INSERT new records in a table. MySQL assigns a new autoincrement int field on each INSERT ... nothing surprising there. It goes 1, 2, 3, etc. What query can I use to find out what value this int autoincrement assigned field was? I could of course SELECT based on what was just inserted, but that seems inefficient. Thanks for any help, Dave. LAST_INSERT_ID() http://dev.mysql.com/doc/refman/4.1/en/information-functions.html Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Find Most Recent Autoincrement Index Assigned???
Hi, Actually i update(increment by 1 ) a value in a table with only one row, ie. update table1 set col = col+1; Can i get the updated value without diong select in the same query with update. Because if i run Select after that than some time is lost and in the mean time any other process updates the table and i do not get the exact value. Thanks, Abhishek Jain On 4/22/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: From 'Section 13.5.3 SET Syntax' of the 5.0.18 ref manual: IDENTITY = value The variable is a synonym for the LAST_INSERT_ID variable. It exists for compatibility with other database systems. You can read its value with SELECT @@IDENTITY, and set it using SET IDENTITY. INSERT_ID = value Set the value to be used by the following INSERT or ALTER TABLE statement when inserting an AUTO_INCREMENT value. This is mainly used with the binary log. LAST_INSERT_ID = value Set the value to be returned from LAST_INSERT_ID(). This is stored in the binary log when you use LAST_INSERT_ID() in a statement that updates a table. Setting this variable does not update the value returned by the mysql_insert_id() C API function. Eg. mysql select @@last_insert_id; +--+ | @@last_insert_id | +--+ |0 | +--+ 1 row in set (0.04 sec) mysql select @@insert_id; +-+ | @@insert_id | +-+ | 0 | +-+ 1 row in set (0.00 sec) mysql select @@identity; ++ | @@identity | ++ | 0 | ++ 1 row in set (0.00 sec) mysql set @@last_insert_id = 5; Query OK, 0 rows affected (0.00 sec) mysql select @@last_insert_id; +--+ | @@last_insert_id | +--+ |5 | +--+ 1 row in set (0.00 sec) mysql select @@insert_id; +-+ | @@insert_id | +-+ | 5 | +-+ 1 row in set (0.00 sec) mysql select @@identity; ++ | @@identity | ++ | 5 | ++ 1 row in set (0.00 sec) So it appears you can use either of the three variables above to achieve the same effect. Regards Keith On Sat, 22 Apr 2006, Michael Stassen wrote: To: David T. Ashley [EMAIL PROTECTED] From: Michael Stassen [EMAIL PROTECTED] Subject: Re: How to Find Most Recent Autoincrement Index Assigned??? David T. Ashley wrote: I'm using PHP, and I sometimes INSERT new records in a table. MySQL assigns a new autoincrement int field on each INSERT ... nothing surprising there. It goes 1, 2, 3, etc. What query can I use to find out what value this int autoincrement assigned field was? I could of course SELECT based on what was just inserted, but that seems inefficient. Thanks for any help, Dave. LAST_INSERT_ID() http://dev.mysql.com/doc/refman/4.1/en/information-functions.html Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Find Most Recent Autoincrement Index Assigned???
- Original Message - From: David T. Ashley [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, April 22, 2006 12:53 AM Subject: How to Find Most Recent Autoincrement Index Assigned??? I'm using PHP, and I sometimes INSERT new records in a table. MySQL assigns a new autoincrement int field on each INSERT ... nothing surprising there. It goes 1, 2, 3, etc. What query can I use to find out what value this int autoincrement assigned field was? I could of course SELECT based on what was just inserted, but that seems inefficient. Thanks for any help, RTFM? If you search the MySQL manual on increment, you'll get several hits, one of which is 3.6.9 Using AUTO_INCREMENT. Here is the link: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html If you read that page, you should find your answer -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Find Most Recent Autoincrement Index Assigned???
David T. Ashley wrote: I'm using PHP, and I sometimes INSERT new records in a table. MySQL assigns a new autoincrement int field on each INSERT ... nothing surprising there. It goes 1, 2, 3, etc. What query can I use to find out what value this int autoincrement assigned field was? I could of course SELECT based on what was just inserted, but that seems inefficient. Thanks for any help, Dave. LAST_INSERT_ID() http://dev.mysql.com/doc/refman/4.1/en/information-functions.html Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
