DATA BRANCH MERGE

语法说明

DATA BRANCH MERGE 把源表的插入、删除和更新变更应用到兼容的目标表。两张表既可以是有关联的分支，也可以是没有公共祖先的独立表。

存在 lineage 时，系统会识别两个表之间的最近公共祖先（LCA），并基于它计算变更；LCA 解析和变更范围计算支持任意 DAG 深度，不仅限于直接父子关系。没有 LCA 时，MatrixOne 直接比较兼容的表镜像。同一主键出现分歧变更时，按冲突处理选项执行。

语法结构

DATA BRANCH MERGE source_table [{ SNAPSHOT = 'snapshot_name' }] 
    INTO destination_table [{ SNAPSHOT = 'snapshot_name' }] 
    [WHEN CONFLICT conflict_option]

冲突处理选项

conflict_option:
    FAIL                            -- 遇到冲突时报错并终止（默认行为）
  | SKIP                            -- 跳过冲突的行，保留目标表的数据
  | ACCEPT                          -- 接受源表的数据，覆盖目标表的冲突数据

语法释义

参数说明

参数

说明

source_table

源表（要合并的数据来源）

destination_table

目标表（接收合并数据的表）

源端 SNAPSHOT = 'snapshot_name'

读取指定快照时刻的源端镜像。

目标端 SNAPSHOT = 'snapshot_name'

把目标快照作为历史比较端点。合并结果仍写入当前 live 目标表，并不会修改快照；因此可能按照所选冲突策略覆盖该快照之后发生在 live 目标表上的修改。

WHEN CONFLICT FAIL

遇到冲突时报错并终止操作（默认）

WHEN CONFLICT SKIP

跳过冲突的行，保留目标表原有数据

WHEN CONFLICT ACCEPT

接受源表的数据，覆盖目标表的冲突数据

冲突定义

同一主键在源端和目标端相对比较基线发生不一致变更时会产生冲突，包括：

源端变更

目标端变更

冲突条件

UPDATE

UPDATE

两侧最终行镜像不同。

UPDATE

DELETE

一侧修改该行，另一侧删除该行。

DELETE

UPDATE

一侧删除该行，另一侧修改该行。

INSERT

INSERT

两侧以不同值插入相同主键。

最终行镜像相同以及可兼容的单侧变更不属于冲突。FAIL 会中止且不应用本次合并；SKIP 保留冲突键的目标端结果，同时应用非冲突变更；ACCEPT 应用源端结果，包括源端删除。

使用说明

合并流程

  1. 系统首先计算源表和目标表之间的差异

  2. 检测是否存在冲突

  3. 根据冲突处理选项处理冲突

  4. 将非冲突的变更应用到目标表

合并操作

  • INSERT：将源表中新增的行插入到目标表

  • DELETE：从目标表中删除源表已删除的行

  • UPDATE：更新目标表中与源表不同的行

事务与目标快照

  • DATA BRANCH MERGE 支持显式 BEGIN / COMMIT / ROLLBACK。但在 autocommit=0 时必须先显式执行 BEGIN；处于隐式事务时会以 DATA BRANCH MERGE/PICK is not supported in transactions 拒绝。

  • 目标端快照只是比较端点，不是写入目标。成功合并始终落到 live 目标表；合并前应检查快照之后发生的 live 修改。

Schema evolution 兼容性

DATA BRANCH MERGE 在保留列身份的前提下支持兼容的 schema evolution。DATA BRANCH DIFF 也使用相同的兼容性规则：

Schema 变更

行为

列身份和属性不变、仅调整列顺序

支持

MatrixOne 可以证明血缘关系的列重命名

支持

具有显式主键且定义兼容的源表独有可见列

支持；该列会出现在 DIFF 输出中，但不会写入缺少它的目标表

源表中的列在目标表 schema 中缺失

拒绝

同名列先 DROP 再 ADD（产生新身份）

拒绝

类型、可空性、生成列定义或主键不兼容

拒绝

使用隐藏 fake primary key 的表存在源表独有列

拒绝

Schema 校验在应用变更前执行；兼容性校验失败时目标表保持不变。

示例

示例 1：简单合并（无冲突）

-- Expected-Rows: 0
CREATE DATABASE test;
-- Expected-Rows: 0
USE test;

