MySQL SELECT同時UPDATE同一張表問題發(fā)生及解決
更新時間：2013年03月25日 11:30:42 作者：
例如用統(tǒng)計數(shù)據(jù)更新表的字段（此時需要用group子句返回統(tǒng)計值），從某一條記錄的字段update另一條記錄，而不必使用非標準的語句，等等感興趣的朋友可以參考下哈
MySQL不允許SELECT FROM后面指向用作UPDATE的表，有時候讓人糾結(jié)。當然，有比創(chuàng)建無休止的臨時表更好的辦法。本文解釋如何UPDATE一張表，同時在查詢子句中使用SELECT.
問題描述
假設我要UPDATE的表跟查詢子句是同一張表，這樣做有許多種原因，例如用統(tǒng)計數(shù)據(jù)更新表的字段（此時需要用group子句返回統(tǒng)計值），從某一條記錄的字段update另一條記錄，而不必使用非標準的語句，等等。舉個例子：
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手冊UPDATE documentation這下面有說明 : “Currently, you cannot update a table and select from the same table in a subquery.”
在這個例子中，要解決問題也十分簡單，但有時候不得不通過查詢子句來update目標。好在我們有辦法。
解決辦法
既然MySQL是通過臨時表來實現(xiàn)FROM子句里面的嵌套查詢，那么把嵌套查詢裝進另外一個嵌套查詢里，可使FROM子句查詢和保存都是在臨時表里進行，然后間接地在外圍查詢被引用。下面的語句是正確的：
update apples
set price = (
select price from (
select * from apples
) as x
where variety = 'gala')
where variety = 'fuji';
如果你想了解更多其中的機制，請閱讀MySQL Internals Manual相關章節(jié)。
沒有解決的問題
一個常見的問題是，IN()子句優(yōu)化廢品，被重寫成相關的嵌套查詢，有時（往往？）造成性能低下。把嵌套查詢裝進另外一個嵌套查詢里并不能阻止它重寫成相關嵌套，除非我下狠招。這種情況下，最好用JOIN重構(gòu)查詢（rewrite such a query as a join）。
另一個沒解決的問題是臨時表被引用多次。“裝進嵌套查詢”的技巧無法解決這些問題，因為它們在編譯時被創(chuàng)建，而上面討論的update問題是在運行時。
問題描述
假設我要UPDATE的表跟查詢子句是同一張表，這樣做有許多種原因，例如用統(tǒng)計數(shù)據(jù)更新表的字段（此時需要用group子句返回統(tǒng)計值），從某一條記錄的字段update另一條記錄，而不必使用非標準的語句，等等。舉個例子：
復制代碼 代碼如下:
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手冊UPDATE documentation這下面有說明 : “Currently, you cannot update a table and select from the same table in a subquery.”
在這個例子中，要解決問題也十分簡單，但有時候不得不通過查詢子句來update目標。好在我們有辦法。
解決辦法
既然MySQL是通過臨時表來實現(xiàn)FROM子句里面的嵌套查詢，那么把嵌套查詢裝進另外一個嵌套查詢里，可使FROM子句查詢和保存都是在臨時表里進行，然后間接地在外圍查詢被引用。下面的語句是正確的：
復制代碼 代碼如下:
update apples
set price = (
select price from (
select * from apples
) as x
where variety = 'gala')
where variety = 'fuji';
如果你想了解更多其中的機制，請閱讀MySQL Internals Manual相關章節(jié)。
沒有解決的問題
一個常見的問題是，IN()子句優(yōu)化廢品，被重寫成相關的嵌套查詢，有時（往往？）造成性能低下。把嵌套查詢裝進另外一個嵌套查詢里并不能阻止它重寫成相關嵌套，除非我下狠招。這種情況下，最好用JOIN重構(gòu)查詢（rewrite such a query as a join）。
另一個沒解決的問題是臨時表被引用多次。“裝進嵌套查詢”的技巧無法解決這些問題，因為它們在編譯時被創(chuàng)建，而上面討論的update問題是在運行時。
相關文章
Mysql 數(shù)據(jù)庫死鎖過程分析(select for update)
最近有項目需求，需要保證多臺機器不拿到相同的數(shù)據(jù)，后來發(fā)現(xiàn)Mysql查詢語句使用select.. for update經(jīng)常導致數(shù)據(jù)庫死鎖問題，下面小編給大家介紹mysql 數(shù)據(jù)庫死鎖過程分析(select for update)，對mysql數(shù)據(jù)庫死鎖問題感興趣的朋友一起學習吧2015-12-12
MySQL如何快速批量插入1000w條數(shù)據(jù)
這篇文章主要給大家介紹了關于MySQL如何快速批量插入1000w條數(shù)據(jù)的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2021-03-03
這篇文章主要為大家詳細介紹了MySQL的索引，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下，希望能夠給你帶來幫助2022-03-03
這篇文章主要介紹了MySQL 兩種恢復數(shù)據(jù)的方法，幫助恢復線上數(shù)據(jù)，保證數(shù)據(jù)完整，感興趣的朋友可以了解下2020-10-10
由于 SupeSite 需要調(diào)用 Discuz! 和 UCHome 的數(shù)據(jù)，所以如果它們不安裝在同一個數(shù)據(jù)庫，SupeSite 的數(shù)據(jù)庫用戶必須要對 Discuz! 和 UCHome 的數(shù)據(jù)庫有讀取、修改、刪除等權限。2011-07-07
關于 MySQL 嵌套子查詢中無法關聯(lián)主表字段問題的解決方法
這篇文章主要介紹了關于 MySQL 嵌套子查詢中，無法關聯(lián)主表字段問題的折中解決方法,本文通過圖文并茂的形式給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-12-12
這篇文章主要介紹了SQL中的開窗函數(shù)(窗口函數(shù))使用，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-08-08
物理服務機的CPU、內(nèi)存、存儲設備、連接數(shù)等資源有限，某個時段大量連接同時執(zhí)行操作，會導致數(shù)據(jù)庫在處理上遇到性能瓶頸。為了解決這個問題，行業(yè)先驅(qū)門充分發(fā)揚了分而治之的思想，對大庫表進行分割2022-01-01
mysql 8.0.18.zip安裝配置方法圖文教程(windows 64位)
這篇文章主要為大家詳細介紹了mysql 8.0.18.zip安裝配置方法圖文教程，以及卸載以前數(shù)據(jù)庫的方法，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-10-10
這篇文章主要給大家介紹了關于MySQL中行級鎖定的相關資料，文中通過示例代碼介紹的非常詳細，對大家學習或者使用MySQL具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧2019-05-05