Insert Ignore in MySQL        

Introduction

When inserting data into a table, you may sometimes face errors such as:

  • Duplicate primary key
  • Duplicate unique values
  • Constraint violations

In such cases, MySQL provides the INSERT IGNORE statement, which allows you to skip problematic rows instead of stopping the query.

What is INSERT IGNORE

The INSERT IGNORE statement works like INSERT, but:

  • It ignores rows that cause errors
  • It continues inserting the remaining valid rows

This helps prevent query failure due to minor issues.

Basic Syntax

INSERT IGNORE INTO table_name (column1, column2)

VALUES (value1, value2);

Example

INSERT IGNORE INTO Students (id, name)

VALUES (1, 'Rahul');

If id = 1 already exists:

  • MySQL will ignore this row
  • No error will be thrown

Insert Multiple Rows with IGNORE

INSERT IGNORE INTO Students (id, name)

VALUES

(1, 'Rahul'),

(2, 'Aisha'),

(3, 'Aman');

If id = 1 exists:

  • It will be skipped
  • Other rows will be inserted

Behaviour of INSERT IGNORE

  • Duplicate key errors are ignored
  • Invalid values may be adjusted to the default
  • Warnings are generated instead of errors

Example Scenario

Suppose your table has:

id

name

1

Rahul

Query:

INSERT IGNORE INTO Students (id, name)

VALUES (1, 'Rahul Sharma'), (2, 'Neha');

Result:

  • Row with id = 1 → ignored
  • Row with id = 2 → inserted

When to Use INSERT IGNORE

  • Bulk data insertion
  • Avoiding duplicate errors
  • Importing data from external sources
  • Skipping invalid rows

Important Notes

  • It does not update existing rows
  • It silently ignores errors
  • Use carefully to avoid unnoticed data issues

Common Mistakes

  • Expecting updates (it only skips)
  • Ignoring important errors unintentionally
  • Not checking warnings

Key Points to Remember

  • INSERT IGNORE skips problematic rows
  • Prevents query failure
  • Useful for bulk inserts
  • Does not modify existing data
  • Generates warnings instead of errors