The primary key id is returned when MySql is updated, and the primary key id is returned by mysql.

The primary key id is returned when MySql is updated, and the primary key id is returned by mysql.

Recently, when I was working on a project, I encountered a logic that the id of the record needs to be returned when I modify a single record.

Let's first talk about my use environment, springMVC + MyBatis. So I checked the results and recorded them for future reference only.

The specific logic is to include a nested query under multiple conditions to update a record and return the primary key id of the record. A variable is used in this query statement.Update_idWhen executing the update statement, the id of this record is assigned to its own id, and its own id is assignedUpdatge_idThis variable is the final purpose of the 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:#ff0000;">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 in Mapper. xml are used as select labels.

<select id="exchangeVoucher" parameterType="hashmap" resultType="java.lang.Integer">   SQL</select>

The method in java Dao is:

int exchangeVoucher(Map<String, Object> queryParams);

This is simple. This function is initially completed and the logic is optimized and adjusted.

============================================================== ===

Well, we will discuss the red color in the preceding SQL statement below the gorgeous split line. It is generally not feasible to use nested queries in upadte and delete in MySQL, however, if you use this nested query AS an object, then you can query this object.

However, here, MySQL locks all index records that have been scanned by the WHERE condition, leading to a deadlock in the red statement during high concurrency. The problem arises: how to control the conditions of the red font, because the statements in the red area are only used to count the number of statements under certain conditions, they can only be independently controlled through code logic.

Finally, the red font code is removed and logical judgment is performed before the update statement is executed.

If anyone has a better method, I hope you can give me feedback. Thank you. I am here to introduce myself.