INSERT IGNORE (MySQL) and INSERT OR IGNORE (SQLite) are extensions that suppress duplicate key, data truncation, and other non-fatal errors, converting them to warnings and allowing the batch to continue.
INSERT OR IGNORE INTO Employee (EmployeeID, Name, City) VALUES (15002, 'Ram', 'Mumbai');If EmployeeID 15002 already exists, the row is skipped without error; a warning is generated. Use SHOW WARNINGS; to inspect the ignored duplicate.
Examples
Multi-Row Insert with Duplicates
INSERT OR IGNORE INTO Employee (EmployeeID, Name, City)
VALUES (15007, 'Shikha', 'Delhi'),
(15002, 'Ram', 'Mumbai'),
(15009, 'Sam', 'Ahmedabad');Rows 15007 and 15009 are inserted; row 15002 is silently skipped. After executing, run SELECT * FROM Employee WHERE EmployeeID IN (15007,15002,15009); to confirm.
Bulk Load with IGNORE and Warning Check
INSERT IGNORE INTO logs (id, message)
SELECT id, message FROM staging_logs;
SHOW WARNINGS;This imports all non-duplicate rows from staging_logs. The SHOW WARNINGS statement reveals how many rows were skipped and why (duplicate key, data truncation, etc.). Essential for data reconciliation after bulk loads.
Troubleshooting & Common Errors
| Symptom / Error | Root Cause | Resolution Command |
|---|---|---|
| Rows missing without error | Duplicates silently ignored; no error raised | SHOW WARNINGS; immediately after INSERT |
| AUTO_INCREMENT gaps | IGNORE allocates auto_increment even for skipped rows | Sequence gaps are inherent; use SELECT MAX(id) for pagination |
| Data integrity issues | Truncation/typecast errors suppressed | Review warnings; use strict mode (SET sql_mode = 'STRICT_TRANS_TABLES') to catch errors |
| Performance degradation | High duplicate ratio causes many internal skipped rows | Pre-filter with NOT EXISTS or use ON DUPLICATE KEY UPDATE for updates |
Error Codes Converted to Warnings
| MySQL Error Code | Meaning | Behavior with IGNORE |
|---|---|---|
| 1062 | Duplicate entry for UNIQUE/PRIMARY KEY | Silently skipped; warning issued |
| 1265 | Data truncated for column | Row inserted with truncated value; warning generated |
| 1366 | Incorrect integer value | Column set to default value; warning emitted |
| 1264 | Out of range value | Value clamped to range; warning produced |
Performance Considerations
Using IGNORE_DUP_KEY on the primary key (SQL Server) or INSERT IGNORE (MySQL) can outperform DISTINCT or NOT EXISTS because duplicate detection occurs at the index level without additional query overhead. To measure real I/O, clear the buffer cache with DBCC DROPCLEANBUFFERS and enable IO statistics (SET STATISTICS IO ON). For MySQL, batch sizes of 100–500 rows per statement reduce round-trips. After a bulk insert, always run SHOW WARNINGS to identify skipped rows and adjust batch size accordingly.
Frequently Asked Questions
What is the difference between INSERT IGNORE and INSERT … ON DUPLICATE KEY UPDATE?
Answer: INSERT IGNORE silently discards duplicate rows; ON DUPLICATE KEY UPDATE performs an update when a duplicate key is encountered.
INSERT IGNORE suppresses errors like duplicate key, foreign key, or unique constraint violations and converts them to warnings. In contrast, ON DUPLICATE KEY UPDATE executes an alternative UPDATE statement on the conflicting existing row. Choose based on whether you need to retain ignored rows or update them.
When should I use the `INSERT IGNORE` statement over a regular INSERT?
Answer: Use INSERT IGNORE when you want to insert rows but silently skip duplicates or other non-fatal errors without aborting the batch.
Typical use-cases include bulk data loading where some rows may violate primary keys, unique constraints, or foreign keys. INSERT IGNORE converts these errors into warnings, allowing the rest of the batch to succeed. Check the warning count via `SHOW WARNINGS;` to review skipped rows.
How do I fix “ERROR 1062 (23000): Duplicate entry” when using INSERT IGNORE?
Answer: Ensure your MySQL version supports INSERT IGNORE (all versions) and verify the statement syntax is correct.
If the error appears, the statement is likely not using the IGNORE keyword or is being run in a context where IGNORE is ignored (e.g., within a transaction with certain isolation levels). Confirm:
mysql -e "INSERT IGNORE INTO t (id) VALUES (1);"If duplicate key exists, no error prints; use `SHOW WARNINGS;` to see the ignored entry.
Does `INSERT IGNORE` work on PostgreSQL or SQL Server?
Answer: No.
For equivalent behavior on PostgreSQL:
psql -c "INSERT INTO t VALUES (1) ON CONFLICT (id) DO NOTHING;"On SQL Server, you cannot suppress per-row errors in bulk without dynamic SQL or table-level error handling like `MERGE` with `WHEN NOT MATCHED`.
What is the fastest way to insert millions of rows with `INSERT IGNORE`?
Answer: Use multi-row INSERT syntax combined with `INSERT IGNORE` and temporarily disable non-unique indexes to maximize speed.
Example for bulk loading from a file:
mysql -u root -p mydb -e "
ALTER TABLE t DISABLE KEYS;
LOAD DATA INFILE 'data.csv' IGNORE INTO TABLE t;
ALTER TABLE t ENABLE KEYS;"Use `LOAD DATA INFILE` with the IGNORE keyword (alternate syntax) for massive inserts. For large INSERT statements, batch 1000 rows per statement and use `INSERT IGNORE` to handle duplicates without aborting.
Command Line Expert & Software Engineer
Welcome! I’m Thomas Heinrich, a software engineer and system administrator with a deep passion for the Command Line Interface (CLI). With years of experience navigating the terminal, building backend architectures, and automating server deployments, I created this space to share practical, real-world terminal knowledge.
Whether you are a beginner taking your first steps in a Linux environment or a seasoned DevOps engineer looking to optimize your deployment scripts, you will find actionable solutions here. My goal is to help you ditch the mouse, speed up your workflow, and harness the full power of the command line.