DELETE · k8s原理与实践

[TOC] # DELETE ## 描述该语句用来删除表中符合条件的行，包括单表删除及多表删除两种方式。 ## 格式 ~~~ Single-Table-Delete Syntax: DELETE [hint_options] FROM tbl_name [PARTITION (partition_name,...)] [WHERE where_condition] [ORDER BY order_expression_list] [LIMIT row_count] Multiple-Table-Delete Syntax: DELETE [hint_options] tbl_name[.*] [, tbl_name[.*]] ... FROM table_references [WHERE where_condition] Or: DELETE [hint_options] FROM tbl_name[.*] [, tbl_name[.*]] ... USING table_references [WHERE where_condition] where_condition: expression order_expression_list: order_expression [, order_expression ...] order_expression: expression [ASC | DESC] limit_row_count: INT_VALUE table_references: {tbl_name | joined_table | table_subquery | select_with_parens} [, ...] ~~~ ## 参数解释 <table data-tag="table" id="table-53s-rw6-1k5" class="table"><colgroup width="360" span="1" data-tag="col" id="col-ibv-zpk-rma" colwidth="1*" colnum="1" colname="col1" style="width:50%" class="col"></colgroup><colgroup width="360" span="1" data-tag="col" id="col-hat-e5w-0s5" colwidth="1*" colnum="2" colname="col2" style="width:50%" class="col"></colgroup><thead id="thead-vyf-9hc-ix0" class="thead"><tr id="tr-ndt-9v6-49x"><th id="td-4qb-pgz-3qh">参数</th><th id="td-4ys-003-400">描述</th></tr></thead><tbody data-tag="tbody" id="tbody-2m1-3fs-zgp" class="tbody"><tr data-tag="tr" id="tr-2va-8z6-9rv" class="tr"><td data-tag="td" id="td-lzo-n49-sve" class="td">hint_options</td><td data-tag="td" id="td-f1r-27c-vc3" class="td">指定hint选项。</td></tr><tr data-tag="tr" id="tr-433-tns-2l9" class="tr"><td data-tag="td" id="td-geg-7g0-17j" class="td">tbl_name</td><td data-tag="td" id="td-yw8-6w3-ldm" class="td">指定需要删除的表名。</td></tr><tr data-tag="tr" id="tr-n3h-qo6-nq8" class="tr"><td data-tag="td" id="td-azs-ozb-j76" class="td">partition_name</td><td data-tag="td" id="td-ho9-ucb-k0k" class="td">需要删除表的对应分区名。</td></tr><tr data-tag="tr" id="tr-es5-xdf-euc" class="tr"><td data-tag="td" id="td-bce-v9e-jhw" class="td">where_condition</td><td data-tag="td" id="td-5fx-j2h-q4p" class="td">删除的表需要满足的过滤条件。</td></tr><tr data-tag="tr" id="tr-8h8-la9-fkd" class="tr"><td data-tag="td" id="td-cgf-uue-gov" class="td">order_expression_list</td><td data-tag="td" id="td-kg1-4do-n65" class="td">删除的表的排序键列表。</td></tr><tr data-tag="tr" id="tr-u0j-gid-su9" class="tr"><td data-tag="td" id="td-5l4-sbw-sqj" class="td">row_count</td><td data-tag="td" id="td-6nw-yxx-u2g" class="td">删除的表的行数指定，指定的值只能为整数。</td></tr><tr data-tag="tr" id="tr-fzm-e1f-0zn" class="tr"><td data-tag="td" id="td-3zu-9nx-e7d" class="td">table_references</td><td data-tag="td" id="td-b54-yq4-7b1" class="td">多表删除时指定的待选择的表序列。</td></tr></tbody></table> ## 示例示例表及数据基于以下定义： ~~~ OceanBase(admin@test)>create table t1(c1 int primary key, c2 int); Query OK, 0 rows affected (0.16 sec) OceanBase(admin@test)>select * from t1; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | +----+------+ 4 rows in set (0.06 sec) OceanBase(admin@test)>create table t2(c1 int primary key, c2 int) partition by key(c1) partitions 4; Query OK, 0 rows affected (0.19 sec) OceanBase(admin@test)>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。 ~~~ OceanBase(admin@test)>DELETE FROM t1 WHERE c1 = 2; Query OK, 1 row affected (0.02 sec) OceanBase(admin@test)>select * from t1; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 3 | 3 | | 4 | 4 | +----+------+ 3 rows in set (0.01 sec) ~~~ * 单表删除：删除表 t2 的按照 c2 列排序之后的第一行数据。 ~~~ OceanBase(admin@test)>DELETE FROM t1 ORDER BY c2 LIMIT 1; Query OK, 1 row affected (0.01 sec) OceanBase(admin@test)>select * from t1; +----+------+ | c1 | c2 | +----+------+ | 2 | 2 | | 3 | 3 | | 4 | 4 | +----+------+ 3 rows in set (0.00 sec) ~~~ * 单表删除：执行删除表 t2 的 p2 分区的数据。 ~~~ OceanBase(admin@test)>DELETE FROM t2 PARTITION(p2); Query OK, 3 rows affected (0.02 sec) OceanBase(admin@test)>select * from t2; +----+------+ | c1 | c2 | +----+------+ | 5 | 5 | +----+------+ 1 row in set (0.02 sec) ~~~ * 多表删除：删除 t1，t2 表中 "t1.c1 = t2.c1" 的数据。 ~~~ OceanBase(admin@test)>DELETE t1, t2 FROM t1, t2 WHERE t1.c1 = t2.c1; Query OK, 3 rows affected (0.02 sec) OceanBase(admin@test)>select * from t1; +----+------+ | c1 | c2 | +----+------+ | 4 | 4 | +----+------+ 1 row in set (0.01 sec) OceanBase(admin@test)>select * from t2; +----+------+ | c1 | c2 | +----+------+ | 5 | 5 | +----+------+ 1 row in set (0.01 sec) ~~~ * 多表删除：删除 t1，t2 表中 "t1.c1 = t2.c1" 的数据。 ~~~ OceanBase(admin@test)>DELETE FROM t1, t2 USING t1, t2 WHERE t1.c1 = t2.c1; Query OK, 4 rows affected (0.02 sec) OceanBase(admin@test)>select * from t1; +----+------+ | c1 | c2 | +----+------+ | 4 | 4 | +----+------+ 1 row in set (0.01 sec) OceanBase(admin@test)>select * from t2; Empty set (0.01 sec) ~~~ * 多表删除：删除 t2 表中的 p2 分区中和 t1 表中 "t1.c1 = t2.c1" 的数据。 ~~~ OceanBase(admin@test)>DELETE t2 FROM t1,t2 PARTITION(p2) WHERE t1.c1 = t2.c1; Query OK, 3 rows affected (0.02 sec) OceanBase(admin@test)>select * from t2; +----+------+ | c1 | c2 | +----+------+ | 5 | 5 | +----+------+ 1 row in set (0.01 sec) ~~~ * 对可更新视图v进行删除操作 ~~~ OceanBase(admin@test)>create view v as select * from t1; Query OK, 0 rows affected (0.07 sec) OceanBase(admin@test)>delete from v where v.c1 = 1; Query OK, 1 row affected (0.02 sec) OceanBase(admin@test)>select * from v; +----+------+ | c1 | c2 | +----+------+ | 2 | 2 | | 3 | 3 | | 4 | 4 | +----+------+ 3 rows in set (0.01 sec) ~~~ ## 注意事项不管是多表删除还是单表删除都不支持直接对子查询进行删除操作，比如： `delete from (select * from t1);`