更新
是修改表中的行的DML语句。
一个更新
语句可以以a开头与
子句中可访问的公共表表达式更新
.看到第13.2.15节“WITH(常用表表达式)”.
文档的语法:
更新[low_priority] [ignore]table_reference集assignment_list(在哪里where_condition[按…订购]][限制row_count]价值: {expr|默认}赋值:col_name=价值assignment_list:赋值(,赋值)……
多个表的语法:
更新[low_priority] [ignore]table_references集assignment_list(在哪里where_condition]
对于单表语法,使用更新
语句用新值更新已命名表中现有行的列。的集
子句指示要修改哪些列以及应该给出哪些列的值。每个值都可以作为表达式或关键字给出默认的
将列显式设置为其默认值。的在哪里
子句指定标识要更新哪些行的条件。没有在哪里
子句,则更新所有行。如果命令
子句时,将按指定的顺序更新行。的限制
子句对可更新的行数施加了限制。
对于多表语法,更新
更新中命名的每个表中的行table_references
满足条件。每个匹配的行只更新一次,即使它多次匹配条件。对于多表语法,命令
而且限制
不能使用。
对于分区表,该语句的单-单和多表形式都支持使用分区
子句作为表引用的一部分。该选项接受一个或多个分区或子分区(或两个都有)的列表。只检查列出的分区(或子分区)是否匹配,不属于任何这些分区或子分区的行不会更新,无论它是否满足where_condition
与否。
有关更多信息和示例,请参见第24.5节“分区选择”.
where_condition
对于要更新的每一行,该表达式的值都为true。有关表达式语法,请参见第9.5节“表达式”.
table_references
而且where_condition
中所描述的第13.2.10节“SELECT语句”.
你需要更新
类中引用的列的权限更新
这些都被更新了。你只需要选择
读取但未修改的任何列的权限。
的更新
语句支持以下修饰符:
与
LOW_PRIORITY
修饰符,执行更新
延迟,直到没有其他客户端从表中读取。这只影响只使用表级锁的存储引擎(例如MyISAM
,内存
,合并
).与
忽略
修饰符,则更新语句即使在更新过程中发生错误也不会中止。在唯一键值上发生重复键冲突的行不会更新。将更新为可能导致数据转换错误的值的行更新为最接近的有效值。有关更多信息,请参见IGNORE对语句执行的影响.
更新忽略
语句,包括有命令
子句,对于基于语句的复制被标记为不安全。(这是因为行更新的顺序决定了哪些行被忽略。)当使用基于语句的模式时,这些语句在错误日志中产生警告,当使用基于行的格式时,这些语句将被写入二进制日志混合
模式。(Bug #11758262, Bug #50439)参见第17.2.1.3节“二进制日志记录中安全与不安全语句的确定”,以获取更多资料。
如果您从表达式中要更新的表中访问一列,更新
使用列的当前值。例如,下面的语句集col1
比当前值大1:
UPDATE t1 SET col1 = col1 + 1;
下面语句中的第二个赋值集合col2
到当前(已更新)col1
价值,不是原创col1
价值。结果是col1
而且col2
有相同的价值。这种行为不同于标准SQL。
UPDATE t1 SET col1 = col1 + 1, col2 = col1;
单表更新
作业通常从左到右计算。对于多表更新,不能保证以任何特定顺序执行赋值。
如果您将一个列设置为它当前拥有的值,MySQL会注意到这一点,并且不会更新它。
如果更新已声明的列非空
通过设置为零
,如果启用了严格SQL模式,则会出现错误;否则,将列设置为列数据类型的隐式默认值,并增加警告计数。隐式默认值为0
对于数字类型,空字符串(”
)用于字符串类型,而”零”值用于日期和时间类型。看到第11.6节,“数据类型默认值”.
如果显式更新生成的列,则唯一允许的值为默认的
.有关生成列的信息,请参见章节13.1.20.8,“创建表和生成的列”.
更新
返回实际更改的行数。的mysql_info ()
函数返回匹配和更新的行数,以及在处理过程中发生的警告数更新
.
你可以使用限制
限制的范围row_count
更新
.一个限制
子句是一个行匹配限制。陈述一旦发现就会停止row_count
满足在哪里
子句,不管它们是否真的被改变了。
如果一个更新
语句包含命令
子句时,将按子句指定的顺序更新行。这在某些情况下很有用,否则可能导致错误。假设有一张表t
包含一列id
它有一个唯一的下标。下面的语句可能会失败,并出现重复键错误,这取决于行更新的顺序:
UPDATE SET id = id + 1;
例如,如果表中包含1和2id
列和1在2更新为3之前更新为2,则发生错误。要避免此问题,请添加命令
子句导致具有较大的行id
在较小值之前更新的值:
UPDATE SET id = id + 1
你也可以执行更新
覆盖多个表的操作。但是,您不能使用命令
或限制
使用多表更新
.的table_references
子句列出了连接中涉及的表。其语法在第13.2.10.2节“JOIN子句”.这里有一个例子:
UPDATE items, SET items.price=month。WHERE items.id=month.id;
前面的示例展示了一个内部连接,它使用逗号操作符,但使用多个表更新
语句可以使用中允许的任何类型的连接选择
语句,例如左连接
.
如果你使用多表更新
声明中涉及InnoDB
对于有外键约束的表,MySQL优化器可能会以不同于它们的父/子关系的顺序处理表。在这种情况下,语句失败并回滚。方法更新单个表并依赖于在更新
功能,InnoDB
提供使其他表进行相应修改的方法。看到第13.1.20.5节,“外键约束”.
不能更新表并在子查询中直接从同一个表中进行选择。您可以通过使用多表更新来解决这个问题,其中一个表派生自您实际希望更新的表,并使用别名引用派生表。假设您希望更新一个名为项目
它是用下面的语句定义的:
创建表项(id BIGINT NOT NULL AUTO_INCREMENT主键,批发DECIMAL(6,2) NOT NULL DEFAULT 0.00,零售DECIMAL(6,2) NOT NULL DEFAULT 0.00,数量BIGINT NOT NULL DEFAULT 0);
要降低任何加价为30%或更高且库存不足100件的商品的零售价格,可以尝试使用更新
语句,例如下面的语句,该语句在在哪里
条款。如下所示,这个语句不起作用:
mysql> UPDATE items > SET retail = retail * 0.9 > WHERE id IN > (SELECT id FROM items > WHERE retail / wholesale >= 1.3 AND quantity > 100);错误1093 (HY000):你不能在FROM子句中指定要更新的目标表“items”
相反,您可以使用多表更新,其中子查询被移动到要更新的表列表中,使用别名在最外层引用它在哪里
从句,像这样:
UPDATE items, (SELECT id FROM items WHERE id IN (SELECT id FROM items WHERE retail / wholesale >= 1.3 AND quantity < 100))作为打折SET items。零售=物品。零售* 0.9 WHERE项目。Id = discount . Id;
因为优化器在默认情况下尝试合并派生表折扣
在最外层的查询块中,只有在强制物化派生表时才会起作用。可以通过设置derived_merge
国旗optimizer_switch
系统变量为从
运行更新之前,或使用NO_MERGE
优化提示,如下所示:
UPDATE /*+ NO_MERGE(折扣)*/ items, (SELECT id FROM items WHERE retail / wholesale >= 1.3 AND quantity < 100) AS discount SET items。零售=物品。零售* 0.9 WHERE项目。Id = discount . Id;
在这种情况下使用优化器提示的好处是,它只应用于所使用的查询块内,因此不需要更改的值optimizer_switch
在执行更新
.
另一种可能是重写子查询,使其不再使用在
或存在
,像这样:
更新项目,(SELECT id,零售/批发作为标记,数量从项目)作为折扣设置项目。零售=物品。零售* 0.9 WHERE打折。标记>= 1.3和折扣。数量< 100个AND项。Id = discount . Id;
在这种情况下,默认情况下子查询是物化的,而不是合并的,因此没有必要禁用派生表的合并。