在数据库操作中，批量更新数据是一个常见的需求，尤其是在处理大量数据时，高效的批量更新方法可以显著提升系统性能。今天咱们就来聊聊 MySQL 里两种常用的批量更新方式：UPDATE ... CASE 和多值插入，并且对比一下它们的性能。
一、应用场景
在实际的业务开发中，我们经常会遇到需要批量更新数据的场景。比如说电商系统里，到了换季的时候，需要对商品的价格进行批量调整；或者是在用户管理系统中，要根据一些规则批量修改用户的权限。这时候就需要使用批量更新的方法，减少与数据库的交互次数，提高更新效率。
UPDATE ... CASE 主要适用于根据不同的条件对不同的记录进行更新的场景。比如说，有一个学生成绩表，要根据学生的不同学号更新他们对应的成绩。多值插入则更适合一次性插入多条数据，并且在某些情况下，结合插入操作来进行更新，比如使用 INSERT ... ON DUPLICATE KEY UPDATE 语句。
二、UPDATE ... CASE 技术详解
1. 语法
UPDATE ... CASE 的基本语法如下：
UPDATE table_name
SET
column1 = CASE id
WHEN 1 THEN 'value1'
WHEN 2 THEN 'value2'
ELSE column1
END,
column2 = CASE id
WHEN 1 THEN 'value3'
WHEN 2 THEN 'value4'
ELSE column2
END
WHERE id IN (1, 2);
2. 示例
咱们来创建一个简单的学生成绩表：
-- 创建学生成绩表
CREATE TABLE student_scores (
id INT PRIMARY KEY,
name VARCHAR(50),
score INT
);
-- 插入一些示例数据
INSERT INTO student_scores (id, name, score) VALUES
(1, '张三', 80),
(2, '李四', 90),
(3, '王五', 70);
-- 使用 UPDATE ... CASE 批量更新成绩
UPDATE student_scores
SET score = CASE id
WHEN 1 THEN 85 -- 如果 id 为 1，将成绩更新为 85
WHEN 2 THEN 92 -- 如果 id 为 2，将成绩更新为 92
ELSE score
END
WHERE id IN (1, 2);
在这个示例中，我们根据学生的 id 来更新他们的成绩。通过 CASE 语句，可以针对不同的 id 设置不同的更新值。
3. 优缺点
优点
- 灵活性高：可以根据不同的条件对不同的记录进行更新，而不需要多次执行
UPDATE语句。 - 语法简单：对于简单的批量更新需求，
UPDATE ... CASE的语法比较直观，容易理解和实现。
缺点
- 性能问题：当需要更新的记录数量较多时，SQL 语句会变得非常长，可能会超出 MySQL 的最大允许长度，而且数据库解析和执行这样的长语句也会消耗较多的时间和资源。
- 维护困难：如果更新条件比较复杂，
CASE语句会变得很长，代码的可读性和可维护性会降低。
4. 注意事项
- SQL 长度限制：要注意 MySQL 的
max_allowed_packet参数，确保生成的 SQL 语句长度不超过该参数的限制。 - 事务处理：在批量更新时，建议使用事务来保证数据的一致性，避免部分更新失败导致数据不一致的问题。
三、多值插入技术详解
1. 语法
多值插入结合 INSERT ... ON DUPLICATE KEY UPDATE 的语法如下：
INSERT INTO table_name (column1, column2, ...)
VALUES
(value1_1, value1_2, ...),
(value2_1, value2_2, ...),
...
ON DUPLICATE KEY UPDATE
column1 = VALUES(column1),
column2 = VALUES(column2),
...;
2. 示例
还是以学生成绩表为例：
-- 使用 INSERT ... ON DUPLICATE KEY UPDATE 批量更新成绩
INSERT INTO student_scores (id, name, score)
VALUES
(1, '张三', 85),
(2, '李四', 92)
ON DUPLICATE KEY UPDATE
score = VALUES(score);
在这个示例中，如果 id 已经存在，就会更新对应的 score 值；如果 id 不存在，就会插入一条新记录。
3. 优缺点
优点
- 性能较好：多值插入可以一次性插入多条数据，减少与数据库的交互次数，提高更新效率。
- 代码简洁：相比
UPDATE ... CASE，INSERT ... ON DUPLICATE KEY UPDATE的代码更简洁，尤其是在更新大量记录时。
缺点
- 依赖唯一索引：该方法依赖表中的唯一索引（如主键），如果没有唯一索引，就无法使用
ON DUPLICATE KEY UPDATE语句。 - 插入新记录风险：如果不小心插入了不存在的
id，会在表中创建新记录，可能会导致数据异常。
4. 注意事项
- 唯一索引：确保表中存在唯一索引，否则
INSERT ... ON DUPLICATE KEY UPDATE语句会失效。 - 数据校验：在执行插入操作前，要对数据进行校验，避免插入不必要的新记录。
四、性能对比
1. 测试环境
为了对比 UPDATE ... CASE 和多值插入的性能，我们在本地搭建一个 MySQL 数据库，创建一个包含 10000 条记录的测试表。
2. 测试代码
UPDATE ... CASE 测试代码
-- 创建测试表
CREATE TABLE test_table (
id INT PRIMARY KEY,
value INT
);
-- 插入 10000 条示例数据
INSERT INTO test_table (id, value)
SELECT seq, 0
FROM seq_1_to_10000;
-- 使用 UPDATE ... CASE 批量更新数据
SET @sql = 'UPDATE test_table SET value = CASE id ';
SET @i = 1;
WHILE @i <= 1000 DO
SET @sql = CONCAT(@sql, 'WHEN ', @i, ' THEN ', @i * 2, ' ');
SET @i = @i + 1;
END WHILE;
SET @sql = CONCAT(@sql, 'ELSE value END WHERE id IN (');
SET @i = 1;
WHILE @i <= 1000 DO
SET @sql = CONCAT(@sql, @i);
IF @i < 1000 THEN
SET @sql = CONCAT(@sql, ',');
END IF;
SET @i = @i + 1;
END WHILE;
SET @sql = CONCAT(@sql, ');');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
多值插入测试代码
-- 使用 INSERT ... ON DUPLICATE KEY UPDATE 批量更新数据
SET @sql = 'INSERT INTO test_table (id, value) VALUES ';
SET @i = 1;
WHILE @i <= 1000 DO
SET @sql = CONCAT(@sql, '(', @i, ', ', @i * 2, ')');
IF @i < 1000 THEN
SET @sql = CONCAT(@sql, ',');
END IF;
SET @i = @i + 1;
END WHILE;
SET @sql = CONCAT(@sql, ' ON DUPLICATE KEY UPDATE value = VALUES(value);');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
3. 测试结果
经过多次测试，发现多值插入的性能明显优于 UPDATE ... CASE。这是因为 UPDATE ... CASE 生成的 SQL 语句较长，数据库解析和执行的时间较长；而多值插入一次性插入多条数据，减少了与数据库的交互次数。
五、总结
在 MySQL 中进行批量更新时，UPDATE ... CASE 和多值插入都有各自的优缺点和适用场景。如果需要根据不同的条件对不同的记录进行更新，并且更新的记录数量较少，UPDATE ... CASE 是一个不错的选择；如果需要一次性更新大量记录，并且表中存在唯一索引，多值插入结合 INSERT ... ON DUPLICATE KEY UPDATE 语句可以获得更好的性能。在实际应用中，我们要根据具体的业务需求和数据特点选择合适的批量更新方法。
评论