MySQL - REPLACE 查询

MySQL - REPLACE 语句

MySQL 的 REPLACE 语句用于向表中插入行。其行为取决于新行是否与 PRIMARY KEY（主键）或 UNIQUE（唯一）索引上的现有行冲突。

REPLACE 的工作原理

• 如果不存在键冲突： REPLACE 的行为与 INSERT 语句完全相同，向表中添加新行。
• 如果存在键冲突： REPLACE 会首先删除具有冲突键值的现有行，然后插入新行。它的功能等同于先执行 DELETE 再执行 INSERT。

语法

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

-- 或使用 SET 语法：
REPLACE INTO table_name
SET column1 = value1, column2 = value2, ...;

关键比较：REPLACE 与 INSERT ... ON DUPLICATE KEY UPDATE

在使用 REPLACE 之前，理解其替代方案 INSERT ... ON DUPLICATE KEY UPDATE（通常称为“upsert”）至关重要。它们并不相同，而且差异显著。

特性	REPLACE	INSERT … ON DUPLICATE KEY UPDATE
操作	删除 + 插入	插入或更新
触发器	触发 DELETE 触发器，然后触发 INSERT 触发器。	触发 INSERT 触发器（针对新行）或 UPDATE 触发器（针对现有行）。
自增 ID	如果表具有自增主键，则会生成新的 ID，因为旧行被删除。	现有的自增 ID 会被保留，因为该行是在原地更新的。
列值	旧行中的所有列都被移除。REPLACE 语句中未指定的列将设置为其默认值。	只有 UPDATE 子句中指定的列会发生更改。其他列保留其现有值。

最佳实践： 在大多数需要“插入或更新”行的情况下，INSERT ... ON DUPLICATE KEY UPDATE 是更安全、更直观的选择。仅当您明确打算完全移除旧行并创建新行，包括所有相关副作用（例如触发删除触发器或生成新的自增 ID）时，才使用 REPLACE。

示例

让我们设置一个 products 表，并在 sku 列上添加 UNIQUE 约束。

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2)
);

INSERT INTO products (sku, name, price) VALUES
('A-001', 'Gaming Mouse', 49.99),
('B-002', 'Mechanical Keyboard', 129.50);

示例 1：将 REPLACE 用作 INSERT

由于 SKU ‘C-003’ 不存在，此语句会作为简单的插入操作。

REPLACE INTO products (sku, name, price)
VALUES ('C-003', 'USB-C Hub', 35.00);

示例 2：使用 REPLACE 替换现有行

SKU ‘A-001’ 已存在。REPLACE 将删除原始的 ‘Gaming Mouse’ 行并插入新的行。如果您检查 id 列，您会发现它已被分配了一个新的自增值。

-- id=1 且 sku='A-001' 的旧行将被删除。
-- 一条新行被插入。
REPLACE INTO products (sku, name, price)
VALUES ('A-001', 'Wireless Gaming Mouse', 69.99);

示例 3：推荐的替代方案（INSERT ... ON DUPLICATE KEY UPDATE）

此查询尝试为 SKU ‘B-002’ 插入新行。因为它已经存在，所以它将转而执行 UPDATE 操作，只更改 price。id 将保持不变。

INSERT INTO products (sku, name, price)
VALUES ('B-002', 'Mechanical Keyboard', 119.99)
ON DUPLICATE KEY UPDATE
    price = VALUES(price); -- 使用 INSERT 部分的值更新 price。

在客户端程序中使用 REPLACE

以下是使用应用程序代码调用 REPLACE 和推荐的 INSERT ... ON DUPLICATE KEY UPDATE 的示例。

Python
NodeJS
Java
PHP



import mysql.connector

config = {
    'user': 'your_user',
    'password': 'your_password',
    'host': '127.0.0.1',
    'database': 'your_database'
}

# 使用 REPLACE
replace_query = "REPLACE INTO products (sku, name, price) VALUES (%s, %s, %s)"
replace_data = ('D-004', 'Webcam', 59.99)

# 使用 INSERT ... ON DUPLICATE KEY UPDATE（推荐）
upsert_query = """
    INSERT INTO products (sku, name, price) VALUES (%s, %s, %s)
    ON DUPLICATE KEY UPDATE name = VALUES(name), price = VALUES(price)
"""
upsert_data = ('D-004', 'HD Webcam', 65.00)

try:
    with mysql.connector.connect(**config) as connection:
        with connection.cursor() as cursor:
            cursor.execute(replace_query, replace_data)
            print(f"{cursor.rowcount} row(s) affected by REPLACE.")

            cursor.execute(upsert_query, upsert_data)
            # 如果行被更新，UPDATE 返回 2 个受影响的行
            print(f"{cursor.rowcount} row(s) affected by INSERT...ON DUPLICATE KEY UPDATE.")
except mysql.connector.Error as err:
    print(f"Error: {err}")



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

const pool = mysql.createPool({
    host: '127.0.0.1',
    user: 'your_user',
    password: 'your_password',
    database: 'your_database'
});

const manageProduct = async () => {
    const upsertQuery = `
        INSERT INTO products (sku, name, price)
        VALUES (?, ?, ?)
        ON DUPLICATE KEY UPDATE name = VALUES(name), price = VALUES(price)
    `;
    const productData = ['E-005', 'Monitor', 250.00];

    try {
        // 首先，插入产品
        let [result] = await pool.execute(upsertQuery, productData);
        console.log(`Initial insert/update affected ${result.affectedRows} row(s).`);

        // 现在，用新价格更新同一产品
        const updatedProductData = ['E-005', '4K Monitor', 399.99];
        [result] = await pool.execute(upsertQuery, updatedProductData);
        console.log(`Second insert/update affected ${result.affectedRows} row(s).`);
    } catch (error) {
        console.error('Query failed:', error);
    } finally {
        await pool.end();
    }
};

manageProduct();



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

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

    public static void main(String[] args) {
        String upsertQuery = """
            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(upsertQuery)) {

            // 为预处理语句设置值
            pstmt.setString(1, "F-006");
            pstmt.setString(2, "Laptop Stand");
            pstmt.setBigDecimal(3, new BigDecimal("25.50"));

            int affectedRows = pstmt.executeUpdate();
            System.out.printf("Query affected %d row(s).%n", affectedRows);

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



<?php
$host = '127.0.0.1';
$db   = 'your_database';
$user = 'your_user';
$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,
];

// 推荐的“upsert”方法
$query = "
    INSERT INTO products (sku, name, price)
    VALUES (:sku, :name, :price)
    ON DUPLICATE KEY UPDATE name = VALUES(name), price = VALUES(price)
";

$productData = [
    'sku' => 'G-007',
    'name' => 'Ergonomic Chair',
    'price' => 299.00
];

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

    $stmt = $pdo->prepare($query);
    $stmt->execute($productData);

    echo "Query executed. {$stmt->rowCount()} row(s) were affected.\n";

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

?>