MySQL INSERT IGNORE

In MySQL, managing data insertion errors is crucial for maintaining data integrity and ensuring smooth database operations. The INSERT IGNORE statement provides a practical solution by allowing records to be inserted without interruption even if some rows would cause errors like duplicate key violations.

In this article, We will learn about MySQL INSERT IGNORE by understanding various examples and so on.

What is MySQL INSERT IGNORE

• MySQL INSERT IGNORE is a variation of the normal INSERT that allows the insertion of records in a database table, but only in case of no duplicate key errors or other constraint violations.
  
• Instead of failing and stopping the whole process of inserting the rows that would cause the errors are just skipped and valid rows are inserted.

Syntax of MySQL INSERT IGNORE

INSERT IGNORE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

where,

• table_name: It is the name of the table used in the command to insert the data.
• (column1, column2, ...): These are the column names in which data is to be inserted
• (value1, value2, ...): These are the values that are to be inserted in the columns.
• INSERT: It is the fundamental statement which is used to insert data into the tables.
• VALUES: This is the clause which is used to hold the values for the statement.

Examples of INSERT Statement

Example 1

First we need to create a table named "college" with columns "id' (PRIMARY KEY) and "name" (UNIQUE):

CREATE TABLE college(
    id INT PRIMARY KEY,
    name VARCHAR(50) UNIQUE
);

Inserting Data to the Table

Now insert some data into the table "college" to perform "MySQL INSERT IGNORE".

-- Inserting valid data
INSERT IGNORE INTO college(id, name) VALUES (1, 'John');
INSERT IGNORE INTO college(id, name) VALUES (2, 'Jane');

Using INSERT IGNORE

Suppose we have to insert records into the college table while avoiding errors due to duplicate keys and then verify the contents of the table to ensure that duplicates are ignored as intended.

-- Attempting to insert duplicate data
INSERT IGNORE INTO college(id, name) VALUES (1, 'John');  -- This row will be ignored due to duplicate key '1'
INSERT IGNORE INTO college(id, name) VALUES (3, 'Mike');

-- Checking the data in the table
SELECT * FROM college;

Output:

Explanation:

• The two INSERT statements at the first are inserted without a problem
• The last of the three INSERT statements INSERT an identical value in column id (1); this one's ignored.
• Finally, we select all rows from the table employees to check the data inserted.

Example 2

In this we aloe need to create a table in the database so we will create a named "students" with columns "id" (PRIMARY KEY) and "name" (UNIQUE):

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) UNIQUE
);

Insert Data

----Start Inserting Data into the table:
INSERT INTO students (id, name) VALUES (1, 'Alice');
INSERT INTO students (id, name) VALUES (2, 'Bob');
INSERT INTO students (id, name) VALUES (3, 'Charlie');

Use INSERT IGNORE

-- Attempting to insert duplicate data
INSERT IGNORE INTO students (id, name) VALUES (4, 'Alice');  -- This row will be ignored due to duplicate name 'Alice'
INSERT IGNORE INTO students (id, name) VALUES (5, 'David');

-- Checking the data in the table
SELECT * FROM students;

Output:

Explanation

• In the example with the students table, the first three INSERT statements add new students named Alice, Bob and Charlie each with a unique ID.
  
• When trying to insert another student named Alice with an ID of 4, MySQL uses INSERT IGNORE to skip this entry because it would create a duplicate name.
  
• This means only the original three students (Alice, Bob, and Charlie) are added to the table. The final query shows only these three students, demonstrating that INSERT IGNORE successfully handled the duplicate and ensured that only unique records are kept in the table.

MySQL INSERT IGNORE and STRICT Mode

STRICT mode makes MySQL much stricter on data validation. Assume that you are going to insert some invalid data such as a string into the column of integers or a value that is going to violate one of the constraints.

MySQL stops insertion with an error and doesn't permit wrong data to get into our database.

For example

INSERT INTO users (id, age) VALUES (1, 'twenty-five');

In STRICT mode, MySQL will throw an error because 'twenty-five' is not a valid integer for the age column.

We can enable STRICT mode in MySQL by setting the sql_mode variable.

Conclusion

Overall, the INSERT IGNORE statement in MySQL efficiently handles data insertion by bypassing rows that cause duplicate key errors or constraint violations while inserting the valid ones. This approach helps maintain data integrity and simplifies the process of managing bulk data operations. By using `INSERT IGNORE`, developers can ensure smoother data imports and updates with fewer disruptions.