mysql进阶(十四) 批量更新与批量更新多条记录的不同值实现方法_mysql 批量 replace 多条 where(1)

那如果更新多条数据为不同的值，可能很多人会这样写：

foreach ($display_order as $id => $ordinal) { 

    $sql = "UPDATE categories SET display_order = $ordinal WHERE id = $id"; 

    mysql_query($sql); 

}

即是循环一条一条的更新记录。一条记录update一次，这样性能很差，也很容易造成阻塞。

那么能不能一条sql语句实现批量更新呢？mysql并没有提供直接的方法来实现批量更新，但是可以用点小技巧来实现。

UPDATE mytable 

    SET myfield = CASE id 

        WHEN 1 THEN 'value'

        WHEN 2 THEN 'value'

        WHEN 3 THEN 'value'

    END

WHERE id IN (1,2,3);

这里使用了case when这个小技巧来实现批量更新。

二、举个例子

UPDATE categories 

    SET display_order = CASE id 

        WHEN 1 THEN 3 

        WHEN 2 THEN 4 

        WHEN 3 THEN 5 

    END

WHERE id IN (1,2,3);

这句sql的意思是，更新display_order 字段，如果id=1 则display_order 的值为3，如果id=2 则 display_order 的值为4，如果id=3 则 display_order 的值为5。即是将条件语句写在了一起。

这里的where部分不影响代码的执行，但是会提高sql执行的效率。确保sql语句仅执行需要修改的行数，这里只有3条数据进行更新，而where子句确保只有3行数据执行。

如果更新多个值的话，只需要稍加修改：

UPDATE categories 

    SET display_order = CASE id 

        WHEN 1 THEN 3 

        WHEN 2 THEN 4 

        WHEN 3 THEN 5 

    END, 

    title = CASE id 

        WHEN 1 THEN 'New Title 1'

        WHEN 2 THEN 'New Title 2'

        WHEN 3 THEN 'New Title 3'

    END

WHERE id IN (1,2,3);

到这里，已经完成一条mysql语句更新多条记录了。

但是要在业务中运用，需要结合服务端语言，这里以php为例，构造这条mysql语句：

$display_order = array( 

    1 => 4, 

    2 => 1, 

    3 => 2, 

    4 => 3, 

    5 => 9, 

    6 => 5, 

    7 => 8, 

    8 => 9 

); 

$ids = implode(',', array_keys($display_order)); 

$sql = "UPDATE categories SET display_order = CASE id "; 

foreach ($display_order as $id => $ordinal) { 

    $sql .= sprintf("WHEN %d THEN %d ", $id, $ordinal); 

} 

$sql .= "END WHERE id IN ($ids)"; 

echo $sql;

这个例子，有8条记录进行更新。代码也很容易理解，你学会了吗

三、性能分析

当使用上万条记录利用mysql批量更新，发现使用最原始的批量update发现性能很差，有以下三种优化办法：

3.1 批量update，一条记录update一次，性能很差

update test_tbl set dr='2' where id=1;

3.2 replace into 或者insert into …on duplicate key update

replace into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y');

或者使用

总结

我个人认为，如果你想靠着背面试题来获得心仪的offer，用癞蛤蟆想吃天鹅肉形容完全不过分。想必大家能感受到面试越来越难，想找到心仪的工作也是越来越难，高薪工作羡慕不来，却又对自己目前的薪资不太满意，工作几年甚至连一个应届生的薪资都比不上，终究是错付了，错付了自己没有去提升技术。

这些面试题分享给大家的目的，其实是希望大家通过大厂面试题分析自己的技术栈，给自己梳理一个更加明确的学习方向，当你准备好去面试大厂，你心里有底，大概知道面试官会问多广，多深，避免面试的时候一问三不知。

大家可以把Java基础，JVM，并发编程，MySQL，Redis，Spring，Spring cloud等等做一个知识总结以及延伸，再去进行操作，不然光记是学不会的，这里我也提供一些脑图分享给大家：

希望你看完这篇文章后，不要犹豫，抓紧学习，复习知识，准备在明年的金三银四拿到心仪的offer，加油，打工人！

链图片转存中…(img-wrOmsO6C-1714428054863)]

[外链图片转存中…(img-LMJFRi6X-1714428054864)]

[外链图片转存中…(img-jXmSEVkK-1714428054864)]

希望你看完这篇文章后，不要犹豫，抓紧学习，复习知识，准备在明年的金三银四拿到心仪的offer，加油，打工人！

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录