-- 创建基础表
-- Expected-Rows: 0
CREATE TABLE test.t0 (a INT PRIMARY KEY, b INT);
-- Expected-Rows: 0
INSERT INTO test.t0 VALUES (1, 1), (2, 2), (3, 3);

-- 创建两个分支
-- Expected-Rows: 0
DATA BRANCH CREATE TABLE test.t1 FROM test.t0;
-- Expected-Rows: 0
INSERT INTO test.t1 VALUES (4, 4);

-- Expected-Rows: 0
DATA BRANCH CREATE TABLE test.t2 FROM test.t0;
-- Expected-Rows: 0
INSERT INTO test.t2 VALUES (5, 5);

-- 查看差异
-- Expected-Rows: 2
DATA BRANCH DIFF test.t2 AGAINST test.t1;
+--------------------+--------+------+------+
| diff t2 against t1 | flag   | a    | b    |
+--------------------+--------+------+------+
| t1                 | INSERT |    4 |    4 |
| t2                 | INSERT |    5 |    5 |
+--------------------+--------+------+------+

-- 合并 t2 到 t1
-- Expected-Rows: 0
DATA BRANCH MERGE test.t2 INTO test.t1;

-- 验证合并结果
-- Expected-Rows: 5
SELECT * FROM test.t1 ORDER BY a;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
|    4 |    4 |
|    5 |    5 |
+------+------+

-- Expected-Rows: 0
DROP TABLE test.t0;
-- Expected-Rows: 0
DROP TABLE test.t1;
-- Expected-Rows: 0
DROP TABLE test.t2;

示例 2：处理 INSERT 冲突

-- 创建基础表
-- Expected-Rows: 0
CREATE TABLE test.t0 (a INT PRIMARY KEY, b INT);
-- Expected-Rows: 0
INSERT INTO test.t0 VALUES (1, 1), (2, 2);

-- 创建两个分支，都插入相同主键但不同值的行
-- Expected-Rows: 0
DATA BRANCH CREATE TABLE test.t1 FROM test.t0;
-- Expected-Rows: 0
INSERT INTO test.t1 VALUES (3, 3);

-- Expected-Rows: 0
DATA BRANCH CREATE TABLE test.t2 FROM test.t0;
-- Expected-Rows: 0
INSERT INTO test.t2 VALUES (3, 4);

-- 查看差异
-- Expected-Rows: 2
DATA BRANCH DIFF test.t2 AGAINST test.t1;
+-------------------+--------+------+------+
| diff t2 against t1 | flag   | a    | b    |
+-------------------+--------+------+------+
| t2                | INSERT |    3 |    4 |
| t1                | INSERT |    3 |    3 |
+-------------------+--------+------+------+

-- 默认行为：遇到冲突报错
-- Expected-Success: false
DATA BRANCH MERGE test.t2 INTO test.t1;
-- ERROR: conflict: t2 INSERT and t1 INSERT on pk(3) with different values

-- 使用 SKIP：跳过冲突，保留 t1 的数据
-- Expected-Rows: 0
DATA BRANCH MERGE test.t2 INTO test.t1 WHEN CONFLICT SKIP;
-- Expected-Rows: 3
SELECT * FROM test.t1 ORDER BY a;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+

-- 使用 ACCEPT：接受 t2 的数据
-- Expected-Rows: 0
DATA BRANCH MERGE test.t2 INTO test.t1 WHEN CONFLICT ACCEPT;
-- Expected-Rows: 3
SELECT * FROM test.t1 ORDER BY a;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    4 |
+------+------+

-- Expected-Rows: 0
DROP TABLE test.t0;
-- Expected-Rows: 0
DROP TABLE test.t1;
-- Expected-Rows: 0
DROP TABLE test.t2;

示例 3：处理 UPDATE 冲突

-- 创建基础表
-- Expected-Rows: 0
CREATE TABLE test.t1 (a INT PRIMARY KEY, b INT);
-- Expected-Rows: 0
INSERT INTO test.t1 VALUES (1, 1), (2, 2);

-- 创建分支并进行不同的更新
-- Expected-Rows: 0
DATA BRANCH CREATE TABLE test.t2 FROM test.t1;
-- Expected-Rows: 1
UPDATE test.t2 SET b = b + 2 WHERE a = 1;
-- Expected-Rows: 1
UPDATE test.t1 SET b = b + 1 WHERE a = 1;

