前言

博客：http://blog.healerjean.com

一、`Delete` 操作

删除操作语句通过 MySQL 协议，按条件删除指定表或分区中的数据。

支持通过简单的谓词组合条件来指定要删除的数据

也支持在主键表上使用 USING 子句关联多表进行删除。

1、通过指定过滤谓词删除

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

1）参数

参数	说明	是否必须
`table_name`	指定需要删除数据的表	是
`column_name`	属于 `table_name` 的列	是
`op`	逻辑比较操作符，包括：=, >, <, >=, <=, !=, in, not in	是
`value \|value_list`:	进行逻辑比较的值或值列表	是

`PARTITION`	指定执行删除数据的分区名，如果表不存在此分区，则报错	否
`table_alias`:	表的别名	否

2）使用限制

使用表模型 Aggregate 时，只能指定 Key 列上的条件。当选定的 Key 列不存在于某个 Rollup 中时，无法进行删除。
对于分区表，需要指定分区。如果不指定，Doris 会从条件中推断分区。
- 两种情况下，Doris 无法从条件中推断分区：
  1. 条件中不包含分区列
  2. 分区列的 op 为 not in
- 如果分区表不是 Unique 表，当分区表未指定分区，或无法从条件中推断分区时，需要设置会话变量 delete_without_partition 为 true，此时删除操作会应用到所有分区。

3）使用示例

1. 删除 my_table 分区 p1 中 k1 列值为 3 的数据行

DELETE FROM my_table PARTITION p1 WHERE k1 = 3;

2. 删除 my_table 分区 p1 中 k1 列值大于等于 3 且 status 列值为 “outdated” 的数据行

DELETE FROM my_table PARTITION p1 WHERE k1 >= 3 AND status = "outdated";

3. 删除 my_table 分区 p1, p2 中 k1 列值大于等于 3 且 dt 列值位于 “2024-10-01” 和 “2024-10-31” 之间的数据行

DELETE FROM my_table PARTITIONS (p1, p2) WHERE k1 >= 3 AND dt >= "2024-10-01" AND dt <= "2024-10-31";

2、通过使用 `USING` 子句删除

在某些场景下，用户需要关联多张表才能精确确定要删除的数据，这种情况下 USING 子句非常有用，语法如下：

DELETE FROM table_name [table_alias]
  [PARTITION partition_name | PARTITIONS (partition_name [, partition_name])]
  [USING additional_tables]
  WHERE condition

1）参数

参数	说明	是否必须
`table_name`	指定需要删除数据的表	是
`WHERE condition`	指定用于选择删除行的条件	是

`PARTITION`	指定执行删除数据的分区名，如果表不存在此分区，则报错	否
`table_alias`:	表的别名	否

2）使用限制

此形式只能在 UNIQUE KEY 模型表上使用

3）使用示例

使用 t2 和 t3 表连接的结果，删除 t1 中的数据，删除的表只支持 unique 模型。

-- 创建 t1, t2, t3 三张表
CREATE TABLE t1
  (id INT, c1 BIGINT, c2 STRING, c3 DOUBLE, c4 DATE)
UNIQUE KEY (id)
DISTRIBUTED BY HASH (id)
PROPERTIES('replication_num'='1', "function_column.sequence_col" = "c4");

CREATE TABLE t2
  (id INT, c1 BIGINT, c2 STRING, c3 DOUBLE, c4 DATE)
DISTRIBUTED BY HASH (id)
PROPERTIES('replication_num'='1');

CREATE TABLE t3
  (id INT)
DISTRIBUTED BY HASH (id)
PROPERTIES('replication_num'='1');

-- 插入数据
INSERT INTO t1 VALUES
  (1, 1, '1', 1.0, '2000-01-01'),
  (2, 2, '2', 2.0, '2000-01-02'),
  (3, 3, '3', 3.0, '2000-01-03');

INSERT INTO t2 VALUES
  (1, 10, '10', 10.0, '2000-01-10'),
  (2, 20, '20', 20.0, '2000-01-20'),
  (3, 30, '30', 30.0, '2000-01-30'),
  (4, 4, '4', 4.0, '2000-01-04'),
  (5, 5, '5', 5.0, '2000-01-05');

INSERT INTO t3 VALUES
  (1),
  (4),
  (5);

-- 删除 t1 中的数据
DELETE FROM t1
  USING t2 INNER JOIN t3 ON t2.id = t3.id
  WHERE t1.id = t2.id;

预期结果为，删除 t1 表中 id 为 1 的行。

