MYSQL擷取更新行的主鍵ID 【轉】

標籤：

在某些情況下我們需要向資料表中更新一條記錄的狀態，然後再把它取出來，但這時如果你在更新前並沒有一個確認惟一記錄的主鍵就沒有辦法知道哪條記錄被更新了。

 

舉例說明下：

有一個發放新手卡的程式，設計資料庫時常見的方案有兩種：

方案一：使用一張表，新手卡和領取記錄都在一起，這樣主要欄位就是新手卡（主鍵）、使用者ID（惟一）、領取狀態（非必要）等

這樣的話資料庫操作就簡單了，直接一條update sql，將使用者id更新到這張表裡，然後根據使用者ID再select出來就好了。但這樣記錄很多時就會有效率的問題，暫不討論。

方案二：使用兩張表，一張存放新手卡，另外一張存放領取記錄。新手卡表裡面有新手卡（主鍵）、新手卡狀態等欄位。

在操作時也可以有兩種方式：

一是先從新手卡表中select出一條記錄，然後去更新它的狀態，再之後插入到領取記錄表中。

但這種方式最大的問題在於高並發情況下，會出現多個使用者select出了同一條記錄，這樣就只能有一個人成功，其他人會失敗。

二是先從新手卡中更新一條記錄，然後取出這條記錄插入到領取記錄表中。由於是先update再select所以很好適應高並發的情況，

但是現在就遇到前面說的問題了：怎樣擷取剛才更新記錄的ID呢？

下面代碼是從stackoverflow上找到的答案，借用一下：

SET @update_id := 0;UPDATE some_table SET row = ‘value‘, id = (SELECT @update_id := id)WHERE some_other_row = ‘blah‘ LIMIT 1; SELECT @update_id;

大致思路就是首先聲明一個使用者變數 @update_id ，之後在update資料時要多更新一個欄位，就是將當前主索引值更新為當前主索引值（其實就是沒更新），更新主鍵欄位並不是目的，只是為了將當前主索引值賦值給@update_id，就是這句： ( SELECT @update_id := id )。 （個人理解，水平有限可能會有出入）

另外如果更新了多條記錄也可以用下面的方式

 

SET @uids := null;UPDATE footable   SET foo = ‘bar‘ WHERE fooid > 5   AND ( SELECT @uids := CONCAT_WS(‘,‘, fooid, @uids) );SELECT @uids;

 

 

註：上面的方法不適用於有HAVING、GROUP BY或者ORDER BY子句中，否則可能出入與預期不同的結果。

參考官方手冊說明 http://dev.mysql.com/doc/refman/5.1/zh/language-structure.html

MYSQL擷取更新行的主鍵ID 【轉】