Summary of saveorupdate statements in Mysql native statements

Background in normal development, this type of data update scenario is often encountered: first, judge whether a data exists in the database table. If a data exists, update is performed. If no data exists, insert is performed. If Hibernate is used, it comes with the saverOrUpdate method, which is easy to use, but what if native SQL statements are used? The most common method for beginners is to query records using select statements first.

Background in normal development, this type of data update scenario is often encountered: first, judge whether a data exists in the database table. If a data exists, update is performed. If no data exists, insert is performed. If Hibernate is used, it comes with the saverOrUpdate method, which is easy to use, but what if native SQL statements are used? The most common method for beginners is to query records using select statements first.

Background

During normal development, Zookeeper often encounters this data update scenario: first, judge whether a data exists in the database table. If yes, update is performed. If no data exists, insert is performed.

If Hibernate is used, it comes with the saverOrUpdate method, which is easy to use, but what if native SQL statements are used?

The most common syntax for beginners of explain analyze is to first query whether a record exists using a select statement. If a record exists, update the record using the update statement. If the record does not exist, insert the record using the insert statement.

However, this is obviously not elegant enough, and there are several problems:

• In order to perform an update operation, two SQL query statements are used in the program. The performance will still be affected when the system load is large.

• The if else statement exists in the code, but the code is very long after one task is done. Code rural development is a lazy person. Why can we make things simple :).

So the question is, how can we use SQL statements to implement saverOrUpdate elegantly?

Zookeeper has encountered similar data update problems in his recent work, and he is tired of writing too much data. Remember that there is a Merge statement in Oracle, So google's similar implementation of mysql is as follows:

Insert if the data does not exist. If the data does not exist, no operation is performed.

When the ignore statement uses the ignore keyword in the insert statement to insert if the data does not exist, the insert statement does not operate. Its implementation logic is that when a primary key conflict or a unique key conflict occurs in an insert statement, no error is thrown and this insert statement is directly ignored. The official website provides the following information:

"

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. for example, without IGNORE, a row that duplicates an existing UNIQUE index or primary key value in the table causes a duplicate-key error and the statement is aborted. with IGNORE, the row is discarded and no error occurs. ignored errors may generate warnings instead, although duplicate-key errors do not.

"

Mysql official documentation provides standard syntax:

The Code is as follows:

INSERT IGNORE

INTO tbl_name

[PARTITION (partition_name,...)]

[(Col_name,...)]

{VALUES | VALUE} ({expr | DEFAULT },...),(...),...

Or

The Code is as follows:

INSERT IGNORE

[INTO] tbl_name

[PARTITION (partition_name,...)]

[(Col_name,...)]

SELECT...

It can be seen that there is no difference between IGNORE keywords and general INSERT statements.

Example:

1. Create a test table

The Code is as follows:

Create table 'test _ tab '(

'Name' varchar (64) not null,

'Age' int (11) not null,

Primary key ('name ')

) ENGINE = InnoDB default charset = utf8;

2. Insert a data entry

The Code is as follows:

Insert into 'test _ tab' ('name', 'age') values ('zhangsan', 24)

The data in the current test_tab table is:

The Code is as follows:

Name | age

: -- | :-

Zhangsan | 24

3. If you execute the insert statement in step 2 again, an exception is reported:

The Code is as follows:

[Err] 1062-Duplicate entry 'hangsan 'for key 'Primary'

4. If the ignore keyword is added to the insert statement in step 2, no exception is reported and existing data is not updated.

The Code is as follows:

Insert IGNORE into 'test _ tab' ('name', 'age') values ('zhangsan', 24 );

------

Statement execution:

Affected rows: 0

Time: 0.000 s

The data in the current test_tab table is:

The Code is as follows:

Name | age

: -- | :-

Zhangsan | 24

If it does not exist, insert it. If it exists, UPDATE it. (use the duplicate key update keyword)

