跳到主要内容
版本:Latest-3.3

DELETE

该语句用于从表中删除数据行。表可以是分区表或者非分区表。

对于明细表、聚合表,以及更新表,支持删除表中指定分区的数据。从 2.3 版本开始,主键表支持完整的 DELETE...WHERE 语义,即支持按主键、任意列、以及子查询结果删除数据。从 3.0 版本开始,主键类型表丰富了 DELETE...WHERE 语义,支持使用多表关联和公用表表达式(CTE)。

注意事项

  • 执行 DELETE 操作需要有对应表的 DELETE 权限。
  • 不建议您执行高频的 DELETE 操作。如需要,请在业务低峰期进行。
  • DELETE 删除的是表中数据,表依然存在。如果要删除表,参见 DROP TABLE
  • 为防止误删整表,必须在 DELETE 语句中指定 WHERE 子句。
  • 删除的数据会标记为“Deleted”,暂时保留在 Segment 中,不会立即进行物理删除。Compaction(数据版本合并)完成之后会被回收。
  • 该操作会同时删除和表相关的物化视图的数据。

DELETE 与明细类型、聚合类型和更新类型表

语法

DELETE FROM [<db_name>.]<table_name> [PARTITION <partition_name>]
WHERE
column_name1 op { value | value_list } [ AND column_name2 op { value | value_list } ...]

参数说明

参数必选描述
db_nameNo要操作的表所在的数据库。如果不指定,默认为当前数据库。
table_nameYes要操作的表名。
partition_nameNo要操作的分区名。
column_nameYes作为删除条件的列,可以指定一个或多个列。
opYes指定的算子。支持 =><>=<=!=INNOT IN

使用限制

  • 聚合类型和更新类型表仅支持 Key 列作为删除条件。明细类型表支持任意列作为删除条件。
  • 条件之间只能是“AND”关系。若希望达成“OR”的关系,需要将条件分别写在两个 DELETE 语句中。
  • 明细类型、聚合类型和更新表类型下,DELETE 语句目前不支持以子查询结果作为删除条件。

影响

执行 DELETE 语句后,可能会导致接下来一段时间内(Compaction 完成之前)的查询效率降低。影响程度取决于语句中指定的删除条件的数量。指定的条件越多,影响越大。

示例

创建表并插入数据

以下示例创建一张明细类型的分区表。

CREATE TABLE `my_table` (
`date` date NOT NULL,
`k1` int(11) NOT NULL COMMENT "",
`k2` varchar(65533) NULL DEFAULT "" COMMENT "")
DUPLICATE KEY(`date`)
PARTITION BY RANGE(`date`)
(
PARTITION p1 VALUES [('2022-03-11'), ('2022-03-12')),
PARTITION p2 VALUES [('2022-03-12'), ('2022-03-13'))
)
DISTRIBUTED BY HASH(`date`)
PROPERTIES
("replication_num" = "3");

INSERT INTO `my_table` VALUES
('2022-03-11', 3, 'abc'),
('2022-03-11', 2, 'acb'),
('2022-03-11', 4, 'abc'),
('2022-03-12', 2, 'bca'),
('2022-03-12', 4, 'cba'),
('2022-03-12', 5, 'cba');

查询表数据

select * from my_table order by date; 
+------------+------+------+
| date | k1 | k2 |
+------------+------+------+
| 2022-03-11 | 3 | abc |
| 2022-03-11 | 2 | acb |
| 2022-03-11 | 4 | abc |
| 2022-03-12 | 2 | bca |
| 2022-03-12 | 4 | cba |
| 2022-03-12 | 5 | cba |
+------------+------+------+

删除数据

删除指定分区中的行

删除 my_tablep1 分区中 k1 列值为 3 的数据行。

DELETE FROM my_table PARTITION p1
WHERE k1 = 3;

-- 可以看到 p1 分区中 k1 为 3 的那行数据被删除。
select * from my_table partition (p1);
+------------+------+------+
| date | k1 | k2 |
+------------+------+------+
| 2022-03-11 | 2 | acb |
| 2022-03-11 | 4 | abc |
+------------+------+------+

