Re: How to Find Most Recent Autoincrement Index Assigned???

[Rhino]

- Original Message - 
From: "David T. Ashley" <[EMAIL PROTECTED]>

To: 
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???

[abhishek jain]

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???

[mysql]

>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???

[Michael Stassen]

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()



Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How to Find Most Recent Autoincrement Index Assigned???

[David T. Ashley]

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.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]