Mysql批量更新

Mysql批量写入

在我们做项目的过程中,经常会碰到一次性写入mysql多条不同的数据.我们知道对于这种批量的写入操作,出于效率的原因,我们大多时候都采用的是把写入的数据打包成一个sql语句一次性的执行完成.对于插入操作我们可以使用如下的方式:

INSERT INTO TableName VALUES (1, "test1"),(2, "test2");

通过上面的sql就可以在表名为TableName的表中一次性建立两条数据,当然如果想写入更多的数据可以使用for循环进行sql语句拼接来实现.那么对于更新多条记录的时候mysql有没有提供一种处理方法呢,答案是有的.

Mysql 批量更新

UPDATE TableName 
    SET ColumnName = Case id
        when 1 then "test1"
        when 2 then "test2"
    end
WHERE id IN (1,2)

这条语句的意思是:更新名字为TableName中的某个字段ColumnName,当id值的1时,该字段的值会被更新为”test1”,当id为2时该字段的值被更新为”test2”.其实对于我们程序猿来说这个是比较好理解的,就相当于我们程序结构中的switch…case语句.上面是一次性更新一个字段,那么要更新多个字段怎么办呢?如下所示:

UPDATE TableName 
    SET ColumnName = CASE id
        when 1 then "test1"
        when 2 then "test2"
    END,
    SET ColumnName2 = CASE id
        when 1 then "test3"
        when 2 then "test4"
    END
WHERE id IN (1,2)

现在我们知道了如何来批量更新一个表中的多个字段,通过一个例子我们来学习一下.表结构和数据如下图:

实现的目标:把id是63506163和62051322这两个用户的角色改为2.我们可以这样写

UPDATE test 
    SET role = CASE mem_id
        when 63506163 then 2
        when 62051322 then 2
    END
WHERE team_id = 327

好，我们来运行一下上面语句,在查看得到结果是不是我们想要的.执行完之后得到了一条错误信息

 Column 'role' cannot be null

上面的错误信息说的很明确没有给role设置正确的值.是我们批量更新的思路不对吗?其实不是,在使用批量更新的更新的时候,where条件中必须使用CASE后面的字段作为筛选条件. 重新修改为:

UPDATE test 
    SET role = CASE mem_id
        when 63506163 then 2
        when 62051322 then 2
    END
WHERE team_id = 327 AND mem_id in (63506163, 62051322)

再次运行,得到如下成功的结果：

affected rows : 2