专栏首页SQL实现MySQL 的更新语句

MySQL 的更新语句

本文将和大家分享 MySQL 更新语句的一些小众语法,及笔者在使用多表关联更新遇到的一些问题。

先来看单表更新的语法:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

大家可能会觉得奇怪,在更新语句中居然能用 ORDER BY 子句和 LIMIT 子句。没错,ORDER BY 子句用来指定数据行的更新顺序,LIMIT 子句限制数据更新的行数。

我们结合例子来看,创建一张 test 表用来演示,它的表结构及数据如下:

CREATE TABLE `test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `col1` int DEFAULT NULL,
  `col2` int DEFAULT NULL,
  `col3` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;


    id    col1    col2  col3    
------  ------  ------  --------
     1       1      10  hello   
     2       1      20  world   
     3       1      30  world   
     4       1      40  nice    
     5       1      50  hello   

test 表有 5 行数据,其中 col1 列的值完全一样,都是数值 1 。

先看 LIMIT 子句的使用。

UPDATE 
  test 
SET
  col1 = 2 
LIMIT 2;

---------------------------------------------------
1 queries executed, 1 success, 0 errors, 0 warnings

查询:update test set col1 = 2 limit 2

共 2 行受到影响

上面的语句将 col2 列的值改为数值 2,但是只改变其中的两行。我们通过观察执行更新后的 test 表的数据,确实只更新了两行。

    id    col1    col2  col3    
------  ------  ------  --------
     1       2      10  hello   
     2       2      20  world   
     3       1      30  world   
     4       1      40  nice    
     5       1      50  hello   

再来看 ORDER BY 子句。

UPDATE 
  test 
SET
  col1 = 3 
ORDER BY id DESC 
LIMIT 2;

---------------------------------------------------
1 queries executed, 1 success, 0 errors, 0 warnings

查询:update test set col1 = 3 order by id desc limit 2

共 2 行受到影响

这回我们指定了按照 id 列的逆序更新 col1 列的值,也只更新两行,结果和我们预期的一致。

    id    col1    col2  col3    
------  ------  ------  --------
     1       2      10  hello   
     2       2      20  world   
     3       1      30  world   
     4       3      40  nice    
     5       3      50  hello   

不过,需要注意的是,如果更新的行的原来的值和要更新的值一致,那么 MySQL 并不会真正执行更新操作,但仍会计入受 LIMIT 子句影响的行数。

比如,我们重复执行上面的更新语句,但 test 表的数据一点也没变。

UPDATE 
  test 
SET
  col1 = 3 
ORDER BY id DESC 
LIMIT 2;

---------------------------------------------------
1 queries executed, 1 success, 0 errors, 0 warnings

查询:update test set col1 = 3 order by id desc limit 2

共 0 行受到影响

另外,ORDER BY 子句和 LIMIT 子句不能用在多表关联更新语句中。

看下面这个例子,是关于列的更新顺序。对于单表的更新,执行顺序通常是从左到右。

UPDATE 
  test a 
SET
  col1 = col1 * 10,
  col2 = col1 
WHERE id = 1; 

猜猜看,上面这条更新语句,执行之后 id = 1 的行的 col2 字段的值是等于 col1 更新前的值,还是更新后的值?

答案是后者,即更新后的值。这和标准 SQL 不太一样。

再来看多表关联更新的语法:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET assignment_list
    [WHERE where_condition]

注意,如果多表关联通过 JOIN 来实现,而不是把关联的条件放到 WHERE 子句中,那么 JOIN 子句要放在 SET 子句之前。

UPDATE 
  test a 
  INNER JOIN test b 
    ON b.id = a.id SET a.col2 = b.col2 * 10 
WHERE a.col3 = 'hello';

-- 等价于下面的写法
UPDATE 
  test a,
  test b 
SET
  a.col2 = b.col2 * 10 
WHERE b.id = a.id 
  AND a.col3 = 'hello' ;

有时候执行多表关联更新时会遇到 ERROR 1093 (HY000): You can't specify target table 'xxx' for update in FROM clause 这个错误提示,其实不止更新语句,删除语句也会有这个问题。

这个问题是怎么产生的呢?实际上是因为要更新的目标表同时存在子查询里面,请看下面这个例子。

UPDATE 
  test 
SET
  col1 = col1 * 10 
WHERE id IN 
  (SELECT 
    MIN(id) AS id 
  FROM
    test 
  GROUP BY col3 
  HAVING COUNT(*) = 1);
  
---------------------------------------------------------------
错误代码:1093
You can't specify target table 'test' for update in FROM clause

这个问题很早就存在了,在 2006 年的时候就有用户向 MySQL 社区反馈,只是到了现在还没处理。

好消息是 MariaDB 在 10.3.2 版本开始支持这类更新语句,相信在 MySQL 后续的版本中,也会加入这一支持。

这个问题在现阶段怎么解决呢?官方文档给出的建议是使用派生表(在 FROM 子句后面可替代表的子查询称作派生表)。

方法一:

UPDATE 
  test 
SET
  col1 = col1 * 10 
WHERE id IN 
  (SELECT 
    id 
  FROM
    (SELECT 
      MIN(id) AS id 
    FROM
      test 
    GROUP BY col3 
    HAVING COUNT(*) = 1) t)

这种改写方式能凑效是因为 MySQL 的优化器将派生表物化了(物化的操作可理解为将查询结果存到内部临时表中),因此更新的目标表和子查询里面的表就不是同一个。

方法二:

UPDATE 
  test a,
  (SELECT 
    MIN(id) AS id 
  FROM
    test 
  GROUP BY col3 
  HAVING COUNT(*) = 1) b 
SET
  col1 = col1 * 10 
WHERE b.id = a.id 

本文分享自微信公众号 - SQL实现(gh_684ee9235a26),作者:zero

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-01-03

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SQL更新语句

    如果,你想更新数据库中其中某一条数据。那么你需要使用UPDATE语句。基本的UPDATE语句如下。

    zy010101
  • 一文看懂MySQL执行update更新语句的流程

    还是先在粉板上记一下方便。如果掌柜没有粉板,每次记账都翻账本,效率是不是低死啦? MySQL也有这个问题,若每次更新操作都写进磁盘,然后磁盘也要找到对应记录,...

    JavaEdge
  • 一条简单的更新语句,MySQL是如何加锁的?

    那这条语句呢?其实这其中包含太多知识点了。要回答这两个问题,首先需要了解一些知识。

    Java_老男孩
  • mysql update语句和原数据一样会更新么

    如果 update 语句和原数据一样会更新么?更具体的来说,如果更新的数据前后是一样的,MySQL 会更新存储引擎中(磁盘)数据么?

    luoxn28
  • MySQL实战 -- 一条SQL更新语句是如何执行的?

    前面我们系统了解了一个查询语句的执行流程,并介绍了执行过程中涉及的处理模块。相信你还记得,一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模...

    MachineLP
  • MySQL语句的语法

    |符号用来指出几个选择中的一个,因为NULL |NOT NULL 表示或者给出NULL或者给出NOT NULL

    用户7657330
  • SQL语句小tips(持续更新)

    expr1 的值为 TRUE,则返回值为 expr2  expr1 的值为FALSE,则返回值为 expr3

    千往
  • Mysql 语句

    运行mysql时候出现:[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Pleas...

    用户2657851
  • 一条更新语句如何执行

    其实更新语句和查询语句的流程是基本一样的,但是他其中不一样的是涉及两个日志模块,也就是我们经常提到的redo log(重做日志)和binlog(归档日志)。

    小土豆Yuki
  • 一条更新语句是如何执行的?

    之前你可能经常听DBA同事说,MySQL可以恢复到半个月内任意一秒的状态,惊叹的同时,你是不是心中也会不免会好奇,这是怎样做到的呢?

    王小明_HIT
  • MySQL中的join语句

    在MySQL中,join语句想必大家都不陌生,今天我们围绕join语句展开,说一些可能平时不关注的知识点。

    AsiaYe
  • MySQL show语句

    JavaEdge
  • 详解一条查询select语句和更新update语句的执行流程

    前面几篇MySQL系列的文章介绍了索引,事务和锁相关知识,那么今天就让我们来看看当我们执行一条select语句和一条update语句的时候,MySQL要经过哪些...

    用户2781897
  • MySQL DELETE语句和TRUNCATE TABLE语句的区别

    MySQL DELETE语句和TRUNCATE TABLE语句功能相似,但是二者究竟有何区别呢?下文就将为您分析MySQL DELETE语句和TRUNCATE ...

    wangxl
  • mysql 语句传参数 -- prepare语句的用法

    mysql默认在语句是不能传参数的,例如 select * from a limit @a,@b;这样是会报错的,那怎么样才能传参数呢?

    仙士可
  • Mysql Join语句的优化

    1. 尽可能减少Join语句中Nested Loop的循环总次数 最有效的办法是让驱动表的结果集尽可能地小,这也正是在本章第二节中所提到的优化基本原则之一——“...

    dys
  • MySQL基本的DDL语句

    — 创建数据库, 指定字符集 CREATE DATABASE IF NOT EXISTS 数据库名 DEFAULT CHARSET UTF8;

    乐心湖
  • Mysql的 If和 Case语句

    比如在上例子中,我们存储了一些不希望暴露性别的用户,存储的值为3.此时想要查询可以:

    呼延十
  • 初识mysql语句

    mysql> update mysql.user set authentication_string =password('root') where User=...

    py3study

扫码关注云+社区

领取腾讯云代金券