MySQL SELECT同時UPDATE同一張表問題發生及解決

2024-07-24 12:42:05

字體：大中小

來源：轉載

供稿：網友

MySQL不允許SELECT FROM后面指向用作UPDATE的表，有時候讓人糾結。當然，有比創建無休止的臨時表更好的辦法。本文解釋如何UPDATE一張表，同時在查詢子句中使用SELECT.

問題描述
假設我要UPDATE的表跟查詢子句是同一張表，這樣做有許多種原因，例如用統計數據更新表的字段（此時需要用group子句返回統計值），從某一條記錄的字段update另一條記錄，而不必使用非標準的語句，等等。舉個例子：

復制代碼代碼如下:m.survivalescaperooms.com

 
create table apples(variety char(10) primary key, price int); 
insert into apples values('fuji', 5), ('gala', 6); 
update apples 
set price = (select price from apples where variety = 'gala') 
where variety = 'fuji'; 

錯誤提示是：ERROR 1093 (HY000): You can't specify target table 'apples' for update in FROM clause. MySQL手冊這下面有說明 : “Currently, you cannot update a table and select from the same table in a subquery.”
在這個例子中，要解決問題也十分簡單，但有時候不得不通過查詢子句來update目標。好在我們有辦法。

解決辦法
既然MySQL是通過臨時表來實現FROM子句里面的嵌套查詢，那么把嵌套查詢裝進另外一個嵌套查詢里，可使FROM子句查詢和保存都是在臨時表里進行，然后間接地在外圍查詢被引用。下面的語句是正確的：

復制代碼代碼如下:m.survivalescaperooms.com

 
update apples 
set price = ( 
select price from ( 
select * from apples 
) as x 
where variety = 'gala') 
where variety = 'fuji'; 

如果你想了解更多其中的機制，請閱讀相關章節。

沒有解決的問題
一個常見的問題是，IN()子句優化廢品，被重寫成相關的嵌套查詢，有時（往往？）造成性能低下。把嵌套查詢裝進另外一個嵌套查詢里并不能阻止它重寫成相關嵌套，除非我下狠招。這種情況下，最好用JOIN重構查詢（rewrite such a query as a join）。

另一個沒解決的問題是臨時表被引用多次。“裝進嵌套查詢”的技巧無法解決這些問題，因為它們在編譯時被創建，而上面討論的update問題是在運行時。

上一篇：Windows環境下重置mysql密碼操作命令

下一篇：服務器不支持 MySql 數據庫的解決方法