通过UPDATE实现列更新

本文介绍如何在云数据库 SelectDB 版实例中,使用UPDATE命令进行列更新

概述

SelectDB在进行UPDATE更新时,利用查询引擎自身的WHERE过滤逻辑,从待更新表中筛选出需要被更新的行,再利用Unique模型提供的Value列中新数据替换旧数据的逻辑,将待更新的行变更后再重新插入到表中,从而实现列更新。UPDATE在SelectDB中是一个同步语法,即UPDATE语句执行成功,代表更新操作完成,数据可见。

适用场景

UPDATE适用场景如下。

  • Unique数据模型。

  • 对满足过滤条件的行,修改其取值。

  • 点更新,小范围更新,待更新的行最好是整个表的非常小的一部分。

Update更新语法

UPDATE table_name [table_alias]
    SET assignment_list
    WHERE condition

assignment_list:
    assignment [, assignment] ...

assignment:
    col_name = value

value:
    {expr | DEFAULT}

参数名称

参数说明

table_name

待更新数据的目标表。格式:db_name.table_name

assignment_list

待更新的目标列。格式:col_name = value, col_name = value

WHERE condition

期望更新的条件,一个返回true或false的表达式。

使用示例

  1. 例如SelectDB中存在一张订单表,其中订单ID是Key列,订单状态和订单金额是Value列。查询数据如下。

    +----------+--------------+--------------+
    | order_id | order_amount | order_status |
    +----------+--------------+--------------+
    | 1        |          100 | 待付款       |
    +----------+--------------+--------------+
    1 row in set (0.01 sec)
  2. 在用户点击付款后,SelectDB需要将订单ID为"1"的订单状态变更为"待发货",就需要用到UPDATE功能,示例如下。

    UPDATE test_order SET order_status = '待发货' WHERE order_id = 1;
    Query OK, 1 row affected (0.11 sec)
    {'label':'update_20ae22daf0354fe0-b5aceeaaddc666c5', 'status':'VISIBLE', 'txnId':'33', 'queryId':'20ae22daf0354fe0-b5aceeaaddc666c5'}
  3. 更新后,查询结果如下。

    +----------+--------------+--------------+
    | order_id | order_amount | order_status |
    +----------+--------------+--------------+
    | 1        |          100 | 待发货       |
    +----------+--------------+--------------+
    1 row in set (0.01 sec)

执行UPDATE命令后,SelectDB会进行如下三步操作。

  1. 读取满足WHERE订单id=1的行(1,100,'待付款')。

  2. 变更该行的订单状态,从“待付款”改为“待发货”(1,100,'待发货')。

  3. 将更新后的行再插入回表中,从而达到更新的效果。

    订单ID

    订单金额

    订单状态

    1

    100

    待付款

    1

    100

    待发货

    由于表test_order是Unique模型,所以相同Key的行,只有后者才会生效,所以最终数据如下。

    订单ID

    订单金额

    订单状态

    1

    100

    待发货

最佳实践

性能

UPDATE语句的性能和如下两个因素密切相关。

  • 待更新的行数:待更新的行数越多,UPDATE语句的执行速度越慢。这和导入的原理是一致的。SelectDB的UPDATE更新适用于偶发更新的场景,例如修改个别行的值。大批量的修改数据并不适合使用UPDATE,因为大批量修改会使UPDATE语句花费较多的运行时间。

  • Condition的检索效率:SelectDB在执行UPDATE操作时,会先将满足condition的行读取处理,如果condition的检索效率高,则UPDATE的速度也会快。若condition列能命中索引或者分区分桶裁剪,则SelectDB无需扫描全表,可以快速定位到需要更新的行,从而提升更新效率。不推荐condition列中包含Unique模型的Value列。

并发控制

默认情况下,SelectDB不允许同一时间对同一张表并发进行多个UPDATE操作。因为SelectDB目前支持的是Update更新,即使声明的是SET v2 = 1,实际上其他所有的Value列也会被覆盖一遍(尽管值没有变化)。因此如果同时有两个UPDATE操作对同一行进行更新,那么其行为可能是不确定的,可能存在脏数据。

在实际应用中,如果可以保证,即使并发更新,也不会同时对同一行进行操作,就可以手动打开并发限制。通过修改FE配置enable_concurrent_update=true,可取消更新并发的限制。

说明

开启配置后,会有一定的性能风险。提升更新效率,详情请参见性能

重要

由于SelectDB目前支持的是行更新,并且采用的是读取后再写入的两步操作,如果UPDATE语句和其他导入或DELETE语句刚好修改的是同一行时,数据更新的结果将存在不确定性。因此在使用时,必须注意UPDATE语句和其他DML语句的并发控制。

更新Key列

目前UPDATE操作只支持更新Value列。如果需要更新Key列,用户需自行把UPDATE操作转换为DELETE+INSERT操作,具体操作如下。

  1. 读取满足条件的行。

  2. 根据WHERE操作,更新对应行的Key列。

  3. 删除更新前的行,建议采用批量删除中的方式进行删除。

  4. 将更新后的行插入表中。