Summary of saving or update statements in Mysql native statements, mysqlsave

Summary of saving or update statements in Mysql native statements, mysqlsave

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:

Copy codeThe Code is as follows:
INSERT IGNORE
INTO tbl_name
[PARTITION (partition_name,...)]
[(Col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT },...),(...),...

Or

Copy codeThe 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

Copy codeThe 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

Copy codeThe Code is as follows:
Insert into 'test _ tab' ('name', 'age') values ('zhangsan', 24)

The data in the current test_tab table is:

Copy codeThe Code is as follows:
Name | age
: -- | :-
Zhangsan | 24

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

Copy codeThe 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.

Copy codeThe 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:

Copy codeThe 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:

Copy codeThe 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:

Copy codeThe 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:

Copy codeThe 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:

Copy codeThe 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.

Copy codeThe 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:

Copy codeThe 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:

Copy codeThe 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:

Copy codeThe 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

Copy codeThe 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:

Copy codeThe Code is as follows:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
[PARTITION (partition_name,...)]
[(Col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT },...),(...),...

Or:

Copy codeThe Code is as follows:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
[PARTITION (partition_name,...)]
SET col_name = {expr | DEFAULT },...

Or:

Copy codeThe 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:

Copy codeThe 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:

Copy codeThe 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:

Copy codeThe Code is as follows:
Name | age
: -- | :-
Zhangsan | 30
Lisi | 30

Merge merge is similar to save or update for the operation result, but the implementation method is different from the "duplicate key update" keyword of INSERT. When the replace into statement is used, duplicate data is deleted directly, and then new data is inserted. Therefore, its update is not an update, but delete-> insert. In most cases, using replace into to update is normal, but you must pay special attention to the following scenarios:
• When the updated table contains insert, update, and delete triggers, you must be especially careful when using the replace statement. Because the update trigger should be triggered after the data is updated according to the business logic, but the replace statement will trigger the delete and insert triggers, if the update trigger has some special operations (such as recording operation logs), using replace will cause business logic confusion.

Therefore, when a trigger exists in the updated table, it is more appropriate to use the "duplicate key update" keyword of INSERT.

The above is all the content described in this article. We hope that you can better understand the save and update statements in mysql.