[数据库] 数据库(Paimon/IotDB/MySQL8/PostgreSQL) 的 Upsert 语法支持情况

0 序

1 Upsert 语法支持情况

速览对比

系统	支持 upsert 语义	实现方式	标准 UPSERT / MERGE 语句
MySQL 8	✅ 支持	INSERT ... ON DUPLICATE KEY UPDATE（8.0.20 起推荐行别名替代 VALUES()，VALUES() 已废弃）；另有 REPLACE INTO（先删后插，非原子更新语义）	❌ 无 UPSERT 关键字，也无 MERGE 语句
Apache IoTDB	⚠️ 仅"隐式/受限"	① 树模型：重复时间戳重插即覆盖（等效 update，但 NULL 不覆盖）② 表模型：UPDATE ... SET 仅限更新 ATTRIBUTE 列，FIELD/TIME 列不可更新	❌ 无数据级标准 UPSERT；UPSERT ALIAS/TAGS/ATTRIBUTES 只是更新时间序列元数据，不是数据 upsert
Apache Paimon	✅ 支持（最完整）	① 主键表流式 upsert（同主键自动合并），merge-engine 可选 deduplicate / partial-update / aggregation / first-row ② 批模式支持 MERGE INTO 与 UPDATE 语句	✅ 支持标准 MERGE INTO

要点说明

• MYSQL 8 / Paimon / IotDB 三者都"支持 upsert 语义"，但实现方式和形态完全不同，关键差异在于"是否有标准的 UPSERT/MERGE 语句"：
• MySQL 8：语义上就是标准 upsert（有则更新、无则插入），触发条件是唯一键/主键冲突。

但 MySQL 一直没有标准 MERGE 语句（这是 Oracle/PostgreSQL/SQL Server 的方言），所以跨库迁移到 MySQL 时 MERGE INTO 要改写为 ON DUPLICATE KEY UPDATE。

• IoTDB 关键坑（你实践的是 2.0.11）：时序数据本身没有真正的更新/upsert 语句，靠"【同时间戳】覆盖写"实现。表模型虽然新增了 UPDATE ... SET ... WHERE，但官方明确限制只允许更新 ATTRIBUTE 列、WHERE 只能引用 TAG/ATTRIBUTE 列——数据（FIELD）和 TIME 列都不在可更新范围。

所以如果业务要按主键更新时序测点值，本质仍是"重新插入同时间戳的数据覆盖"。

• Paimon 最接近"开箱即用的 upsert"：主键表 + CDC 就是天然 upsert 链路，批处理还能用标准 MERGE INTO/UPDATE，这跟大数据做流批一体的场景匹配度最高。

总结：要做标准的、按主键的 upsert，选 Paimon（甚至 MySQL 也行）；IoTDB 的"upsert"只针对时序点，靠时间戳覆盖，不能当关系型 UPDATE 用。

2 综合示例

MySQL 8

DDL

-- drop table device_status;
CREATE TABLE device_status (
    device_id          VARCHAR(64) PRIMARY KEY      COMMENT '设备ID',
    latest_time        BIGINT                       COMMENT '最新更新时间',
    latest_status      VARCHAR(16)                  COMMENT '最新运行状态',
    latest_longitude   DECIMAL(24,14)               COMMENT '最新经度（东西方向；0-180°）', -- 总位数=24，小数位=14
    latest_latitude    DECIMAL(24,14)               COMMENT '最新纬度（南北方向；0-90°）',  -- 总位数=24，小数位=14
    remark             VARCHAR(128)                 COMMENT '备注说明', 
    create_time        TIMESTAMP   DEFAULT NOW()    COMMENT '创建时间',
    create_by          VARCHAR(32) DEFAULT 'system' COMMENT '创建人员'
);

DML

select * from device_status

-- upsert：撞主键 → 更新 latest_time、latest_status、latest_longitude、latest_latitude
INSERT INTO device_status (device_id, latest_time, latest_status, latest_longitude, latest_latitude)
VALUES 
('DEV001', 1720000000000, 'ONLINE', 116.31061245141704, 39.90784792902948) AS NEW
ON DUPLICATE KEY UPDATE latest_time = NEW.latest_time, latest_status = NEW.latest_status, latest_longitude = NEW.latest_longitude , latest_latitude = NEW.latest_latitude;

select * from device_status

INSERT INTO device_status (device_id, latest_time, latest_status, latest_longitude, latest_latitude)
VALUES 
('DEV001', 1720000005000, 'ONLINE', 116.39798125513646, 39.907711431007414) AS NEW
ON DUPLICATE KEY UPDATE latest_time = NEW.latest_time, latest_status = NEW.latest_status, latest_longitude = NEW.latest_longitude , latest_latitude = NEW.latest_latitude;

select * from device_status;

-- 等价 REPLACE（先删后插，触发 DELETE）
REPLACE INTO device_status (device_id, latest_time, latest_status)
VALUES ('DEV001', 1720000008000, 'OFFLINE');

