文档

通过DELETE命令实现删除

更新时间:
一键部署

本文介绍如何在云数据库 SelectDB 版实例中,通过DELETE命令删除数据

概述

DELETE不同于其他导入方式,它是一个同步过程,与INSERT INTO相似。所有的DELETE操作在SelectDB中是一个独立的导入作业。通常DELETE语句需要指定表和分区以及删除的条件来筛选要删除的数据,并会同时删除BASE表和ROLLUP表的数据。

使用方式

该语句用于按条件删除指定Table及其ROLLUP中的数据。

语法

DELETE FROM table_name [PARTITION partition_name | PARTITIONS (partition_name [, partition_name])]
WHERE
column_name op { value | value_list } [ AND column_name op { value | value_list } ...];

参数说明

参数名称

参数说明

table_name

指定需要删除数据的表。

PARTITION partition_name | PARTITIONS (partition_name[, partition_name])

可选参数。指定执行删除数据的分区名,如果表不存在此分区,则报错。

column_name

指定需要删除数据的表的列名。

op

逻辑比较操作符,可选类型包括:=, >, <, >=, <=, !=, in, not in

value | value_list

做逻辑比较的值或值列表。

重要
  • 使用聚合类的表模型(AGGREGATE、UNIQUE)只能指定Key列上的条件。

  • 若选定的Key列不存在于某个物化视图或ROLLUP中,则无法进行DELETE。

  • 条件之间只能是“与”的关系。若希望达成“或”的关系,需要将条件分写在两个DELETE语句中。

  • 如果为分区表,删除时需要指定分区。如果不指定,SelectDB会从条件中推断出分区。当分区表未指定分区,或者无法从条件中推断分区的时,需要设置会话变量delete_without_partition为 true,此时DELETE会应用到所有分区。以下两种情况会使SelectDB无法从条件中推断出分区。

    • 条件中不包含分区列。

    • 分区列的op为not in。

  • DELETE语句可能会降低执行后一段时间内的查询效率。影响程度取决于语句中指定的删除条件的数量。指定的条件越多,影响越大。

  • 不同于Insert into命令,DELETE不能手动指定label

返回结果

DELETE命令是一个SQL命令,返回结果是同步的,分为以下三种。

执行成功

DELETE顺利执行完成并可见,将返回如下结果,Query OK表示成功。

DELETE FROM test_tbl PARTITION p1 WHERE k1 = 1;
Query OK, 0 rows affected (0.04 sec)
{'label':'delete_e7830c72-eb14-4cb9-bbb6-eebd4511d251', 'status':'VISIBLE', 'txnId':'4005'}

提交成功,但未可见

SelectDB的事务提交分为两步提交和发布版本。

完成了发布版本步骤后,结果才对您可见。若已经提交成功,那么最终一定会发布成功。SelectDB会尝试在提交完后等待发布版本一段时间,如果超时后发布版本还未完成,会优先返回,提示您提交已经完成。

DELETE FROM test_tbl PARTITION p1 WHERE k1 = 1;
Query OK, 0 rows affected (0.04 sec)
{'label':'delete_e7830c72-eb14-4cb9-bbb6-eebd4511d251', 'status':'COMMITTED', 'txnId':'4005', 'err':'delete job is committed but may be taking effect later' }

返回结果的JSON字符串参数说明如下。

参数名称

参数说明

rows affected

表示此次删除影响的行。由于SelectDB的删除目前是逻辑删除,因此当前的这个值恒为0。

label

自动生成的Label,是该导入作业的标识。每个导入作业都有一个在单Database内部唯一的Label。

status

表示数据删除是否可见,如果可见则显示VISIBLE,如果不可见则显示COMMITTED。

txnId

这个Delete job对应的事务ID。

err

显示一些本次删除的详细报错信息。

提交失败,事务取消

DELETE语句没有提交成功,将会被SelectDB自动中止,返回如下结果。

DELETE FROM test_tbl PARTITION p1 WHERE k1 > 80;
ERROR 1064 (HY000): errCode = 2, detailMessage = {错误原因}

若删除操作超时,将会返回timeout时间和未完成的(tablet=replica)

DELETE FROM test_tbl PARTITION p1 WHERE k1 > 80;
ERROR 1064 (HY000): errCode = 2, detailMessage = failed to delete replicas from job: 4005, Unfinished replicas:10000=60000, 10001=60000, 10002=60000

通过上述示例,DELETE操作返回结果的正确处理逻辑为:

  • 返回结果为ERROR 1064 (HY000),则表示删除失败。

  • 返回结果为Query OK,则表示删除执行成功。

    • 如果status为COMMITTED,表示数据仍不可见,您可以稍等一段时间再用show delete命令查看结果。

    • 如果status为VISIBLE,表示数据删除成功。

查看历史记录

您可以通过SHOW DELETE语句查看历史上已执行完成的删除记录。

语法如下。

SHOW DELETE [FROM db_name]

示例如下。

SHOW DELETE FROM test_db;
+-----------+---------------+---------------------+-----------------+----------+
| TableName | PartitionName | CreateTime          | DeleteCondition | State    |
+-----------+---------------+---------------------+-----------------+----------+
| empty_tbl | p3            | 2020-04-15 23:09:35 | k1 EQ "1"       | FINISHED |
| test_tbl  | p4            | 2020-04-15 23:09:53 | k1 GT "80"      | FINISHED |
+-----------+---------------+---------------------+-----------------+----------+
2 rows in set (0.00 sec)

相关FE配置

TIMEOUT配置

SelectDB删除作业的超时时间限制在30秒到5分钟时间内,具体时间可通过如下配置项调整。

  • tablet_delete_timeout_second

    DELETE自身的超时时间是由分区下Tablet的数量弹性改变的。此项配置为平均一个Tablet所贡献的Timeout时间,默认值为2,单位是秒。假设此次删除所指定分区下有5个Tablet,那么可提供给DELETE的Timeout时间为10秒,由于低于最低超时时间30秒,因此最终超时时间为30秒。

  • load_straggler_wait_second

    如果您预估的删除数据量确实比较大,使得5分钟的上限不足时,您可以通过此项调整Timeout上限,默认值为300,单位是秒。Timeout的具体计算规则为:

    TIMEOUT = MIN(load_straggler_wait_second, MAX(30, tablet_delete_timeout_second * tablet_num))
  • query_timeout

    DELETE本身是一个SQL命令,因此删除语句也会受Session限制。Timeout还受Session中的query_timeout值影响,因此可以通过SET query_timeout = xxx来增加超时时间,默认值为900,单位是秒。

IN谓词配置

  • max_allowed_in_element_num_of_delete

    如果在使用IN谓词时需要占用的元素比较多,您可以通过此项调整允许携带的元素上限,默认值为1024。

  • 本页导读 (1)