mysql-insert-on-duplicate-key-update

MySQL: ON DUPLICATE KEY UPDATE 的“插入或更新”操作

在应用程序开发中，一个常见的挑战是需要插入新行，但如果存在具有相同唯一键的行，则改为更新它。这通常称为“插入或更新”（upsert）操作。将其作为单独的 SELECT 后跟 INSERT 或 UPDATE 执行效率低下，并可能在并发环境中导致竞态条件。MySQL 提供了一个强大的原子解决方案：ON DUPLICATE KEY UPDATE 子句。

核心概念

您将此子句与标准 INSERT 语句一起使用。要使其工作，表必须具有 PRIMARY KEY 或 UNIQUE 索引。当您尝试插入一行，该行会导致该键或索引中出现重复值时，将执行该子句的 UPDATE 部分，而不是查询因错误而失败。

基本语法

INSERT INTO table_name (col1, col2, col3)
VALUES (val1, val2, val3)
ON DUPLICATE KEY UPDATE
    col2 = new_val2,
    col3 = new_val3;

实际示例：跟踪页面浏览量

ON DUPLICATE KEY UPDATE 的一个完美用例是跟踪统计数据，例如网站上的页面浏览量。让我们创建一个表来存储这些计数。

CREATE TABLE page_views (
    page_slug VARCHAR(255) NOT NULL PRIMARY KEY,
    view_count INT UNSIGNED NOT NULL DEFAULT 1,
    last_viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

现在，每次页面被查看时，我们都可以运行一个高效的查询。如果这是 /about-us 的首次查看，它将插入一个 view_count 为 1 的新行。如果该行已存在，它将使 view_count 增加 1。

INSERT INTO page_views (page_slug)
VALUES ('/about-us')
ON DUPLICATE KEY UPDATE view_count = view_count + 1;

多次运行此查询后，您可以检查结果：

SELECT * FROM page_views WHERE page_slug = '/about-us';

页面标识	浏览次数	最后浏览时间
/about-us	5	YYYY-MM-DD HH:MM:SS

现代语法：使用 VALUES() 函数和别名

更新时，您通常希望使用您尝试插入的值。VALUES() 函数允许您引用这些值。例如，让我们更新用户配置文件，如果用户不存在则插入。

-- 让我们创建一个简单的用户配置文件表
CREATE TABLE user_profiles (
  user_id INT PRIMARY KEY,
  name VARCHAR(100),
  last_login TIMESTAMP
);

-- 使用 VALUES() 的插入或更新查询
INSERT INTO user_profiles (user_id, name, last_login)
VALUES (101, 'Alice', NOW())
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    last_login = VALUES(last_login);

在 MySQL 8.0.19+ 中，您可以使用行和列别名来表示新行，这更具可读性，也是推荐的方法：

-- 推荐的现代语法
INSERT INTO user_profiles (user_id, name, last_login)
VALUES (101, 'Alice Smith', NOW()) AS new_values
ON DUPLICATE KEY UPDATE
    name = new_values.name,
    last_login = new_values.last_login;

理解受影响的行数

此语句返回的受影响行数可能会令人惊讶：

成功 INSERT 返回 1。
成功 UPDATE 返回 2（概念上，一行被删除并插入新行）。
如果匹配到现有行但 UPDATE 子句未导致其值发生任何更改，则返回 0。

客户端程序：安全地进行插入或更新操作

在应用程序中构建插入或更新查询时，所有用户提供的数据都必须通过参数化查询传递。

Node.js (mysql2)：更新产品库存

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

async function upsertProduct(product) {
  let connection;
  try {
    connection = await mysql.createConnection({ host: 'localhost', user: 'root', password: 'my-secret-pw', database: 'your_db' });

    const query = `
      INSERT INTO products (id, name, quantity, updated_at)
      VALUES (?, ?, ?, NOW())
      ON DUPLICATE KEY UPDATE
        name = VALUES(name),
        quantity = VALUES(quantity),
        updated_at = NOW()
    `;

    const [result] = await connection.execute(query, [product.id, product.name, product.quantity]);
    console.log(`Upsert successful. Affected rows: ${result.affectedRows}`);

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

upsertProduct({ id: 'SKU-123', name: 'Wireless Keyboard', quantity: 50 });

Python (mysql-connector-python)：更新用户分数

import mysql.connector

def upsert_user_score(user_id, score, name):
    query = """
        INSERT INTO user_scores (user_id, name, high_score)
        VALUES (%s, %s, %s)
        ON DUPLICATE KEY UPDATE
            name = VALUES(name),
            high_score = GREATEST(high_score, VALUES(high_score))
    """
    # GREATEST() 确保只有当新分数更高时才更新
    try:
        conn = mysql.connector.connect(user='root', password='my-secret-pw', database='your_db')
        cursor = conn.cursor()

        cursor.execute(query, (user_id, name, score))
        conn.commit()
        print(f"Upserted score for user {user_id}. Rows affected: {cursor.rowcount}")

    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

if __name__ == "__main__":
    upsert_user_score(505, 9800, 'Alex')