MySQL中批量插入和批量更新

数据库,MySQL

2018-02-27

243

0

目录


大批量同步数据到MySQL中,涉及到更新和新增数据,如果仅仅循环一次次插入,不仅耗费过多的网络开销,性能低下,而且频繁的获取数据库连接、操作数据库,增加数据库压力,甚至严重影响数据库性能。因此,此时我们需要批量操作,来减轻数据库压力,提升应用性能。

一、数据准备

首先创建一张测试表,插入一些初始数据:

CREATE TABLE `hello` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `test`.`hello` (`id`, `name`, `age`) VALUES ('1', 'zhangsan', '30');
INSERT INTO `test`.`hello` (`id`, `name`, `age`) VALUES ('2', 'lisi', '18');
INSERT INTO `test`.`hello` (`id`, `name`, `age`) VALUES ('3', 'wangwu', '22');
INSERT INTO `test`.`hello` (`id`, `name`, `age`) VALUES ('4', 'zhaoliu', '25');

二、批量更新

MySQL批量更新总结下来有大概有四种常用方式:

1、replace into

基本语法

replace into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y');
用于替换指定条件的字段值,本质上是先删除记录,在重新添加,所以 未指定的字段会被重置为默认值(例如NULL)。
举例:
replace into hello (id, age) VALUES (1, 30)
结果:受影响的行数为2,而且name字段被更新为NULL了。

2、insert into on duplicate key update

插入时检查主键是否存在,存在则进行更新。

基本语法

insert into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y') on duplicate key update dr=values(dr);
举例:
insert into hello(id, name, age) VALUES(2,'李四', 30),(3,'王五',31) ON DUPLICATE key UPDATE name=values(name),age=values(age)
结果:受影响的行数为4.
 
注意:
replace into  和 insert into on duplicate key update的不同在于:
  • replace into 操作本质是对重复的记录先delete 后insert,如果更新的字段不全会将缺失的字段置为缺省值,用这个要悠着点否则不小心清空大量数据可不是闹着玩的。
  • insert into 则是只update重复记录,不会改变其它字段。

3、临时表批量更新

DROP TEMPORARY TABLE tmp;
create temporary table tmp(id bigint primary key, name varchar(255), age int(11));
insert into tmp values(2,'lisi', 21),(3,'wangwu',22);
update hello, tmp set hello.name=tmp.name,hello.age=tmp.age where hello.id=tmp.id;
这种方法需要用户有temporary表的create权限,并且较为繁琐。

4、mysql特性语句

UPDATE hello
SET name =
CASE id
WHEN 2 THEN '李四'
WHEN 3 THEN '王五'
END,
age =
CASE id
WHEN 2 THEN 30
WHEN 3 THEN 31
END
WHERE id IN(2, 3)
 
利用CASE WHEN语句,根据主键id判断,设置对应的值。
 
由于没有做具体的性能测试,目前项目采用的这种方式。

三、批量插入

基本语法:

INSERT INTO
[表名]([列名],[列名])
VALUES
([列值],[列值])),
([列值],[列值])),
([列值],[列值]));
例如:
INSERT INTO hello VALUES(NULL, 'NAME1', 10), (NULL, 'NAME2', 11), (NULL, 'NAME3', 12)

四、MyBatis实现

批量更新:

批量插入:


前一篇:RabbitMQ基础(二)——工作队列Work Queues
后一篇:股票是什么

belonk

轻轻地我走了,正如我轻轻地来,我挥一挥衣袖,不带走一片云彩