Return primary Key ID when MYSQL update

Recent projects have encountered the logic that the ID of the record needs to be returned when a single record is modified.

First talk about my use of the environment, Springmvc+mybatis, so the mother looked at the degree, the results recorded, only for future reference and we discuss together

The specific logic is that under multiple conditions, including a nested query, update a record, and return the record's primary key ID, in this query statement to use a variable update_id when the UPDATE statement is executed, the ID of this record is re-assigned to its own ID, and the ID of its own value is assigned to updatge_id This variable, which finally returns this variable, is the final purpose of this update.

SET @update_id: = 0; UPDATE activity_prize_voucher Apvset apv.mobile = 18888888888, apv.state = 1, Apv.exchange_time = Now (), apv.id = (SELECT @ UPDATE_ID: = id) whereapv.prize_id = 2AND Apv.state = 0AND Mobile is Nulland exchange_time is Null<span style= "color: #ff 0000; " >and (Selectvv.exchangecount <= 2000FROM (SelectCount (*) as Exchangecountfromactivity_prize_voucher apvvwhereapvv.prize_id = 2AND Apvv.state = 1AND date_format (now (), '%y%m%d ') = Date_format (Apvv.exchange_time, '%Y%m%d ') ) as VV) </span>limit 1; select@update_id;

labels within mapper.xml are used as select labels

<select id= "Exchangevoucher" parametertype= "HashMap" resulttype= "Java.lang.Integer" >   sql</select>

inside the Java DAO method is:

int Exchangevoucher (map<string, object> queryparams);

As simple as this, this function is initially completed and the logic is tuned

================= Split Line =========================

OK, in the ornate section of the line we are discussing the above SQL inside the red color, in MySQL if you want to upadte and delete inside the nested query is generally not possible, but if you will this nested query as an object, and then query this object should be OK.

However, here MySQL locks all index records scanned by the Where condition, when high concurrency causes the red statement to deadlock, then the problem, how to control the red font conditions, because the red area of the statement is only used to count the number of conditions, Therefore, independent control can only be done through code logic.

Finally, the red font code is put out, before executing the UPDATE statement to make a logical judgment to control.

If any of the talent has a better way, hope to give me feedback, thank you. I'm just here to stimulate.

Return primary Key ID when MYSQL update