MySQL gets the update row's primary key ID "Go"

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

MySQL gets the update row's primary key ID "Go"