Summary of database INSERT OR UPDATE issues

We often encounter a problem in database-related development: Inserting a record into a database table and updating it if it exists. For example, the following data table user (ID is the primary key):

Id	Name	passwd
1	Usr1	Pswd1
...	...	...

The record to which ID=1,NAME=USR1,PASSWD=PSWD2 is inserted, and if there is a record of id=1, update the operation.

This problem is simple under the condition of single-connection access, you can perform an update operation on the record first, if the number of bars affected is 0, the description does not have this record, and then you can be assured of the bold insert operation.

However, under the condition of multiple concurrent accesses, there is a synchronization problem in the above practice. If the introduction of transactions is a bit of a fuss, if it is possible to have a statement in an atomic way to complete the above functions that is very good. Here is a summary of the MySQL and Oracle processing methods.

1. MySQL

MySQL is handled in two ways: REPLACE and on DUPLICATE KEY

(1) REPLACE

Replace is similar to the syntax for INSERT, in the following form:

Replace into table (Col1,col2,...) VALUES (Val1,val2,...);

For example:

Replace into user (ID,NAME,PASSWD) VALUES (1, ' usr1 ', ' pawd2 ');

If the inserted record does not duplicate the record in the table, the insert operation is performed, the number of records affected is 1, and if the inserted record duplicates a record in the table, the original record is first delete and the insert is executed, and the number of records affected is 2.

(2) on DUPLICATE KEY

The on DUPLICATE key statement connects the INSERT statement and the UPDATE statement to be executed. The form is as follows:

Insert_statement on duplicate key update_statement

For example:

Insert into User (ID,NAME,PASSWD) VALUES (1, ' usr1 ', ' pswd2 ') on duplicate key update name= ' USR1 ', passwd= ' pswd2 ';

If the inserted record does not duplicate the record in the table, the first half of the insert operation is performed, the number of records affected is 1, and if the inserted record duplicates the record in the table, the second half of the update operation is performed, and the number of records affected is 2.

2. Oracle

Oracle uses the merge statement primarily for processing. For example:

Merge into the user using (select 1 ID, ' USR1 ', ' pswd2 ' from dual) T in (t.id=user.id) when matched then update set User.name= ' USR1 ', user.passwd= ' pswd2 ' when isn't matched then insert (USER.ID,USER.NAME,USER.PASSWD) VALUES (1, ' usr1 ', ' pswd2 ');

This article is from the "Zhong Technology blog" blog, make sure to keep this source http://zhongziyuan.blog.51cto.com/3450270/1561994

Summary of database INSERT OR UPDATE issues