Compilation of Save or update in Mysql native statement

　　 Background

In normal development, often encountered this update data scenario: first to determine whether a data in the library table exists, there is update, does not exist insert.

If you use Hibernate, it takes a saverorupdate method and is convenient to use, but how about using native SQL statements?

The most common form of a novice is to query whether a record exists through a SELECT statement, to update it with an UPDATE statement, or to insert it using an INSERT statement.

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

• In order to perform an update operation, but in the program used two times SQL query statements, in the system load is relatively large, performance will still have impact.

• There is an if Else statement in the code, but the code is very long when you do something. Code farmers are lazy, can make things simple to do why to complicate it:.

So the question is, how to gracefully implement saverorupdate with SQL statements?

Recent work has also encountered a similar problem of updating data, write more also began to feel annoyed. Remember the Oracle under the writing of the merge, Google a similar implementation of MySQL, sorted as follows:

Insert if data does not exist, there is no action

Insert if the Ignore keyword is used in the INSERT statement to implement the data, and there is no action. Its implementation logic is that when the INSERT statement has a primary key conflict, or when a unique key conflict occurs, do not throw an error, ignoring the insertion statement directly. The relevant introduction of the official website is as follows:

“

If you use the IGNORE keyword, errors this occur while executing the INSERT statement are. For example, without IGNORE, a row that duplicates a existing UNIQUE index or PRIMARY KEY value in the table causes a DUP Licate-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.

”

The standard syntax is provided in the MySQL official documentation:

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

Visible except for a ignore keyword, there is no difference from the general INSERT statement.

　　Give me a chestnut:

1. Build a test table

The code is as follows:

CREATE TABLE ' Test_tab ' (

' Name ' varchar not NULL,

' Age ' int (one) not NULL,

PRIMARY KEY (' name ')

) Engine=innodb DEFAULT Charset=utf8;

2. Insert a piece of data

The code is as follows:

Insert INTO ' test_tab ' (' Name ', ' age ') VALUES (' Zhangsan ', 24)

The data for the current Test_tab table is:

The code is as follows:

Name|age

:--|:-

Zhangsan|24

3. If you perform the INSERT statement again in step 2, you will report an exception:

The code is as follows:

[ERR] 1062-duplicate entry ' Zhangsan ' for key ' PRIMARY '

4. Add the Ignore keyword to the INSERT statement for step 2, the exception is not reported, and the 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.000s

The data for the current Test_tab table is:

The code is as follows:

Name|age

:--|:-

Zhangsan|24

If not, insert, existing is updated, one (using the duplicate key update keyword)

Insert when using the on DUPLICATE key update keyword in INSERT statement to implement data that does not exist, there is an action that is updated. The logic for judging data duplication is still a primary key conflict or a unique key conflict.

The relevant introduction of the official website is as follows:

“

If you are specify on DUPLICATE KEY UPDATE, and a row are inserted that would cause a DUPLICATE value in a UNIQUE index or PRIM ARY KEY, an UPDATE of the "old" row is performed. The Affected-rows value per row are 1 if the row is inserted as a new row, 2 if it existing row is updated, and 0 if an Exi Sting row is set to its current values.

”

The standard syntax is provided in the MySQL official documentation:

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] ...]

Visible, or the original INSERT statement.

Give me a chestnut:

1. Using the Test_tab table you just created, the data in the table is as follows:

The code is as follows:

Name|age

:--|:-

Zhangsan|24

2. Using the same INSERT statement as the primary key will still duplicate the key error

The code is as follows:

Insert INTO ' test_tab ' (' Name ', ' age ') VALUES (' Zhangsan ', 50);

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

[ERR] 1062-duplicate entry ' Zhangsan ' for key ' PRIMARY '

3. Add on duplicate key update for the INSERT statement just now ... Key words:

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.025s

4. When the primary key is ' Zhangsan ' data, 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 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.009s

The code is as follows:

Name|age

:--|:-

Zhangsan|50

Lisi|30

Insert if not present, existing is updated, second (implemented with replace statement)

Save or update has another implementation in MySQL, 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. The standard syntax is provided in the MySQL official documentation:

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

Give me a chestnut:

1. The data in the above Test_tab table is still used, and the data is as follows

The code is as follows:

Name|age

:--|:-

Zhangsan|50

Lisi|30

2. Insert the Name=zhangsan data using a generic INSERT statement, and report the primary key conflict. But replace into ... Statement is no problem:

The code is as follows:

Replace into the ' test_tab ' (' Name ', ' age ') VALUES (' Zhangsan ', 30);

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

Affected Rows: 2

Time: 0.009s

3. The results are as follows:

The code is as follows:

Name|age

:--|:-

Zhangsan|30

Lisi|30

For the operation result, it looks like save or update, but the implementation is different from the "DUPLICATE key update" keyword in insert. When you use the REPLACE into statement, the duplicate data is deleted directly, and then the new data is inserted. So it's not update, it's Delete->insert. In most cases, there is no problem in using the replace into to complete the update operation, but there is one scenario that must be paid special attention to:

• Use the Replace statement must be especially cautious when the table being updated has insert,update, and delete triggers. Because the update trigger should be triggered after the data is updated according to the business logic, the DELETE and insert triggers are triggered when the Replace statement is used, and if the update trigger has some special actions, such as logging the action log, Using replace can cause business logic to be confusing.

Therefore, it is more appropriate to use the Insert "DUPLICATE key Update" keyword when the table is updated with the trigger's scene.

The

Above is all that is described in this article, hoping to let you better understand the save and update statements in MySQL.