删除指定分区中满足 AND 条件的行

删除 my_tablep1 分区中 k1 列值大于等于 3k2 列值为 "abc" 的数据行。

DELETE FROM my_table PARTITION p1
WHERE k1 >= 3 AND k2 = "abc";

select * from my_table partition (p1);
+------------+------+------+
| date | k1 | k2 |
+------------+------+------+
| 2022-03-11 | 2 | acb |
+------------+------+------+

删除所有分区中满足条件的行

删除 my_table 表所有分区中 k2 列值为 "abc""cba" 的数据行。

DELETE FROM my_table
WHERE k2 in ("abc", "cba");

select * from my_table order by date;
+------------+------+------+
| date | k1 | k2 |
+------------+------+------+
| 2022-03-11 | 2 | acb |
| 2022-03-12 | 2 | bca |
+------------+------+------+

DELETE 与主键类型表

从 2.3 版本开始,主键类型表支持完整的 DELETE...WHERE 语义。支持按主键、任意列、以及子查询结果删除数据。从 3.0 版本开始,StarRocks 丰富了 DELETE...WHERE 语义,支持使用多表关联和公用表表达式(CTE)。如果需要将待操作的表与数据库中其他表关联,则可以在 USING 子句或 CTE 中引用其他的表。

语法

[ WITH <with_query> [, ...] ]
DELETE FROM <table_name>
[ USING <from_item> [, ...] ]
[ WHERE <where_condition> ]

参数说明

参数是否必选描述
with_queryNo一个或多个可以在 DELETE 语句中通过名字引用的 CTE。CTE 是一个临时结果集,可以提高复杂语句的易读性。
table_nameYes要操作的表名。
from_itemNo引用数据库中一个或者多个其他的表。该表与待操作的表进行连接,WHERE 子句指定连接条件,最终 StarRocks 基于连接查询的结果集删除待操作表中的匹配行。 例如 USING 子句为 USING t1 WHERE t0.pk = t1.pk;,StarRocks 实际执行 DELETE 语句时会将该 USING 子句的表表达式会转换为 t0 JOIN t1 ON t0.pk=t1.pk;
where_conditionNo只有满足 WHERE 条件的行才会被删除。该参数为必选,防止误删整张表。如需删除表中的所有行,请使用 WHERE true

注意事项

  • 主键类型表目前还不支持删除指定分区中的数据,例如 DELETE FROM <table_name> PARTITION <partition_id> WHERE <where_condition>
  • 支持如下比较运算符:=><>=<=!=INNOT IN
  • 支持如下逻辑运算符:ANDOR
  • 不支持并发删除或导入数据时删除,因为可能无法保证导入的事务性。

示例

创建表并插入数据

在数据库 test 中创建一张名为 score_board 的主键类型表:

CREATE TABLE `score_board` (
`id` int(11) NOT NULL COMMENT "",
`name` varchar(65533) NULL DEFAULT "" COMMENT "",
`score` int(11) NOT NULL DEFAULT "0" COMMENT "")
ENGINE=OLAP
PRIMARY KEY(`id`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`id`)
PROPERTIES
(
"replication_num" = "3",
"storage_format" = "DEFAULT",
"enable_persistent_index" = "false"
);

INSERT INTO score_board VALUES
(0, 'Jack', 21),
(1, 'Bob', 21),
(2, 'Stan', 21),
(3, 'Sam', 22);

查询表数据

select * from score_board;
+------+------+------+
| id | name | score|
+------+------+------+
| 0 | Jack | 21 |
| 1 | Bob | 21 |
| 2 | Stan | 21 |
| 3 | Sam | 22 |
+------+------+------+
4 rows in set (0.00 sec)

删除数据

按主键删除数据

通过指定主键,可以避免全表扫描。

删除 score_board 表中 id 列值为 0 的数据行。

DELETE FROM score_board WHERE id = 0;

