MySql利用case when实现批量更新多条记录的不同值实现方法
mysql更新语句很简单,更新一条数据的某个字段,一般这样写:UPDATE mytable SET myfield = 'value' WHERE other_field = 'other_value';
如果更新同一字段的不同值,mysql也很简单,利用 in 查询,修改下where即可:UPDATE mytable SET myfield = 'value' WHERE other_field in ('other_values');
这里注意 ‘other_values' 是一个逗号(,)分隔的字符串,如:1,2,3。并且 in 后面是括号()哦,不是数组
那如果更新多条数据为不同的值,可能很多人会这样写:
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 'value1'
WHEN 2 THEN 'value2'
WHEN 3 THEN 'value3'
END
WHERE id IN (1,2,3)
这里使用了case when 这个小技巧来实现批量更新。
这句 sql 的意思是,更新 myfield 字段,如果 id=1 则 myfield 的值为 value1,如果 id=2 则为 value2,如果id=3 则为 value3。
即是将条件语句写在了一起。这里的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发现性能很差,将网上看到的总结一下一共有以下三种办法:
1、批量update,一条记录update一次,性能很差
2、replace into 或者insert into ...on duplicate key update
replace into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y');
// 或者使用
insert into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y') on duplicate key update dr=values(dr);
3、创建临时表,先更新临时表,然后从临时表中update。代码如下
create temporary table tmp(id int(4) primary key,dr varchar(50));
insert into tmp values (0,'gone'), (1,'xx'),...(m,'yy');
update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;
注意:这种方法需要用户有temporary 表的create 权限。
下面是上述方法update 100000条数据的性能测试结果:
逐条update
real 0m15.557s
user 0m1.684s
sys 0m1.372s
replace into
real 0m1.394s
user 0m0.060s
sys 0m0.012s
insert into on duplicate key update
real 0m1.474s
user 0m0.052s
sys 0m0.008s
create temporary table and update:
real 0m0.643s
user 0m0.064s
sys 0m0.004s
就测试结果来看,测试当时使用replace into性能较好。
replace into 和insert into on duplicate key update的不同在于:
replace into 操作本质是对重复的记录先 delete 后 insert,如果更新的字段不全会将缺失的字段置为缺省值
insert into 则是只update重复记录,不会改变其它字段。
/**
* 将二维数组转换成CASE WHEN THEN的批量更新条件
* @param $data array 二维数组
* @param $field string 列名
* @return string sql语句
*/
private function parseUpdate($data, $field,$table)
{
$sql = " update {$table} set ";
$keys = array_keys(current($data));
foreach ($keys as $column) {
$sql .= sprintf("`%s` = CASE `%s` \n", $column, $field);
foreach ($data as $line) {
$sql .= sprintf("WHEN '%s' THEN '%s' \n", $line[$field], $line[$column]);
}
$sql .= "END,";
}
$fanhui = implode(',',array_column($data,'id'));
return rtrim($sql, ',')." where id in ({$fanhui})";
}
相关文章
- Mysql net start mysql启动,提示发生系统错误 5 拒绝訪问 解决之道
- mysql binlog 参数_MySQL Binlog常用参数
- mysql中使用show variables同时查询多个参数值?show variables的使用?
- 报错:MySQL Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'
- MySQL修改密码的3种方式以及启动方式
- 【MySQL高级】MySql中常用工具及Mysql 日志
- 【MySQL高级】Mysql的体系结构概览及存储引擎以及索引的使用
- mysql启动时报错:Starting MySQL... ERROR! The server quit without updating PID file (/opt/mysql/data/mysql.pid) 的解决方法
- 关于Mysql 查询所有表的实时记录用于对比2个MySQL 库的数据是否异步
- MySQL第四讲 MySql Undo日志 - 对聚簇索引进行CUD操作
- 【MySQL进阶-04】深入理解mysql事务本质(超级详解)
- MySQL mysqldump备份数据库及恢复数据库(mysql命令)
- Mysql备份工具mysqldump和mysqlhotcopy
- 《MySQL排错指南》——1.3 当错误可能由之前的更新引起时
- mysql只更新日期不更新时分秒,Mysql取30天内每天最大的数据
- 使用navicat连接mysql连接错误:Lost connection to Mysql server at 'waiting for initial communication packet'
- 转 mysql 自动安装部署
- python操作mysql数据库系列-操作MySql数据库(四)
- Mysql状态查看QPS
- mysql远程连接 Host is not allowed to connect to this MySQL server
- mysql导出数据报错The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
- Mysql_mysql多个TimeStamp设置
- liunux mysql MySQL表名不区分大小写的设置方法
- mysql存储过程,获取指定数据库的某个表的字段信息
- mysql5.5无法启动,Can't open and lock privilege tables: Table 'mysql.host' doesn't exist 问题的解决方法
- (5.5)mysql高可用系列——MySQL半同步复制(实践)
- (1.16)mysql server优化之buffer pool
- [Mysql] 聚合函数
- MySQL出现“Lock wait timeout exceeded; try restarting transaction”