MySQL INSERT IGNORE Statement

The INSERT IGNORE statement in MySQL is used to insert data into a table while ignoring rows that would cause errors. This is especially useful when dealing with duplicate key constraints, allowing the query to continue without stopping execution or throwing an error.

Syntax

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

table_name: The table where data is inserted.
column1, column2, ...: The columns where values are inserted.
value1, value2, ...: The values to insert into the specified columns.

How `INSERT IGNORE` Works

If there is no error, the row is inserted as usual.
If a duplicate key or other constraint violation occurs (e.g., UNIQUE or PRIMARY KEY), the row causing the issue is skipped.
Errors that are ignored:
- Duplicate entry for a UNIQUE or PRIMARY KEY constraint.
- Violations of constraints like NOT NULL (when a default value is set).

Examples

1. Avoid Duplicate Entries

Suppose you have a table users with the following structure:

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE
);

Insert data while avoiding duplicate entries:

INSERT IGNORE INTO users (id, username)
VALUES 
    (1, 'john_doe'),
    (2, 'jane_doe'),
    (1, 'john_doe'); -- This row will be ignored

Output:

Rows inserted: (1, 'john_doe') and (2, 'jane_doe').
Row (1, 'john_doe') is ignored due to the duplicate id.

2. Handle UNIQUE Constraints

If the email column in a table has a UNIQUE constraint, you can use INSERT IGNORE to skip duplicate email addresses.

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE
);

INSERT IGNORE INTO customers (email)
VALUES 
    ('alice@example.com'),
    ('bob@example.com'),
    ('alice@example.com'); -- Ignored

3. Use with Auto-Increment

When using an auto-increment column, INSERT IGNORE ensures that the insertion continues without interruption.

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(50) UNIQUE
);

INSERT IGNORE INTO orders (product_name)
VALUES 
    ('Laptop'),
    ('Phone'),
    ('Laptop'); -- Ignored

Behavior with Constraints

PRIMARY KEY: Rows with duplicate PRIMARY KEY values are ignored.
UNIQUE: Rows with duplicate UNIQUE values are skipped.
NOT NULL: If a column has a NOT NULL constraint and a NULL value is inserted, it will fail unless a default value is provided.

Key Considerations

No Feedback for Ignored Rows: MySQL does not provide feedback on which rows were ignored.
- Use the ROW_COUNT() function to check how many rows were inserted:
```
SELECT ROW_COUNT();
```
Performance Impact: Although INSERT IGNORE prevents errors, it may slow down performance for large datasets due to additional checks.
Error Handling: Use INSERT IGNORE cautiously; it suppresses errors, which may make debugging harder.

Alternatives to `INSERT IGNORE`

ON DUPLICATE KEY UPDATE: Updates existing rows if a duplicate key conflict occurs.

INSERT INTO users (id, username)
VALUES (1, 'john_doe')
ON DUPLICATE KEY UPDATE username = 'john_doe_updated';

Manual Check for Duplicates: Check for duplicates before insertion:
```
SELECT * FROM users WHERE id = 1;
```

Conclusion

The INSERT IGNORE statement is a powerful tool for ensuring that insert operations continue without interruption, even in the presence of constraints like UNIQUE and PRIMARY KEY. It simplifies handling duplicate entries but should be used carefully to avoid unintentionally ignoring critical data.

Souy Soeng

MySQL INSERT IGNORE Statement