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)的語(yǔ)句，等等感興趣的朋友可以參考下哈
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)的語(yǔ)句，等等。舉個(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手冊(cè)UPDATE documentation這下面有說明 : “Currently, you cannot update a table and select from the same table in a subquery.”
在這個(gè)例子中，要解決問題也十分簡(jiǎn)單，但有時(shí)候不得不通過查詢子句來update目標(biāo)。好在我們有辦法。
解決辦法
既然MySQL是通過臨時(shí)表來實(shí)現(xiàn)FROM子句里面的嵌套查詢，那么把嵌套查詢裝進(jìn)另外一個(gè)嵌套查詢里，可使FROM子句查詢和保存都是在臨時(shí)表里進(jìn)行，然后間接地在外圍查詢被引用。下面的語(yǔ)句是正確的：
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í)表被引用多次。“裝進(jìn)嵌套查詢”的技巧無法解決這些問題，因?yàn)樗鼈冊(cè)诰幾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)的語(yǔ)句，等等。舉個(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手冊(cè)UPDATE documentation這下面有說明 : “Currently, you cannot update a table and select from the same table in a subquery.”
在這個(gè)例子中，要解決問題也十分簡(jiǎn)單，但有時(shí)候不得不通過查詢子句來update目標(biāo)。好在我們有辦法。
解決辦法
既然MySQL是通過臨時(shí)表來實(shí)現(xiàn)FROM子句里面的嵌套查詢，那么把嵌套查詢裝進(jìn)另外一個(gè)嵌套查詢里，可使FROM子句查詢和保存都是在臨時(shí)表里進(jìn)行，然后間接地在外圍查詢被引用。下面的語(yǔ)句是正確的：
復(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í)表被引用多次。“裝進(jìn)嵌套查詢”的技巧無法解決這些問題，因?yàn)樗鼈冊(cè)诰幾g時(shí)被創(chuàng)建，而上面討論的update問題是在運(yùn)行時(shí)。
您可能感興趣的文章:
- MySQL中SELECT+UPDATE處理并發(fā)更新問題解決方案分享
- mysql SELECT FOR UPDATE語(yǔ)句使用示例
- Mysql 數(shù)據(jù)庫(kù)死鎖過程分析(select for update)
- mysql事務(wù)select for update及數(shù)據(jù)的一致性處理講解
- MySQL中Update、select聯(lián)用操作單表、多表，及視圖與臨時(shí)表的區(qū)別
- MySQL select、insert、update批量操作語(yǔ)句代碼實(shí)例
- MySQL將select結(jié)果執(zhí)行update的實(shí)例教程
相關(guān)文章
Ubuntu配置Mysql主從數(shù)據(jù)庫(kù)
MySQL數(shù)據(jù)庫(kù)支持?jǐn)?shù)據(jù)庫(kù)的主從復(fù)制功能，因此在集群方面具有其獨(dú)特的優(yōu)勢(shì)。眾多國(guó)內(nèi)外大型網(wǎng)站架構(gòu)體系中，均采用了MySQL的主從數(shù)據(jù)庫(kù)配置來實(shí)現(xiàn)查詢負(fù)載、數(shù)據(jù)庫(kù)熱備等功能。本人在實(shí)際的Web項(xiàng)目中也涉及到這一需求，在此將如何配置實(shí)現(xiàn)做個(gè)簡(jiǎn)單小結(jié)。2014-07-07
詳解MySQL到SelectDB的實(shí)時(shí)同步策略
MySQL?到?SelectDB?的實(shí)時(shí)數(shù)據(jù)同步技術(shù),通過?NineData?的數(shù)據(jù)復(fù)制控制臺(tái),僅需輕點(diǎn)鼠標(biāo),即可輕松完成?MySQL?到?SelectDB?的同步任務(wù)配置,這篇文章主要介紹了MySQL到SelectDB的實(shí)時(shí)同步策略,需要的朋友可以參考下2023-09-09
近我將筆記本重裝了,為了保留之前的程序,我把相關(guān)的注冊(cè)表和環(huán)境備份了下來,重裝之后重新導(dǎo)入成功再現(xiàn)了部分軟件,下面給大家分享mysql 8.0.29 卸載問題記錄,感興趣的朋友一起看看吧2024-04-04
MYSQL使用.frm恢復(fù)數(shù)據(jù)表結(jié)構(gòu)的實(shí)現(xiàn)方法
在這里我們探討使用.frm文件恢復(fù)數(shù)據(jù)表機(jī)構(gòu)（當(dāng)然如果你以前備份過數(shù)據(jù)表，你可以使用調(diào)用備份的數(shù)據(jù)表）2010-02-02
Linux下mysql 5.7 部署及遠(yuǎn)程訪問配置
這篇文章主要為大家詳細(xì)介紹了Linux下mysql 5.7 部署及遠(yuǎn)程訪問的配置方法，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-09-09
這篇文章主要為大家詳細(xì)介紹了Mysql WorkBench安裝配置圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2019-06-06
MySQL函數(shù)與存儲(chǔ)過程字符串長(zhǎng)度限制的解決
本文主要介紹了MySQL函數(shù)與存儲(chǔ)過程字符串長(zhǎng)度限制的解決，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2022-08-08
mysql mysqldump只導(dǎo)出表結(jié)構(gòu)或只導(dǎo)出數(shù)據(jù)的實(shí)現(xiàn)方法
mysql mysqldump只導(dǎo)出表結(jié)構(gòu)或只導(dǎo)出數(shù)據(jù)的實(shí)現(xiàn)方法，需要的朋友可以參考下。2011-11-11
當(dāng)數(shù)據(jù)庫(kù)響應(yīng)時(shí)間超過500ms時(shí),系統(tǒng)將面臨三大災(zāi)難鏈?zhǔn)椒磻?yīng),所以本文將為大家介紹一下MySQL中慢SQL優(yōu)化的常用方法,有需要的小伙伴可以了解下2025-03-03
這篇文章主要介紹了MySQL 索引的一些細(xì)節(jié)分享，幫助大家更好的理解和使用MySQL中的索引，感興趣的朋友可以了解下2020-12-12