Syntax:
Here is the syntax for the INSERT IGNORE query.
INSERT IGNORE via Workbench:
Open your MySQL Workbench 8.0 from your system and connect it to the database instance.
In the command area, you have to create a table “Employee” with four columns where one of them must be specified as “UNIQUE”. Try the below query in the navigator query area to create this table. Select the whole query and click the flash sign to execute it.
Upon creation, you can find the table “employee” within the list under the option of “Tables” beneath the database “data”.
In the grid view, you can enter the records without typing any query. So, open the grid view of table “employee” and add some records in it as shown below. We have entered all the unique records without any duplicates. Hit the ‘Apply’ button to apply the changes.
A new window will be opened with the relevant queries related to the records we have entered above. This screen can be called a “Review” screen. If you want to change something, you can do it here. Otherwise, hit the Apply button to execute the queries.
As you can see, the query has been executed successfully and the records are saved into the database and its table “Employee”. It would have generated an error if we had added any duplicate value in the column “ID”. Tap the “Finish” button.
This was all about the grid view. Now, we will be inserting records via the query area. Meanwhile, we have been inserting duplicate records this time to check the output. So, we have tried the below “INSERT” query, where we have two lists of values. Both lists of values have the same value at the column ‘ID’. Select the query and hit the flash sign to execute the query.
The query will not work correctly, and it will generate an error due to the duplicated values in the INSERT command as displayed in the image.
Now try the same above query with the INSERT IGNORE clause and execute it as presented.
You can see that it doesn’t generate an error in the output area, but it gives a warning that the command contains duplicate values.
Refresh the grid view of table “Employee”. The INSERT IGNORE query has worked half. It inserted the first list of values into the table, but the second list of values has been ignored due to the repeated value “13”.
INSERT IGNORE via Command-Line Shell:
To understand this concept, let’s open the MySQL command-line client shell in your system. Upon asking, type your MySQL password to start working on it.
Now it’s time to create a table. Try the below command to do so. We have created a table named ‘minister’ while one of its columns has a UNIQUE constraint. It is clear that the column “ID” will only accept the unique values and not the duplicate values.
The query works appropriately, and the table has been created. To understand the INSERT IGNORE clause, you have to first see the simple INSERT command’s working. If you’re using the INSERT command to insert several information data to a table, MySQL suspends the transaction and generates an exception if an error happens throughout the processing. As a consequence, the table does not have any rows added to it. Let’s insert the first record in the table “minister” using the below-shown query. The query will work successfully because the table is presently blank, and there is no record to counterpart with.
As the column “ID” is UNIQUE, when we try the below instruction on the command-line shell, it will generate an error. This is because we have added the value “11” in the previous query, and due to the UNIQUE key it doesn’t allow us to add the repeated value again.
Hence, on checking the table, we can see that the table has only 1 record added by the first INSERT query.
Conversely, if you’re using the INSERT IGNORE clause, the incorrect data rows that trigger the error will be overlooked and will enter only the accurate ones. In the below command, we have been using the INSERT IGNORE command to avoid adding repeated values into the table and overlook the error. As you can see, the first list of values has a duplicate value “11” same as in the previous query. While the second list of values is unique, it will show 1 record inserted in the table, which is the second list of values. MySQL also indicates that only 1 record has been inserted, and 1 warning is generated in the message. You may then assume that if we’re using the INSERT IGNORE clause, MySQL provides a warning.
As you can see from the below output, we only have two records in this table —thefirst list of values provided in the above query, which is overlooked.
Conclusion:
We have done all the necessary examples of INSERT IGNORE on duplicate values via MySQL Workbench and MySQL command-line client shell.
