replace and insert_or_update are two operations that can be easily confused. In many cases, they may appear to have the same behavior externally, but their underlying semantics differ. This topic will provide a detailed explanation of the differences between the two.
replace represents an insertion operation. When conflicts occur, it deletes all rows causing conflicts and then performs the insertion.
insert_or_update also represents an insertion operation. However, when conflicts occur, it performs an update operation instead.
Let's illustrate the differences between the two with examples.
insert_or_update
The insert_or_update operation provided by OBKV-Table is equivalent to a special SQL syntax. The following example shows how insert_or_update works:
OceanBase (root@test)> desc test_replace;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | int(11) | YES | | NULL | |
| c3 | int(11) | YES | UNI | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.03 sec)
OceanBase (root@test)> select * from test_replace;
+----+------+------+
| c1 | c2 | c3 |
+----+------+------+
| 1 | 2 | 3 |
| 4 | 5 | 6 |
+----+------+------+
2 rows in set (0.03 sec)
OceanBase (root@test)> insert into test_replace (c1, c2, c3) values (4, 7, 8), (5, 8, 9) on duplicate key update c1=values(c1), c2=values(c2), c3=values(c3);
Query OK, 3 rows affected (0.03 sec)
Records: 2 Duplicates: 1 Warnings: 0
OceanBase (root@test)> select * from test_replace;
+----+------+------+
| c1 | c2 | c3 |
+----+------+------+
| 1 | 2 | 3 |
| 4 | 7 | 8 |
| 5 | 8 | 9 |
+----+------+------+
3 rows in set (0.03 sec)
replace
OceanBase (root@test)> select * from test_replace;
+----+------+------+
| c1 | c2 | c3 |
+----+------+------+
| 1 | 2 | 3 |
| 4 | 7 | 8 |
| 5 | 8 | 9 |
+----+------+------+
3 rows in set (0.03 sec)
OceanBase (root@test)> replace into test_replace (c1, c3) values (4, 9);
Query OK, 3 rows affected (0.04 sec)
OceanBase (root@test)> select * from test_replace;
+----+------+------+
| c1 | c2 | c3 |
+----+------+------+
| 1 | 2 | 3 |
| 4 | NULL | 9 |
+----+------+------+
2 rows in set (0.02 sec)
Based on the preceding examples, unlike insert_or_update, the replace operation deletes rows and changes the value of c2 in the row where c1 is 4 to the default value NULL because of the insertion of a new value. The insert_or_update operation does not perform these two special actions. In most cases, you need to use the insert_or_update operation, which is similar to the put method in NoSQL mode.
The two operations achieve the same results in the following two circumstances:
The table only has the primary key but no unique indexes.
The operation specifies the values of all columns in a row and the system does not need to insert the default value for columns where values are missing.
Although the results are the same in the preceding two circumstances, OBKV-Table optimizes the implementation of insert_or_update to provide better performance than that of replace.