In the process of project development, some data can be covered when it is written, if it already exists. This prevents multiple iterations of unique key collision errors. Here are two examples of using saveOrUpdate in MyBatis configuration files

<!-- Single Data Storage -->
<insert id="saveOrUpdate" parameterType="TestVo">
    insert into table_name (
        col1,
        col2,
        col3
    )
    values (
        #{field1},
        #{field2},
        #{field3}
    )
    on duplicate key update
        col1 = #{field1},
        col2 = #{field2},
        col3 = #{field3}
</insert>  

<!-- Batch storage -->
<insert id="batchSaveOrUpdate" parameterType="java.util.List">
    insert into table_name (
        col1,
        col2,
        col3
    )
    <foreach collection="list" item="item" index="index" separator=",">
        values (
            #{item.field1},
            #{item.field2},
            #{item.field3}
        )
    </foreach>
    on duplicate key update
        col1 = VALUES (col1),
        col2 = VALUES (col2),
        col3 = VALUES (col3)
</insert>

Get a first glimpse of MySQL ON DUPLICATE KEY UPDATE grammar through the above examples, and continue to learn below.~~

2. ON DUPLICATE KEY UPDATE grammar

MySQL's ON DUPLICATE KEY UPDATE grammar refers to the INSERT statement that contains the ON DUPLICATE KEY UPDATE clause. When the new statement already exists in the database (which means that the primary or unique key contained in the data already exists in the database), the old data corresponding to the database will be updated.

The following two sql statements are equivalent, where a is the only key in the table table table

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

• 1
• 2
• 3
• 4

If there is not only a unique key but also b in the table table table, the following two statements are equivalent.

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;  

UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

• 1
• 2
• 3
• 4

The following questions are highlighted:

2.1 More than one unique key

As can be seen from the second example above, ON DUPLICATE KEY UPDATE will update one data according to a=1 or b=2 matching, and this update operation will be uncertain when there are multiple data. (From another point of view, if multiple unique keys are one-to-one, then the update operation will not be a problem.)

2.2 affects the number of rows returned

Whether the data exists is judged by the unique key, and whether the data is modified is judged by the statement after ON DUPLICATE KEY UPDATE.

Following is a simple example of various cases of ON DUPLICATE KEY UPDATE return values:

mysql> CREATE TABLE test1 (a INT PRIMARY KEY AUTO_INCREMENT , b INT, c INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test1(a, b ,c) VALUES (1, 1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    1 |
+---+------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO test1(a, b ,c) VALUES (1, 1, 1) ON DUPLICATE KEY UPDATE c = c + 1;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from test1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    2 |
+---+------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO test1(a, b ,c) VALUES (2, 2, 2) ON DUPLICATE KEY UPDATE c = c + 1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    2 |
| 2 |    2 |    2 |
+---+------+------+
2 rows in set (0.00 sec)

mysql> INSERT INTO test1(a, b ,c) VALUES (2, 2, 3) ON DUPLICATE KEY UPDATE c = VALUES(c);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from test1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    2 |
| 2 |    2 |    3 |
+---+------+------+
2 rows in set (0.00 sec)
mysql> INSERT INTO test1(a, b ,c) VALUES (2, 2, 3) ON DUPLICATE KEY UPDATE c = VALUES(c);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    2 |
| 2 |    2 |    3 |
+---+------+------+
2 rows in set (0.00 sec)

Note the relationship between return values and additions and modifications

2.3 New and Old Data Reference

2.4 Batch Storage

Batch save scenarios using ON DUPLICATE KEY UPDATE, refer back to the second usage in the example at the beginning of the article.

Reference from the official website: http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html