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.