MySQL save or update saveOrUpdate

Posted by mckooter on Sun, 20 Feb 2022 09:29:36 +0100

1. Introduction

In the process of project development, when some data is written, if it already exists, it can be overwritten. This can prevent multiple repeated writes to the only key conflict and error reporting. Here are two examples of using saveOrUpdate in MyBatis configuration files

<!-- Single data saving -->
<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 save -->
<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>

In fact, for single line data, on duplicate key update can also use the VALUES expression (VALUES point to new data) as for batch data saving.

Learn MySQL ON DUPLICATE KEY UPDATE syntax through the above example. Now continue to learn~~

2. ON DUPLICATE KEY UPDATE syntax

MySQL's ON DUPLICATE KEY UPDATE syntax refers to the INSERT statement containing the ON DUPLICATE KEY UPDATE clause. When the new statement already exists in the database (existing means that the primary key 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, in which a is the unique key in the 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;

If there is not only a unique key but also b unique key in the 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;

The meaning of the above UPDATE statement is to take a piece of data satisfying a=1 or b=2 from the table and update it.

Here are the following questions:

2.1 multiple unique keys

When a sheet contains multiple unique keys (multiple unique keys refer to multiple keys, rather than multiple fields in a key), you must pay attention to whether multiple unique keys correspond to multiple pieces of data

As can be seen from the second example above, ON DUPLICATE KEY UPDATE will match one piece of data according to a=1 or b=2 for updating. When multiple pieces of data are corresponding at this time, this update operation will be uncertain. (from another perspective, if multiple unique keys correspond to each other one by one, there will be no problem with the update operation)

2.2 return value of affected rows

If the data does not exist, 1 will be returned for the new data
The data already exists. If you modify the data, return 2
The data already exists but has not changed. 0 is returned

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

Index field does not exist, add a record. Index field exists, update other fields.

The following is a simple example of the return value of ON DUPLICATE KEY UPDATE:

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)

Pay attention to the relationship between the return value and adding and modifying

2.3 new and old data reference

From the above example, it is analogized with the trigger. After the ON DUPLICATE KEY UPDATE clause, the field name is directly used, and the old data is referenced; With VALUES, you refer to the new data to insert the update. (for example, c=c+1 is to add 1 to the c field of old data, c=VALUES) © Overwrite old data with new data)

2.4 batch saving

For batch saving scenarios using ON DUPLICATE KEY UPDATE, please go back and refer to the second usage in the example at the beginning of the article.

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

Topics: Database MySQL SQL

Programmer Think