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:

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

----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');

-- 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;

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