MySQL防止重複插入相同記錄 insert if not exists

琦彥發表於2018-03-21

在 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

MySql避免重複插入記錄的幾種方法
2021-09-09
MySql
MySQL：使用INSERT 插入多條記錄
2014-08-08
MySql
mysql避免插入重複資料
2019-05-11
MySql
mysql表刪除重複記錄方法
2017-01-20
MySql
MYSQL中刪除重複記錄的方法薦
2008-05-20
MySql
防止Toast重複提醒
2016-08-01
AST
Oracle刪重複記錄
2007-12-27
Oracle
Mysql語句查詢指定重複記錄和刪除重複記錄僅保留一條【親測可以】
2018-06-05
MySql
mysql怎麼插入空記錄
2021-09-11
MySql
MySQL刪除重複記錄並保留第一條
2021-01-14
MySql
js 防止重複提交方案
2019-04-26
JS
刪除Oracle重複記錄
2009-03-10
Oracle
SQL重複記錄查詢
2010-10-26
SQL
oracle刪除重複記錄
2007-10-11
Oracle
SQL INSERT INTO 語句詳解：插入新記錄、多行插入和自增欄位
2023-11-19
SQL
陣列重複不新增、相同刪除，相同賦值
2017-09-07
陣列賦值
PHP 防止表單重複提交
2019-05-13
PHP
前端如何防止介面重複提交
2024-04-22
前端
sql重複插入問題
2017-03-10
SQL
MySQL刪除表重複記錄的三種方法舉例
2016-08-25
MySql
mysql刪除重複記錄，儲存Id最小的一條
2016-08-05
MySql
Oracle查詢重複資料與刪除重複記錄
2015-03-13
Oracle
Oracle查詢重複資料與刪除重複記錄方法
2013-05-24
Oracle
WinForm MDIParent如何防止重複開啟
2019-01-11
ORM
防止指令碼重複執行方法
2015-02-08
指令碼
DB2 刪除重複記錄
2011-01-06
DB2
MySQL 當記錄不存在時插入，當記錄存在時更新
2014-07-04
MySql
mysql去除某些欄位重複的紀錄
2009-08-14
MySql
MySQL INSERT插入條件判斷：如果不存在則插入
2014-05-28
MySql
mysql忽略主鍵衝突、避免重複插入的幾種方式
2016-02-24
MySql
mysql 忽略主鍵衝突、避免重複插入的幾種方式
2014-07-31
MySql
sql server 複製相同表批次插入資料包僅當使用列列表並且 IDENTITY_INSERT 為ON時
2024-03-13
SQLServerIDE
mysql 存在該記錄則更新，不存在則插入記錄的sql
2021-09-09
MySql
前端防止使用者重複提交-js
2018-04-02
前端JS
防止表單重複提交的程式碼
2017-03-09
如何防止jQuery物件動畫重複執行
2017-03-14
jQuery物件動畫
jquery防止重複提交程式碼例項
2017-03-12
jQuery
Oracle使用over()partition by刪除重複記錄
2017-04-05
Oracle

MySQL防止重複插入相同記錄 insert if not exists

1. INSERT INTO IF EXISTS

1.1.語法

1.2.插入一條記錄

1.3.插入多條記錄

2.唯一性約束

相關文章