Shulou(Shulou.com)05/31 Report--

How to insert and deal with duplicate keys in MYSQL. In view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

When the unique column inserts a record with duplicate values on a UNIQUE key, the default insert will report a 1062 error. MYSQL has three different processing methods, which we will describe below.

First set up two test tables and create unique constraints on the id column.

Mysql > create table test1 (id int,name varchar (5), type int,primary key (id))

Query OK, 0 rows affected (0.01 sec)

Mysql > create table test2 (id int,name varchar (5), type int,primary key (id))

Query OK, 0 rows affected (0.01 sec)

Mysql > select * from test1

+-+

| | id | name | type | |

+-+

| | 101 | aaa | 1 | |

| | 102 | bbb | 2 | |

| | 103 | ccc | 3 | |

+-+

3 rows in set (0.00 sec)

Mysql > select * from test2

+-+

| | id | name | type | |

+-+

| | 201 | aaa | 1 | |

| | 202 | bbb | 2 | |

| | 203 | ccc | 3 | |

| | 101 | xxx | 5 | |

+-+

4 rows in set (0.00 sec)

1 、 REPLACE INTO

If the record has more than one field, if some fields are not assigned at the time of insertion, then these fields of the newly inserted record are blank.

Mysql > replace into test1 (id,name) (select id,name from test2)

Query OK, 5 rows affected (0.04 sec)

Records: 4 Duplicates: 1 Warnings: 0

Mysql > select * from test1

+-+

| | id | name | type | |

+-+

| | 101 | xxx | NULL |

| | 102 | bbb | 2 | |

| | 103 | ccc | 3 | |

| | 201 | aaa | NULL |

| | 202 | bbb | NULL |

| | 203 | ccc | NULL |

+-+

6 rows in set (0.00 sec)

It is important to note that when you replace, if the inserted table does not specify a column, it will be represented by NULL instead of the original contents of the table. If the content column inserted is the same as the inserted table column, NULL does not appear. For example

Mysql > replace into test1 (id,name,type) (select id,name,type from test2)

Query OK, 8 rows affected (0.04 sec)

Records: 4 Duplicates: 4 Warnings: 0

Mysql > select * from test1

+-+

| | id | name | type | |

+-+

| | 101 | xxx | 5 | |

| | 102 | bbb | 2 | |

| | 103 | ccc | 3 | |

| | 201 | aaa | 1 | |

| | 202 | bbb | 2 | |

| | 203 | ccc | 3 | |

+-+

6 rows in set (0.00 sec)

If you need to keep the columns inserted into the table and update only the specified columns when you INSERT, you can use the second method.

2 、 INSERT INTO ON DUPLICATE KEY UPDATE

It is found that the update operation is repeated. On the basis of the original record, the contents of the specified fields are updated and the contents of other fields are retained. For example, I only want to insert the id,name field of the test2 table, but keep the type field of the test1 table:

Mysql > insert into test1 (id,name,type) (select id,name,type from test2) on DUPLICATE KEY UPDATE test1.name=test2.name

Query OK, 5 rows affected (0.04 sec)

Records: 4 Duplicates: 1 Warnings: 0

Mysql > select * from test1

+-+

| | id | name | type | |

+-+

| | 101 | xxx | 1 | |

| | 102 | bbb | 2 | |

| | 103 | ccc | 3 | |

| | 203 | ccc | 3 | |

| | 202 | bbb | 2 | |

| | 201 | aaa | 1 | |

+-+

6 rows in set (0.00 sec)

If you only want to insert data that is not available in the original table when you INSERT, you can use the third method.

3 、 IGNORE INTO

Judge whether it exists or not, otherwise insert. It is easy to understand that MySQL will not attempt to execute this statement when inserting violates the uniqueness constraint. For example:

Mysql > insert ignore into test1 (id,name,type) (select id,name,type from test2)

Query OK, 3 rows affected (0.01sec)

Records: 4 Duplicates: 1 Warnings: 0

Mysql > select * from test1

+-+

| | id | name | type | |

+-+

| | 101 | aaa | 1 | |

| | 102 | bbb | 2 | |

| | 103 | ccc | 3 | |

| | 203 | ccc | 3 | |

| | 202 | bbb | 2 | |

| | 201 | aaa | 1 | |

+-+

6 rows in set (0.00 sec)

This is the answer to the question of how to insert and deal with duplicate keys in MYSQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.