MySQL - INSERT IGNORE

MySQL：插入时处理重复数据

将数据插入 MySQL 表时，您经常会遇到行可能违反 UNIQUE 或 PRIMARY KEY 约束的情况。默认情况下，MySQL 会拒绝整个 INSERT 语句并返回错误，阻止该语句中的任何行被插入。本教程将探讨处理这些场景的现代有效方法，重点关注 INSERT IGNORE 及其强大的替代方案。

`INSERT IGNORE` 语句

INSERT IGNORE 语句指示 MySQL 执行插入操作，但静默丢弃任何会导致重复键冲突的行。MySQL 不会返回错误，而是发出警告。这对于批量加载数据非常有用，因为您可能预期存在一些重复项，并且乐于直接跳过它们。

语法

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

示例：基本用法

首先，我们创建一个 users 表，并在 email 列上添加 UNIQUE 约束。

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;

现在，让我们插入一些初始数据：

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

如果我们尝试插入一个邮箱已存在的用户，语句会失败：

-- 这会失败
INSERT INTO users (username, email) VALUES ('cathy', 'cathy@example.com'), ('alex_new', 'alex@example.com');

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

使用 INSERT IGNORE 时，有效行会被插入，而重复行则被跳过：

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

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

我们可以检查警告以查看发生了什么：

SHOW WARNINGS;

级别	代码	消息
Warning	1062	Duplicate entry ‘alex@example.com’ for key ‘users.email’

验证表后显示，‘cathy’ 已添加，但 ‘alex_new’ 被忽略了：

SELECT * FROM users;

-- 输出:
+----+----------+---------------------+---------------------+
| 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 |
+----+----------+---------------------+---------------------+

`INSERT IGNORE` 的现代替代方案

虽然 INSERT IGNORE 很有用，但通常最好有更明确的控制。现代 MySQL 提供了强大的替代方案。

1. `INSERT ... ON DUPLICATE KEY UPDATE`

这是最常见和最灵活的方法。如果一行会导致重复键冲突，它会转而对现有行执行 UPDATE 操作。这对于“upsert”（更新或插入）逻辑非常理想。

-- 尝试插入 'david' 并更新 'alex'
INSERT INTO users (username, email) VALUES ('david', 'david@example.com'), ('alex_updated', 'alex@example.com')
ON DUPLICATE KEY UPDATE username = VALUES(username);

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

VALUES(username) 指的是我们尝试插入的行中的 username。现在表中显示 ‘david’ 已插入，‘alex’ 已更新。

2. `REPLACE INTO`

REPLACE INTO 的工作原理是先尝试 INSERT。如果因重复键而失败，它会 DELETE 冲突的行，然后 INSERT 新行。注意： 这是一种破坏性操作。它会改变 id（如果它是 AUTO_INCREMENT）并触发 DELETE 和 INSERT 触发器，这可能会产生意想不到的副作用。

-- 这将删除原始的 'brian' 行 (id=2) 并插入新行。
REPLACE INTO users (username, email) VALUES ('brian_new', 'brian@example.com');

-- 新的 'brian_new' 行将拥有一个新的自增 ID。

方法比较

语句	遇到重复时的行为	最佳应用场景
`INSERT IGNORE`	跳过新行，保持现有行不变。	批量加载数据，其中重复项可以安全地丢弃。
`ON DUPLICATE KEY UPDATE`	使用新值更新现有行。	“Upsert”逻辑：同步数据、更新配置文件等。
`REPLACE INTO`	删除现有行并插入新行。	极少数需要完全替换行的情况。谨慎使用。

`INSERT IGNORE` 与严格 SQL 模式

在现代 MySQL 版本中，严格 SQL 模式（STRICT_TRANS_TABLES 或 STRICT_ALL_TABLES）默认启用。此模式会导致无效数据（例如插入过长的字符串到 VARCHAR 列）产生错误。INSERT IGNORE 将抑制这些错误，而是截断数据以适应，并生成警告。

-- 假设有一个表：CREATE TABLE test (name VARCHAR(5));
-- 在严格模式下，这通常会导致错误。
INSERT IGNORE INTO test (name) VALUES ('Spongebob');

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

-- 表中将包含截断后的值 'Spongeb'。

虽然这避免了错误，但会导致数据丢失。通常，最好在插入之前清理您的数据，而不是依赖 IGNORE 来截断它。

实际应用：在代码中使用 `INSERT IGNORE`

以下是使用 INSERT IGNORE 的现代、安全的编程语言示例。请注意使用参数化查询以防止 SQL 注入。

Node.js (使用 `mysql2/promise`)

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

async function addUsers(users) {
  let connection;
  try {
    // 在实际应用中，最好使用连接池
    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' } // 这是一个重复项
];

addUsers(newUsers);

Python (使用 `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)'
                # executemany 方法对于批量插入效率很高
                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'} # 重复项
]

add_users(new_users)

PHP (使用 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);

        // 使用预处理语句构建多行插入
        $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'] // 重复项
];

addUsers($newUsers);
?>

MySQL - INSERT IGNORE

MySQL：插入时处理重复数据

INSERT IGNORE 语句

语法

示例：基本用法

INSERT IGNORE 的现代替代方案

1. INSERT ... ON DUPLICATE KEY UPDATE

2. REPLACE INTO