MySQL Insert Ignore
MySQL INSERT IGNORE clause is used to insert rows in a table while ignoring any duplicate key error, the rows with the same PRIMARY KEY or UNIQUE KEY are skipped in the process of insertion.
Suppose we have a table containing data related to a firm.
The data consists of the following columns:
- EmpID
- Name
- Department
- Salary
When we try to insert details of some employees in bulk, there's a chance of encountering records with the same primary key. In such cases, MySQL will throw an error and the whole insertion process will be halted.
To solve this issue, MySQL INSERT IGNORE clause is used.
The MySQL INSERT IGNORE clause ignores the entry of records in the following conditions:
- Insertion of a row that has duplicate PRIMARY KEY or UNIQUE KEY
- Insertion of NULL values when the column is defined with a NOT NULL constraint.
- If we attempt to insert a record into a partitioned table with values that do not conform to the format specified for the available partitions.
When one of these three conditions occurs, the MySQL INSERT IGNORE generates a warning instead of throwing an error which prevents halting of the insertion process.
Note: The error encountered when a duplicated row is found in the INSERT IGNORE clause is error 1062.
Syntax
The following is the SYNTAX of the MySQL INSERT IGNORE clause:
Note that, column_1, column_2 and column_3 are the columns of the table where we want to insert the data. Similarly, value_1, value_2 and value_3 represent the values of the specific fields, following the same order as the columns provided in the query.
Examples:
We can illustrate how to use the INSERT IGNORE clause with the following example:
First, let's create a database named "db" with the following query:
To use this database, we'll have to run the following query:
Now that we have our database "db", we can create the "Employee" table in which we'll insert the employee records:
Now, let's add the following rows to the table.
Note:
- The PRIMARY KEY (EmpID) of the first and the second entries are the same.
- The Department of the fourth record is missing.
If we had used the INSERT clause for these four rows, it would probably throw an error. However, when we use the INSERT IGNORE clause, it only generates a warning and the process of insertion is not halted.
This is the output of the query without the INSERT IGNORE clause:
This is the output of the query with the INSERT IGNORE clause:
This is how the table looks like after the insertion with the INSERT IGNORE clause:
| Emp_ID | Name | Department | Salary |
|---|---|---|---|
| 111 | John Doe | IT | 100000 |
| 113 | Emily Blake | HR | 80000 |
| 114 | Paul Wood | 70000 |
Let's discuss some of the advantages and disadvantages of the MySQL INSERT IGNORE clause.
Advantages:
- Simplicity: The MySQL INSERT IGNORE clause is a simple way to deal with data redundancy. It is a straightforward way to insert data without checking for the duplicated values explicitly.
- Query Optimization: Queries written by using the INSERT IGNORE clause are very efficient in terms of speed and performance because it ignores all the duplicate rows without any further checking. This is a great way of dealing with a huge chunk of data.
- Error Handling: The MySQL INSERT IGNORE clause is very helpful in terms of handling errors, especially when dealing with large-scale data.
Disadvantages:
Apart from various advantages of the MySQL INSERT IGNORE clause, they are not preferred by database administrators because of the following reasons:
- Data Integrity: Data Integrity is considered one of the most important features of a database management system. However, when using the INSERT IGNORE clause, we may have to make some compromises with it.
- Lack of Control: When using the MySQL INSERT IGNORE clause, the database administrator only has limited control over the insertion. The database engine skips all the redundant rows without providing any information. This can cause some problems during troubleshooting.
- Truncated Data: The MySQL INSERT IGNORE clause forces the data to fit in the given constraints. This results in the data being truncated and inserted into the table which can result in the insertion of a wrong or incomplete entry into the table.
MySQL INSERT IGNORE and STRICT Mode
STRICT MODE in MySQL prevents any illegal entry of records in the table. When the strict mode is ON and we try inserting rows that have some illegal value concerning the structure of the table then it will throw an error
However, when the INSERT IGNORE clause is used, MySQL ignores these illegal entries and generates a warning instead of an error. MySQL also tries to truncate the data according to the structure and the constraints of the table in which the records are being inserted.
Conclusion
- MySQL INSERT IGNORE clause allows the administrator to insert the rows that have duplicate PRIMARY KEY or UNIQUE KEY.
- Whenever such records are found, MySQL throws a warning instead of an error, which is very helpful in the case of adding bulk entries because it prevents the halting of the insertion process.
- The INSERT IGNORE function also tries to modify the data in illegal entries by truncating the extra part in it.