select * from score_board;
+------+------+------+
| id | name | score|
+------+------+------+
| 1 | Bob | 21 |
| 2 | Stan | 21 |
| 3 | Sam | 22 |
+------+------+------+
3 rows in set (0.01 sec)

按条件删除数据

条件中的列,可以为任意列。

示例一:删除 score_board 表中 score 列值等于 22 的所有数据行。

DELETE FROM score_board WHERE score = 22;

select * from score_board;
+------+------+------+
| id | name | score|
+------+------+------+
| 0 | Jack | 21 |
| 1 | Bob | 21 |
| 2 | Stan | 21 |
+------+------+------+
3 rows in set (0.01 sec)

示例二:删除 score_board 表中 score 列值小于 22 的所有数据行。

DELETE FROM score_board WHERE score < 22;

select * from score_board;
+------+------+------+
| id | name | score|
+------+------+------+
| 3 | Sam | 22 |
+------+------+------+
1 row in set (0.01 sec)

示例三:删除 score_board 表中 score 列值小于 22、且 name 列值不为 Bob 的所有数据行。

DELETE FROM score_board WHERE score < 22 and name != "Bob";

select * from score_board;
+------+------+------+
| id | name | score|
+------+------+------+
| 1 | Bob | 21 |
| 3 | Sam | 22 |
+------+------+------+
2 rows in set (0.00 sec)

按子查询结果删除数据

可以在 DELETE 语句中嵌套一个或多个子查询,并使用子查询结果作为删除条件。

开始删除操作之前,先在数据库 test 中再创建一张名为 users 的主键类型表:

CREATE TABLE
`users` (`uid` int(11) NOT NULL COMMENT "",
`name` varchar(65533) NOT NULL COMMENT "",
`country` varchar(65533) NULL COMMENT "")
ENGINE=OLAP
PRIMARY KEY(`uid`)COMMENT "OLAP"
DISTRIBUTED BY HASH(`uid`)
PROPERTIES
(
"replication_num" = "3",
"storage_format" = "DEFAULT",
"enable_persistent_index" = "false"
);

users 表中插入数据:

INSERT INTO users VALUES
(0, "Jack", "China"),
(2, "Stan", "USA"),
(1, "Bob", "China"),
(3, "Sam", "USA");
select * from users;
+------+------+---------+
| uid | name | country |
+------+------+---------+
| 0 | Jack | China |
| 1 | Bob | China |
| 2 | Stan | USA |
| 3 | Sam | USA |
+------+------+---------+
4 rows in set (0.00 sec)

查询 users 表中 country 列值为 China 的数据行,然后删除 score_board 表中与 users 表中查询到的数据行具有相同 name 列值的所有数据行。有两种实现方法:

  • 方法 1
DELETE FROM score_board
WHERE name IN (select name from users where country = "China");
select * from score_board;
+------+------+------+
| id | name | score|
+------+------+------+
| 2 | Stan | 21 |
| 3 | Sam | 22 |
+------+------+------+
2 rows in set (0.00 sec)
  • 方法 2
DELETE FROM score_board
WHERE EXISTS (select name from users
where score_board.name = users.name and country = "China");
select * from score_board;
+------+------+-------+
| id | name | score |
+------+------+-------+
| 2 | Stan | 21 |
| 3 | Sam | 22 |
+------+------+-------+
2 rows in set (0.00 sec)

按多表关联或者 CTE 的结果集删除数据

删除制片人 foo 制作的所有电影,则可以执行以下语句:

DELETE FROM films USING producers
WHERE producer_id = producers.id
AND producers.name = 'foo';

您也可以使用 CTE 改写上述语句,增加易读性。

WITH foo_producers as (
SELECT * from producers
where producers.name = 'foo'
)
DELETE FROM films USING foo_producers
WHERE producer_id = foo_producers.id;

相关 SQL

SHOW DELETE:用于查询在指定数据库下,所有在明细表、聚合表、更新表上成功执行的历史删除 (DELETE) 任务。