数据库批量插入与更新

引言

最近在做项目的时候总是会遇到需要批量插入数据和批量更新数据的时候，故将数据库批量插入与更新的几种方法结合案例总结下。

批量插入

方法一：insert select

有一次在项目中遇到了需要将一张表中的部分字段中的数据存入另一个表中，一开始不知道怎么做，以为是把所有的数据取出来，然后一条条存进去，后来觉得时间效率太慢了，看了看文档发现了有insert select 方法，可以插入某个表中的数据。

---语法
insert into
表名(列名1,列名2) 
select 列名1,列名2 
from 表名

方法二：insert values 字符串拼接

在后端中常常会有插入大量数据的情况，在这种情况下循环遍历insert values一条一条SQL语句进行执行效率会大大下降。例如执行一条insert values的时间为60ms，那么1000条数据就需要60s的时间，执行时间会随着数据的条数不断增长。所以我们就想要是只执行一次SQL语句那就好了，那么就可以采用字符串拼接的方式进行大批量数据插入。

---语法
insert into 
表名(列名1,列名2)  
values
(值1,值2),
(值1,值2),
(值1,值2),
(值1,值2)

批量更新

方法一：update when case

在网上看到最多的批量更新语法就是利用when case进行字符串拼接，这个方法在数据量不大的情况下效率很高，但是当数据量太大的时候就会导致SQL语句太长，导致SQL语句执行效率大大下降。

---语法 
update 表名 set 
列名1 =
	case id 
	when id值1 then 值1
    when id值2 then 值2
    end,
列名2 =
	case id
	when id值1 then 值1
	when id值2 then 值2
	end
where
	id in (id值1,id值2);

方法二：update select

有时候在业务中有需要将某张表中的数据，如果一条条取出来再进行update那会使得效率大大下降，数据库也提供了一个很方便的语句，就是使用update select 可以快速更新id值相同的值。

---语法
update 表1
set 
	表1.列1 = 表2.列1,
	表1.列2 = 表2.列2,
	表1.列3 = 表2.列3
from (select 列1,列2,列3) as 表2
where
	表1.id = 表2.id

方法三：临时表

有时候采用update case的方法会导致SQL语句太长，那么如果你拥有创建临时表的权限，你可以先创建一张临时表，然后将需要更新的数据插入进去，再利用update select的方法将数据大批量更新。

---创建临时表
create table *tmp(
id varchar(36),
name varchar(128)
)
---插入数据
insert into *tmp(id,name)
values
(值1,值2),
(值1,值2)
---更新数据
update 表1
set name = 表2.name
from (select id,name from *tmp) as 表2
where
	表1.id = 表2.id
---删除临时表
drop table *tmp