-- 查看差异
-- Expected-Rows: 2
DATA BRANCH DIFF test.t2 AGAINST test.t1;
+--------------------+--------+------+------+
| diff t2 against t1 | flag   | a    | b    |
+--------------------+--------+------+------+
| t2                 | UPDATE |    1 |    3 |
| t1                 | UPDATE |    1 |    2 |
+--------------------+--------+------+------+

-- 使用 SKIP：保留 t1 的更新
-- Expected-Rows: 0
DATA BRANCH MERGE test.t2 INTO test.t1 WHEN CONFLICT SKIP;
-- Expected-Rows: 2
SELECT * FROM test.t1 ORDER BY a;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    2 |    2 |
+------+------+

-- 使用 ACCEPT：接受 t2 的更新
-- Expected-Rows: 0
DATA BRANCH MERGE test.t2 INTO test.t1 WHEN CONFLICT ACCEPT;
-- Expected-Rows: 2
SELECT * FROM test.t1 ORDER BY a;
+------+------+
| a    | b    |
+------+------+
|    1 |    3 |
|    2 |    2 |
+------+------+

-- Expected-Rows: 0
DROP TABLE test.t1;
-- Expected-Rows: 0
DROP TABLE test.t2;

示例 4：无共同祖先的合并

-- 创建两个独立的表
-- Expected-Rows: 0
CREATE TABLE test.t1 (a INT PRIMARY KEY, b INT);
-- Expected-Rows: 0
INSERT INTO test.t1 VALUES (1, 1), (2, 2);

-- Expected-Rows: 0
CREATE TABLE test.t2 (a INT PRIMARY KEY, b INT);
-- Expected-Rows: 0
INSERT INTO test.t2 VALUES (1, 2), (3, 3);

-- 查看差异
-- Expected-Rows: 4
DATA BRANCH DIFF test.t2 AGAINST test.t1;
+--------------------+--------+------+------+
| diff t2 against t1 | flag   | a    | b    |
+--------------------+--------+------+------+
| t2                 | INSERT |    1 |    2 |
| t1                 | INSERT |    1 |    1 |
| t1                 | INSERT |    2 |    2 |
| t2                 | INSERT |    3 |    3 |
+--------------------+--------+------+------+

-- 使用 SKIP 合并
-- Expected-Rows: 0
DATA BRANCH MERGE test.t2 INTO test.t1 WHEN CONFLICT SKIP;
-- Expected-Rows: 3
SELECT * FROM test.t1 ORDER BY a;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+

-- 使用 ACCEPT 合并
-- Expected-Rows: 0
DATA BRANCH MERGE test.t2 INTO test.t1 WHEN CONFLICT ACCEPT;
-- Expected-Rows: 3
SELECT * FROM test.t1 ORDER BY a;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

-- Expected-Rows: 0
DROP TABLE test.t1;
-- Expected-Rows: 0
DROP TABLE test.t2;

示例 5：复杂合并场景

-- 创建基础表
-- Expected-Rows: 0
CREATE TABLE test.t0 (a INT PRIMARY KEY, b VARCHAR(10));
-- Expected-Rows: 0
INSERT INTO test.t0 SELECT result, 't0' FROM generate_series(1, 100) g;

-- 创建多个分支并进行不同的修改
-- Expected-Rows: 0
DATA BRANCH CREATE TABLE test.t1 FROM test.t0;
-- Expected-Rows: 6
UPDATE test.t1 SET b = 't1' WHERE a IN (1, 20, 40, 60, 80, 100);

-- Expected-Rows: 0
DATA BRANCH CREATE TABLE test.t2 FROM test.t0;
-- Expected-Rows: 5
UPDATE test.t2 SET b = 't2' WHERE a IN (2, 22, 42, 62, 82);

-- Expected-Rows: 0
DATA BRANCH CREATE TABLE test.t3 FROM test.t0;
-- Expected-Rows: 5
UPDATE test.t3 SET b = 't3' WHERE a IN (3, 23, 43, 63, 83);

-- 依次合并到 t0
-- Expected-Rows: 0
DATA BRANCH MERGE test.t1 INTO test.t0;
-- Expected-Rows: 2
SELECT COUNT(*) AS cnt, b FROM test.t0 GROUP BY b ORDER BY cnt;
+-----+------+
| cnt | b    |
+-----+------+
|   6 | t1   |
|  94 | t0   |
+-----+------+

