Various ways to save or update in Mysql native statements

Various ways to save or update in Mysql native statementsBackground

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

If you use Hibernate, it comes with the Saverorupdate method, which is handy, but what about using native SQL statements?

?? The most common notation for beginners is to query for the existence of a record by using the SELECT statement, which is updated with the UPDATE statement and inserted using the 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 SQL query statements, in the case of large system load, performance will be affected.
• There is an if Else statement in the code, and the code is very long, obviously doing one thing. Yards of farmers are lazy people, can do things simple why to do it complex:).

So here's the question, how do you gracefully implement saverorupdate with SQL statements?

?? Recently work also encountered similar update data problems, write more also began to feel bored. Remember that Oracle has a merge under the writing, Google a bit similar to the implementation of MySQL, organized as follows:

Data does not exist then inserted, there is no action

?? Use the Ignore keyword in the INSERT statement to implement data that does not exist then insert, and there is no action. Its implementation logic is that when the INSERT statement appears a primary key conflict, or a unique key conflict, do not throw an error, directly ignore the INSERT statement. The official web-related information is as follows:

If you use the IGNORE keyword, errors this occur while executing the INSERT statement is ignored. For example, without IGNORE, a row this duplicates an 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 does not.

The standard syntax is available in the MySQL official documentation:

INSERT  IGNORE    INTO tbl_name    [PARTITION (partition_name,...)]     [(col_name,...)]    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...或者  INSERT IGNORE    [INTO] tbl_name    [PARTITION (partition_name,...)]     [(col_name,...)]    SELECT ...

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

Give me a chestnut:

1. Build a table for testing

CREATE TABLE `test_tab` (  `name` varchar(64) NOT NULL,  `age` int(11) NOT NULL,  PRIMARY KEY (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Inserting a piece of data

The data for the current Test_tab table is:

Name|age

:--|:-

Zhangsan|24

3. Execute the INSERT statement for step 2 again, and the exception will be reported:

[Err] 1062 - Duplicate entry ‘zhangsan‘ for key ‘PRIMARY‘

4. Adding the Ignore keyword to the INSERT statement in step 2 will not report an exception and the existing data will not be updated.

insert IGNORE into `test_tab` (`name`,`age`) values (‘zhangsan‘,24) ;------语句执行情况：受影响的行: 0时间: 0.000s

The data for the current Test_tab table is:

Name|age

:--|:-

Zhangsan|24

Does not exist then insert, existing is updated, one (using duplicate key update keyword)

?? Using the on DUPLICATE key in the INSERT statement to implement data that does not exist is inserted, and there is an updated operation. The logic to judge data duplication remains a primary key conflict or a unique key violation.

The official web-related information is as follows:

If you 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 is 1 if the row was inserted as a new row, 2 if an existing row was updated, and 0 if an Exi Sting row is set to its current values.

The standard syntax is available in the MySQL official documentation:

INSERT     [INTO] tbl_name    [PARTITION (partition_name,...)]     [(col_name,...)]    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...    [ ON DUPLICATE KEY UPDATE      col_name=expr        [, col_name=expr] ... ]或者：INSERT     [INTO] tbl_name    [PARTITION (partition_name,...)]    SET col_name={expr | DEFAULT}, ...    [ ON DUPLICATE KEY UPDATE      col_name=expr        [, col_name=expr] ... ]或者：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 of the wording.

Give me a chestnut:

1. Using the newly created Test_tab table, the data in this table is as follows:

Name|age

:--|:-

Zhangsan|24

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

insert into `test_tab` (`name`,`age`) values (‘zhangsan‘,50) ;------------[Err] 1062 - Duplicate entry ‘zhangsan‘ for key ‘PRIMARY‘

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

insert into `test_tab` (`name`,`age`) values (‘zhangsan‘,50)  ON DUPLICATE KEY UPDATE `age`=50 ;------------受影响的行: 2时间: 0.025s

4. At this point the primary key is ' Zhangsan ' data, the Age field has been updated:

Name|age

:--|:-

Zhangsan|50

5. Of course, if the primary key does not conflict, the effect is the same as the normal INSERT statement:

insert into `test_tab` (`name`,`age`) values (‘lisi‘,30)  ON DUPLICATE KEY UPDATE `age`=30 ;------------受影响的行: 1时间: 0.009s

Name|age

:--|:-

Zhangsan|50

Lisi|30

Does not exist then inserts, exists then updates, second (implemented with replace statement)

?? Save or update has another implementation in MySQL, the replace into statement, which is somewhat like the merge of Oracle. The logic to judge data duplication remains a primary key or a unique key conflict. The standard syntax is available in the MySQL official documentation:

REPLACE [LOW_PRIORITY | DELAYED]    [INTO] tbl_name    [PARTITION (partition_name,...)]     [(col_name,...)]    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...或:REPLACE [LOW_PRIORITY | DELAYED]    [INTO] tbl_name    [PARTITION (partition_name,...)]     SET col_name={expr | DEFAULT}, ...或:REPLACE [LOW_PRIORITY | DELAYED]    [INTO] tbl_name    [PARTITION (partition_name,...)]      [(col_name,...)]    SELECT ...

Give me a chestnut:

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

Name|age

:--|:-

Zhangsan|50

Lisi|30

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

replace into `test_tab` (`name`,`age`) values (‘zhangsan‘,30) ;------------受影响的行: 2时间: 0.009s

3. The results are as follows:

Name|age

:--|:-

Zhangsan|30

Lisi|30

?? For the result of the operation, it is much like save or update, but it is implemented in a different way from the insert's "DUPLICATE key update" keyword. When you use the Replace into statement, for duplicate data, it is deleted directly, and then the new data is inserted. So it's not updating, it's delete->insert. In most cases, there is no problem using replace into to complete the update operation, but there is a scenario where you must pay special attention to:

• When a table is updated, there are insert,update, and delete triggers, use of the Replace statement must be especially careful. Because following the business logic, when the data is updated, the update trigger should be triggered, but with the Replace statement, the DELETE and insert triggers are triggered, and if the update trigger has some special actions (such as logging the operation log), Using replace can cause confusion in the business logic.

So when a scene with a trigger is being updated, it is more appropriate to use the Insert DUPLICATE key update keyword.

Resources

1. MySQL website insert syntax Introduction: http://dev.mysql.com/doc/refman/5.6/en/insert.html
2. MySQL official website replace syntax Introduction:
   
   Http://dev.mysql.com/doc/refman/5.6/en/replace.html

Various ways to save or update in Mysql native statements