PostgreSQL

• PostgreSQL 9.5 起正式支持 upsert，语法为 INSERT ... ON CONFLICT，无需第三方扩展（如 MySQL 的 ON DUPLICATE KEY UPDATE 或 SQLite 的 INSERT OR REPLACE）。

DDL

-- 1. 基础表：主键作为冲突目标
CREATE TABLE IF NOT EXISTS users (
    id        BIGSERIAL PRIMARY KEY,           -- 主键 -> 唯一约束，可作 ON CONFLICT 目标
    name      TEXT        NOT NULL,
    email     TEXT        NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now()
);

-- 给 email 加唯一约束（常见业务：冲突按邮箱判断）
CREATE UNIQUE INDEX IF NOT EXISTS uq_users_email ON users (email);

DML

1. 冲突时更新（核心用法）

INSERT INTO users (id, name, email)
VALUES (1, 'Alice', 'alice@example.com')
ON CONFLICT (id)
DO UPDATE SET name  = EXCLUDED.name, email = EXCLUDED.email;


-- 插入，邮箱冲突则更新
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com')
ON CONFLICT (email)                     -- 冲突目标：唯一索引列
DO UPDATE SET
    name       = EXCLUDED.name,
    updated_at = now();

2. 冲突时忽略

INSERT INTO users (id, name)
VALUES (1, 'Alice')
ON CONFLICT (id) DO NOTHING;

关键点

要点	说明
冲突目标	ON CONFLICT (id) 括号内是唯一约束/主键列；也可用 ON CONFLICT ON CONSTRAINT users_pkey 指定约束名
EXCLUDED	代表本次尝试插入的行，用于 DO UPDATE 中引用新值（如上例）
多列唯一	ON CONFLICT (a, b) 支持复合唯一键
条件更新	可加 WHERE，如 DO UPDATE SET ... WHERE users.updated_at < EXCLUDED.updated_at
无冲突目标	ON CONFLICT DO NOTHING 可不指定列，匹配任意唯一冲突
RETURNING	可与 upsert 连用，返回实际生效的行

注意事项：

• ON CONFLICT (列) 必须在这些列上存在唯一索引/约束，否则报错 there is no unique or exclusion constraint matching the ON CONFLICT specification。
• 批量 upsert 可用 INSERT ... VALUES (...), (...), ... ON CONFLICT ... 或 INSERT ... SELECT ... ON CONFLICT ...。
• 高并发写入下，DO UPDATE 遇到同一行同时冲突可能抛 deadlock detected，需注意事务顺序或重试。

Apache Paimon

DDL

-- Flink SQL：主键只留 device_id，同设备自动覆盖
CREATE TABLE paimon_db.device_status (
    device_id STRING,
    ts        BIGINT,
    status    STRING,
    PRIMARY KEY (device_id) NOT ENFORCED
) WITH (
    'connector'    = 'paimon',
    'path'         = 'oss://your-bucket/device_status',
    'merge-engine' = 'deduplicate'
);

DML

INSERT INTO paimon_db.device_status SELECT device_id, ts, status FROM kafka_device_source;  -- 同 device_id 覆盖

-- Spark 批模式：ON 条件只比 device_id
MERGE INTO paimon_db.device_status AS t
USING (SELECT 'DEV001' AS device_id, 1720000000000 AS ts, 'ONLINE' AS status) AS s
ON t.device_id = s.device_id
WHEN MATCHED THEN UPDATE SET ts = s.ts, status = s.status
WHEN NOT MATCHED THEN INSERT (device_id, ts, status)
                    VALUES (s.device_id, s.ts, s.status);

Apache IoTDB —— 状态表语义不适用，需明确取舍

IoTDB 是追加式时序库，"一设备一行最新状态" 不是它的设计场景。硬做有两种做法，各有代价：

-- 做法A：固定时间戳反复覆盖（相当于每设备一个"状态点"）
INSERT INTO root.device.DEV001(status) VALUES ('ONLINE');      -- 用系统当前时间戳 = 追加历史，不是一行
INSERT INTO root.device.DEV001(0, status) VALUES ('ONLINE');   -- 用固定时间戳0反复覆盖 = 勉强接近状态表

做法	写法	代价
A 固定时间戳(如 0) 反复覆盖	固定 ts 重插	接近"一设备一行"，但丢历史、与时序定位矛盾
B 系统时间戳	正常插入	追加历史，不是状态表语义

建议：IoTDB 只承担时序/历史数据；"每设备最新状态"这种读频繁覆盖的键值场景，交给 MySQL 或 Paimon（或 Redis）更合适。如果你的链路里 IoTDB 必须出最新状态，就用固定时间戳覆盖方案，别把它当状态表用。

Y 推荐文献

• 获取经纬度

• https://jingweidu.bmcx.com/

• 获取时间

• https://tool.lu/timestamp/

X 参考文献