在 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，说明实现了单条记录的防重插入；
2.唯一性约束
https://blog.csdn.net/fly910905/article/details/79693070
