更新数据 · k8s原理与实践

[TOC] # 更新数据使用`UPDATE`语句修改表中的字段值。示例如下：创建示例表`t1`和`t2`。 ~~~ obclient> CREATE TABLE t1(c1 int primary key, c2 int); Query OK, 0 rows affected (0.16 sec) obclient> INSERT t1 VALUES(1,1),(2,2),(3,3),(4,4); Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 obclient> SELECT * FROM t1; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | +----+------+ 4 rows in set (0.06 sec) obclient> CREATE TABLE t2(c1 int primary key, c2 int) partition by key(c1) partitions 4; Query OK, 0 rows affected(0.19 sec) obclient> INSERT t2 VALUES(5,5),(1,1),(2,2),(3,3); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 obclient> SELECT * FROM t2; +----+------+ | c1 | c2 | +----+------+ | 5 | 5 | | 1 | 1 | | 2 | 2 | | 3 | 3 | +----+------+ 4 rows in set (0.02 sec) ~~~ * 将表`t1`中`t1.c1=1`对应的那一行数据的`c2`列值修改为`100`。 ~~~ obclient> UPDATE t1 SET t1.c2 = 100 WHERE t1.c1 = 1; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 obclient> SELECT * FROM t1; +----+------+ | c1 | c2 | +----+------+ | 1 | 100 | | 2 | 2 | | 3 | 3 | | 4 | 4 | +----+------+ 4 rows in set (0.01 sec) ~~~ * 将表`t1`中按照`c2`列排序的前两行数据的`c2`列值修改为`100`。 ~~~ obclient> UPDATE t1 set t1.c2 = 100 ORDER BY c2 LIMIT 2; Query OK, 2 rows affected (0.02 sec) Rows matched: 2 Changed: 2 Warnings: 0 obclient> SELECT * FROM t1; +----+------+ | c1 | c2 | +----+------+ | 1 | 100 | | 2 | 100 | | 3 | 3 | | 4 | 4 | +----+------+ 4 rows in set (0.01 sec) ~~~ * 将表`t2`中`p2`分区的数据中`t2.c1 > 2`的对应行数据的`c2`列值修改为`100`。 ~~~ obclient> SELECT * FROM t2 partition (p2); +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +----+------+ 3 rows in set (0.01 sec) obclient> UPDATE t2 partition(p2) SET t2.c2 = 100 WHERE t2.c1 > 2; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 obclient> SELECT * FROM t2 partition (p2); +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 100 | +----+------+ 3 rows in set (0.00 sec) ~~~ * 修改多个表。对于表`t1`和表`t2`中满足`t1.c2 = t2.c2`对应行的数据，将表`t1`中的`c2`列值修改为`100`，表`t2`中的`c2`列值修改为`200`。 ~~~ obclient> UPDATE t1,t2 SET t1.c2 = 100, t2.c2 = 200 WHERE t1.c2 = t2.c2; Query OK, 6 rows affected (0.03 sec) Rows matched: 6 Changed: 6 Warnings: 0 obclient> SELECT * FROM t1; +----+------+ | c1 | c2 | +----+------+ | 1 | 100 | | 2 | 100 | | 3 | 100 | | 4 | 4 | +----+------+ 4 rows in set (0.00 sec) obclient> SELECT * FROM t2; +----+------+ | c1 | c2 | +----+------+ | 5 | 5 | | 1 | 200 | | 2 | 200 | | 3 | 200 | +----+------+ 4 rows in set (0.01 sec) ~~~ 更多`UPDATE`语句相关的语法请参见《SQL 参考（MySQL 模式）》中[UPDATE](https://open.oceanbase.com/docs/community/oceanbase-database/V3.1.0/UPDATE)章节。