MySQL的SQL语句 - 数据操作语句（15）- UPDATE 语句

数据库杂货铺 2021-04-12

1099

UPDATE 语句

UPDATE 是修改表中行的 DML 语句。

UPDATE 语句可以用 WITH 子句开头，定义在 UPDATE 中可访问的公共表表达式。

单表语法：

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


value:
    {expr | DEFAULT}


assignment:
    col_name = value


assignment_list:
    assignment [, assignment] ...

多表语法：

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

对于单表语法，UPDATE 语句用新值更新命名表中现有行的列。SET 子句指示要修改的列及其应给定的值。每个值可以用表达式给定，也可以使用关键字 DEFAULT 将列显式设置为其默认值。WHERE 子句（如果给定）指定要更新哪些行。如果没有 WHERE 子句，所有行都将更新。如果指定了 ORDER BY 子句，则按指定的顺序更新行。LIMIT 子句对可以更新的行数进行了限制。

对于多表语法，UPDATE 更新 table_references 中每个表中满足条件的行。每个匹配的行都会更新一次，即使它与条件匹配多次。对于多表语法，不能使用 ORDER BY 和 LIMIT。

对于分区表，此语句的单表和多表形式都支持使用 PARTITION 选项用作表引用的一部分。此选项接受分区或子分区列表。只检查列出的分区（或子分区）是否匹配，不在这些分区或子分区中的行不会更新，无论它是否满足 where_condition 条件。

注意

与在 INSERT 或 REPLACE 语句中使用 PARTITION 的情况不同，即使列出的分区（或子分区）中没有与 where_condition 匹配的行，UPDATE ... PARTITION 语句也被认为是成功的。

where_condition 是一个表达式，要更新的每一行都必须满足此表达式的条件。

只需要拥有在 UPDATE 语句实际更新引用的列的 UPDATE 权限。对于任何已读取但未修改的列，只需要 SELECT 权限。

UPDATE 语句支持以下修饰符：

● 使用 LOW_PRIORITY 修饰符，UPDATE 的执行将被延迟，直到没有其他客户端从表中读取数据。这只影响只使用表级锁定的存储引擎（如 MyISAM、MEMORY 和 MERGE）。

● 使用 IGNORE 修饰符，即使在更新过程中发生错误，更新语句也不会中止。不会更新在唯一键值上引发重复键冲突的行。可能导致数据转换错误的值的行将更新为最接近的有效值。

包括 ORDER BY 子句的 UPDATE IGNORE 语句被标记为不安全的基于语句的复制。（这是因为行的更新顺序决定了哪些行被忽略。）当使用基于语句的模式时，这些语句在错误日志中生成警告，在使用 MIXED 模式时，这些语句将使用基于行的格式写入二进制日志。

如果从要在表达式中更新的表中访问列，则 UPDATE 将使用该列的当前值。例如，下面的语句将 col1 设置为比当前值多1：

UPDATE t1 SET col1 = col1 + 1;

下面语句中的第二个赋值将 col2 设置为当前（更新的）col1 值，而不是原始 col1 值。结果是 col1 和 col2 的值相同。此行为与标准 SQL 不同。

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

单表 UPDATE 分配通常从左到右进行计算。对于多表更新，不能保证以任何特定的顺序执行分配。

如果将列设置为当前的值，MySQL 会注意到这一点，并且不会更新它。

如果把已声明为 NOT NULL 的列设置为 NULL，则在启用了严格 SQL 模式会出错；否则，该列将设置为列数据类型的隐式默认值，并且警告计数将递增。对于数值类型，隐式默认值为0；对于字符串类型，隐式默认值为空字符串（''），对于日期和时间类型，默认值为“零”。

如果显式更新生成列，则唯一允许的值是 DEFAULT。

UPDATE 返回实际更改的行数。mysql_info() C API 函数返回匹配和更新的行数以及更新过程中出现的警告数。

可以使用 LIMIT row_count 来限制 UPDATE 的范围。LIMIT 子句是匹配行的限制。只要找到满足 WHERE 子句的 row_count 行，语句就会立即停止，而不管这些行是否实际被更改。