+----+----+----+--------+------------+
| id | c1 | c2 | c3     | c4         |
+----+----+----+--------+------------+
| 2  | 2  | 2  |    2.0 | 2000-01-02 |
| 3  | 3  | 3  |    3.0 | 2000-01-03 |
+----+----+----+--------+------------+

3、查看历史记录

用户可以通过 SHOW DELETE 语句查看历史上已执行完成的删除记录。语法如下：

SHOW DELETE [FROM db_name]

示例：

mysql> 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)

4、注意事项

1、在主键表（Unique Key）上，删除操作被转换成 INSERT INTO 语句，涉及大范围删除时执行速度较慢，但短时间内大量删除不会对查询性能有较大影响。

2、在明细表（Duplicate Key）和聚合表（Aggregate Key）上，删除操作执行速度较快，但短时间内大量删除操作会影响查询性能。

二、基于导入的批量删除

删除操作可以视为数据更新的一种特殊形式。在主键模型（Unique Key）表上，Doris 支持通过导入数据时添加删除标记来实现删除操作。相比 DELETE 语句，使用删除标记在以下场景中具有更好的易用性和性能优势：

CDC （Change Data Capture，变更数据捕获）场景：在从 OLTP 数据库同步数据到 Doris 时，binlog 中的 Insert 和 Delete 操作通常交替出现。使用 DELETE 语句无法高效处理这些删除操作。通过使用删除标记，可以统一处理 Insert 和 Delete 操作，简化 CDC 写入 Doris 的代码，同时提高数据导入和查询性能。

批量删除指定主键：如果需要删除大量主键，使用 DELETE 语句的效率较低。每次执行 DELETE 都会生成一个空的 rowset 来记录删除条件，并产生一个新的数据版本。频繁删除或删除条件过多时，会严重影响查询性能。

1、删除标记的工作原理

1）原理说明

表结构：删除标记在主键表上存储为一个隐藏列 __DORIS_DELETE_SIGN__，该列值为 1 时表示删除标记生效。
数据导入：用户在导入任务中可以指定删除标记列的映射条件，不同导入任务的用法不同，详见下文语法说明。
查询：在查询时，Doris FE 会在查询规划中自动添加 __DORIS_DELETE_SIGN__ != true 的过滤条件，将删除标记为 1 的数据过滤掉。
数据合并（compaction）：Doris 的后台数据合并会定期清理删除标记为 1 的数据。

2）使用示例

创建一个示例表：

CREATE TABLE example_table (
    id BIGINT NOT NULL,
    value STRING
)
UNIQUE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 10
PROPERTIES (
    "replication_num" = "3"
);

mysql> set show_hidden_columns=true;
-- 使用 session 变量 `show_hidden_columns` 查看隐藏列：
mysql> desc example_table;
+-----------------------+---------+------+-------+---------+-------+
| Field                 | Type    | Null | Key   | Default | Extra |
+-----------------------+---------+------+-------+---------+-------+
| id                    | bigint  | No   | true  | NULL    |       |
| value                 | text    | Yes  | false | NULL    | NONE  |
| __DORIS_DELETE_SIGN__ | tinyint | No   | false | 0       | NONE  |
| __DORIS_VERSION_COL__ | bigint  | No   | false | 0       | NONE  |
+-----------------------+---------+------+-------+---------+-------+

a、数据导入

表中有如下存量数据：

+------+-------+
| id   | value |
+------+-------+
|    1 | foo   |
|    2 | bar   |
+------+-------+

通过 INSERT INTO 写入 id 为 1 的删除标记（此处仅做原理展示，不介绍各种导入使用删除标记的方法）：

mysql> insert into example_table (id, __DORIS_DELETE_SIGN__) values (1, 1);

b、查询

直接查看数据，可以发现 id 为 1 的记录已被删除：

mysql> select * from example_table;
+------+-------+
| id   | value |
+------+-------+
|    2 | bar   |
+------+-------+

-- 使用 session 变量 `show_hidden_columns` 查看隐藏列，可以看到 id 为 1 的行并未被实际删除，其隐藏列 `__DORIS_DELETE_SIGN__` 值为 1，在查询时被过滤掉：

mysql> set show_hidden_columns=true;
mysql> select * from example_table;
+------+-------+-----------------------+-----------------------+
| id   | value | __DORIS_DELETE_SIGN__ | __DORIS_VERSION_COL__ |
+------+-------+-----------------------+-----------------------+
|    1 | NULL  |                     1 |                     3 |
|    2 | bar   |                     0 |                     2 |
+------+-------+-----------------------+-----------------------+

2、语法说明

Doris 在数据导入时支持三种合并策略，用于控制新数据与现有数据的交互方式。这些策略通过 Delete Flag（删除标记） 实现

