MySQL - INSERT ON DUPLICATE KEY UPDATE

MySQL: INSERT ON DUPLICATE KEY UPDATE

在现代应用程序开发中，我们经常需要向数据库插入一条新记录，但如果具有相同唯一标识符的记录已经存在，我们则希望更新它。这种常见的操作被称为“upsert”（UPDATE 和 INSERT 的合成词）。MySQL 提供了强大且高效的方式来处理此操作，即使用 INSERT ... ON DUPLICATE KEY UPDATE 语句。

本教程将指导你了解在 MySQL 中直接以及通过各种编程语言执行 upsert 操作的语法、用法和最佳实践。

核心概念：处理唯一键约束

当你尝试向表中插入一行数据，而这会导致 PRIMARY KEY 或 UNIQUE 索引列中出现重复值时，MySQL 通常会抛出错误（ERROR 1062: Duplicate entry）。这是数据库强制执行数据完整性（data integrity）的方式。

ON DUPLICATE KEY UPDATE 子句扩展了 INSERT 语句，提供了一种替代操作。MySQL 不会因错误而失败，而是对导致冲突的现有行执行指定的 UPDATE 操作。

语法

该语句的基本语法如下：

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
  column1 = new_value1,
  column2 = new_value2, ...;

要在 UPDATE 子句中引用你尝试插入的值，你可以使用 VALUES() 函数，或者在 MySQL 8.0.20 及更高版本中，使用行别名（row alias）以提高可读性。

-- Using VALUES()
ON DUPLICATE KEY UPDATE
  column_to_update = VALUES(column_to_update);

-- Using a row alias (MySQL 8.0.20+)
INSERT INTO table_name (c1, c2) VALUES (1, 2), (3, 4) AS new_values
ON DUPLICATE KEY UPDATE
  c2 = new_values.c2;

示例：单行 Upsert

让我们从一个实际例子开始。首先，创建一个 products 表，其中每个产品都有一个唯一的 SKU。

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

现在，让我们插入一些初始产品：

INSERT INTO products (sku, name, price) VALUES
('TSHIRT-RED-L', 'Red T-Shirt (Large)', 19.99),
('MUG-COFFEE-1', 'Standard Coffee Mug', 12.50);

如果我们尝试插入一个具有现有 SKU 的产品，我们会得到一个错误：

INSERT INTO products (sku, name, price)
VALUES ('MUG-COFFEE-1', 'Branded Coffee Mug', 14.99);

-- Result:
-- ERROR 1062 (23000): Duplicate entry 'MUG-COFFEE-1' for key 'products.sku'

现在，让我们使用 ON DUPLICATE KEY UPDATE 来更新价格和名称：

INSERT INTO products (sku, name, price)
VALUES ('MUG-COFFEE-1', 'Branded Coffee Mug', 14.99)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    price = VALUES(price);

理解 affected-rows（受影响行数）计数

此查询返回的受影响行数提供了有用的反馈：

• 1 行受影响： 插入了一条新行。
• 2 行受影响： 更新了现有行。这看起来可能不直观，但 MySQL 将旧行的删除和新行的插入算作两个操作。
• 0 行受影响： 匹配到现有行，但更新未导致其数据发生任何更改。

验证

让我们检查表格，查看更新后的记录。

SELECT * FROM products WHERE sku = 'MUG-COFFEE-1';

id	sku	name	price	last_updated
2	MUG-COFFEE-1	Branded Coffee Mug	14.99	2023-10-27 10:30:00

示例：多行 Upsert

VALUES() 函数在同时插入多行时特别有用，因为它正确引用了导致重复键冲突的特定行的值。

INSERT INTO products (sku, name, price)
VALUES
    ('MUG-COFFEE-1', 'Branded Coffee Mug (New!)', 15.50), -- This one will update
    ('HAT-BLUE-OS', 'Blue Baseball Cap', 24.99)             -- This one will insert
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    price = VALUES(price);

执行此查询后，MUG-COFFEE-1 记录将更新为新的名称和价格，并且会插入一条 HAT-BLUE-OS 的新记录。查询将报告 3 行受影响（1 条新记录插入 + 1 条现有记录更新）。

在你的应用程序中实现 Upsert

在应用程序中使用 ON DUPLICATE KEY UPDATE 很简单。关键是使用预处理语句（prepared statements）（或参数化查询）以防止 SQL 注入漏洞。下面是流行语言的现代最佳实践示例。

应用程序代码的先决条件

在运行示例之前，请确保你已安装必要的数据库驱动程序：

• Node.js： npm install mysql2
• Python： pip install mysql-connector-python
• Java： 将 mysql-connector-java 依赖项添加到你的 pom.xml (Maven) 或 build.gradle (Gradle) 中。
• PHP： PDO_MySQL 扩展通常在现代 PHP 安装中默认启用。

Node.js (使用 async/await 和 mysql2)

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

