| WIKI | FAQ | Tech FAQ | |
 http://csua.com/feed/ | |
| ||||||
| 5/24 |
| 2004/4/25-26 [Computer/SW/Database] UID:13368 Activity:nil |
4/24 In SQL, when I insert a row with sequence (auto_increment), I don't
know which sequence it is assigned to. How do I find out what number
I just inserted? I guess I could try (SELECT max(id) ...) then insert
max+1, but if I have 2 people doing it, I may be screwed. By the way
I'm using mysql so there is no transaction...
\_ Um, the whole point of auto_increment fields is that *you* don't
have to worry about them. The way you phrased your question seems
to suggest you're trying to perform an insert where you specify the
auto_increment. That's just silly.
\_ Take a look at:
http://dev.mysql.com/doc/mysql/en/Getting_unique_ID.html
http://dev.mysql.com/doc/mysql/en/mysql_insert_id.html
(If you ever switch to postgres you will have to rewrite this
to use curval/nextval instead)
to use curval/netval instead)
\_ fixed.
\_ That's funny, I'm using transactions on some InnoDB tables with
mysql 3....
\_ probably a typo. should be nextval |
| 5/24 |
|
| dev.mysql.com/doc/mysql/en/Getting_unique_ID.html When a new AUTO_INCREMENT value has been generated, you can also obtain it by executing a SELECT LAST_INSERT_ID statement mysql_query and retrieving the value from the result set returned by the statement. For LAST_INSERT_ID , the most recently generated ID is maintained in the server on a per-connection basis. It will not even be changed if you update another AUTO_INCREMENT column with a non-magic value that is, a value that is not NULL and not 0 . If you want to use the ID that was generated for one table and insert it into a second table, you can use SQL statements like this: INSERT INTO foo auto,text VALUESNULL,text; |
| dev.mysql.com/doc/mysql/en/mysql_insert_id.html INSERT statements that generate an AUTO_INCREMENT value by inserting LAST_INSERT_IDexpr into any column. INSERT statements that generate an AUTO_INCREMENT value by updating any column to LAST_INSERT_IDexpr . The value of mysql_insert_id is not affected by statements such as SELECT that return a result set. If the previous statement returned an error, the value of mysql_insert_id is undefined. Note that mysql_insert_id returns 0 if the previous statement does not use an AUTO_INCREMENT value. If you need to save the value for later, be sure to call mysql_insert_id immediately after the statement that generates the value. The value of mysql_insert_id is affected only by statements issued within the current client connection. Also note that the value of the SQL LAST_INSERT_ID function always contains the most recently generated AUTO_INCREMENT value, and is not reset between statements because the value of that function is maintained in the server. Another difference is that LAST_INSERT_ID is not updated if you set an AUTO_INCREMENT column to a specific non-special value. The reason for the difference between LAST_INSERT_ID and mysql_insert_id is that LAST_INSERT_ID is made easy to use in scripts while mysql_insert_id tries to provide a little more exact information of what happens to the AUTO_INCREMENT column. Previous / Next / Up / Table of Contents User Comments Add your own comment. Top / Previous / Next / Up / Table of Contents 1995-2004 MySQL AB. |