mysql如何批量保存数据，而不重复

vlambda
2021-04-13

mysql如何批量保存数据，而不重复

This browser does not support music or audio playback. Please play it in Weixin or another browser.

一、在工作的过程中，有涉及批量导入数据的需求，实现主要有以下方式

读取一条数据，查询数据库，不存在则保存，存在则更新，耗时，适用保存少量数据。
查询已存在的全部数据与保存的数据比较，包含则更新，不包含则保存，减少查询次数，适用保存少量数据。
直接利用数据库语句判断保存数据，存在则忽略或更新，不存在则保存。

二、mysql保存方式

menu表

CREATE TABLE `menu` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(40) DEFAULT NULL, `url` varchar(20) DEFAULT NULL, `parent_id` bigint(20) DEFAULT NULL, `index` int(11) DEFAULT NULL, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `menu_url` (`url`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.1 insert ignore into

当插入数据时，出现错误，主键或存在唯一约束字段数据相等，将忽略不再保存数据。

例子：

insert ignore into menu(name,url,parent_id,`index`,create_time) VALUES("用户","user/list",NULL,1,"2021-04-11 20:10:57");

2.2 replace into

当插入数据时，出现主键或存在唯一约束字段数据相等，则先删除数据，再保存数据，对于存在自增主键的，其它表中有关联，则影响较大，此方法一般不建议使用。

例子：

replace into menu(name,url,parent_id,`index`,create_time) VALUES("用户","user/list",NULL,1,"2021-04-11 20:10:57");

2.3 insert into select not exists

当插入数据时，先判断数据是否存在，不存在则插入数据库。

例子：

insert into menu(name,url,parent_id,`index`,create_time) select "用户","user/list",NULL,1,"2021-04-11 20:10:57" from DUALwhere not exists(select url from menu where url="user/list")

2.4 on duplicate key update

当存在唯一约束（key或unique），可以使用此方式，当插入数据时，先判断数据是否存在，不存在则插入数据库，存在则更新数据。

例子：

url唯一

INSERT INTO menu(name,url,parent_id,`index`,create_time) VALUES ("用户","user/list",NULL,1,"2021-04-11 20:10:57") ON DUPLICATE KEY UPDATE name = VALUES(name),parent_id=VALUES(parent_id),`index`=VALUES(`index`)

三、springboot2.3.2+JPA

实体类

package demo.springboot.domain;
import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.GeneratedValue;import javax.persistence.GenerationType;import javax.persistence.Id;import javax.persistence.Table;import com.fasterxml.jackson.annotation.JsonFormat;import lombok.Data;import java.io.Serializable;import java.util.Date;
@Entity@Data@Table(name = "menu")public class Menu implements Serializable {  private static final long serialVersionUID = 8033624715179323397L;   @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "`id`")    private Long id; @Column(name = "`name`") private String name; @Column(name = "`url`") private String url; @Column(name = "`parent_id`")    private Long parentId; @Column(name = "`index`")    private Integer index;  @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")  @Column(name = "`create_time`") private Date createTime;}

3.1 on duplicate key update

Repository方法

 @Query(nativeQuery = true,value = "insert ignore into menu(name,url,parent_id,`index`,create_time) VALUES(:name,:url,:parentId,:index,:createTime)")  @Modifying  @Transactional public int ignoreSave( @Param(value="name")String name, @Param(value="url")String url, @Param(value="parentId")Long parentId, @Param(value="index")int index, @Param(value="createTime")Date createTime);

3.2 insert ignore into

Repository方法

 @Query(nativeQuery = true,value = "insert into menu(name,url,parent_id,`index`,create_time) select :#{#t.name},:#{#t.url},:#{#t.parentId},:#{#t.index},:#{#t.createTime} from DUAL where not exists(select url from menu where url=:#{#t.url})")  @Modifying  @Transactional public int notExistsSave( @Param(value="t")Menu menu);

3.3批量保存

@PersistenceContextprivate EntityManager entityManager; @Transactionalpublic int batchSave(List<Menu> menus) { if(CollectionUtils.isEmpty(menus)) { return 0;  } StringBuilder sb = new StringBuilder()  .append("INSERT INTO menu(name,url,parent_id,`index`,create_time) VALUES "); int size=menus.size(); for(int i=0;i<size;i++) { if(i!=0) { sb.append(",");  } sb.append("(?,?,?,?,?)");  }   sb.append(" ON DUPLICATE KEY UPDATE name = VALUES(name),parent_id=VALUES(parent_id),`index`=VALUES(`index`)");  String sql = sb.toString(); Query query = entityManager.createNativeQuery(sql); int num = 1; for(Menu menu : menus) { query.setParameter(num++, menu.getName()); query.setParameter(num++, menu.getUrl()); query.setParameter(num++, menu.getParentId()); query.setParameter(num++, menu.getIndex()); query.setParameter(num++, menu.getCreateTime()); } return query.executeUpdate();}

四、springboot2.3.2+mybatis

user表

CREATE TABLE `user` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_name` varchar(20) DEFAULT NULL, `password` varchar(50) DEFAULT NULL, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `user_user_name` (`user_name`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=276 DEFAULT CHARSET=utf8mb4;

实体类

package org.spring.springboot.domain;
import java.util.Date;import com.fasterxml.jackson.annotation.JsonFormat;import lombok.Data;
@Datapublic class User { private Long id;  private String userName;  private String password; @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")   private Date createTime;    }

Dao层

/** * 用户DAO 接口类 */public interface UserDao {  Long ignoreSave(User user);  Long replaceSave(User user); Long notExistsSave(User user);  Long batchSave(List<User> list);}

4.1 insert ignore into

 <insert id="ignoreSave" parameterMap="User" useGeneratedKeys="true" keyProperty="id"> insert ignore into user(id,user_name, password, create_time) values (#{id},#{userName},#{password},#{createTime}) </insert>

4.2 replace into

 <insert id="replaceSave" parameterMap="User"> replace into user(user_name, password, create_time) values (#{userName},#{password},#{createTime}) </insert>

4.3 on duplicate key update

 <insert id="notExistsSave" parameterMap="User" useGeneratedKeys="true" keyProperty="id"> insert into user(user_name, password, create_time) select #{userName},#{password},#{createTime}  from DUAL  where not exists(select user_name from user where user_name=#{userName}) </insert>

4.4 批量保存

 <insert id="batchSave" parameterType="list"> insert into user(user_name, password, create_time) values <foreach collection="list" item="item" index="index" separator=","> ( #{item.userName}, #{item.password}, #{item.createTime} ) </foreach> ON duplicate KEY UPDATE `password` = VALUES(`password`),create_time=VALUES(create_time) </insert>