Go: MySQL gets the primary key ID of the update row

In some cases we need to update the status of a record in the datasheet and then take it out, but if you don't have a primary key to confirm the unique record before the update, there's no way to know which record is being updated.

To illustrate the following:

There is a program for issuing novice cards, there are two common scenarios for designing a database:

Scenario One: Use a table, the novice card and the collection record together, so that the main field is the Novice card (primary key), User ID (unique), pick up status (not necessary), etc.

In this case, the database operation is simple, a direct update SQL, the user ID updated to this table, and then based on the user ID and then select the better. However, there will be a lot of efficiency when the record is not discussed.

Scenario Two: The use of two tables, one for the new card, and another to store the collection records. The Novice card table contains the Novice card (primary key), the status of the novice card and other fields.

There are two ways to do this:

First, select a record from the Novice card list, then update its status, and then insert it into the Pickup record table.

But the biggest problem with this approach is that in high concurrency situations, multiple users select the same record, so that only one person succeeds and others fail.

The second is to update a record from the novice card, and then remove the record and insert it into the collection record table. Because it is the first update and then select so good to adapt to high concurrency situation,

But now you have the question: how do I get the ID of the record I just updated?

The following code is the answer from the StackOverflow, borrow:

 set  @update_id  :=  0;=  ' value ' , ID =  (select  @update_id  := Id where some_other_row =  ' blah ' 1; Select  @update_id           

The general idea is to first declare a user variable @update_id, and then update the data with more than one field, that is, the current primary key value is updated to the current primary key value (in fact, is not updated), update the primary key field is not the purpose, just to assign the current primary key value to @update_id, This is the phrase: (SELECT @update_id: = ID). (Personal understanding, the level of limited may be a discrepancy)

In addition, if you update more than one record, you can also use the following method

SET @uids  := null update footable SET foo =  ' bar '  WHERE fooid > 5 and Span class= "pun" > ( select  @uids  := Concat_ws (, Fooid,  @uids )  select  @uids         

Note: The above method does not apply to a have, GROUP by, or ORDER BY clause, otherwise the result may differ from the expected.

Refer to the official manual for instructions http://dev.mysql.com/doc/refman/5.1/zh/language-structure.html

Transferred from: http://blog.csdn.net/rainday0310/article/details/25037295

StackOverflow Questions:

Http://stackoverflow.com/questions/1388025/how-to-get-id-of-the-last-updated-row-in-mysql

MySQL last_insert_id

MySQL function can implement many of the functions we need, the following is the MySQL function last_insert_id () is one of them, I hope you learn MySQL function can be helpful.

Automatically returns the value that occurs for the first table set in the last insert or UPDATE query in the auto_increment column. 1. Mysql> SELECT last_insert_id (); 2. 195 generation ID is saved in the server after each connection. This means that the value returned by the function to a given client is the first Auto_increment value that the client produces to the most recent statement affecting the Auto_increment column .。 This value cannot be affected by other clients, that is, they produce their own auto_increment values. This behavior ensures that you can retrieve your ID without worrying about being affected by other clients, and that you do not need to lock. If you use a non-"magic" value to update the Auto_increment column of a row, the value of last_insert_id () does not change (in other words, a value that is not NULL or 0). IMPORTANT: If you use an INSERT statement to insert multiple rows, last_insert_id () returns only the value produced when the first row of data is inserted. The reason for this is that it makes it easier to rely on other servers to replicate the same INSERT statement. For example: 1. mysql> use Test;2. Database changed3. mysql> CREATE TABLE T (4. ID INT auto_increment not NULL PRIMARY key,5. Name VARCHAR (+) not NULL6.) ; 7. Query OK, 0 rows affected (0.09 sec) 8.9. Mysql> INSERT into T VALUES (NULL, ' Bob '); 10. Query OK, 1 row affected (0.01 sec) 11.12. Mysql> SELECT * from t;13. +----+------+14. | ID | Name |15. +----+------+16. | 1 | Bob |17. +----+------+18. 1 row in Set (0.01 sec) 19.20. Mysql> SELECT last_insert_id (); 21. +------------------+22. | LAST_INSERT_ID () |23. +------------------+24. | 1 |25. +------------------+26. 1 row in Set (0.00 sec) 27.28. Mysql> INSERT into T VALUES29. (NULL, ' Mary '), (null, ' Jane '), (null, ' Lisa '); 30. Query OK, 3 rows AFFECTed (0.00 sec) 31. Records:3 duplicates:0 warnings:032.33. Mysql> SELECT * from t;34. +----+------+35. | ID | Name |36. +----+------+37. | 1 | Bob |38. | 2 | Mary |39. | 3 | Jane |40. | 4 | Lisa |41. +----+------+42. 4 rows in Set (0.01 sec) 43.44. Mysql> SELECT last_insert_id ();//Note 45. +------------------+46. | LAST_INSERT_ID () |47. +------------------+48. | 2 |49. +------------------+50. 1 row in Set (0.00 sec) 51. Although the second query inserts 3 new rows into T, the first row of these rows is given an ID of 2, which is also the value returned by last_insert_id () if the record is ignored using INSERT ignore, the Auto_i The ncrement counter does not increment, and last_insert_id () returns 0, which reflects that no records were inserted.

= Common usage =============================================

If you give the parameter expr as to last_insert_id (), the value of the parameter is returned by the function and is remembered as the next value returned by LAST_INSERT_ID (). This can be used for analog sequences:

Create a table to control the order counter and initialize it:
CREATE TABLE sequence (id INT not NULL);
INSERT into sequence VALUES (0);

Use this table to produce such a sequence number:
mysql> UPDATE sequence SET id=last_insert_id (id+1);
Mysql> SELECT last_insert_id ();
->1;
The UPDATE statement increments the sequential counter and raises the next call to LAST_INSERT_ID () to return the upgraded value.  This value is retrieved by the SELECT statement. The mysql_insert_id () C API function can also be used to get this value.

You can generate a sequence without calling last_insert_id (), but the utility of using this function is that the ID value is stored in the server as an automatically generated value. It works for multiple users because multiple users can use the UPDATE statement and get their own sequence values using the SELECT statement (or mysql_insert_id ()). Not affect other clients that produce their own sequence values or are affected by other clients that produce their own sequence values.

Note that mysql_insert_id () will only be upgraded after the INSERT and UPDATE statements, so you cannot use the C API function to retrieve last_insert_id after executing other SQL statements such as Select or SET (expr ) corresponds to the value.

@winkey Zhao @NeoTags: last_insert_id mysql mysql gets the self-growth ID of the most recently inserted dataSource: http://blog.sina.com.cn/s/blog_5b5460eb0100nwvo.html MORE: http://blog.chinaunix.net/uid-167175-id-3981768.html

Go: MySQL gets the primary key ID of the update row