MySQL on duplicate key update detailed

Have you ever encountered this scenario in our daily development: to see if a record exists, to create a new record if it doesn't exist, to update some fields if it exists. Is your approach just as follows?

$result = mysql_query(‘select * from xxx where id = 1‘);$row = mysql_fetch_assoc($result);if($row){mysql_query(‘update ...‘);}else{mysql_query(‘insert ...‘);}

There are a few drawbacks to this notation:

• A small amount of performance consumption. Executed two times SQL, followed by a SQL one to two times the network transmission, then this is 4 times.
  Trouble A very simple logical lack to write ten lines of code.
• There is a problem with high concurrency. For example, when we obtain the required data, before the update, there is another request just delete the record, our update operation does not play a role, or, if we have a problem in the way of updating the operation, such as updating column A, we use a = $row[a] + 1 instead of a = a +1 this atomic operation, It is possible that the field has been modified by another request, resulting in an error in the data.

Fortunately, MySQL takes this into account, providing the syntax that, when an insert is made insert … on duplicate key update , if the insert data causes a unique index (including the primary key index) to conflict, that is, if the unique value is duplicated, the insert operation is not performed and the subsequent update operation is performed.

For example, there is now a table test,test table with field A, a primary key or a unique index on a, and only one a=1 in the table, b=1 data, now execute the following sql:

insert into test (a,b) values (1,2) on duplicate key update b = b = 1;#因为a=1的记录已存在了，所以不会执行insert，而会在该条记录上执行update语言`b=b+1`,记录会变成a=1,b=2insert into test (a,b) values (2,2) on duplicate key update b = b + 1;#a=2的记录不存在，所以执行insert

This way we don't have to judge the existence of records in the application, nor do we need to be in a situation where the data goes wrong in high concurrency.

If the row is inserted as a new record, the affected behavior is 1, and if the original record is updated, the affected behavior is 2, and if the original record already exists, but the updated value and the original value are the same, the affected behavior is 0.

Multiple Unique index conflicts

For testing convenience, we have built the following data sheet:

create table test(a int not null primary key,b int not null UNIQUE key,c int not null)

To test the case where two unique indexes are conflicting, insert the following data:

insert into test values(1,1,1), (2,2,2);

Then execute:

insert into test values(1,2,3) on duplicate key update c = c + 1;

Because both A and B are unique indexes, the inserted data creates a conflict on two records, but only the first record is modified after execution:

mysql> select * from test;+---+---+---+| a | b | c |+---+---+---+| 1 | 1 | 2 || 2 | 2 | 2 |+---+---+---+2 rows in set (0.00 sec)

The above statement is equivalent to:

update test set c=c+1 where a=1 or b = 2 limit 1;

If a=1 or b =2 more than one record is matched, only the first record is updated. So, in general, we should avoid using tables with multiple unique indexes on duplicate key update .

Use the values () method

You can use methods in update to values() reference values in the insert, such as:

insert into test values(1,3,5) on duplicate key update c = values( c )+ 1;

This statement updates the value of the C field in the A=1 record to 6 because the value of values (c) is the value of the insert portion of the reference, which in this case is insert into test values(1,3,5)  5, so the value of the final update is 6.

LAST_INSERT_ID ()

insert … on duplicate key update last_insert_id() Returns the value of the Auto_increment field if the table contains a auto_increment field, using Insert or update.

concurrency control

When used on a partitioned table with table-level locks such as MyISAM insert … on duplicate key update , all partitioned tables are locked, and all partitioned tables are not locked on partition tables such as row-level locks that use InnoDB.

Delayed options

The delayed option is ignored when we use the on duplicate key update.

MySQL on duplicate key update detailed