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