Duplicate key update in MySQL

Two 、duplicate key update

duplicate key update ( Duplicate key update )

How to judge whether to update or insert when inserting fields

2.1 Basic usage

2.1.1 Suppose there is no data in our table at this time

Execute statement

INSERT INTO test ( id, NAME )
VALUES
	( '1', ' Xiaoming ' ) 
ON DUPLICATE KEY UPDATE
id='123',
name='xiaoming'

result

2.1.2 Execute again when there is data

Execute statement

ditto

sql result

Here's the key ： We can see the change as 2, This means that the original data has been updated , The update content is the field value set below

result

Q： Let's execute this statement again , What is the result ？

A： Naturally, it is judged that there is no such data in the table , Add an additional new data

sql result

result

Summary

You must understand by now , In fact, it will automatically detect whether it exists , If there is values The following values will change automatically , Insert if it doesn't exist

2.3 Expand usage , Batch insert

Execute statement

INSERT INTO test(`id`,`name`,`address`)
VALUES('4',' modify 10',' Beijing '),
	( '1', ' Xiaoming ' ,1) 
ON DUPLICATE KEY UPDATE
name=VALUES(name),address=VALUES(address);

result

Batch automatically judge whether there is , If it exists, it will be modified automatically

2.4 Mybatis Writing in Chinese

Insert separately

<insert id="insertUser" parameterType="com.test.User">
    INSERT INTO user(
    id,
    name,
    gender,
    birthday,
    address)
    VALUES
    (#{id},
    #{name},
    #{gender},
    #{birthday},
    #{address})
    ON DUPLICATE KEY UPDATE
    id = VALUES(id),
    name = VALUES(name),
    gender = VALUES(gender)
    birthday = VALUES(birthday),
    address = VALUES(address)
</insert>

Batch insert

<insert id="insertUser" parameterType="java.util.List">
    INSERT INTO user(
    id,
    name,
    gender,
    birthday,
    address)
    VALUES
    <foreach collection="list" item="item" index="index" separator=",">
        (#{item.id},
        #{item.name},
        #{item.gender},
        #{item.birthday},
        #{item.address})
    </foreach>
    ON DUPLICATE KEY UPDATE
    id = VALUES(id),
    name = VALUES(name),
    gender = VALUES(gender)
    birthday = VALUES(birthday),
    address = VALUES(address)
</insert>