SQL IGNORE

What is the IGNORE keyword in MySQL?

Modifier that tells certain MySQL statements to skip rows that would normally raise errors and continue processing.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL IGNORE: Supported: MySQL, MariaDB Not supported: PostgreSQL, SQL Server, Oracle, SQLite, Snowflake

SQL IGNORE Full Explanation

IGNORE is not a standalone command but an optional modifier supported by MySQL (and MariaDB) for INSERT, UPDATE, DELETE, LOAD DATA, ALTER TABLE, CREATE TABLE ... SELECT, and LOAD XML. When present, MySQL suppresses specific run-time errors (typically duplicate-key, foreign-key, or data-conversion violations) and continues executing the statement. Conflicting rows are discarded, and warnings are recorded in the client session (retrievable with SHOW WARNINGS). IGNORE is respected only if the server is not running in STRICT mode (sql_mode contains STRICT_*) or if strict checks are selectively disabled. If strict mode is enabled, IGNORE silently downgrades many errors to warnings but still aborts on problems that cannot be ignored (for example, out-of-range auto-increment values in InnoDB). Because IGNORE discards problematic rows, it can hide data issues; use with caution in production environments where data integrity is critical.

SQL IGNORE Syntax

INSERT IGNORE INTO table_name (col1,col2,...) VALUES (...);

UPDATE IGNORE table_name SET col1 = value WHERE ...;

DELETE IGNORE FROM table_name WHERE ...;

LOAD DATA INFILE 'file.csv' IGNORE INTO TABLE table_name;

ALTER IGNORE TABLE table_name ADD UNIQUE (col1);

SQL IGNORE Parameters

Example Queries Using SQL IGNORE

-- Skip duplicate primary keys while bulk-inserting
INSERT IGNORE INTO users(id,email) VALUES (1,'a@b.com'),(2,'c@d.com');

-- Attempt to enforce a new UNIQUE constraint without failing on duplicates
ALTER IGNORE TABLE orders ADD UNIQUE (order_number);

-- Keep updating even if some rows hit a foreign-key error
UPDATE IGNORE line_items SET price = price * 1.1 WHERE order_id = 999;

Expected Output Using SQL IGNORE

Statements complete without fatal errors
Rows that violate constraints are not written or modified; the affected row count excludes them
SHOW WARNINGS lists each skipped row with the reason

Use Cases with SQL IGNORE

Bulk imports where occasional duplicate keys are acceptable
Schema migrations that add UNIQUE or PRIMARY KEY constraints to tables containing dirty data
ETL pipelines that should continue processing despite bad records
Legacy applications that cannot easily be cleaned but need new constraints added gradually

Common Mistakes with SQL IGNORE

Expecting IGNORE to work in PostgreSQL or SQL Server (it is MySQL-only)
Assuming IGNORE overrides all constraint checks; some errors still stop execution
Forgetting that skipped rows are lost, leading to silent data gaps
Using IGNORE while sql_mode is STRICT and wondering why behavior differs
Believing that affected-rows count includes discarded rows

First Introduced In

MySQL 3.23

Frequently Asked Questions

What is the difference between IGNORE and REPLACE?

REPLACE deletes the conflicting row and reinserts the new one, which can reset auto-increment values and trigger ON DELETE cascades. IGNORE simply skips the new row, leaving the existing data untouched.

Can I use IGNORE with DELETE?

Yes. DELETE IGNORE suppresses foreign-key constraint errors and deletes rows that can be removed, skipping those that cannot.

How do I know how many rows were skipped?

The client returns an affected-rows number that excludes ignored rows. Run SHOW WARNINGS to see each skipped row.

Does IGNORE slow down queries?

A small overhead exists for generating warnings, but it is usually negligible compared to the cost of the data operation itself.