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

When an unique column inserts a record with a duplicate value on an UNIQUE key, we can control how MySQL handles the situation: using the IGNORE keyword or the ON DUPLICATE KEY UPDATE clause skips INSERT, interrupts operations, or updates the old record to 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 now insert a record in the unique column that violates the only one constraint, MySQL interrupts the operation, prompting 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 was added in the previous INSERT statement, MySQL would not even attempt to execute the statement if it was considered to violate the only 1 constraint, so the following statement does 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) 

The IGNORE keyword makes it easy when there are many INSERT statements that need to be executed sequentially. Using it ensures that no matter which INSERT contains a duplicate key value, MySQL skips it (rather than abandoning all operations).

In this case, we can also make MySQL automatically convert the INSERT operation to the UPDATE operation by adding the new ON DUPLICATE KEY UPDATE clause to MySQL4.1. This clause must have a list of fields that need to be updated, the same 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 1 key, it automatically updates 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) 

This is the site's complete tutorial on how to insert duplicate values into the unique column in Mysql, hoping to help you.