Troubleshooting MySQL composite primary key on DUPLICATE key UPDATE statement invalidation

First describe the cause of this problem, suppose there is a table, which holds the trade orders, each order has a unique ID, the last update time, and the data, the details are as follows:

+-------+----------+------+-----+---------------------+-------+| field | type      | Null | Key | Default              | extra |+-------+----------+------+-----+---------------------+------ -+| uid   | int (one)   | no   | pri | 0                     |       | |  Time  | datetime | NO   |     |  0000-00-00 00:00:00 |       | |  data  | int (one)   | YES  |     |  null                |        |+-------+----------+------+-----+---------------------+-------+ 

This table will be added and updated, specifically, if the order does not exist insert a new, if already exists on the update. Because it is not possible to know whether a record exists before warehousing, the usual practice cannot be as follows:

1, select First, then decide insert or update;

2, direct update, if the number of rows affected is 0, and then insert;

3, direct Insert, if there is a primary key conflict, and then update;

These methods are flawed, but the best thing for MySQL is to use the insert directly ... On DUPLICATE KEY UPDATE ... statement, specific to the test table above, execute the following statement:

INSERT into Test VALUES (1, ' 2016-1-1 ', ten) on DUPLICATE KEY UPDATE time= ' 2016-1-1 ', data=10;

Can be very good to insert or update data, a statement is done, so far has been working very well.

Later, because of changes in query mode, the UID and time two fields are required to do the federated primary key, the table structure is as follows:

+-------+----------+------+-----+---------------------+-------+| field | type      | Null | Key | Default              | extra |+-------+----------+------+-----+---------------------+------ -+| uid   | int (one)   | no   | pri | 0                     |       | |  Time  | datetime | NO   | PRI | 0000-00-00  00:00:00 |       | |  data  | int (one)   | YES  |     |  null                |        |+-------+----------+------+-----+---------------------+-------+ 

But the problem is: one time field is updated, even the same UID, the database is considered to be a different primary key, so there is no primary key conflict, the above statement is invalid, the database has a lot of the same UID data.

It's easy to start looking for a solution, as explained in the MySQL documentation, the on DUPLICATE key UPDATE statement Determines whether a conflict relies on a primary key or a unique index, so a unique index on the UID is OK. To build the index first:

CREATE UNIQUE INDEX idx_uid on test (UID);

Test the Insert again:

INSERT into test values (1, ' 2016-1-1 ', ten) on DUPLICATE KEY UPDATE time= ' 2016-1-1 ', data=10;insert into test values (1, ' 2 016-2-1 ', "on DUPLICATE KEY UPDATE time= ' 2016-2-1 ', data=20;

Check the database, you can see that there will not be more than one data generation, the only data field is updated to 20, the success.

This article is from the "Rabbit Nest" blog, please be sure to keep this source http://boytnt.blog.51cto.com/966121/1736690

Troubleshooting MySQL composite primary key on DUPLICATE key UPDATE statement invalidation