MySql insert unique key conflict handling methods

MySql > insert a primary key conflict (primary key conflict);

When inserting data in batches, it is found that some records have unique key conflicts, which is more troublesome to jump out one by one. Is there a good way to directly ignore the conflicting records?

The following three processing methods are briefly recorded

I. Unique key conflict during insert

1. Ignore keywords

In some cases, we need to batch insert data, some of which is already in DB, so I want to skip the conflict and just insert whatever is available. In this case, ignore keyword is appropriate

A practical case is as follows

insert ignore into table (xxx, xxx) values (xxx,xxx), (xxx, xxx);
Copy the code

The screenshot is as follows. Notice the content in the red box below, which indicates that two items are ignored and one item is successfully inserted

2. Replace Into

Replace into can be used if I want to replace my old data with my new data when there is a conflict in a batch insert

Common postures are as follows

replace into `user` (`id`.`name`.`create_at`.`update_at`) 
values
	(1.'test'.'the 2018-07-10 18:54:00'.'the 2018-07-10 19:54:52'),
	(2.'test2'.'the 2018-07-10 18:54:00'.'the 2018-07-10 19:54:52'),
	(3.'test3'.'the 2018-07-10 18:54:00'.'the 2018-07-10 19:54:52');
Copy the code

The execution screenshot is as follows. Notice in the red box, when a record conflicts and is modified, the number of affected lines is 2. The actual process is

  • Deleting Conflicting Data
  • Insert new data

3. ON DUPLICATE KEY UPDATE

Add on Duplicate Key UPDATE at the end of the INSERT statement if you want to update some data in case of a conflict

Instance as follows

insert into `user` (`id`.`name`.`create_at`.`update_at`) values (1.'test0'.'the 2018-07-10 18:54:00'.'the 2018-07-10 18:54:52') ON DUPLICATE KEY UPDATE `update_at`='the 2018-07-10 19:58:05';
Copy the code

Update test0 (name); update test0 (name); update test0 (name)

II. The other

1. A gray Blog: https://liuyueyi.github.io/hexblog.

A gray personal blog, recording all the study and work in the blog, welcome everyone to go to stroll

2. Statement

As far as the letter is not as good as, has been the content, purely one’s own words, because of the limited personal ability, it is hard to avoid omissions and mistakes, such as finding bugs or better suggestions, welcome criticism and correction, not grudging gratitude

  • Micro Blog address: Small Gray Blog
  • QQ: a gray /3302797840

3. Scan attention