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);
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));
正文到此结束
评论插件初始化中...
Loading...
本文目录