MySQL防止重复插入相同记录 insert if not exists

在 MySQL 中，插入（insert）一条记录，经常需要检查这条记录是否已经存在，只有当记录不存在时才执行插入操作
1. INSERT INTO IF EXISTS
1.1.语法
INSERT INTO TABLE (field1, field2, fieldn) SELECT
'field1',
'field2',
'fieldn'
FROM
DUAL
WHERE
NOT EXISTS (
SELECT
field
FROM
TABLE
WHERE
field = ?
)
1.2.插入一条记录
先创建一张表
CREATE TABLE `pay_namelist` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`batchno` varchar(20) DEFAULT NULL COMMENT '批次号',
`idserial` varchar(20) DEFAULT NULL COMMENT '证件号',
`useranme` varchar(60) DEFAULT NULL COMMENT '姓名',
`payproid` int(11) DEFAULT NULL COMMENT '缴费项目ID',
`subpayproid` int(11) DEFAULT NULL COMMENT '子缴费项ID',
`impdate` datetime DEFAULT NULL COMMENT '导入时间',
`paystatus` varchar(2) DEFAULT NULL COMMENT '支付状态 0-未缴费 1-已缴费',
`payamt` int(11) DEFAULT NULL COMMENT '缴费金额',
`status` varchar(2) DEFAULT NULL COMMENT '状态 0-删除 1-正常',
`orgcode` varchar(20) DEFAULT NULL COMMENT '机构代码',
`orderno` varchar(32) DEFAULT NULL,
`reservestr1` varchar(200) DEFAULT NULL COMMENT '预留字段1',
`reservestr2` varchar(200) DEFAULT NULL COMMENT '预留字段2',
PRIMARY KEY (`id`),
KEY `idx_paynl_idserial` (`idserial`) USING BTREE,
KEY `idx_paynl_orderno` (`orderno`)
) ENGINE=InnoDB AUTO_INCREMENT=352119 DEFAULT CHARSET=utf8 COMMENT='缴费人员名单';
在pay_namelist重复插入相同的一条数据
INSERT INTO pay_namelist (
`batchno`,
`idserial`,
`useranme`,
`payproid`,
`subpayproid`,
`impdate`,
`paystatus`,
`payamt`,
`status`,
`orgcode`,
`orderno`,
`reservestr1`,
`reservestr2`
) SELECT
'2018032016204085',
'2431503022',
'wanghan',
'470',
NULL,
'2018-03-20 16:22:05',
'0',
'1300000',
'1',
'26',
'20180320162241705',
NULL,
NULL
FROM
DUAL
WHERE
NOT EXISTS (
SELECT
batchno,
payproid,
idserial,
payamt
FROM
pay_namelist
WHERE
batchno = '2018032016204085'
AND payproid = '470'
AND idserial = '161'
AND payamt = '1300000'
)

第一次执行：Affected rows: 1，之后执行都是Affected rows: 0，说明实现了单条记录的防重插入；

