MySQL update 语法

2022-01-22 22:19:24

更新语法分单表和多表

# 单表操作
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]

1、update语句更新匹配到的数据行中的部分或全部列的值。复值语句中的值可以是：明确的值、表达式、关键字 DEFAULT (使用默认值)
2、WHERE 从句给出过滤条件，确定要更新的行。如果没有条件where，则所有行都会被更新。
3、如果有 ORDER BY 从句，则多个数据行按该顺序更新
4、LIMIT 限制更新条数
5、多表操作没有 order by 排序和 limit 条数限制。
6、每行只更新一次，即使多次配匹配
7、table_references

table_references:
    escaped_table_reference [, escaped_table_reference] ...

escaped_table_reference: {
    table_reference
  | { OJ table_reference }
}

table_reference: {
    table_factor
  | joined_table
}

table_factor: {
    tbl_name [PARTITION (partition_names)]
        [[AS] alias] [index_hint_list]
  | [LATERAL] table_subquery [AS] alias [(col_list)]
  | ( table_references )
}

joined_table: {
    table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification]
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
  | table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor
}

join_specification: {
    ON search_condition
  | USING (join_column_list)
}

join_column_list:
    column_name [, column_name] ...

index_hint_list:
    index_hint [, index_hint] ...

index_hint: {
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | {IGNORE|FORCE} {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
}

index_list:
    index_name [, index_name] ...

8、where_condition 是一个表达式，可以使用MySQL支持的除聚合函数外的所有函数和操作符
9、修饰符：
- 1）LOW_PRIORITY ：将更新操作设为低优先级，在没有其他客户端读取时，执行更新操作
- 2）IGNORE ：会忽略一些错误，即使发生错误也不会停止语句的执行。唯一键的重复值错误也会忽略，但不会更新数据。
10、 UPDATE IGNORE 语句和包含 ORDER BY 从句的更新，对于基于语句的复制集时不安全的
11、在表示式中访问将要更新的列，该列的值是其当前的值

# col1 最后的值是 在执行表示式时的当前值 + 1，即 原来的值 + 1
UPDATE t1 SET col1 = col1 + 1;

# col1 的值 同上， col2 的值 是 col1 的当前值，这时col1 已经 +1 了，所以 col2 最后和 col1 值相等
UPDATE t1 SET col1 = col1 + 1, col2 = col1;

12、单表更新的赋值一般是从左到右，而多表更新无法保证按照特定顺序执行。
13、如果将某一列更新设置为其当前值（即新值和旧值相同），MySQL 不会更新这个列，不会多此一举。
14、在 SQL 严格模式下，将 NOT NULL 的列更新为 NULL，会报错。相反，在非严格模式下，会将值设置为其类型的隐式默认值，如，数字类型设置为 0，字符串设置为空字符串 ''，日期和时间类型设置为 ‘zero’
15、给会自动生成值的列指定值，只能指定 DEFAULT。自动生成值的列，指的是在创建表时指定了计算表达式，通过其他列计算得到的列。
16、UPDATE 语句会返回实际更新的行数。 mysql_info() C API 函数返回在更新过程中匹配到的行数，更新了的行数，以及警告数
17、ORDER BY 的使用

# id 是唯一的数字列，若id有多个从小到大的连续值（1,2,3），此时更新会报错。因为默认会按顺序更新id：1+1 =》2 就会和已存在的 2 冲突
UPDATE t SET id = id + 1;

# 使用 order by id desc 按 id 倒序排列。先更新最大的值，就不会出新 重复值 错误
UPDATE t SET id = id + 1 ORDER BY id DESC;

18、多表操作可以使用任何在 SELECT 语句中的允许的 JOIN 从句，比如内连接

# 在 table_references  中使用了 内连接
UPDATE  items,month SET items.price=month.price
WHERE items.id=month.id;

19、如果多表更新时，包含有外键的InnoDB的表，则MySQL 优化器可能不会按照父子关系的顺序处理表，这样的话，语句执行失败，并回滚。
20、不能更新一个和一个直接子查询表相同的表。可以将子查询作为一个导出表，再用多表更新的方式更新。

# 表结构
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
);

# 更新 retail，直接子查询，报错
 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

# 将子查询作为导出表（临时表），再用多表更新的方式
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;

# 或者
UPDATE 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 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
表达式： https://dev.mysql.com/doc/refman/8.0/en/expressions.html
函数和操作符： https://dev.mysql.com/doc/refman/8.0/en/functions.html
自动生成列：https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html

码农公寓

相关文章