async function upsertProduct(product) {
    let connection;
    try {
        connection = await mysql.createConnection({
            host: 'localhost',
            user: 'root',
            password: 'your_password',
            database: 'your_database'
        });

        const sql = `
            INSERT INTO products (sku, name, price)
            VALUES (?, ?, ?)
            ON DUPLICATE KEY UPDATE
                name = VALUES(name),
                price = VALUES(price);
        `;

        const [result] = await connection.execute(sql, [product.sku, product.name, product.price]);

        if (result.affectedRows === 1) {
            console.log(`Product ${product.sku} inserted with ID: ${result.insertId}`);
        } else if (result.affectedRows === 2) {
            console.log(`Product ${product.sku} was updated.`);
        } else {
            console.log(`Product ${product.sku} already existed with the same data.`);
        }

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

// Example usage
upsertProduct({ sku: 'TSHIRT-RED-L', name: 'Red T-Shirt (Large)', price: 21.99 }); // Should update
upsertProduct({ sku: 'SOCKS-BLK-M', name: 'Black Socks (Medium)', price: 9.99 }); // Should insert

Python (使用 mysql-connector-python)

import mysql.connector
from mysql.connector import errorcode

def upsert_product(config, product):
    try:
        with mysql.connector.connect(**config) as connection:
            with connection.cursor(prepared=True) as cursor:
                sql = """
                    INSERT INTO products (sku, name, price)
                    VALUES (%s, %s, %s)
                    ON DUPLICATE KEY UPDATE
                        name = VALUES(name),
                        price = VALUES(price);
                """
                cursor.execute(sql, (product['sku'], product['name'], product['price']))
                connection.commit()
                print(f"Upsert operation successful for SKU: {product['sku']}. Rows affected: {cursor.rowcount}")

    except mysql.connector.Error as err:
        print(f"Error: {err}")

# --- Usage ---
DB_CONFIG = {
    'user': 'root',
    'password': 'your_password',
    'host': '127.0.0.1',
    'database': 'your_database'
}

product_to_update = {'sku': 'TSHIRT-RED-L', 'name': 'Red T-Shirt (Large)', 'price': 21.99}
product_to_insert = {'sku': 'SOCKS-BLK-M', 'name': 'Black Socks (Medium)', 'price': 9.99}

upsert_product(DB_CONFIG, product_to_update)
upsert_product(DB_CONFIG, product_to_insert)

Java (使用 JDBC 和 try-with-resources)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class ProductUpsert {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USER = "root";
    private static final String PASS = "your_password";

    public void upsertProduct(String sku, String name, double price) {
        String sql = "INSERT INTO products (sku, name, price) "
                   + "VALUES (?, ?, ?) "
                   + "ON DUPLICATE KEY UPDATE name = VALUES(name), price = VALUES(price)";

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, sku);
            pstmt.setString(2, name);
            pstmt.setDouble(3, price);

            int affectedRows = pstmt.executeUpdate();

            if (affectedRows > 0) {
                System.out.println("Upsert successful for SKU: " + sku);
            } else {
                System.out.println("No changes made for SKU: " + sku);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        ProductUpsert dao = new ProductUpsert();
        dao.upsertProduct("MUG-COFFEE-1", "Premium Branded Mug", 17.99); // Update
        dao.upsertProduct("KEYCHAIN-LOGO-1", "Company Logo Keychain", 5.99); // Insert
    }
}

PHP (使用 PDO)

<?php
function upsertProduct(string $sku, string $name, float $price) {
    $host = '127.0.0.1';
    $db   = 'your_database';
    $user = 'root';
    $pass = 'your_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,
    ];

    $sql = "INSERT INTO products (sku, name, price)
            VALUES (:sku, :name, :price)
            ON DUPLICATE KEY UPDATE
                name = VALUES(name),
                price = VALUES(price);";

    try {
        $pdo = new PDO($dsn, $user, $pass, $options);
        $stmt = $pdo->prepare($sql);
        $stmt->execute(['sku' => $sku, 'name' => $name, 'price' => $price]);

        echo "Upsert operation successful for SKU: $sku. Rows affected: " . $stmt->rowCount() . "\n";

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

// Example usage
upsertProduct('MUG-COFFEE-1', 'Premium Branded Mug', 17.99); // Update
upsertProduct('KEYCHAIN-LOGO-1', 'Company Logo Keychain', 5.99); // Insert
?>

常见陷阱和最佳实践

• 缺少唯一/主键： 如果你的 INSERT 语句中的列没有定义 PRIMARY KEY 或 UNIQUE 索引，ON DUPLICATE KEY UPDATE 将不起作用。查询每次都会简单地插入一条新行。
• 性能： 这个语句通常比执行 SELECT 后跟 INSERT 或 UPDATE 要快得多，因为它节省了一次到数据库服务器的往返。然而，在写入竞争（write contention）高的表上，它仍然可能导致锁定。务必在相关环境中进行基准测试。
• 使用别名： 对于 MySQL 8.0.20+，优先使用行别名（... AS new_values ON DUPLICATE KEY UPDATE col = new_values.col），因为这使得复杂的多行插入比 VALUES(col) 更具可读性。
• 幂等性： 这种模式非常适合创建幂等（idempotent）API，即多次发出相同的请求与发出一次请求的效果相同。