删除数据 · k8s原理与实践

[TOC] # 删除数据使用 DELETE 语句删除数据。示例如下：假设有如下所示数据的表`t1`和`t2`。其中，表`t2`为 KEY 分区表，且分区名由系统根据分区命令规则自动生成，即分区名为`p0`、`p1`、`p2`、`p3`。 ~~~ 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.00 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 INTO 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) ~~~ * 单表删除，删除`c1=2`的行，其中`c1`列为表`t1`中的 Primary Key。 ~~~ obclient> DELETE FROM t1 WHERE c1 = 2; Query OK, 1 row affected (0.02 sec) obclient> SELECT * FROM t1; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 3 | 3 | | 4 | 4 | +----+------+ 3 rows in set (0.01 sec) ~~~ * 单表删除，删除表`t1`中按照`c2`列排序之后的第一行数据。 ~~~ obclient> DELETE FROM t1 ORDER BY c2 LIMIT 1; Query OK, 1 row affected (0.01 sec) obclient> SELECT * FROM t1; +----+------+ | c1 | c2 | +----+------+ | 2 | 2 | | 3 | 3 | | 4 | 4 | +----+------+ 3 rows in set (0.00 sec) ~~~ * 单表删除，删除表`t2`的`p2`分区的数据。 ~~~ obclient> SELECT * FROM t2 PARTITION(p2); +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +----+------+ 3 rows in set (0.01 sec) obclient> DELETE FROM t2 PARTITION(p2); Query OK, 3 rows affected (0.02 sec) obclient> SELECT * FROM t2; +----+------+ | c1 | c2 | +----+------+ | 5 | 5 | +----+------+ 1 row in set (0.02 sec) ~~~ * 多表删除，删除`t1`、`t2`表中`t1.c1 = t2.c1`的数据。 ~~~ obclient> DELETE t1, t2 FROM t1, t2 WHERE t1.c1 = t2.c1; Query OK, 3 rows affected (0.02 sec) obclient> SELECT * FROM t1; +----+------+ | c1 | c2 | +----+------+ | 4 | 4 | +----+------+ 1 row in set (0.01 sec) obclient> SELECT * FROM t2; +----+------+ | c1 | c2 | +----+------+ | 5 | 5 | +----+------+ 1 row in set (0.01 sec) ~~~ * 多表删除，删除`t1`、`t2`表中`t1.c1 = t2.c1`的数据。 ~~~ obclient> DELETE FROM t1, t2 USING t1, t2 WHERE t1.c1 = t2.c1; Query OK, 4 rows affected (0.02 sec) obclient> SELECT * FROM t1; +----+------+ | c1 | c2 | +----+------+ | 4 | 4 | +----+------+ 1 row in set (0.01 sec) obclient> SELECT * FROM t2; Empty set (0.01 sec) ~~~ 更多`DELETE`语句相关的语法说明请参见《SQL 参考（MySQL 模式）》中[DELETE](https://open.oceanbase.com/docs/community/oceanbase-database/V3.1.0/DELETE)章节。