replace and insert_or_update are two operations that are confusing due to their similar performance in many cases. However, they are semantically different. This topic compares the differences between these two operations in detail.
If a conflict occurs when you perform the replace operation, all conflicting rows are deleted before new rows are inserted.
If a conflict occurs when you perform the insert_or_update operation, the conflicting rows are updated.
The following examples show the differences between the two operations.
insert_or_update
The insert_or_update operation of TableAPI 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. 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, TableAPI optimizes the implementation of insert_or_update to provide better performance than that of replace.