产品概述
应用场景
产品架构
实例类型
兼容性说明
使用规范建议
INSERT...ON DUPLICATE KEY UPDATE 实现存在则更新的操作，以及批量更新的最佳实践。UPDATE 权限。UPDATE 语句用于修改表中已有记录的列值。以下介绍 UPDATE 语句的基本用法和常见场景。UPDATE table_nameSET column1 = new_value1, column2 = new_value2, ...WHERE condition;
参数 | 说明 |
table_name | 需要更新的目标表名 |
SET 子句 | 指定需要更新的列及其新值，多列之间使用英文逗号分隔 |
WHERE 子句 | 指定更新条件，只有满足条件的行才会被更新 |
WHERE 子句，UPDATE 语句将更新表中的所有行。在生产环境中执行 UPDATE 操作时，请务必指定 WHERE 条件，以避免误更新全表数据。UPDATE employees SET salary = 20000.00 WHERE id = 1;
Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0
UPDATE 语句可以同时更新多个列。UPDATE employeesSET salary = 22000.00, department = '架构部'WHERE id = 1;
SET 子句中可以使用表达式来计算新值。例如，将所有技术部员工的薪资上调10%。UPDATE employeesSET salary = salary * 1.10WHERE department = '技术部';
UPDATE 语句支持 ORDER BY 和 LIMIT 子句，可以限制更新的行数和顺序。该语法常用于分批更新或只更新前几行的场景。UPDATE table_nameSET column1 = new_value1WHERE conditionORDER BY sort_columnLIMIT row_count;
UPDATE employeesSET salary = 12000.00ORDER BY salary ASCLIMIT 3;
ORDER BY 配合 LIMIT 可以确保更新操作的确定性。在需要分批更新大量数据时，建议使用 ORDER BY 主键配合 LIMIT 进行分批处理。UPDATE 语句中同时更新多个表的数据，通常配合 JOIN 子句使用。UPDATE table1JOIN table2 ON table1.column = table2.columnSET table1.column1 = new_value1, table2.column2 = new_value2WHERE condition;
-- 创建部门调薪表CREATE TABLE department_raise (department VARCHAR(50) PRIMARY KEY,raise_rate DECIMAL(4, 2));INSERT INTO department_raise VALUES ('技术部', 1.15), ('产品部', 1.10), ('市场部', 1.08);-- 使用多表 JOIN 更新员工薪资UPDATE employees eJOIN department_raise d ON e.department = d.departmentSET e.salary = e.salary * d.raise_rate;
INSERT...ON DUPLICATE KEY UPDATE 语句在插入数据时，如果遇到主键或唯一键冲突，则执行 UPDATE 操作。该语法适用于"不存在则插入，存在则更新"的业务场景。INSERT INTO table_name (column1, column2, ..., columnN)VALUES (value1, value2, ..., valueN)ON DUPLICATE KEY UPDATE column1 = new_value1, column2 = new_value2, ...;
-- 创建评分表CREATE TABLE book_ratings (user_id INT,book_id INT,rating TINYINT,rated_at DATETIME,PRIMARY KEY (user_id, book_id));-- 插入或更新评分INSERT INTO book_ratings (user_id, book_id, rating, rated_at)VALUES (1001, 50, 4, NOW())ON DUPLICATE KEY UPDATE rating = VALUES(rating), rated_at = VALUES(rated_at);
VALUES() 函数用于引用 INSERT 子句中待插入的值。在 MySQL 8.0.20 及以上版本中，VALUES() 在此场景下已标记为废弃，建议使用别名方式替代。UPDATE 操作可能导致事务过大、锁持有时间过长等问题。建议将大批量更新拆分为多次小批量更新。SELECT 查询待更新的记录主键范围。UPDATE...WHERE...LIMIT 分批更新数据。ALTER TABLE employees ADD COLUMN salary_updated TINYINT DEFAULT 0;
-- 每次更新 1000 行UPDATE employeesSET salary = salary * 2, salary_updated = 1WHERE salary_updated = 0LIMIT 1000;
Rows matched 为0。ALTER TABLE employees DROP COLUMN salary_updated;
UPDATE 语句时，建议遵循以下最佳实践。UPDATE 操作时，必须指定 WHERE 子句来限定更新范围。缺少 WHERE 子句的 UPDATE 语句会更新全表数据，可能导致严重的数据问题。WHERE 子句中使用主键或索引列，可以避免全表扫描，提升更新效率。UPDATE employees SET salary = 20000.00 WHERE id = 1;
UPDATE employees SET salary = 20000.00 WHERE name = '张三';
name 列没有索引，该语句将执行全表扫描。tdsql_lock_wait_timeout）。UPDATE 之前，建议先使用相同条件的 SELECT 语句查询将要更新的数据，确认更新范围符合预期。-- 先查看即将被更新的数据SELECT id, name, salary FROM employees WHERE department = '技术部';-- 确认无误后再执行更新UPDATE employees SET salary = salary * 1.10 WHERE department = '技术部';
UPDATE 语句时，请注意以下事项：UPDATE 语句在默认的 autocommit = 1 模式下会自动提交。如果需要回滚，请在执行 UPDATE 前开启事务。UPDATE 操作会触发对应的 BEFORE UPDATE 和 AFTER UPDATE 触发器。UPDATE 操作会对匹配的行加行级锁。在高并发场景下，如果多个事务同时更新相同的行，可能产生锁等待或死锁。UPDATE 的 SET 值与当前值相同，受影响的行数 (Changed) 为0，但匹配的行数 (Rows matched) 仍会计入。UPDATE 操作会使用两阶段提交（Two-Phase Commit，2PC）协议来保证数据一致性。文档反馈