Differences between replace and insert_or_update

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 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. 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, TableAPI optimizes the implementation of insert_or_update to provide better performance than that of replace.