Signature

mysql批量更新的几种方式

Mar 04, 2016

最近安排我去帮同事做基础数据的导入,知道了一些老师没教过的批量更新的黑科技,特整理一下:

首先先假设有student表吧,有name,age,class三个column,主键是name。

基础数据:

INSERT INTO student (name, age, class)
VALUES
	('张三', '3', '三班'),
	('李四', '4', '四班'),
	('王五', '5', '五班');

当我们要把张三换到二班:

update student set class = '二班' 
where name = '张三';

当我们要把张三、李四、王五都换到二班去,前提是不能删除后新增,毕竟删除有风险,断个电啥的:

update student set class = '二班' 
where name in ('张三','李四','王五');

上面这条语句只适用于所有同学都换到二班的情况,现在他们都在二班了,把他们还原到原来班级的话,就不适用了,只能这样先:

update student set class = '三班' where name = '张三';
...                        '四班'  ...         '李四';
...                        '五班'  ...         '王五';

那么一个foreach,循环上述3条语句,这也是很好实现的。问题就是当成千上万条数据,那么就有成千上万个数据库连接,这样是效率很低的,我们考虑一条sql完成批量更新不同字段,下面有三种方式。

case when

二班所有人都回到原班级:

UPDATE student SET class = 
CASE
	WHEN NAME = '张三' THEN '三班'
	WHEN NAME = '李四' THEN '四班'
	WHEN NAME = '王五' THEN '五班' 
END;

这样我们就实现了一条sql完成批量更新的任务了,具体实现呢,也很直观,用循环去拼接sql。

不好的地方:如果是修改很多字段,那么每个字段都必须有case,如果修改的记录很多,when也同样那么多,这样的话,这条sql就太长了吧。。

replace into

还是假设他们在二班,二班所有人都回到原班级:

REPLACE INTO student (name,class) 
values 
	('张三','三班'),
	('李四','四班'),
	('王五','五班');

首先说下 replace into,它和 insert into 类似,可以说是insert into的加强版。

加强的地方在于:replace into 先尝试插入数据到表,如果发现表中已有此行数据(根据主键或唯一索引)则先删除原数据,然后再插入新数据,否则就是直接插入新数据。

我们看下上面这条语句的执行结果,受影响的行是6,说明先删了3条,再增了3条,增加的三条age字段都是null,因为我们没有给age字段的数据,所以真正完整的sql应该是这样:

REPLACE INTO student (name,age,class) 
values 
	('张三','3','三班'),
	('李四','4','四班'),
	('王五','5','五班');

注意的地方:要记得把所有的字段都放进去吧,不然age字段就没啦。必须要有主键或者索引,如果有多个索引,它会删除所有索引匹配到的所有记录的。

on duplicate key update

还是假设他们在二班,二班所有人都回到原班级:

INSERT INTO student (NAME, class)
VALUES
	('张三', '三班'),
	('李四', '四班'),
	('王五', '五班') 
ON DUPLICATE KEY UPDATE class = VALUES(class);

如果只是单纯的修改的话,上述语句已经达到了我们的要求。这种方式也是先增,如果记录中有则update。修改多条记录的话,就是这样:

INSERT INTO student (NAME, age, class)
VALUES
	('张三', 1, '三班'),
	('李四', 1, '四班'),
	('王五', 1, '五班') 
ON DUPLICATE KEY UPDATE 
	class = VALUES(class),
	age = VALUES(age);

要注意的是:还是需要主键或索引的。

Share: