MySQL solution for inserting duplicate values in unique Columns

The unique value insertion solution for unique columns in this article is mainly based on the MySQL platform. Through this, you can achieve some new functions and applications. I hope this article will help you.

When a unique column inserts records containing duplicate values on a UNIQUE key, 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 no matter which INSERT contains duplicate key values, MySQL skips it instead of giving up 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) 
 
 

1. Implementation of Like indexes in MySQL full-text search
2. MySQL Query Optimization Using scheduling and locking
3. Analysis of basic MySQL Scheduling Policies
4. Adding "light" Drizzle + PHP to MySQL
5. Learn to set five categories of MySQL Parameters