mysql在存在主键冲突或者唯一键冲突的情况下,根据插入策略不同,一般有以下三种避免方法。
1、insert ignore
2、replace into
3、insert on duplicate key update
4、insert … select … where not exist
前面三种请移步:https://www.cnblogs.com/prayer21/p/6018864.html
主要结合mybatis,说下第四种:
<insert id="saveEmployeeDevice">
insert into sys_user_device(
sz_employ_id , device_name)
SELECT employId, deviceName
FROM
(<foreach collection="records" item="record" separator=" union all ">
SELECT #{record.FENo, jdbcType=VARCHAR} as employId,
#{record.FMNo, jdbcType=VARCHAR} as deviceName
</foreach>) r
WHERE NOT EXISTS
(SELECT 1 FROM sys_user_device WHERE sz_employ_id = r.employId AND device_name = r.deviceName)
</insert>
上面代码保证了sz_deploy_id和device_name这两个字段不会重复:批量插入的数据中,如果有某条数据的 sz_deploy_id和device_name字段跟sys_user_device表里的相同;则该条数据不会插入表中。
注:这个方法只是将插入的数据跟表里面的逐条比较,不存在表中则插入,存在则跳过,但是如果批量插入的数据中,本身就有重复数据,就会导致插入表里的数据sz_deploy_id和device_name也会有重复。
例如:插入两条数据,sz_deploy_id=4800,device_name=’黄村设备‘
INSERT INTO sys_user_device (sz_employ_id, device_name) SELECT
employId,
deviceName
FROM
(
SELECT
'100867' AS employId,
'珠村设备1' AS deviceName
UNION ALL
SELECT
'4800' AS employId,
'黄村设备' AS deviceName
UNION ALL
SELECT
'4800' AS employId,
'黄村设备' AS deviceName
) r
WHERE
NOT EXISTS (
SELECT
1
FROM
sys_user_device
WHERE
sz_employ_id = r.employId
AND device_name = r.deviceName
)
结果会是这样: