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的区别:
- REPLACE执行DELETE+INSERT,导致自增ID变化
- ON DUPLICATE仅执行UPDATE,保留原始ID
- 触发器触发差异: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)); |
当表存在多个唯一约束时,按以下优先级触发更新:
- 主键优先
- 第一个定义的非空唯一键
- 其他唯一键按定义顺序
处理复合唯一键:
| 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); |
- 确保冲突检测字段有合适索引
- 避免在UPDATE部分使用非索引字段条件
- 对大表采用分批次提交(每次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; |
| |
| |
| |
| 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行:成功更新已有记录
查看影响行数:
| EXPLAIN |
| INSERT INTO inventory |
| (item_id, quantity) |
| VALUES |
| (7, 50) |
| ON DUPLICATE KEY UPDATE |
| quantity = quantity + 50; |
- 明确业务需求:是否需要保留原始记录时间戳等字段
- 索引配置:确保冲突检测字段有合适索引
- 批量操作:单次处理1000-5000条数据
- 事务管理:对关键操作使用事务保证一致性
- 版本控制:添加version字段处理并发更新
- 审计追踪:添加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; |
| |
| |
| INSERT INTO user_profiles (user_id, profile) |
| VALUES (2001, '{"prefs":{"theme":"dark"}}') |
| ON DUPLICATE KEY UPDATE |
| profile = JSON_MERGE_PATCH(profile, VALUES(profile)); |
微信扫一扫:分享
微信里点“发现”,扫一下
二维码便可将本文分享至朋友圈。