13.2.11 UPDATE 语句

UPDATE是 DML 语句，用于修改 table 中的行。

Single-table syntax:

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] ...

Multiple-table syntax:

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

对于单 table 语法，UPDATE语句使用新值更新命名 table 中现有行的列。 SET子句指示要修改的列以及应提供的值。每个值都可以作为 table 达式或关键字DEFAULT给出，以将列明确设置为其默认值。 WHERE子句(如果提供)指定标识要更新哪些行的条件。没有WHERE子句，将更新所有行。如果指定了ORDER BY子句，则将按照指定的 Sequences 更新行。 LIMIT子句限制了可以更新的行数。

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

对于分区 table，此语句的单 table 形式和多 table 形式都支持使用PARTITION选项作为 table 引用的一部分。此选项采用一个或多个分区或子分区(或两者)的列 table。仅检查列出的分区(或子分区)是否匹配，无论这些分区或子分区中是否存在* where_condition *，都不会更新该行。

Note

与将PARTITION与INSERT或REPLACE语句一起使用的情况不同，即使列出的分区(或子分区)中没有行与* where_condition *匹配，否则其他有效的UPDATE ... PARTITION语句也被视为成功。

有关更多信息和示例，请参见第 22.5 节“分区选择”。

where_condition *是对要更新的每一行求值为 true 的 table 达式。有关 table 达式语法，请参见第 9.5 节“table 达式”。
table_references 和 where_condition *如第 13.2.9 节“ SELECT 语句”中所述指定。

您仅需要对UPDATE中引用的实际更新的列具有UPDATE特权。对于已读取但未修改的任何列，您只需要SELECT特权。

UPDATE语句支持以下修饰符：

使用LOW_PRIORITY修饰符，将延迟UPDATE的执行，直到没有其他 Client 端从 table 中读取。这仅影响仅使用 table 级锁定(例如MyISAM，MEMORY和MERGE)的存储引擎。

使用IGNORE修饰符，即使更新期间发生错误，update 语句也不会中止。在唯一键值上发生重复键冲突的行不会更新。更新为会导致数据转换错误的值的行将更新为最接近的有效值。有关更多信息，请参见IGNORE 关键字和严格 SQL 模式的比较。

UPDATE IGNORE语句(包括具有ORDER BY子句的语句)被标记为对基于语句的复制不安全。 (这是因为行的更新 Sequences 决定了忽略哪些行.)在使用基于语句的模式时，此类语句在错误日志中产生警告，而在使用MIXED时，将使用基于行的格式将这些语句写入二进制日志。模式。 (错误号 11758262，错误号 50439)请参见第 16.2.1.3 节“二进制日志 Logging 安全和不安全语句的确定”，以获取更多信息。

如果您访问 table 中要在 table 达式中更新的列，则UPDATE使用该列的当前值。例如，以下语句将col1设置为比当前值大一：

UPDATE t1 SET col1 = col1 + 1;

以下语句中的第二个赋值将col2设置为当前(更新的)col1值，而不是原始col1值。结果是col1和col2具有相同的值。此行为不同于标准 SQL。

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

单 tableUPDATE分配通常从左到右评估。对于多 table 更新，不能保证以任何特定 Sequences 执行分配。

如果将列设置为其当前值，则 MySQL 会注意到这一点，并且不会对其进行更新。

如果通过设置为NULL来更新已声明NOT NULL的列，则在启用严格 SQL 模式的情况下会发生错误；否则，该列将设置为该列数据类型的隐式默认值，并且警告计数将增加。隐式默认值对于数字类型为0，对于字符串类型为空字符串('')，对于日期和时间类型为“零”值。参见第 11.6 节“数据类型默认值”。

如果显式更新了生成的列，则唯一允许的值为DEFAULT。有关生成的列的信息，请参见第 13.1.18.7 节“创建 table 和生成的列”。

UPDATE返回实际更改的行数。 mysql_info() C API 函数返回已匹配和更新的行数以及在UPDATE期间发生的警告数。

您可以使用LIMIT row_count来限制UPDATE的范围。 LIMIT子句是行匹配的限制。一旦找到* row_count *满足WHERE子句的行(无论实际上是否已更改)，该语句就会停止。

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

UPDATE t SET id = id + 1;

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

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

您还可以执行涵盖多个 table 的UPDATE操作。但是，不能将ORDER BY或LIMIT与多 tableUPDATE一起使用。 * table_references *子句列出了参与连接的 table。第 13.2.9.2 节“ JOIN 子句”中描述了其语法。这是一个例子：

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

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

如果您使用包含InnoDBtable 的多 tableUPDATE语句，而这些 table 具有外键约束，则 MySQL 优化器可能以与其父/子关系不同的 Sequences 处理 table。在这种情况下，该语句将失败并回滚。而是，更新单个 table 并依靠InnoDB提供的ON UPDATE功能来相应地修改其他 table。参见第 13.1.18.5 节“外键约束”。

您不能更新 table 并直接在子查询中从同一 table 中选择。您可以使用多 table 更新来解决此问题，在该更新中，其中一个 table 是从您实际希望更新的 table 派生的，并使用别名引用派生 table。假设您希望更新一个名为items的 table，该 table 是使用此处显示的语句定义的：

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

相反，您可以采用多 table 更新，其中将子查询移到要更新的 table 列 table 中，使用别名在最外层的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;

因为优化器默认情况下会尝试将派生 tablediscounted合并到最外面的查询块中，所以仅当您强制实现派生 table 时，此方法才有效。您可以通过在运行更新之前将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;

在这种情况下，默认情况下将实现子查询而不是合并子查询，因此没有必要禁用派生 table 的合并。

Name	MySql 中文文档
Version
Language
Badge
Last Updated	2021-07-03T12:24:35