How to solve the problem of inserting duplicate values in the unique column in Mysql _ MySQL

This article describes how to insert duplicate values in the unique column in mysql. it is mainly based on the mysql platform. through these solutions, you can implement some new functions and applications. This article will be shared here with developers. When unique columns on a UNIQUE key to insert records containing duplicate values, we can control how MySQL handles this situation: use the IGNORE keyword or the on duplicate key update clause to skip the INSERT operation, interrupt the operation, or UPDATE the old record as the new value.

mysql> create table menus(id tinyint(4) not null auto_increment,   -> label varchar(10) null,url varchar(20) null,unique key(id)); Query OK, 0 rows affected (0.13 sec) mysql> insert into menus(label,url) values('Home','home.html'); Query OK, 1 row affected (0.06 sec) mysql> insert into menus(label,url) values('About us','aboutus.html'); Query OK, 1 row affected (0.05 sec) mysql> insert into menus(label,url) values('Services','services.html'); Query OK, 1 row affected (0.05 sec) mysql> insert into menus(label,url) values('Feedback','feedback.html'); Query OK, 1 row affected (0.05 sec) mysql> select * from menus; +----+----------+---------------+ | id | label  | url     | +----+----------+---------------+ | 1 | Home  | home.html  | | 2 | About us | aboutus.html | | 3 | Services | services.html | | 4 | Feedback | feedback.html | +----+----------+---------------+ 4 rows in set (0.00 sec) 

If you insert a record in the unique column that violates the unique constraint, MySQL will interrupt the operation and prompt an error:

mysql> insert into menus(id,label,url) values(4,'Contact us','contactus.html'); ERROR 1062 (23000): Duplicate entry '4' for key 'id' 

When the IGNORE keyword is added to the previous INSERT statement, if the statement violates the unique constraint, MySQL will not even try to execute this statement. Therefore, the following statement will not return an error:

mysql> insert ignore into menus(id,label,url) values(4,'Contact us','contactus.html'); Query OK, 0 rows affected (0.00 sec) mysql> select * from menus; +----+----------+---------------+ | id | label  | url     | +----+----------+---------------+ | 1 | Home  | home.html  | | 2 | About us | aboutus.html | | 3 | Services | services.html | | 4 | Feedback | feedback.html | +----+----------+---------------+ 4 rows in set (0.00 sec) 

When many INSERT statements need to be executed sequentially, the IGNORE keyword makes the operation very convenient. Using it ensures that MySQL skips an INSERT regardless of which one contains duplicate key values (instead of dropping all operations ).

In this case, we can also add the on duplicate key update clause added by MySQL4.1 to enable MySQL to automatically convert the INSERT operation to the UPDATE operation. This clause must have a list of fields to be updated, which is the same as the list used by the UPDATE statement.

mysql> insert into menus(id,label,url) values(4,'Contact us','contactus.html')   -> on duplicate key update label='Contact us',url='contactus.html'; Query OK, 2 rows affected (0.05 sec) 

In this case, if MySQL finds that the table already contains records with the same unique KEY, it will automatically UPDATE the old record to the new value specified in the on duplicate key update clause:

mysql> select * from menus; +----+------------+----------------+ | id | label   | url      | +----+------------+----------------+ | 1 | Home   | home.html   | | 2 | About us | aboutus.html | | 3 | Services | services.html | | 4 | Contact us | contactus.html | +----+------------+----------------+ 4 rows in set (0.01 sec) 

The above content is a series of tutorials on how to solve the problem of inserting duplicate values in the unique column in Mysql. I hope this will help you.