1）导入合并方式选择

合并方式	作用
`APPEND`	直接追加新数据，不处理旧数据（无论主键是否重复）。适用于新增数据场景。
`DELETE`	删除所有与导入数据 `Key` 列值相同的行。适用于批量删除场景。
`MERGE`	根据导入数据中的 `Delete` `Flag`（删除标记）决定是追加还是删除。适用于增删混合场景。

APPEND（追加模式）
- 新数据直接追加到表中，无论主键是否重复。
- 若主键重复，会保留多条相同主键的数据（需注意 Unique Key 模型会自动去重，仅保留最新版本）。
DELETE（删除模式）
- 导入文件中只需包含 Key 列（主键或唯一键）。
- Doris 会删除表中所有 Key 列值与导入数据匹配 的行。
MERGE（智能合并模式）
- 导入数据中需包含一个 Delete Flag 列（通常为布尔值或枚举值）。
- Delete Flag 为真：删除表中对应 Key 的行；
- Delete Flag 为假：追加新数据（或按 Unique Key 规则更新）。

2）`MERGE` 使用示例

一、场景背景

假设我们有一个用户信息表 user_info，需要定期从业务系统同步增量数据，其中包含新增用户和需要删除的用户。使用 MERGE 模式可同时处理增删操作，避免分两次导入。

二、表结构设计（Unique Key 模型）

CREATE TABLE user_info (
    user_id BIGINT,              -- 主键
    name VARCHAR(32),
    age INT,
    version BIGINT,              -- 版本列（非Key列）
    create_time DATETIME
) ENGINE=OLAP
UNIQUE KEY(user_id)
PROPERTIES (
    "function_column.sequence_col" = "version",
    "function_column.sequence_type" = "AUTO_INCREMENT"
);

三、导入数据准备（含删除标记）

假设导入文件 user_merge_data.csv 内容如下（逗号分隔）：

1,张三,25,true,2025-06-26 10:00:00   -- 删除标记为true（删除user_id=1）
2,李四,30,false,2025-06-26 11:00:00  -- 追加/更新user_id=2
3,王五,28,false,2025-06-26 12:00:00  -- 追加user_id=3

第 1 列：user_id（主键）
第 4 列：delete_flag（删除标记，true表示删除，false表示追加）

四、MERGE 导入语句与执行逻辑

LOAD LABEL merge_label (
    DATA INFILE("hdfs:///path/user_merge_data.csv")
    INTO TABLE user_info
    COLUMNS TERMINATED BY ",",
    user_id, name, age, delete_flag, create_time  -- 列映射
)
WITH MERGE_TYPE MERGE
DELETE ON (delete_flag = "true");  -- 当delete_flag为true时执行删除

执行过程解析：

读取导入数据：解析 CSV 文件，识别每条记录的user_id和delete_flag。
判断操作类型
- user_id=1：delete_flag=true → 执行删除操作，删除表中user_id=1的记录。
- user_id=2：delete_flag=false → 执行追加 / 更新（因 Unique Key 模型存在，若 user_id = 2 已存在则更新，否则插入）。
- user_id=3：delete_flag=false → 追加新记录。
版本控制
- 对于user_id=2，若表中已有记录，Doris 会比较 version列，新版本（自动生成）会覆盖旧版本。

五、执行前后的数据对比

执行前表中数据：

SELECT * FROM user_info;
+---------+------+-----+---------+---------------------+
| user_id | name | age | version | create_time         |
+---------+------+-----+---------+---------------------+
| 1       | 张三 | 25  | 1       | 2025-06-25 09:00:00 |
| 2       | 李四 | 30  | 2       | 2025-06-25 10:00:00 |
+---------+------+-----+---------+---------------------+

执行后表中数据：

SELECT * FROM user_info;
+---------+------+-----+---------+---------------------+
| user_id | name | age | version | create_time         |
+---------+------+-----+---------+---------------------+
| 2       | 李四 | 30  | 3       | 2025-06-26 11:00:00 |  -- 版本号递增，时间更新
| 3       | 王五 | 28  | 1       | 2025-06-26 12:00:00 |  -- 新增记录
+---------+------+-----+---------+---------------------+

三、`truncate`

1、语法

TRUNCATE TABLE [db.]tbl [PARTITION(p1, p2, ...)];

该语句清空数据，但保留表或分区结构。
与 DELETE 不同，TRUNCATE 仅进行元数据操作，速度快且不会影响查询性能。
该操作删除的数据不可恢复。
表状态需为 NORMAL，不能有正在进行的 SCHEMA CHANGE 等操作。
该命令可能会导致正在进行的导入任务失败。

2、示例

