Re: @@identity
Aaron wrote: How can I select the last record that was inserted? An ASP/VB example would be great too! I believe you have it already. I use: select last_insert_id() as MyID but from memory @@identity is a synonym of last_insert_id() Dan -- sig Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: @@identity
My Aaron, I don´t know any means to get the last inserted record into a MySQL table, as built-in command. What I would do to get this record is to have a timestamp field in the record (mmddmilisecsinday) to solve the problem. That´s it. Leandro Neves. - Original Message - From: Aaron [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 10, 2004 7:04 PM Subject: @@identity How can I select the last record that was inserted? An ASP/VB example would be great too! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: @@identity
Rough example. Assuming that you are using the same connection since last_insert_id() is connection specific. rset.open INSERT INTO table1 values(someValue) rset.open SELECT last_insert_id() as identity identity = rset.fields(identity) -Original Message- From: Aaron To: [EMAIL PROTECTED] Sent: 3/10/04 4:04 PM Subject: @@identity How can I select the last record that was inserted? An ASP/VB example would be great too! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: @@identity
Great - thanks! -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: March 10, 2004 5:36 PM To: 'Aaron '; '[EMAIL PROTECTED] ' Subject: RE: @@identity Rough example. Assuming that you are using the same connection since last_insert_id() is connection specific. rset.open INSERT INTO table1 values(someValue) rset.open SELECT last_insert_id() as identity identity = rset.fields(identity) -Original Message- From: Aaron To: [EMAIL PROTECTED] Sent: 3/10/04 4:04 PM Subject: @@identity How can I select the last record that was inserted? An ASP/VB example would be great too! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IDENTITY column
Hi, I think what you need is an auto increment (primary key) column. When a record is inserted, it will automatically generate the next number in the sequence. Being a primary key, the column must be unique (i.e no two rows can be the same), so you can identify a specify record in the table. When you insert a record, you can run SELECT LAST_INSERT_ID(); to retrieve the id that was used. The LAST_INSERT_ID is determined on a per connection basis, so you are guaranteed to get the last id that _you_ inserted, even if there are other users inserting records, as long as you do not run another INSERT. As for 3, you should not worry about how MySQL stores the rows. You should specify an ORDER BY clause in your SELECT statements to get the order in which you want. If you want to know, MySQL stores the rows ascending (1 is first, 2 is second, ...). However, if a record gets deleted from the table, then MySQL will try to re-use the space that once held that row. Check out the following links: http://www.mysql.com/doc/en/CREATE_TABLE.html http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html HTH Matt At 01:14 PM 12/23/2003, you wrote: Hi, I am a beginner in using database. And I appreciate your support and help. When we first create a table, 1. is it possible to create a column that identifies each record that is to be inserted? 2. If we can create this IDENTITY column, how do we create it? Do we set a maximum to the value of this column? Or the value simply increases as the number of records get inserted into the table grows? 3. when we try to insert the first record to this table, does this record go to the first row in the table? And the value of the IDENTITY for this record is 1? When we try to insert the second record to this table, does the second record automatically go to the second row in the table? And the value of the IDENTITY is 2? __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: @@identity
This may not be the correct solution exactly, but what I do to ensure that I get the correct ID back is the following: I know what I just inserted, therefore I can use this information in a WHERE clause to ensure retrieving the correct ID. Example: INSERT name='scott', age=20 into myTable WHERE ; thenSELECT id FROM myTable WHERE name='scott' AND age=20; Just one idea, someone might have a more efficient solution, afterall this is a common pattern! - Scott Carter -Original Message- From: Jonas Geiregat [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 18, 2003 1:37 PM To: 'Mysql' Subject: @@identity I'm using php + mysql for my project I want to get the last insert ID. I could use the php function mysql_last_id(); but I could aslo use @@identity. Now some people have advised me NOT to use @@identity, cause it's not save buggy sometimes slow .. is this true am I better of with the php function ? also I do my query insert something after that query I do an other query to get the last insert ID, if someone else does a insert query between those 2 query's won't that affect my last insert ID and won't I get the wrong one ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: @@identity
The below query is not good, due to the fact that if name and age aren't unique, I'll be returning multiple ids with it. One should always use the LAST_INSERT_ID() or after an insert, select max(id) from myTable. -Original Message- From: Carter, Scott [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 18, 2003 12:59 PM To: 'Jonas Geiregat'; 'Mysql' Subject: RE: @@identity This may not be the correct solution exactly, but what I do to ensure that I get the correct ID back is the following: I know what I just inserted, therefore I can use this information in a WHERE clause to ensure retrieving the correct ID. Example: INSERT name='scott', age=20 into myTable WHERE ; thenSELECT id FROM myTable WHERE name='scott' AND age=20; Just one idea, someone might have a more efficient solution, afterall this is a common pattern! - Scott Carter -Original Message- From: Jonas Geiregat [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 18, 2003 1:37 PM To: 'Mysql' Subject: @@identity I'm using php + mysql for my project I want to get the last insert ID. I could use the php function mysql_last_id(); but I could aslo use @@identity. Now some people have advised me NOT to use @@identity, cause it's not save buggy sometimes slow .. is this true am I better of with the php function ? also I do my query insert something after that query I do an other query to get the last insert ID, if someone else does a insert query between those 2 query's won't that affect my last insert ID and won't I get the wrong one ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: @@identity
On Wednesday 18 June 2003 13:59, Carter, Scott wrote: This may not be the correct solution exactly, but what I do to ensure that I get the correct ID back is the following: I know what I just inserted, therefore I can use this information in a WHERE clause to ensure retrieving the correct ID. Example: INSERT name='scott', age=20 into myTable WHERE ; thenSELECT id FROM myTable WHERE name='scott' AND age=20; That works IFF name='scott' and age=20 is GUARANTEED to be unique. It's much better to get the last_insert_id() from a query. -- Eric Calvert kyconnection.com, inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: @@identity
One should always use the LAST_INSERT_ID() or after an insert, select max(id) from myTable. Select max(id) from myTable should never be used to get the last insert id. On a high traffic table you are not guaranteed it will be the ID for the record you just inserted, another could have been inserted between the two queries. Where as last_insert_id is per connection, so you don't have to worry about getting someone else's ID. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: @@identity
On 18-Jun-2003 Jonas Geiregat wrote: I'm using php + mysql for my project I want to get the last insert ID. I could use the php function mysql_last_id(); but I could aslo use @@identity. Now some people have advised me NOT to use @@identity, cause it's not save buggy sometimes slow .. is this true am I better of with the php function ? Don't use either one. PHP's mysql_last_id() is stored as a _long; it'll break on BIGINT ids. If you need to keep the value in a PHP variable, use 'SELECT LAST_INSERT_ID() as id', and retrive it as a string. also I do my query insert something after that query I do an other query to get the last insert ID, if someone else does a insert query between those 2 query's won't that affect my last insert ID and won't I get the wrong one ? No. The id is per connection. The return value is the last insert performed by your connection. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: @@identity
Don Read wrote: On 18-Jun-2003 Jonas Geiregat wrote: I'm using php + mysql for my project I want to get the last insert ID. I could use the php function mysql_last_id(); but I could aslo use @@identity. Now some people have advised me NOT to use @@identity, cause it's not save buggy sometimes slow .. is this true am I better of with the php function ? Don't use either one. PHP's mysql_last_id() is stored as a _long; it'll break on BIGINT ids. If you need to keep the value in a PHP variable, use 'SELECT LAST_INSERT_ID() as id', and retrive it as a string. also I do my query insert something after that query I do an other query to get the last insert ID, if someone else does a insert query between those 2 query's won't that affect my last insert ID and won't I get the wrong one ? No. The id is per connection. The return value is the last insert performed by your connection. Regards, Why did they make select @@identity then ? Is it just cause most other db's have this thing ? and what's the difference between @@identity and select last_insert_id() ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: @@IDENTITY
Even the last_insert_id() returns 0
is it coz the table type is INNODB
- Original Message -
From: Jan Peuker [EMAIL PROTECTED]
To: Arul [EMAIL PROTECTED]
Sent: Friday, June 21, 2002 4:16 PM
Subject: Re: @@IDENTITY
You are using M$-Syntax, in MySQL you should use:
SELECT last_insert_id()
http://www.mysql.com/doc/e/x/example-AUTO_INCREMENT.html
regards,
jan
- Original Message -
From: Arul
To: MySQL
Sent: Friday, June 21, 2002 12:17 PM
Subject: @@IDENTITY
Hi
I m using mysql 3.23.51 on win 2k
Well..I have a user table where i have the userid column as primary key
and
auto_increment column and name as varchar(25).
I insert values in the table such as
Insert into user (name) values ('AA');
Then When i do select @@Identity in the next time , it tells me as 0
Why is this happenning..
I need to get the last inserted value of the primary key ..right
-Arul
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: @@IDENTITY
On Friday 21 June 2002 06:55, Arul wrote:
No, but last_insert_id() is only valid until you do another query on the same
database handle. The only explanation I can think of is that somehow you're
code is interjecting some other query between the insert and the call to
last_insert_id(). Maybe a commit? I'd make sure that the very next thing you
do after insert is to get that id.
Even the last_insert_id() returns 0
is it coz the table type is INNODB
- Original Message -
From: Jan Peuker [EMAIL PROTECTED]
To: Arul [EMAIL PROTECTED]
Sent: Friday, June 21, 2002 4:16 PM
Subject: Re: @@IDENTITY
You are using M$-Syntax, in MySQL you should use:
SELECT last_insert_id()
http://www.mysql.com/doc/e/x/example-AUTO_INCREMENT.html
regards,
jan
- Original Message -
From: Arul
To: MySQL
Sent: Friday, June 21, 2002 12:17 PM
Subject: @@IDENTITY
Hi
I m using mysql 3.23.51 on win 2k
Well..I have a user table where i have the userid column as primary key
and
auto_increment column and name as varchar(25).
I insert values in the table such as
Insert into user (name) values ('AA');
Then When i do select @@Identity in the next time , it tells me as 0
Why is this happenning..
I need to get the last inserted value of the primary key ..right
-Arul
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED] Trouble
unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: @@IDENTITY
-Original Message- From: Richard Davidson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 29, 2002 1:31 PM To: MySql List; [EMAIL PROTECTED] Subject: FYI: @@IDENTITY I don't know if this is just weirdness on my system but I thought I would pass this on in case anyone runs into this problem. I'm converting some Java/JDBC code from a commercial DBMS to MySQL. The existing code executed the SQL statement Select @@identity after an INSERT to retrieve the autoincrement value. But, MySQL would never return from this query -- just hang until I stopped and restarted the service. After some trial-and-error-and-error, I found that @@IDENTITY worked as expected but @@identity would cause this hang symptom. Moral of the story: don't use lower case letters in SELECT @@IDENTITY. This is fixed from MySQL 3.23.50 and 4.0.2 versions, so you can give a try with the 3.23.50 from: http://www.mysql.com/downloads/mysql-3.23-pre.html Make a note that, the Windows binaries are not pre-releases, and they are official and can be used in production. Regards, Venu -- For technical support contracts, go to https://order.mysql.com __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Venu [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer /_/ /_/\_, /___/\___\_\___/ California, USA ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
