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避免插入重複資料
2019-05-11
MySql
mysql怎麼插入空記錄
2021-09-11
MySql
SQL INSERT INTO 語句詳解：插入新記錄、多行插入和自增欄位
2023-11-19
SQL
Mysql語句查詢指定重複記錄和刪除重複記錄僅保留一條【親測可以】
2018-06-05
MySql
MySQL刪除重複記錄並保留第一條
2021-01-14
MySql
js 防止重複提交方案
2019-04-26
JS
sql server 複製相同表批次插入資料包僅當使用列列表並且 IDENTITY_INSERT 為ON時
2024-03-13
SQLServerIDE
前端如何防止介面重複提交
2024-04-22
前端
PHP 防止表單重複提交
2019-05-13
PHP
刪除重複id的記錄
2024-11-12
SpringBoot如何防止重複提交？- Adrian Adendrata
2019-03-01
Spring Boot
WinForm MDIParent如何防止重複開啟
2019-01-11
ORM
mysql 存在該記錄則更新，不存在則插入記錄的sql
2021-09-09
MySql
mysql插入資料時如果有相同資料就不插入或者替換
2018-04-19
MySql
MySQL唯一鍵約束場景下卻能插入重複行？
2023-03-10
MySql
Struts2防止表單重複提交
2020-11-17
前端防止使用者重複提交-js
2018-04-02
前端JS
MQ 如何防止訊息重複入隊
2024-07-04
MQ
MySQL資料庫中刪除重複記錄的方法總結[推薦]
2021-09-09
MySql資料庫
【BUG記錄】MySQL插入Emoji表情報錯"Incorrect string value"
2024-06-24
MySql
#每日一記#防止按鈕在短時間內重複點選
2018-03-29
MariaDB刪除重複記錄效能測試
2018-06-13
SQL INSERT批次插入方式
2024-07-03
SQL
insert批量插入優化方案
2021-07-07
優化
PHP防止使用者重複提交表單
2019-02-16
PHP
sql刪除重複記錄只保留一條
2021-05-12
SQL
MySQL並行複製-原始碼理解記錄
2023-02-17
MySql並行原始碼
關聯物件（防止自己忘記記錄）
2019-01-14
物件
併發請求的重複插入問題
2020-04-05
如何防止使用者重複提交訂單？（上）
2022-11-22
如何防止使用者重複提交訂單？（下）
2022-11-23
如何防止使用者重複提交訂單？（中）
2022-11-22
Mysql Master-slave複製簡單配置記錄
2018-06-19
MySqlAST
MySQL資料庫查詢多個欄位值全部相同的記錄
2018-09-07
MySql資料庫
Oracle批量插入資料insert all into用法
2018-11-20
Oracle
[LeetCode] 57. Insert Interval 插入區間
2020-09-28
LeetCode
mysql 刪除重複項
2018-07-09
MySql

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

1. INSERT INTO IF EXISTS

1.1.語法

1.2.插入一條記錄

1.3.插入多條記錄

2.唯一性約束

相關文章