How do I solve the unique column insert duplicate value in Mysql? _mysql

Source: Internet
Author: User

When a unique column inserts a record containing duplicate values on a unique key, we can control how MySQL handles this: Skip inserts using the Ignore keyword or on the DUPLICATE key update clause, Break operations or update old records for new values.

 mysql> CREATE table menus (ID tinyint (4) NOT NULL auto_increment,-> label Varcha 
R (a) Null,url varchar () 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 that violates a unique constraint in the unique column, MySQL interrupts the operation and prompts for an error:

mysql> INSERT into menus (id,label,url) VALUES (4, ' Contact us ', ' contactus.html '); 

When you add the Ignore keyword to the previous INSERT statement, if you think that the statement violates the unique constraint, MySQL does not even attempt to execute the statement, 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 | 
+----+----------+---------------+ 

When there are a lot of INSERT statements that need to be executed sequentially, the Ignore keyword makes the operation easier. Use it to ensure that MySQL skips over it (rather than discarding it all), regardless of which insert contains duplicate key values.

In this case, we can also enable MySQL to automatically convert the insert operation to an update operation by adding the MySQL4.1 newly added on DUPLICATE KEY update clause. This clause must have a list of fields that need to be updated, and the list is the same as the list used by the UPDATE statement.

mysql> INSERT into menus (id,label,url) VALUES (4, ' Contacts us ', ' contactus.html ') 
  -> on duplicate key update Label= ' Contact us ', url= ' contactus.html '; 

In this case, if the MySQL discovery table already contains a record with the same unique key, it automatically updates the new value specified in the old record as 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 | 
+----+------------+----------------+ 

The above is a small set of MySQL to introduce you to the unique column to insert a duplicate value of how to solve all the tutorials, I hope to help you.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.