1.前言
Mysql在插入数据时，需要忽略或替换掉重复的数据（依据某个字段，比如Primary Key或
Unique Key），这时候我们既可以在应用层处理，也可以使用复杂的 SQL 语句来处理（如果仅仅知道一些简单的 SQL 语法的话），当然也可以使用一些简单的 SQL 语法，不过它并不是通用所有的数据库类型。
下面我们以MySQL为例，研究一下insert 怎样去忽略或替换重复数据
2.表实例
表名称：person
表字段：
| Column Name | Primary Key | Auto Increment | Unique |
|---|
| id | true | true | | | name | | | true | | age | | |
初始表数据：
3.三个简单例子：
Note：本文的3个例子都需要被插入的表中存在UNIQUE索引或PRIMARY KEY字段
1. 不存在则插入，存在则更新
1.1 on duplicate key update
如果插入的数据会导致UNIQUE 索引或PRIMARY KEY发生冲突/重复，则执行UPDATE语句，例：
INSERT INTO `person`(`name`, `age`) VALUES('Bruce', 18)
? ON DUPLICATE KEY?
? UPDATE `age`=19; -- If will happen conflict, the update statement is executed
-- 2 row(s) affected
这里受影响的行数是2，因为数据库中存在name='Bruce'的数据，如果不存在此条数据，则受影响的行数为1。
最新的表数据如下：
1.2 replace into
如果插入的数据会导致UNIQUE 索引或PRIMARY KEY发生冲突/重复，则先删除旧数据再插入最新的数据，例：
REPLACE INTO `person`(`name`, `age`) VALUES('Bruce', 20);
-- 2 row(s) affected
这里受影响的行数是2，因为数据库中存在name='Jack'的数据，并且id的值会变成2，因为它是先删除旧数据，然后再插入数据，最新的表数据如下：
2. 避免重复插入（存在则忽略）
关键字/句：insert ignore into，如果插入的数据会导致UNIQUE索引或PRIMARY KEY发生冲突/重复，则忽略此次操作/不插入数据，例：
INSERT IGNORE INTO `person`(`name`, `age`) VALUES('Bruce', 18);
-- 0 row(s) affected
这里已经存在name='Bruce'的数据，所以会忽略掉新插入的数据，受影响行数为0，表数据不变。
4.三个复杂例子：
?Mapper类：
package com.example.springbootmybatisplusbruce.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.springbootmybatisplusbruce.model.E**Customer;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface E**CustomerMapper extends BaseMapper<E**Customer> {
/**
* 不存在则插入，存在则更新
* on duplicate key update: 如果插入的数据会导致UNIQUE 索引或PRIMARY KEY发生冲突/重复，则执行UPDATE语句
* @param e**Customer
* @return
*/
public int insertDuplicateKeyUpdate(E**Customer e**Customer);
/**
* replace into: 如果插入的数据会导致UNIQUE索引 或 PRIMARY KEY 发生冲突/重复，则先删除旧数据,再插入最新的数据
* @param etcCustomer
* @return
*/
public int insertReplaceInto(E**Customer e**Customer);
/**
* 避免重复插入
* insert ignore into: 如果插入的数据会导致UNIQUE索引或PRIMARY KEY发生冲突/重复，则忽略此次操作/不插入数据
* @param e**Customer
* @return
*/
public int insertIgnore(E**Customer e**Customer);
}
xml文件：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.springbootmybatisplusbruce.mapper.E**CustomerMapper">
<resultMap type="com.example.springbootmybatisplusbruce.model.E**Customer" id="E**CustomerResult">
<result property="id" column="id" />
<result property="customerType" column="customer_type" />
<result property="customerName" column="customer_name" />
<result property="customerMobile" column="customer_mobile" />
.......................................................................
</resultMap>
<sql id="selectE**CustomerVo">
select id, customer_type, customer_name, customer_mobile,
...........................................................................
from etc_customer
</sql>
<select id="selectE**CustomerList" parameterType="com.example.springbootmybatisplusbruce.model.E**Customer" resultMap="E**CustomerResult">
<include refid="selectE**CustomerVo"/>
<where>
<if test="customerType != null "> and customer_type = #{customerType}</if>
<if test="customerName != null and customerName != ''"> and customer_name like concat('%', #{customerName}, '%')</if>
<if test="customerMobile != null and customerMobile != ''"> and customer_mobile = #{customerMobile}</if>
.......................................................................
</where>
</select>
<select id="selectE**CustomerById" parameterType="Long" resultMap="E**CustomerResult">
<include refid="selectE**CustomerVo"/>
where id = #{id}
</select>
<!-- 不存在则插入，存在则更新 -->
<!-- on duplicate key update: 如果插入的数据会导致UNIQUE 索引或PRIMARY KEY发生冲突/重复，则执行UPDATE语句 -->
<insert id="insertDuplicateKeyUpdate" parameterType="com.example.springbootmybatisplusbruce.model.E**Customer">
INSERT INTO e**_customer(id, customer_type, customer_name, customer_mobile, credential_type, credential_no, status, del_flag, create_by, create_time, update_by, update_time, remark)
VALUES(#{id}, #{customerType}, #{customerName}, #{customerMobile}, #{credentialType}, #{credentialNo}, #{status}, #{delFlag}, #{createBy}, #{createTime}, #{updateBy}, #{updateTime}, #{remark})
ON DUPLICATE KEY
UPDATE
<if test="customerType != null">customer_type=#{customerType},</if>
<if test="customerName != null and customerName != ''">customer_name=#{customerName},</if>
<if test="customerMobile != null and customerMobile != ''">customer_mobile=#{customerMobile},</if>
<if test="credentialType != null">credential_type=#{credentialType},</if>
<if test="credentialNo != null and credentialNo != ''">credential_no=#{credentialNo},</if>
<if test="status != null">status=#{status}</if>
</insert>
<!-- replace into: 如果插入的数据会导致UNIQUE索引 或 PRIMARY KEY 发生冲突/重复，则先删除旧数据再插入最新的数据 -->
<insert id="insertReplaceInto">
REPLACE INTO e**_customer(id, customer_type, customer_name, customer_mobile, credential_type, credential_no, status, del_flag, create_by, create_time, update_by, update_time, remark)
VALUES(#{id}, #{customerType}, #{customerName}, #{customerMobile}, #{credentialType}, #{credentialNo}, #{status}, #{delFlag}, #{createBy}, #{createTime}, #{updateBy}, #{updateTime}, #{remark})
</insert>
<!-- 避免重复插入 -->
<!-- insert ignore into: 如果插入的数据会导致UNIQUE索引或PRIMARY KEY发生冲突/重复，则忽略此次操作/不插入数据 -->
<insert id="insertIgnore">
INSERT IGNORE INTO e**_customer(id, customer_type, customer_name, customer_mobile, credential_type, credential_no, status, del_flag, create_by, create_time, update_by, update_time, remark)
VALUES(#{id}, #{customerType}, #{customerName}, #{customerMobile}, #{credentialType}, #{credentialNo}, #{status}, #{delFlag}, #{createBy}, #{createTime}, #{updateBy}, #{updateTime}, #{remark})
</insert>
</mapper>
?service类：
package com.example.springbootmybatisplusbruce.service;
import com.example.springbootmybatisplusbruce.mapper.ETCCustomerMapper;
import com.example.springbootmybatisplusbruce.model.EtcCustomer;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.LineIterator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.util.List;
@Service
public class FTPFileParseService {
@Autowired
private E**CustomerMapper e**CustomerMapper;
/**
* 参考文章：https://blog.csdn.net/t894690230/article/details/77996355
* https://blog.csdn.net/weixin_45607513/article/details/117470118
* @throws IOException
*/
@Transactional(rollbackFor = Exception.class)
public void fileParse() throws IOException {
StringBuilder result = new StringBuilder();
String path = "F:\\Digital marketing\\E** system\\txt-from-ftp\\20210302_VEHICLE.txt";
long start = System.currentTimeMillis(); //程序执行前的时间戳
BufferedReader br = new BufferedReader(new FileReader(path));//构造一个BufferedReader类来读取文件
String line = null;
while((line = br.readLine())!=null){//使用readLine方法，一次读一行
// result.append(System.lineSeparator()+s);
System.out.println("Debug:" + line);
String [] infoArray = line.split("@~@");
EtcCustomer e**Customer = new EtcCustomer();
if(infoArray[0].isEmpty()){continue;}
e**Customer.setId(Long.valueOf(infoArray[0]).longValue());
e**Customer.setCustomerType(Long.valueOf(infoArray[4]).longValue());
e**Customer.setCustomerName(infoArray[2]);
e**Customer.setCustomerMobile(infoArray[3]);
e**Customer.setCredentialType(Long.valueOf(infoArray[5]).longValue());
e**Customer.setCredentialNo(infoArray[6]);
e**Customer.setStatus(Long.valueOf(infoArray[15]).longValue());
e**Customer.setDelFlag(0L);
//on duplicate key update: 如果插入的数据会导致UNIQUE 索引或PRIMARY KEY发生冲突/重复，则执行UPDATE语句
// e**CustomerMapper.insertDuplicateKeyUpdate(etcCustomer);
//insert ignore into: 如果插入的数据会导致UNIQUE索引或PRIMARY KEY发生冲突/重复，则忽略此次操作/不插入数据
e**CustomerMapper.insertIgnore(etcCustomer);
//replace into: 如果插入的数据会导致UNIQUE索引 或 PRIMARY KEY 发生冲突/重复，则先删除旧数据,再插入最新的数据
// e**CustomerMapper.insertReplaceInto(etcCustomer);
}
br.close();
long end = System.currentTimeMillis(); //程序执行后的时间戳
System.out.println("程序执行花费时间：" + (end - start));
}
}
|