1.3.插入多条记录
插入多条记录，需要借助一张临时表
创建临时表
CREATE TABLE `pay_namelist_temp` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`batchno` varchar(20) DEFAULT NULL COMMENT '批次号',
`idserial` varchar(20) DEFAULT NULL COMMENT '证件号',
`useranme` varchar(60) DEFAULT NULL COMMENT '姓名',
`payproid` int(11) DEFAULT NULL COMMENT '缴费项目ID',
`subpayproid` int(11) DEFAULT NULL COMMENT '子缴费项ID',
`impdate` datetime DEFAULT NULL COMMENT '导入时间',
`paystatus` varchar(2) DEFAULT NULL COMMENT '支付状态 0-未缴费 1-已缴费',
`payamt` int(11) DEFAULT NULL COMMENT '缴费金额',
`status` varchar(2) DEFAULT NULL COMMENT '状态 0-删除 1-正常',
`orgcode` varchar(20) DEFAULT NULL COMMENT '机构代码',
`orderno` varchar(32) DEFAULT NULL,
`reservestr1` varchar(200) DEFAULT NULL COMMENT '预留字段1',
`reservestr2` varchar(200) DEFAULT NULL COMMENT '预留字段2',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=352124 DEFAULT CHARSET=utf8 COMMENT='缴费人员名单';
在临时表插入8条记录
INSERT INTO pay_namelist_temp ( `batchno`, `idserial`, `useranme`, `payproid`, `subpayproid`, `impdate`, `paystatus`, `payamt`, `status`, `orgcode`, `orderno`, `reservestr1`, `reservestr2`) VALUES ( '201712251109117', '106', '测6', '276', NULL, '2017-12-25 11:09:18', '0', '1', '1', '89', '20171225110917911', NULL, NULL);
INSERT INTO pay_namelist_temp ( `batchno`, `idserial`, `useranme`, `payproid`, `subpayproid`, `impdate`, `paystatus`, `payamt`, `status`, `orgcode`, `orderno`, `reservestr1`, `reservestr2`) VALUES ( '201712251109117', '105', '测5', '276', NULL, '2017-12-25 11:09:18', '1', '1', '1', '89', '20171225110917914', NULL, NULL);
INSERT INTO pay_namelist_temp ( `batchno`, `idserial`, `useranme`, `payproid`, `subpayproid`, `impdate`, `paystatus`, `payamt`, `status`, `orgcode`, `orderno`, `reservestr1`, `reservestr2`) VALUES ( '201712251109117', '107', '测7', '276', NULL, '2017-12-25 11:09:18', '1', '1', '1', '89', '20171225110917917', NULL, NULL);
INSERT INTO pay_namelist_temp ( `batchno`, `idserial`, `useranme`, `payproid`, `subpayproid`, `impdate`, `paystatus`, `payamt`, `status`, `orgcode`, `orderno`, `reservestr1`, `reservestr2`) VALUES ( '201712251109117', '102', '测2', '276', NULL, '2017-12-25 11:09:18', '0', '1', '1', '89', '20171225110917920', NULL, NULL);
INSERT INTO pay_namelist_temp ( `batchno`, `idserial`, `useranme`, `payproid`, `subpayproid`, `impdate`, `paystatus`, `payamt`, `status`, `orgcode`, `orderno`, `reservestr1`, `reservestr2`) VALUES ( '201712251109117', '103', '测3', '276', NULL, '2017-12-25 11:09:18', '0', '1', '1', '89', '20171225110917923', NULL, NULL);
INSERT INTO pay_namelist_temp ( `batchno`, `idserial`, `useranme`, `payproid`, `subpayproid`, `impdate`, `paystatus`, `payamt`, `status`, `orgcode`, `orderno`, `reservestr1`, `reservestr2`) VALUES ( '201712251109117', '108', '测8', '276', NULL, '2017-12-25 11:09:18', '0', '1', '1', '89', '20171225110917925', NULL, NULL);
INSERT INTO pay_namelist_temp ( `batchno`, `idserial`, `useranme`, `payproid`, `subpayproid`, `impdate`, `paystatus`, `payamt`, `status`, `orgcode`, `orderno`, `reservestr1`, `reservestr2`) VALUES ( '201712251109117', '104', '测4', '276', NULL, '2017-12-25 11:09:18', '1', '1', '1', '89', '20171225110917928', NULL, NULL);
INSERT INTO pay_namelist_temp ( `batchno`, `idserial`, `useranme`, `payproid`, `subpayproid`, `impdate`, `paystatus`, `payamt`, `status`, `orgcode`, `orderno`, `reservestr1`, `reservestr2`) VALUES ( '201712251109117', '101', '测1', '276', NULL, '2017-12-25 11:09:18', '1', '1', '1', '89', '20171225110917930', NULL, NULL);
在pay_namelist重复插入相同的多条数据
INSERT INTO pay_namelist (
`batchno`,
`idserial`,
`useranme`,
`payproid`,
`subpayproid`,
`impdate`,
`paystatus`,
`payamt`,
`status`,
`orgcode`,
`orderno`,
`reservestr1`,
`reservestr2`
) SELECT
`batchno`,
`idserial`,
`useranme`,
`payproid`,
`subpayproid`,
`impdate`,
`paystatus`,
`payamt`,
`status`,
`orgcode`,
`orderno`,
`reservestr1`,
`reservestr2`
FROM
pay_namelist_temp
WHERE
NOT EXISTS (
SELECT
batchno,
payproid,
idserial,
payamt
FROM
pay_namelist
WHERE
pay_namelist.batchno = pay_namelist_temp.batchno
AND pay_namelist.payproid = pay_namelist_temp.payproid
AND pay_namelist.idserial = pay_namelist_temp.idserial
AND pay_namelist.payamt = pay_namelist_temp.payamt
)
第一次执行：Affected rows: 1，之后执行都是Affected rows: 0，说明实现了单条记录的防重插入；