有一个user表:字段id,name,age
现在发现所有用户的年龄都录入错误了,然后重新搜集到了所有用户的年龄,现在需要将所有用户的年龄重新更新进去;
现在有所有用户id及其对应的age,怎么更新比较好?
create table user(
id int primary key AUTO_INCREMENT,
name varchar(200) not null,
age int
);
思路:
1 写一个update语句:update user set age=xx where id=xx;
然后循环执行上面的update语句,比如数据有1万条,执行一万次sql更新。可以更新成功没问题,但是速度会比较慢。
2 批量更新,见下面
一、MySQL
首先要知道:MySQL没有提供直接的方法来实现批量更新,但可以使用case when语法来实现这个功能,如下语法格式:
UPDATE user
SET age = CASE id
WHEN 1 THEN age1
WHEN 2 THEN age2
WHEN 3 THEN age3
END,
name = CASE id
WHEN 1 THEN 'name1'
WHEN 2 THEN 'name2'
WHEN 3 THEN 'name3'
END
WHERE id IN (1,2,3)
在这个例子中,具体的实现如下:
//id和正确的age都装在一个list集合中,有所有的User对象
String sql1 = "";
String sql2 = "";
for(int i=1;i<=10000;i++){
User user = list.get(i);
sql1 += "when "+user.getId()+" then "+user.getAge()+" ";
sql2 += user.getId()+",";
}
sql2 = sql2.substring(0,sql2.length()-1);//去掉最后一个","
String sql = "update test set name = case id "+sql1+" end where id in ("+sql2+")";
//打印出组装完成的sql,最后执行该sql语句
System.out.println("sql="+sql);
二、myBatis(现在在公司里边基本上都使用框架,而不使用纯sql语句了),实现如下:
//传参并执行
Map<String, Object> map = new HashMap<>();
map.put("paramList",paramList);
ssqBaseDao.update("userMapping.updateAllAge", map);
//sql
<update id="updateAllAge" parameterType="java.util.HashMap">
update analysis_ssq_base_red_blue set red_sum_omission=
<foreach collection="paramList" item="item" index="index"
separator=" " open="case id" close="end">
when #{item.id} then #{item.age}
</foreach>
where id in
<foreach collection="paramList" item="item" index="index"
separator="," open="(" close=")">
#{item.id}
</foreach>
</update>
三、同样是mybatis方式,但是是多个字段,作为条件的字段类型不是整数,是varchar,有点不一样,如下:
<!--
1 类型上的变化导致改变
case id when #{item.id} then #{item.foreSumValueOmission}变成了
case when issue_no=#{item.issueNo} then #{item.foreSumValueOmission}变成了
2 多个字段更新导致变化,加了这段即可
back_sum_value_omission=
<foreach collection="paramList" item="item" index="index"
separator=" " open="case" close="end">
when issue_no=#{item.issueNo} then #{item.backSumValueOmission}
</foreach>
-->
<!--初始化前区和后区和值遗漏 fore_sum_value_omission,back_sum_value_omissio-->
<update id="initDltForeAndBackSumOmission" parameterType="java.util.HashMap">
update analysis_dlt_base_fore_back set fore_sum_value_omission=
<foreach collection="paramList" item="item" index="index"
separator=" " open="case" close="end,">
when issue_no=#{item.issueNo} then #{item.foreSumValueOmission}
</foreach>
back_sum_value_omission=
<foreach collection="paramList" item="item" index="index"
separator=" " open="case" close="end">
when issue_no=#{item.issueNo} then #{item.backSumValueOmission}
</foreach>
where issue_no in
<foreach collection="paramList" item="item" index="index"
separator="," open="(" close=")">
#{item.issueNo}
</foreach>
</update>