MySQL ON DUPLICATE KEY UPDATE与高效批量更新实践

基础概念与语法解析

MySQL中的ON DUPLICATE KEY UPDATE是INSERT语句的扩展功能，当检测到唯一键冲突时自动触发更新操作。其核心价值体现在处理数据"存在即更新，不存在则插入"的场景。

标准语法结构：

INSERT INTO table (col1, col2, ...)
VALUES (val1, val2, ...)
ON DUPLICATE KEY UPDATE
    col1 = VALUES(col1),
    col2 = expr;

关键特性：

• 触发条件：主键或唯一索引冲突
• 原子性操作：整个过程在单条SQL中完成
• 字段引用：可通过VALUES()函数获取待插入值
• 表达式支持：可使用函数或运算表达式

与REPLACE INTO的区别：

1. REPLACE执行DELETE+INSERT，导致自增ID变化
2. ON DUPLICATE仅执行UPDATE，保留原始ID
3. 触发器触发差异：REPLACE触发DELETE和INSERT，ON DUPLICATE触发INSERT和UPDATE

批量更新实战技巧

基础批量操作

INSERT INTO user_scores(user_id, score)
VALUES 
    (101, 5),
    (102, 3),
    (103, 7)
ON DUPLICATE KEY UPDATE
    score = VALUES(score) + IF(score < 10, 1, 0);

跨表更新示例

INSERT INTO product_inventory (product_id, stock)
SELECT product_id, 50 
FROM new_shipments
WHERE arrival_date = CURDATE()
ON DUPLICATE KEY UPDATE
    stock = stock + VALUES(stock);

JSON字段处理

INSERT INTO user_preferences (user_id, settings)
VALUES (1001, '{"theme":"dark","notify":true}')
ON DUPLICATE KEY UPDATE
    settings = JSON_MERGE_PATCH(settings, VALUES(settings));

复合唯一键处理

当表存在多个唯一约束时，按以下优先级触发更新：

1. 主键优先
2. 第一个定义的非空唯一键
3. 其他唯一键按定义顺序

处理复合唯一键：

CREATE TABLE device_log (
    device_id INT,
    log_date DATE,
    count INT,
    UNIQUE KEY (device_id, log_date)
);

INSERT INTO device_log (device_id, log_date, count)
VALUES (5, '2023-08-15', 1)
ON DUPLICATE KEY UPDATE
    count = count + 1;

高级应用场景

版本控制更新

INSERT INTO document_versions 
    (doc_id, version, content)
VALUES 
    (2001, 5, 'Updated content')
ON DUPLICATE KEY UPDATE
    content = IF(VALUES(version) > version, VALUES(content), content),
    version = GREATEST(version, VALUES(version));

时间窗口统计

INSERT INTO hourly_metrics 
    (metric_name, hour_ts, count)
VALUES 
    ('page_views', UNIX_TIMESTAMP() DIV 3600 * 3600, 1)
ON DUPLICATE KEY UPDATE
    count = count + 1,
    last_updated = NOW();

性能优化策略

批量写入优化

使用扩展的INSERT语法提升吞吐量：

INSERT INTO temperature_data 
    (sensor_id, record_time, temp)
VALUES
    (1, '2023-08-15 10:00', 23.5),
    (2, '2023-08-15 10:00', 24.1),
    (3, '2023-08-15 10:00', 22.9)
ON DUPLICATE KEY UPDATE
    temp = VALUES(temp);

索引优化建议

1. 确保冲突检测字段有合适索引
2. 避免在UPDATE部分使用非索引字段条件
3. 对大表采用分批次提交（每次1000-5000条）

事务控制

START TRANSACTION;

INSERT INTO ... ON DUPLICATE KEY UPDATE ...;
-- 其他关联操作

COMMIT;

常见问题解决方案

自增主键跳号问题

解决方案：

ALTER TABLE orders AUTO_INCREMENT = 1;

触发器干扰处理

临时禁用触发器：

SET @old_triggers = @@session.sql_require_primary_key;
SET SESSION sql_require_primary_key = 0;

-- 执行ON DUPLICATE操作

SET SESSION sql_require_primary_key = @old_triggers;

条件更新控制

INSERT INTO user_balances 
    (user_id, balance)
VALUES 
    (3001, 100)
ON DUPLICATE KEY UPDATE
    balance = IF(VALUES(balance) > balance, 
                VALUES(balance), 
                balance + VALUES(balance));

监控与调试

影响行数解读

• 0行：无插入且无更新
• 1行：成功插入新记录
• 2行：成功更新已有记录

查看影响行数：

SELECT ROW_COUNT();

执行计划分析

EXPLAIN 
INSERT INTO inventory 
    (item_id, quantity)
VALUES 
    (7, 50)
ON DUPLICATE KEY UPDATE 
    quantity = quantity + 50;

最佳实践指南

1. 明确业务需求：是否需要保留原始记录时间戳等字段
2. 索引配置：确保冲突检测字段有合适索引
3. 批量操作：单次处理1000-5000条数据
4. 事务管理：对关键操作使用事务保证一致性
5. 版本控制：添加version字段处理并发更新
6. 审计追踪：添加modified_time等审计字段

替代方案对比

方案	优点	缺点
ON DUPLICATE KEY UPDATE	原子操作，高性能	依赖唯一约束
REPLACE INTO	简单易用	删除重建记录，影响自增ID
INSERT IGNORE	忽略错误	无法更新现有记录
事务+独立查询	灵活控制	需要多次查询，性能较低

真实案例解析

电商库存更新场景：

DELIMITER //

CREATE PROCEDURE update_inventory(
    IN p_sku VARCHAR(50),
    IN p_qty INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    INSERT INTO inventory (sku, quantity)
    VALUES (p_sku, p_qty)
    ON DUPLICATE KEY UPDATE
        quantity = quantity + p_qty,
        version = version + 1;

    INSERT INTO inventory_log 
        (sku, change_qty, update_type)
    VALUES
        (p_sku, p_qty, 'STOCK_UPDATE');

    COMMIT;
END //

DELIMITER ;

未来演进方向

MySQL 8.0+增强功能：

-- 使用别名提高可读性
INSERT INTO employees (emp_id, salary)
VALUES (1005, 8000) AS new
ON DUPLICATE KEY UPDATE
    salary = new.salary * 1.1;

-- JSON合并增强
INSERT INTO user_profiles (user_id, profile)
VALUES (2001, '{"prefs":{"theme":"dark"}}')
ON DUPLICATE KEY UPDATE
    profile = JSON_MERGE_PATCH(profile, VALUES(profile));