刪除數據 · 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)章節。