MySQL 更新資料並返回主鍵 ID 技巧：利用用戶變數實現

MySQL 更新資料並返回主鍵 ID 技巧：利用用戶變數實現

應用場景

SpringMVC + MyBatis 專案中，需在多條件（含巢狀子查詢）下更新單筆記錄，並返回該記錄主鍵 ID。

核心 SQL 實作

SET @update_id := 0;

UPDATE activity_prize_voucher apv
SET apv.mobile = 18888888888,
    apv.state = 1,
    apv.exchange_time = NOW(),
    apv.id = (SELECT @update_id := id)  -- 將 id 賦值給變數，自身 id 不變
WHERE apv.prize_id = 2
  AND apv.state = 0
  AND apv.mobile IS NULL
  AND apv.exchange_time IS NULL
  AND (
    SELECT vv.exchangeCount <= 2000
    FROM (
      SELECT COUNT(*) AS exchangeCount
      FROM activity_prize_voucher apvv
      WHERE apvv.prize_id = 2
        AND apvv.state = 1
        AND DATE_FORMAT(NOW(), '%Y%m%d') = DATE_FORMAT(apvv.exchange_time, '%Y%m%d')
    ) AS vv
  )
LIMIT 1;

SELECT @update_id;  -- 返回剛更新記錄的 ID

MyBatis Mapper 配置

<select id="exchangeVoucher" parameterType="hashmap" resultType="java.lang.Integer">
  -- 上述完整 SQL
</select>

DAO 介面定義

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

原理解析

• 利用 MySQL 用戶變數 @update_id 暫存更新行的主鍵
• apv.id = (SELECT @update_id := id) 在更新時將 id 賦值給變數，因設定值為自身 id，故資料不變
• 最後 SELECT @update_id 取得目標 ID
• Mapper 使用 <select> 標籤而非 <update>，因需返回結果集

此技巧避免了額外查詢，單次資料庫往返完成更新與 ID 獲取，適合高併發兌換券、搶單等場景。