Sidebar

x

How to handle duplicate data in MySQL

 In the scenario where data uniqueness is required, the individual feels that any repeated verification using the program logic is unreliable, and only the uniqueness check can be performed at the data storage layer. In MySQL, the unique key guarantees the uniqueness of the data. If we insert new duplicate data, how can we let MySQL handle it?


MySQL supports atomic operations for three data repetitions, as explained below with examples. The example table structure is:
CREATE TABLE `allowed_user`
(
  `id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(36)  DEFAULT ''  NOT NULL,
  `last_time` TIMESTAMP  NOT NULL,
  UNIQUE (uid)
)

INSERT INTO `allowed_user` (`uid`, `last_time`) VALUES ('8e9b8c14-fae8-49d4-bbac-a733c09ec82f', '2017-09-03 19:31:15') 

Replace Into

The behavior of the Replace Into mode is to overwrite the original record when there is a record with Unique. In fact, the atomic operation of the combination of Delete and Insert will change the primary key of the record.


REPLACE INTO `allowed_user` (`uid`, `last_time`) VALUES ('8e9b8c14-fae8-49d4-bbac-a733c09ec82f', '2017-09-01 19:31:15')
// 2 rows affected in 76ms
 

On Duplicate Key Update

In this mode, when there is a record with the same Unique, the Update clause is executed to update the record, otherwise the Insert clause is executed to insert a new record. The primary key recorded at Update does not change.

INSERT INTO `allowed_user` (`uid`, `last_time`) VALUES ('8e9b8c14-fae8-49d4-bbac-a733c09ec82f', '2017-09-01 19:31:15') ON DUPLICATE  KEY UPDATE `last_time` = '2017-09-01 19:40:15'
//The number of SQL execution impact records is 2.
 

Ignore

The Ignore mode does not perform any update and insert operations when the same record is unique. Ignore this record and generally do not use it.

INSERT IGNORE INTO `allowed_user` (`uid`, `last_time`) VALUES ('8e9b8c14-fae8-49d4-bbac-a733c09ec82f', '2017-09-01 19:41:15')
 

Scene case
In an activity scenario, it is necessary to distinguish whether the user is eligible, and the user who has signed the appointment is considered to have such qualification. The above example table is used as a storage for the user qualification relationship.

Push uid to 8e9b8c14-fae8-49d4-bbac-a733c09ec82f user's qualification interface operation logic, which is probably like this:

try {
    $user = $model->query("SELECT * FROM `allowed_user` WHERE `uid` = '8e9b8c14-fae8-49d4-bbac-a733c09ec82f'");
    if (user) {
       $model->exec("UPDATE `allowed_user` SET `last_time` = '2017-09-01 19:50:15' WHERE `uid` = '8e9b8c14-fae8-49d4-bbac-a733c09ec82f'");
    } else {
       $model->exec("INSERT INTO `allowed_user` (`uid`, `last_time`) VALUES ('8e9b8c14-fae8-49d4-bbac-a733c09ec82f', '2017-09-01 19:50:15'");
    }
} catch(Exception $e) {

} 

This code attempts to guarantee uniqueness through program logic, but in the case of high concurrency, data uniqueness is not guaranteed, because it is not atomic operation, modified:

try {
    $model->exec("INSERT INTO `allowed_user` (`uid`, `last_time`) VALUES ('8e9b8c14-fae8-49d4-bbac-a733c09ec82f', '2017-09-01 19:50:15') ON DUPLICATE  KEY UPDATE `last_time` = '2017-09-01 19:50:15'");
} catch(Exception $e) {

} 
Use the Supervisor to manage processes
Go keyword defer some pits
 

Comments

No comments made yet. Be the first to submit a comment
Already Registered? Login Here
Guest
Sunday, 16 December 2018