1. 清空 example_db 下的表 tbl

TRUNCATE TABLE example_db.tbl;

2. 清空表 tbl 的 p1 和 p2 分区

TRUNCATE TABLE tbl PARTITION(p1, p2);

四、表原子替换

1、适用场景

在某些情况下，用户希望重写表数据，但如果先删除再导入，会有一段时间无法查看数据。此时，用户可以先使用 CREATE TABLE LIKE 语句创建一个相同结构的新表，将新数据导入新表后，通过替换操作原子替换旧表。分区级别的原子覆盖写操作，请参阅

2、语法说明

ALTER TABLE [db.]tbl1 REPLACE WITH TABLE tbl2
[PROPERTIES('swap' = 'true')];

将表 tbl1 替换为表 tbl2。

如果 swap 参数为 true，替换后，tbl1 表中的数据为原 tbl2 表中的数据，tbl2 表中的数据为原 tbl1 表中的数据，即两张表数据互换。
如果 swap 参数为 false，替换后，tbl1 表中的数据为原 tbl2 表中的数据，tbl2 表被删除。

3、原理

替换表功能将以下操作集合变成一个原子操作。

假设将表 A 替换为表 B，且 swap 为 true，操作如下：
1. 将表 B 重命名为表 A。
2. 将表 A 重命名为表 B。
如果 swap 为 false，操作如下：
1. 删除表 A。
2. 将表 B 重命名为表 A。

4、注意事项

如果 swap 参数为 false，被替换的表（表 A）将被删除，且无法恢复。
替换操作仅能发生在两张 OLAP 表之间，不检查两张表的表结构是否一致。
替换操作不会改变原有的权限设置，因为权限检查以表名称为准。

5、使用场景

五、临时分区

1、介绍

Doris 支持在分区表中添加临时分区。临时分区与正式分区不同，临时分区不会被常规查询检索到，只有通过特殊查询语句才能查询。

临时分区的分区列与正式分区相同且不可修改。
所有临时分区之间的分区范围不可重叠，但临时分区与正式分区的范围可以重叠。
临时分区的名称不能与正式分区或其他临时分区重复。

临时分区的主要应用场景：

原子覆盖写操作：用户希望重写某一分区的数据，但不希望在删除旧数据和导入新数据之间有数据缺失。此时，可以创建一个临时分区，将新数据导入临时分区后，通过替换操作原子性地替换原有分区。对于非分区表的原子覆盖写操作，
修改分桶数：用户在创建分区时使用了不合适的分桶数，可以创建一个新的临时分区并指定新的分桶数，然后通过 INSERT INTO 命令将正式分区的数据导入临时分区，再通过替换操作原子性地替换原有分区。
合并或分割分区：用户希望修改分区范围，如合并两个分区或将一个大分区分割成多个小分区。可以先建立新的临时分区，然后通过 INSERT INTO 命令将正式分区的数据导入临时分区，再通过替换操作原子性地替换原有分区。

2、添加临时分区

使用 ALTER TABLE ADD TEMPORARY PARTITION 语句添加临时分区：

ALTER TABLE tbl1 ADD TEMPORARY PARTITION tp1 VALUES LESS THAN("2020-02-01");

ALTER TABLE tbl2 ADD TEMPORARY PARTITION tp1 VALUES [("2020-01-01"), ("2020-02-01"));

ALTER TABLE tbl1 ADD TEMPORARY PARTITION tp1 VALUES LESS THAN("2020-02-01")
("replication_num" = "1")
DISTRIBUTED BY HASH(k1) BUCKETS 5;

ALTER TABLE tbl3 ADD TEMPORARY PARTITION tp1 VALUES IN ("Beijing", "Shanghai");

ALTER TABLE tbl4 ADD TEMPORARY PARTITION tp1 VALUES IN ((1, "Beijing"), (1, "Shanghai"));

ALTER TABLE tbl3 ADD TEMPORARY PARTITION tp1 VALUES IN ("Beijing", "Shanghai")
("replication_num" = "1")
DISTRIBUTED BY HASH(k1) BUCKETS 5;

3、删除临时分区

使用 ALTER TABLE DROP TEMPORARY PARTITION 语句删除临时分区：

ALTER TABLE tbl1 DROP TEMPORARY PARTITION tp1;

4、替换正式分区

使用 ALTER TABLE REPLACE PARTITION 语句将正式分区替换为临时分区：

ALTER TABLE tbl1 REPLACE PARTITION (p1) WITH TEMPORARY PARTITION (tp1);

ALTER TABLE tbl1 REPLACE PARTITION (p1, p2) WITH TEMPORARY PARTITION (tp1, tp2, tp3);