When the on duplicate key update keyword is used in the insert statement to insert data if the data does not exist, the insert operation is performed. If the data does not exist, the UPDATE operation is performed. The logic for judging data duplication is still a primary key conflict or a unique key conflict.

The official website provides the following information:

"

If you specify on duplicate key update, and a row is inserted that wowould cause a duplicate value in a UNIQUE index or primary key, an UPDATE of the old row is already med. the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.

"

Mysql official documentation provides standard syntax:

The Code is as follows:

INSERT

[INTO] tbl_name

[PARTITION (partition_name,...)]

[(Col_name,...)]

{VALUES | VALUE} ({expr | DEFAULT },...),(...),...

[ON DUPLICATE KEY UPDATE

Col_name = expr

[, Col_name = expr]...]

Or:

The Code is as follows:

INSERT

[INTO] tbl_name

[PARTITION (partition_name,...)]

SET col_name = {expr | DEFAULT },...

[ON DUPLICATE KEY UPDATE

Col_name = expr

[, Col_name = expr]...]

Or:

The Code is as follows:

INSERT

[INTO] tbl_name

[PARTITION (partition_name,...)]

[(Col_name,...)]

SELECT...

[ON DUPLICATE KEY UPDATE

Col_name = expr

[, Col_name = expr]...]

It can be seen that the original insert statement is still written.

Example:

1. Use the newly created test_tab table. The data in the table is as follows:

The Code is as follows:

Name | age

: -- | :-

Zhangsan | 24

2. If the insert statement with the same primary key is used, the duplicate key is still incorrect.

The Code is as follows:

Insert into 'test _ tab' ('name', 'age') values ('zhangsan', 50 );

------------

[Err] 1062-Duplicate entry 'hangsan 'for key 'Primary'

3. Add the on duplicate key update... Keywords:

The Code is as follows:

Insert into 'test _ tab' ('name', 'age') values ('zhangsan', 50)

On duplicate key update 'age' = 50;

------------

Affected rows: 2

Time: 0.025 s

4. In this case, the primary key is 'hangsan' and the age field has been updated:

The Code is as follows:

Name | age

: -- | :-

Zhangsan | 50

5. Of course, if the primary key does not conflict, the effect is the same as that of the general insert statement:

The Code is as follows:

Insert into 'test _ tab' ('name', 'age') values ('lisi', 30)

On duplicate key update 'age' = 30;

------------

Affected rows: 1

Time: 0.009 s

The Code is as follows:

Name | age

: -- | :-

Zhangsan | 50

Lisi | 30

Insert if no data exists, update if data exists, and use the replace statement)

There is another implementation in mysql, namely, the replace into Statement, which is a bit like Oracle's Merge. The logic for judging data duplication is still a primary key or a unique key conflict. Mysql official documentation provides standard syntax:

The Code is as follows:

REPLACE [LOW_PRIORITY | DELAYED]

[INTO] tbl_name

[PARTITION (partition_name,...)]

[(Col_name,...)]

{VALUES | VALUE} ({expr | DEFAULT },...),(...),...

Or:

The Code is as follows:

REPLACE [LOW_PRIORITY | DELAYED]

[INTO] tbl_name

[PARTITION (partition_name,...)]

SET col_name = {expr | DEFAULT },...

Or:

The Code is as follows:

REPLACE [LOW_PRIORITY | DELAYED]

[INTO] tbl_name

[PARTITION (partition_name,...)]

[(Col_name,...)]

SELECT...

Example:

1. The above test_tab table data is still used. The data is as follows:

The Code is as follows:

Name | age

: -- | :-

Zhangsan | 50

Lisi | 30

2. Use the General insert statement to insert data with name = zhangsan, and a primary key conflict is reported. But replace it with replace... Statement:

The Code is as follows:

Replace into 'test _ tab' ('name', 'age') values ('zhangsan', 30 );

------------

Affected rows: 2

Time: 0.009 s

3. The result is as follows:

The Code is as follows:

Name | age

: -- | :-

Zhangsan | 30

Lisi | 30