MySQL - Insert Ignore

MySQL: Handling Duplicate Data on Insert

When inserting data into a MySQL table, you often encounter situations where a row might violate a UNIQUE or PRIMARY KEY constraint. By default, MySQL will reject the entire INSERT statement and return an error, preventing any rows in that statement from being inserted. This tutorial explores modern and effective ways to handle these scenarios, focusing on INSERT IGNORE and its powerful alternatives.

The INSERT IGNORE Statement

The INSERT IGNORE statement instructs MySQL to proceed with an insertion but to silently discard any rows that would cause a duplicate-key violation. Instead of an error, MySQL issues a warning. This is useful for bulk-loading data where you expect some duplicates and are content to simply skip them.

Syntax

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

Example: Basic Usage

First, let’s create a users table with a UNIQUE constraint on the email column.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

Now, let’s insert some initial data:

INSERT INTO users (username, email) VALUES
('alex', 'alex@example.com'),
('brian', 'brian@example.com');

If we try to insert a new user with an existing email, the statement fails:

-- This will fail
INSERT INTO users (username, email) VALUES ('cathy', 'cathy@example.com'), ('alex_new', 'alex@example.com');

-- Output:
-- ERROR 1062 (23000): Duplicate entry 'alex@example.com' for key 'users.email'

Using INSERT IGNORE, the valid row is inserted, and the duplicate is skipped:

INSERT IGNORE INTO users (username, email) VALUES ('cathy', 'cathy@example.com'), ('alex_new', 'alex@example.com');

-- Output:
-- Query OK, 1 row affected, 1 warning (0.01 sec)
-- Records: 2  Duplicates: 1  Warnings: 1

We can inspect the warning to see what happened:

SHOW WARNINGS;

Level	Code	Message
Warning	1062	Duplicate entry ‘alex@example.com’ for key ‘users.email’

Verifying the table shows that ‘cathy’ was added, but ‘alex_new’ was ignored:

SELECT * FROM users;

-- Output:
+----+----------+---------------------+---------------------+
| id | username | email               | created_at          |
+----+----------+---------------------+---------------------+
| 1  | alex     | alex@example.com    | 2023-10-27 10:00:00 |
| 2  | brian    | brian@example.com   | 2023-10-27 10:00:00 |
| 3  | cathy    | cathy@example.com   | 2023-10-27 10:01:00 |
+----+----------+---------------------+---------------------+

Modern Alternatives to INSERT IGNORE

While INSERT IGNORE is useful, it’s often better to have more explicit control. Modern MySQL offers powerful alternatives.

1. INSERT ... ON DUPLICATE KEY UPDATE

This is the most common and flexible approach. If a row would cause a duplicate-key violation, it performs an UPDATE on the existing row instead. This is ideal for “upsert” (update or insert) logic.

-- Attempt to insert 'david' and update 'alex'
INSERT INTO users (username, email) VALUES ('david', 'david@example.com'), ('alex_updated', 'alex@example.com')
ON DUPLICATE KEY UPDATE username = VALUES(username);

-- Output:
-- Query OK, 3 rows affected (0.01 sec)
-- Records: 2  Duplicates: 1  Warnings: 0

VALUES(username) refers to the username from the row we attempted to insert. The table now shows ‘david’ inserted and ‘alex’ updated.

2. REPLACE INTO

REPLACE INTO works by first trying an INSERT. If that fails due to a duplicate key, it performs a DELETE on the conflicting row and then INSERTs the new row. Caution: This is a destructive operation. It changes the id (if it’s AUTO_INCREMENT) and triggers both DELETE and INSERT triggers, which can have unintended side effects.

-- This will delete the original 'brian' row (id=2) and insert a new one.
REPLACE INTO users (username, email) VALUES ('brian_new', 'brian@example.com');

-- The new 'brian_new' row will have a new auto-incremented id.

Comparison of Approaches

Statement	Behavior on Duplicate	Best For
INSERT IGNORE	Skips the new row, leaves the existing row untouched.	Bulk-loading data where duplicates can be safely discarded.
ON DUPLICATE KEY UPDATE	Updates the existing row with new values.	”Upsert” logic: synchronizing data, updating profiles, etc.
REPLACE INTO	Deletes the existing row and inserts the new row.	Rare cases where a complete row replacement is needed. Use with caution.