ALTER TABLE tbl1 REPLACE PARTITION (p1, p2) WITH TEMPORARY PARTITION (tp1, tp2)
PROPERTIES (
  "strict_range" = "false",
  "use_temp_partition_name" = "true"
);

1）`strict_range`

Range 分区：
默认为 true，所有被替换的正式分区的范围并集需要与替换的临时分区的范围并集完全相同。
为 false 时，只需保证替换后新的正式分区间的范围不重叠即可。
List 分区，该参数恒为 true。所有被替换的正式分区的枚举值必须与替换的临时分区枚举值完全相同。

2）`use_temp_partition_name`

默认为 false。当该参数为 false，并且待替换的分区和替换分区的个数相同时，替换后的正式分区名称维持不变。
如果为 true，替换后正式分区的名称为替换分区的名称。示例如下：

正确-示例

use_temp_partition_name 默认为 false，则在替换后，分区的名称依然为 p1，但相关的数据和属性都替换为 tp1 的。
use_temp_partition_name 为 true，则在替换后，分区的名称为 tp1，p1 分区不再存在。

ALTER TABLE tbl1 REPLACE PARTITION (p1) WITH TEMPORARY PARTITION (tp1);

错误-示例

use_temp_partition_name 默认为 false，但因待替换分区的个数与替换分区的个数不同，该参数无效。替换后，分区名称为 tp1，p1 和 p2 不再存在。

ALTER TABLE tbl1 REPLACE PARTITION (p1, p2) WITH TEMPORARY PARTITION (tp1);

5、导入临时分区

根据导入方式的不同，指定导入临时分区的语法稍有差别。示例如下：

INSERT INTO tbl TEMPORARY PARTITION(tp1, tp2, ...) SELECT ....
curl --location-trusted -u root: -H "label:123" -H "temporary_partitions: tp1, tp2, ..." -T testData http://host:port/api/testDb/testTbl/_stream_load    
LOAD LABEL example_db.label1
(
DATA INFILE("hdfs://hdfs_host:hdfs_port/user/palo/data/input/file")
INTO TABLE my_table
TEMPORARY PARTITION (tp1, tp2, ...)
...
)
WITH BROKER hdfs ("username"="hdfs_user", "password"="hdfs_password");
CREATE ROUTINE LOAD example_db.test1 ON example_tbl
COLUMNS(k1, k2, k3, v1, v2, v3 = k1 * 100),
TEMPORARY PARTITIONS(tp1, tp2, ...),
WHERE k1 > 100
PROPERTIES
(...)
FROM KAFKA
(...);

6、查询临时分区

SELECT ... FROM
tbl1 TEMPORARY PARTITION(tp1, tp2, ...)
JOIN
tbl2 TEMPORARY PARTITION(tp1, tp2, ...)
ON ...
WHERE ...;

7、与其他操作的关系

DROP

使用 Drop 操作直接删除数据库或表后，可以通过 Recover 命令恢复数据库或表（限定时间内），但临时分区不会被恢复。
使用 Alter 命令删除正式分区后，可以通过 Recover 命令恢复分区（限定时间内）。操作正式分区和临时分区无关。
使用 Alter 命令删除临时分区后，无法通过 Recover 命令恢复临时分区。

TRUNCATE

使用 Truncate 命令清空表，表的临时分区会被删除，且不可恢复。
使用 Truncate 命令清空正式分区时，不影响临时分区。
不可使用 Truncate 命令清空临时分区。

ALTER

当表存在临时分区时，无法使用 Alter 命令对表进行 Schema Change、Rollup 等变更操作。
当表在进行变更操作时，无法对表添加临时分区。

ContactAuthor

一、Delete 操作

1、通过指定过滤谓词删除

1）参数

2）使用限制

3）使用示例

2、通过使用 USING 子句删除

1）参数

2）使用限制

3）使用示例

3、查看历史记录

4、注意事项

二、基于导入的批量删除

1、删除标记的工作原理

1）原理说明

2）使用示例

a、数据导入

b、查询

2、语法说明

1）导入合并方式选择

2）MERGE 使用示例

三、truncate

1、语法

2、示例

四、表原子替换

1、适用场景

2、语法说明

3、原理

4、注意事项

5、使用场景

五、临时分区

1、介绍

2、添加临时分区

3、删除临时分区

4、替换正式分区

1）strict_range

2）use_temp_partition_name

5、导入临时分区

6、查询临时分区

7、与其他操作的关系

一、`Delete` 操作

2、通过使用 `USING` 子句删除

2）`MERGE` 使用示例

三、`truncate`

1）`strict_range`

2）`use_temp_partition_name`