更新时间:2025-05-29 GMT+08:00

更新表中数据

修改已经存储在数据库中数据的行为叫做更新。用户可以更新单独一行、所有行或者指定的部分行。还可以独立更新每个字段,而其他字段则不受影响。

使用UPDATE命令更新现有行,需要提供以下三种信息:

  • 表的名称和需要更新的字段名
  • 字段的新值
  • 需要更新的行

SQL通常不会为数据行提供唯一标识,因此更新操作需通过WHERE子句的条件表达式定位目标行。若表中定义主键,则可通过主键值精确指定待更新行;否则必须依赖业务逻辑条件组合进行更新。

建立表和插入数据的步骤请参见创建表向表中插入数据

需要将表customer_t1中c_customer_sk为9527的地域重新定义为9876。

1
gaussdb=# UPDATE customer_t1 SET c_customer_sk = 9876 WHERE c_customer_sk = 9527;

这里的表名称也可以使用模式名修饰,否则会从数据库的默认模式查询该表。SET后面紧跟字段和新的字段值。新的字段值不仅可以是常量,也可以是表达式。

比如,把所有c_customer_sk的值增加100。

1
gaussdb=# UPDATE customer_t1 SET c_customer_sk = c_customer_sk + 100;

在这里省略了WHERE子句,表示表中的所有行都要被更新。如果出现了WHERE子句,那么只有匹配其条件的行才会被更新。

在SET子句中的等号是一个赋值,而在WHERE子句中的等号是比较。WHERE条件不一定是相等比较,许多其他的操作符也可以使用。

用户可以在一个UPDATE命令中更新更多的字段,方法是在SET子句中列出更多赋值,比如:

1
gaussdb=# UPDATE customer_t1 SET  c_customer_id = 'Admin', c_first_name = 'Local' WHERE c_customer_sk = 4421; 

批量更新或删除数据后,会在数据文件中产生大量的删除标记,查询过程中标记删除的数据也是需要扫描的。故多次批量更新/删除后,标记删除的数据量过大会严重影响查询的性能。建议在批量更新/删除业务会反复执行的场景下,定期执行VACUUM FULL以保证查询性能。

批量更新表中数据

对包含数百万条记录的表进行批量更新时,直接执行更新操作会导致表锁定,显著影响其他操作的性能。为了避免这种情况,可以采取以下几种方法来优化批量更新操作:

  • 分批更新

    将大规模更新拆分为多个小批次,每次仅更新部分数据,从而缩短单次锁表的时间。

    例如,原批量更新语句:UPDATE products SET price = price * 1.1 WHERE categroy = 'Electronics';

    优化方案:将批量更新按ID拆分为多条UPDATE语句,每次更新部分数据。

    1
    2
    3
    4
    gaussdb=# UPDATE products SET price = price * 1.1 WHERE categroy = 'Electronics' AND id >= 1 and id <= 10000;
    gaussdb=# UPDATE products SET price = price * 1.1 WHERE categroy = 'Electronics' AND id > 10000 and id <= 20000;
    gaussdb=# UPDATE products SET price = price * 1.1 WHERE categroy = 'Electronics' AND id > 20000 and id <= 30000;
    ...
    
  • 使用临时表

    事务中存在复杂查询的情况下,可以将需要更新的记录先插入到临时表中,然后再从临时表中更新主表,避免长时间锁定这些数据行。

    例如,原批量更新语句:UPDATE products SET price = price * 1.1 WHERE (size = 101 OR size = 102) AND categroy = 'Electronics' AND amount > 150;

    优化方案:创建临时表存储符合条件的目标记录并进行更新,随后使用该临时表更新主表。

    1
    2
    3
    gaussdb=# CREATE TEMPORARY TABLE temp_products AS SELECT id, price * 1.1 as new_price from products WHERE (size = 101 OR size = 102) AND categroy = 'Electronics' AND amount > 150;
    gaussdb=# UPDATE products p SET price = t.new_price FROM temp_products t where p.id = t.id;
    gaussdb=# DROP TABLE temp_products;
    
  • 使用并行更新

    在多线程或多进程环境中,可以将更新操作并行化,多个线程同时更新不同的记录。这需要确保每个线程处理的记录不重叠。

    例如,原批量更新语句:UPDATE products SET price = price * 1.1 WHERE categroy = 'Electronics';

    优化方案:根据ID将数据拆分为多个小批次,启动多个会话同时执行UPDATE操作,通过并发更新降低批量操作的总耗时。需确保各会话更新的元组无重叠。

    session1:

    1
    gaussdb=# UPDATE products SET price = price * 1.1 WHERE categroy = 'Electronics' AND id >= 1 and id <= 10000;
    

    session2:

    1
    gaussdb=# UPDATE products SET price = price * 1.1 WHERE categroy = 'Electronics' AND id > 10000 and id <= 30000;
    

    session3:

    1
    gaussdb=# UPDATE products SET price = price * 1.1 WHERE categroy = 'Electronics' AND id > 30000;