如果 UPDATE 语句包含 ORDER BY 子句，则按该子句指定的顺序更新行。这在某些可能导致错误的情况下非常有用。假设表 t 包含一个具有唯一索引的列 id。以下语句可能会出现重复键错误而失败，这取决于行的更新顺序：

UPDATE t SET id = id + 1;

例如，如果表在 id 列中包含值 1 和 2，并且在 2 更新为 3 之前 1 先更新为2，则会发生错误。若要避免此问题，请添加 ORDER BY 子句，使 id 值较大的行在值较小的行之前更新：

UPDATE t SET id = id + 1 ORDER BY id DESC;

还可以执行覆盖多个表的 UPDATE 操作。但是，不能将 ORDER BY 或 LIMIT 用于多表更新。table_references 子句列出了连接中涉及的表。

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

前面的示例显示了使用逗号运算符的内部联接，但多表更新语句可以使用 SELECT 语句中允许的任何类型的联接，例如 LEFT JOIN。

如果使用包含 InnoDB 表且有外键约束的多表 UPDATE 语句，那么 MySQL 优化器可能会按照与父/子关系不同的顺序处理表。在本例中，语句失败并回滚。相反，更新一个表并依赖 InnoDB 提供的 ON UPDATE 功能来相应地修改其他表。

不能在更新一个表的同时直接从子查询中对同一表进行选择。可以通过使用多表更新来解决此问题，其中一个表是从实际要更新的表派生的，并使用别名引用派生表。假设希望更新一个名为 items 的表，该表是使用以下语句定义的：

CREATE TABLE items (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    wholesale DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    retail DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    quantity BIGINT NOT NULL DEFAULT 0
);

若要降低利润为30%或更高并且库存少于100的商品的零售价，可以尝试使用如下所示的 UPDATE 语句，该语句在 WHERE 子句中使用子查询。如下所示，此语句不起作用：

mysql> UPDATE items
     > SET retail = retail * 0.9
     > WHERE id IN
     >     (SELECT id FROM items
     >         WHERE retail  wholesale >= 1.3 AND quantity > 100);
ERROR 1093 (HY000): You can't specify target table 'items' for update in FROM clause

替代方法是可以使用多表更新，其中子查询被移动到要更新的表列表中，使用别名在最外层的 WHERE 子句中引用它，如下所示：

UPDATE items,
       (SELECT id FROM items
        WHERE id IN
            (SELECT id FROM items
             WHERE retail  wholesale >= 1.3 AND quantity < 100))
        AS discounted
SET items.retail = items.retail * 0.9
WHERE items.id = discounted.id;

因为默认情况下，优化器会尝试将派生表 discounted 合并到最外层的查询块中，只有在强制物化派生表时，这才有效。可以在运行更新之前将 optimizer_switch 系统变量的 derived_merge 标志设置为 off，或使用 NO_MERGE 优化器提示来执行此操作，如下所示：

UPDATE /*+ NO_MERGE(discounted) */ items,
       (SELECT id FROM items
        WHERE retail  wholesale >= 1.3 AND quantity < 100)
        AS discounted
    SET items.retail = items.retail * 0.9
    WHERE items.id = discounted.id;

在这种情况下使用优化器提示的好处是，它只适用于使用它的查询块中，因此在执行 UPDATE 之后，不必再次更改 optimizer_switch 的值。

另一种可能是重写子查询，使其不使用 IN 或 EXISTS，如下所示：

UPDATE items,
       (SELECT id, retail / wholesale AS markup, quantity FROM items)
       AS discounted
    SET items.retail = items.retail * 0.9
    WHERE discounted.markup >= 1.3
    AND discounted.quantity < 100
    AND items.id = discounted.id;

在这种情况下，子查询默认情况下是物化的，而不是合并的，因此不需要禁用派生表的合并。

官方网址：

https://dev.mysql.com/doc/refman/8.0/en/update.html

mysql

文章转载自数据库杂货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的SQL语句 - 数据操作语句（15）- UPDATE 语句

评论