mysql select同时update同一张表教程详解

发布时间:2019-08-07编辑:脚本学堂
本文介绍了mysql select同时update同一张表的方法,本文解释如何update一张表,同时在查询子句中使用select,需要的朋友参考下。

mysql不允许select from后面指向用作update的表,有比创建无休止的临时表更好的办法。
本文解释如何update一张表,同时在查询子句中使用select。

一,问题描述
假设要update的表跟查询子句是同一张表,例如用统计数据更新表的字段(此时需要用group子句返回统计值),从某一条记录的字段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目标。(www.jb200.com 脚本学堂)

二,解决办法
既然mysql是通过临时表来实现from子句里面的嵌套查询,那么把嵌套查询装进另外一个嵌套查询里,可使from子句查询和保存都是在临时表里进行,然后间接地在外围查询被引用。
正确语句:
 

复制代码 代码示例:
update apples 
   set price = ( 
      select price from ( 
         select * from apples 
      ) as x 
      where variety = 'gala') 
   where variety = 'fuji'; 

如果想了解更多其中的机制,请阅读mysql internals manual相关章节。

三,没有解决的问题
一个常见的问题是,in()子句优化废品,被重写成相关的嵌套查询,有时(往往?)造成性能低下。把嵌套查询装进另外一个嵌套查询里并不能阻止它重写成相关嵌套,除非我下狠招。这种情况下,最好用join重构查询(rewrite such a query as a join)。

另一个未解决的问题:
临时表被引用多次。“装进嵌套查询”的技巧无法解决这些问题,因为它们在编译时被创建,而上面讨论的update问题是在运行时。(译者注:个人认为跟文章讨论的主题没几毛钱关系)

转载:http://blog.csdn.net/afeiqiang/article/details/8589535