MySQL :: MySQL 8.3 Reference Manual :: 15.2.17 UPDATE Statement

Documentation Home

version 8.3

8.0 current
5.7

8.0 Japanese

MySQL 8.3 Reference Manual / ... / UPDATE Statement

15.2.17 更新语句

UPDATE 是一种 DML 语句，用于修改表中的行。

一个 UPDATE 语句可以以 WITH 子句开始，以定义在 UPDATE 中可访问的公共表表达式。请参阅第 15.2.20 节，“WITH (公共表表达式)”。

单表语法：

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 也不会被更新。

Note

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

更多信息和示例，请参阅第 26.5 节，“分区选择”。

where_condition 是一个表达式，用于评估每个要更新的行的真实性。有关表达式语法，请参阅第 11.5 节，“表达式”。

table_references 和 where_condition 按照第 15.2.13 节，“SELECT 语句” 中所述进行指定。

您需要 UPDATE 权限，只有在 UPDATE 中实际更新的列上。您只需要 SELECT 权限，以读取但不修改的列。

该 UPDATE 语句支持以下修饰符：

使用 LOW_PRIORITY 修饰符，UPDATE 语句的执行将被延迟，直到没有其他客户端从表中读取为止。这只影响使用表级锁定的存储引擎（例如 MyISAM、MEMORY 和 MERGE）。
使用 IGNORE 修饰符，更新语句不会在更新过程中出现错误，即使出现错误也不会中止。具有唯一键值的行不会被更新。如果更新的值会导致数据转换错误，将其更新为最近的有效值。有关更多信息，请参阅忽略对执行的影响。

UPDATE IGNORE 语句，包括具有 ORDER BY 子句的语句，被标记为基于语句的复制不安全。（这是因为更新的顺序确定了哪些行被忽略。）这些语句在使用基于语句的模式时会在错误日志中生成警告，并在使用 MIXED 模式时以基于行的格式写入二进制日志。（Bug #11758262, Bug #50439）请参阅第 19.2.1.3 节，“二进制日志记录中的安全和不安全语句”，以获取更多信息。

如果您在表达式中访问要更新的表的列，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，字符串类型的空字符串 ('')，日期和时间类型的“零”值。请参阅第 13.6 节，“数据类型默认值”。

如果显式更新生成的列，唯一允许的值是 DEFAULT。有关生成的列的信息，请参阅第 15.1.20.8 节，“CREATE TABLE 和生成的列”。

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

您可以使用 LIMIT row_count 限制 UPDATE 的范围。LIMIT 子句是一个行匹配限制。语句将在找到 row_count 行时停止，满足 WHERE 子句，无论它们是否实际被更改。

如果 UPDATE 语句包括 ORDER BY 子句，行将按照子句指定的顺序更新。这在某些情况下可能很有用，否则可能会导致错误。假设表 t 包含一个唯一索引的 id 列。以下语句可能会因更新顺序而失败：

UPDATE t SET id = id + 1;

例如，如果表包含 id 列的 1 和 2，并且 1 被更新为 2 之前 2 被更新为 3，将发生错误。为了避免这个问题，添加 ORDER BY 子句，以便在更新时按照 id 值的大小顺序更新行：

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

您也可以执行UPDATE操作，涵盖多个表格。但是，您不能使用ORDER BY或LIMIT与多表UPDATE。该table_references子句列出了参与连接的表格。其语法在第 15.2.13.2 节，“JOIN 子句”中描述。以下是一个示例：

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

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

如果您使用多表UPDATE语句，涉及到具有外键约束的InnoDB表格，在这种情况下，MySQL 优化器可能会以与父/子关系不同的顺序处理表格。在这种情况下，该语句将失败并回滚。相反，更新单个表格，并依靠InnoDB提供的ON UPDATE功能来使其他表格相应地修改。请参阅第 15.1.20.5 节，“FOREIGN KEY 约束”。

您不能更新表格并直接从同一个表格中选择子查询。您可以使用多表更新，其中一个表格来自您实际想要更新的表格，并使用别名引用派生表格。假设您想更新一个名为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
);

要减少零售价格的任何项目，其中markup 为 30% 或更高，并且您只有少于一百个库存，您可能想使用以下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合并到最外层查询块中，因此这只在您强制派生表格的materialization时才起作用。您可以通过将derived_merge标志设置为optimizer_switch系统变量的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;

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

PREV HOME UP NEXT