-- Expected-Rows: 0
DATA BRANCH MERGE test.t2 INTO test.t0;
-- Expected-Rows: 3
SELECT COUNT(*) AS cnt, b FROM test.t0 GROUP BY b ORDER BY cnt;
+-----+------+
| cnt | b    |
+-----+------+
|   5 | t2   |
|   6 | t1   |
|  89 | t0   |
+-----+------+

-- Expected-Rows: 0
DATA BRANCH MERGE test.t3 INTO test.t0;
-- Expected-Rows: 4
SELECT COUNT(*) AS cnt, b FROM test.t0 GROUP BY b ORDER BY cnt;
+-----+------+
| cnt | b    |
+-----+------+
|   5 | t2   |
|   5 | t3   |
|   6 | t1   |
|  84 | t0   |
+-----+------+

-- Expected-Rows: 0
DROP TABLE test.t0;
-- Expected-Rows: 0
DROP TABLE test.t1;
-- Expected-Rows: 0
DROP TABLE test.t2;
-- Expected-Rows: 0
DROP TABLE test.t3;

示例 6：处理 NULL 值的合并

-- 创建包含 NULL 值的表
-- Expected-Rows: 0
CREATE TABLE test.payout_template (
    batch_id INT PRIMARY KEY,
    region VARCHAR(8),
    amount DECIMAL(12,2),
    reviewer VARCHAR(20)
);

-- Expected-Rows: 0
INSERT INTO test.payout_template VALUES
    (10, 'east', 1200.50, 'amy'),
    (20, 'west', NULL, NULL),
    (30, NULL, 4800.00, 'leo');

-- 创建两个分支
-- Expected-Rows: 0
DATA BRANCH CREATE TABLE test.payout_stage FROM test.payout_template;
-- Expected-Rows: 0
DATA BRANCH CREATE TABLE test.payout_ops FROM test.payout_template;

-- 在 stage 分支上修改
-- Expected-Rows: 1
UPDATE test.payout_stage SET amount = NULL, reviewer = NULL WHERE batch_id = 10;
-- Expected-Rows: 1
UPDATE test.payout_stage SET reviewer = 'nina' WHERE batch_id = 20;

-- 在 ops 分支上修改
-- Expected-Rows: 1
UPDATE test.payout_ops SET amount = 1250.75 WHERE batch_id = 10;
-- Expected-Rows: 1
UPDATE test.payout_ops SET amount = NULL WHERE batch_id = 30;

-- 查看差异
-- Expected-Rows: 6
DATA BRANCH DIFF test.payout_stage AGAINST test.payout_ops;

-- 使用 SKIP 合并
-- Expected-Rows: 0
DATA BRANCH MERGE test.payout_stage INTO test.payout_ops WHEN CONFLICT SKIP;
-- Expected-Rows: 3
SELECT batch_id, region, amount, reviewer FROM test.payout_ops ORDER BY batch_id;

-- 使用 ACCEPT 合并
-- Expected-Rows: 0
DATA BRANCH MERGE test.payout_stage INTO test.payout_ops WHEN CONFLICT ACCEPT;
-- Expected-Rows: 3
SELECT batch_id, region, amount, reviewer FROM test.payout_ops ORDER BY batch_id;

-- Expected-Rows: 0
DROP TABLE test.payout_template;
-- Expected-Rows: 0
DROP TABLE test.payout_stage;
-- Expected-Rows: 0
DROP TABLE test.payout_ops;
-- Expected-Rows: 0
DROP DATABASE test;

示例 7：链式合并（线性分支链）

DATA BRANCH MERGE 支持跨线性分支链（如 t0 -> t1 -> t2）的合并。支持两种模式：

  1. 级联合并：将 t2 合并到 t1，再将 t1 合并到 t0 – 每一步将一层变更沿链向上应用。

  2. 直接孙节点合并：将 t2 直接合并到 t0，跳过中间节点 t1。LCA 探测通过遍历 t1 的克隆边来确定 t0 为 LCA。

冲突模式（WHEN CONFLICT SKIP / ACCEPT）在多个链深度下工作方式相同。

DROP DATABASE IF EXISTS test_chain_merge;
CREATE DATABASE test_chain_merge;
USE test_chain_merge;

-- 场景：沿链级联合并
CREATE TABLE t0(a INT PRIMARY KEY, b INT);
INSERT INTO t0 VALUES (1, 1), (2, 2), (3, 3);