INSERT IGNORE and Strict SQL Mode

In modern MySQL versions, strict SQL mode (STRICT_TRANS_TABLES or STRICT_ALL_TABLES) is enabled by default. This mode causes errors for invalid data, such as inserting a string that’s too long for a VARCHAR column. INSERT IGNORE will suppress these errors and instead truncate the data to fit, generating a warning.

-- Assume a table: CREATE TABLE test (name VARCHAR(5));
-- This would normally cause an error in strict mode.
INSERT IGNORE INTO test (name) VALUES ('Spongebob');

-- Output:
-- Query OK, 1 row affected, 1 warning (0.01 sec)

-- The table will contain the truncated value 'Spongeb'.

While this avoids an error, it leads to data loss. It’s generally better to clean your data before insertion rather than relying on IGNORE to truncate it.

Practical Application: Using INSERT IGNORE in Code

Here are modern, secure examples of using INSERT IGNORE in various programming languages. Note the use of parameterized queries to prevent SQL injection.

Node.js (with mysql2/promise)

const mysql = require('mysql2/promise');

async function addUsers(users) {
  let connection;
  try {
    // It's best practice to use a connection pool in real applications
    connection = await mysql.createConnection({
      host: process.env.DB_HOST || 'localhost',
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || 'password',
      database: 'your_database_name'
    });

    const sql = 'INSERT IGNORE INTO users (username, email) VALUES ?';
    const values = users.map(user => [user.username, user.email]);

    const [result] = await connection.execute(sql, [values]);

    console.log(`Query finished. ${result.affectedRows} new user(s) added.`);
    if (result.warningStatus > 0) {
        console.log(`${result.warningStatus} warning(s) occurred (e.g., duplicate entries ignored).`);
    }

  } catch (error) {
    console.error('Database operation failed:', error);
  } finally {
    if (connection) await connection.end();
  }
}

const newUsers = [
  { username: 'emily', email: 'emily@example.com' },
  { username: 'alex_ignored', email: 'alex@example.com' } // This one is a duplicate
];

addUsers(newUsers);

Python (with mysql-connector-python)

import mysql.connector
import os

def add_users(users):
    try:
        with mysql.connector.connect(
            host=os.getenv('DB_HOST', 'localhost'),
            user=os.getenv('DB_USER', 'root'),
            password=os.getenv('DB_PASSWORD', 'password'),
            database='your_database_name'
        ) as connection:
            with connection.cursor() as cursor:
                sql = 'INSERT IGNORE INTO users (username, email) VALUES (%s, %s)'
                # The executemany method is efficient for bulk inserts
                values = [(user['username'], user['email']) for user in users]

                cursor.executemany(sql, values)
                connection.commit()

                print(f'{cursor.rowcount} new user(s) added.')
                if cursor.warning_count > 0:
                    print(f'{cursor.warning_count} warning(s) occurred.')

    except mysql.connector.Error as err:
        print(f'Database error: {err}')

new_users = [
    {'username': 'frank', 'email': 'frank@example.com'},
    {'username': 'brian_ignored', 'email': 'brian@example.com'} # Duplicate
]

add_users(new_users)

PHP (with PDO)

<?php

function addUsers(array $users)
{
    $host = getenv('DB_HOST') ?: 'localhost';
    $db   = 'your_database_name';
    $user = getenv('DB_USER') ?: 'root';
    $pass = getenv('DB_PASSWORD') ?: 'password';
    $charset = 'utf8mb4';

    $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
    $options = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ];

    try {
        $pdo = new PDO($dsn, $user, $pass, $options);

        // Building a multi-row insert with prepared statements
        $sql = 'INSERT IGNORE INTO users (username, email) VALUES ';
        $insertValues = [];
        $params = [];
        foreach ($users as $user) {
            $insertValues[] = '(?, ?)';
            array_push($params, $user['username'], $user['email']);
        }

        $sql .= implode(', ', $insertValues);
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);

        echo "Query finished. {$stmt->rowCount()} new user(s) added.\n";

    } catch (PDOException $e) {
        throw new PDOException($e->getMessage(), (int)$e->getCode());
    }
}

$newUsers = [
    ['username' => 'grace', 'email' => 'grace@example.com'],
    ['username' => 'cathy_ignored', 'email' => 'cathy@example.com'] // Duplicate
];

addUsers($newUsers);
?>