熟悉 MySQL 租户的同学对 INSERT ... ON DUPLICATE KEY UPDATE 再熟悉不过：不存在则插入，主键 / 唯一键冲突时执行更新。 但在 OceanBase 中， 这条 SQL 的底层实现，随版本发生了变化，默认行为不一定是原地 UPDATE。

4.2.5.6版本之前版本

INSERT ON DUP KEY UPDATE修改普通列，会默认拆分为 delete+insert

4.2.5.7版本

1、准备环境

observer版本：4.2.5.7

MySQL租户

表结构

| Table  | Create Table                                                                                                                                                                                                                                                                                                         |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_test | CREATE TABLE `t_test` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `value` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.07 sec)

2、先插入一条数据，再修改数据

VALUES (2, 'US', 20001);
INSERT INTO t_test(id, name, value)
VALUES (2, 'USA', 20002)
ON DUPLICATE KEY UPDATE name = VALUES(name), value = VALUES(value);

3、解析clog

  
"DmlFlag": 
"DELETE",

                
"ModifyCount": 1,

                
"AccChecksum": 408231420,

                
"Version": 1789702517101371,

                
"Flag": 0,

                
"SeqNo": 
"{branch:0, seq:10915028}",

                
"NewRowSize": 25,

                
"OldRowSize": 29,

                
"ColumnCnt": 3

            },

            
"RowHeader": 
"{mutator_type_str_:\"MUTATOR_ROW\", mutator_type_:0, tablet_id_:{id:200011}}",

            
"NORMAL_ROW": {

                
"RowKey": 
"{\"INT\":2}",

                
"TableVersion": 1789702512408456,

                
"NewRow Cols": {

                    
"0": 
"{len: 8, flag: 0, null: 0, ptr: 0x7ffcfb2fd250, hex: 0200000000000000, int: 2}",

                    
"1": 
"{len: 3, flag: 0, null: 0, ptr: 0x7fb8e0353d91, hex: 555341, cstr: USA}",

                    
"2": 
"{len: 8, flag: 0, null: 0, ptr: 0x7ffcfb2fd2c0, hex: 224E000000000000, int: 20002}"

                },

                
"OldRow Cols": {},

                
"DmlFlag": 
"INSERT",

                
"ModifyCount": 2,

                
"AccChecksum": 1152602979,

                
"Version": 1789702517101371,

                
"Flag": 0,

                
"SeqNo": 
"{branch:0, seq:10915029}",

                
"NewRowSize": 30,

                
"OldRowSize": 0,

                
"ColumnCnt": 3

此时发现，解析出的clog，这个SQL变成了delete+insert

4、修改参数

ALTER SYSTEM SET _enable_insertup_direct_update = 
'true' tenant=
'test_mysql';

5、修改完后再次修改数据并解析clog

INSERT INTO t_test(id, name, value) VALUES (2, 
'NEW', 200001) ON DUPLICATE KEY UPDATE name = VALUES(name), value = VALUES(value);

此时可看到是update语句

4.3.5版本

在该版本通过上述方式操作同理

                "ModifyCount": 3,
                "AccChecksum": 424335685,
                "Version": 1789722744393694,
                "Flag": 0,
                "SeqNo": "{branch:0, seq:18432521}",
                "NewRowSize": 30,
                "OldRowSize": 30,
                "ColumnCnt": 3

最终结论

INSERT ON DUP KEY UPDATE在修改普通列时，在4.2.5.7以前版本暂时没有参数可控制行为，统一为delete+insert

在4.2.5.7,4.3.5版本，打开_enable_insertup_direct_update参数后，变为update