DATA BRANCH CREATE TABLE t1 FROM t0;
DATA BRANCH CREATE TABLE t2 FROM t1;

INSERT INTO t2 VALUES (4, 4), (5, 5);
UPDATE t2 SET b = b + 100 WHERE a = 1;
DELETE FROM t2 WHERE a = 2;

-- 合并 t2 -> t1
DATA BRANCH MERGE t2 INTO t1;
SELECT * FROM t1 ORDER BY a;

-- 级联 t1 -> t0
DATA BRANCH MERGE t1 INTO t0;
SELECT * FROM t0 ORDER BY a;

-- 完整级联后差异为空
DATA BRANCH DIFF t2 AGAINST t0 OUTPUT SUMMARY;

DROP TABLE t2;
DROP TABLE t1;
DROP TABLE t0;

-- 场景：直接孙节点 -> 祖节点合并（t2 合并到 t0，跳过 t1）
CREATE TABLE t0(a INT PRIMARY KEY, b INT);
INSERT INTO t0 VALUES (1, 1), (2, 2), (3, 3);

DATA BRANCH CREATE TABLE t1 FROM t0;
DATA BRANCH CREATE TABLE t2 FROM t1;

INSERT INTO t2 VALUES (10, 10), (11, 11);
UPDATE t2 SET b = b + 1000 WHERE a = 1;

DATA BRANCH MERGE t2 INTO t0;
SELECT * FROM t0 ORDER BY a;

-- t1 未被此次合并影响
DATA BRANCH DIFF t2 AGAINST t0 OUTPUT SUMMARY;
DATA BRANCH DIFF t2 AGAINST t1 OUTPUT SUMMARY;

DROP TABLE t2;
DROP TABLE t1;
DROP TABLE t0;
DROP DATABASE test_chain_merge;

示例 8：链式合并的冲突模式

当链的多个层级（t0 和 t2 都修改了同一行）上有 DML 操作时，将孙节点合并到祖节点可能会产生冲突。WHEN CONFLICT 子句解决冲突：

  • SKIP 保留目标（t0）的值。

  • ACCEPT 用源（t2）的值覆盖。

DROP DATABASE IF EXISTS test_chain_conflict;
CREATE DATABASE test_chain_conflict;
USE test_chain_conflict;

CREATE TABLE t0(a INT PRIMARY KEY, b INT, tag VARCHAR(8));
INSERT INTO t0 VALUES (1, 1, 'orig'), (2, 2, 'orig');

DATA BRANCH CREATE TABLE t1 FROM t0;
DATA BRANCH CREATE TABLE t2 FROM t1;

-- 两端同时分叉
UPDATE t0 SET b = 111, tag = 't0-won' WHERE a = 1;
UPDATE t2 SET b = 222, tag = 't2-won' WHERE a = 1;
INSERT INTO t0 VALUES (10, 10, 't0-ins');
INSERT INTO t2 VALUES (20, 20, 't2-ins');

-- SKIP 在冲突时保留 t0 的值
DATA BRANCH MERGE t2 INTO t0 WHEN CONFLICT SKIP;
SELECT * FROM t0 ORDER BY a;

-- ACCEPT 用 t2 的值覆盖
DATA BRANCH MERGE t2 INTO t0 WHEN CONFLICT ACCEPT;
SELECT * FROM t0 ORDER BY a;

DROP TABLE t2;
DROP TABLE t1;
DROP TABLE t0;
DROP DATABASE test_chain_conflict;

注意事项

  1. Schema 兼容性：遵循上面的 schema evolution 兼容矩阵。现已支持包含 BINARY、VARBINARY 和 BLOB 列的表；二进制值在合并过程中通过十六进制字面量编码精确保留。

  2. 主键要求：建议使用带主键的表进行合并操作，以便准确识别和处理冲突。

  3. 冲突处理：

    • FAIL（默认）：最安全的选项，确保不会意外覆盖数据

    • SKIP：保守策略，保留目标表的现有数据

    • ACCEPT：激进策略，优先使用源表的数据

  4. 事务性：合并操作是原子的，要么全部成功，要么全部失败。

  5. 性能考虑：对于大表，合并操作可能需要较长时间。建议先使用 DATA BRANCH DIFF 了解变更规模。

  6. 数据备份：在执行合并操作前，建议创建快照或备份，以便在需要时回滚。

  7. LCA 影响：系统会自动检测 LCA，LCA 的存在会影响冲突的判定方式。