MySQL 批量插入，如何不插入重复数据？

今天我们来聊一下mysql 批量插入时，数据如何防止重复插入?

业务很简单：需要批量插入一些数据，数据来源可能是其他数据库的表，也可能是一个外部excel的导入。

mysql使用用insert往数据表中插入数据时，为了不重复插入数据，往往先查询一下该条数据是否已经存在，若不存在才进行插入操作。

那么问题来了，是不是每次插入之前都要查一遍，看看重不重复，在代码里筛选一下数据，重复的就过滤掉呢？

向大数据数据库中插入值时，还要判断插入是否重复，然后插入。如何提高效率？

注意：本文内容仅在 MariaDB-10.2.15 版本下验证，其它环境下可能略有差异。

当数据库中存量数据较多时，或者是在批量插入操作时，很容易出现插入重复数据的问题。

一、三种方法

在 mysql 中，当存在主键冲突或唯一键冲突的情况下，根据插入策略不同，一般有以下三种避免方法：

insert ignore into：若没有则插入，若存在则忽略
replace into：若没有则正常插入，若存在则先删除后插入
insert into ... on duplicate key update：若没有则正常插入，若存在则更新

注意，使用以上方法的前提是表中有一个 PRIMARY KEY 或 UNIQUE 约束/索引，否则，使用以上三个语句没有特殊意义，与使用单纯的 INSERT INTO 效果相同。

在具体介绍三种方法的使用方式之前，均使用如下语句初始化表数据：

"-- 创建表 ljtest --"
CREATE TABLE IF NOT EXISTS ljtest (
id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,  -- 自增主键 id
emp_no INT(11) NOT NULL,
title VARCHAR(50) NOT NULL,
from_date DATE NOT NULL,
to_date DATE DEFAULT NULL)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

"-- 插入三条数据 --"
INSERT INTO ljtest VALUES 
('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),

"-- 查看表数据 --"
select * from ljtest;
+----+--------+-----------------+------------+------------+
| id | emp_no | title           | from_date  | to_date    |
+----+--------+-----------------+------------+------------+
|  1 |  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
|  2 |  10002 | Staff           | 1996-08-03 | 9999-01-01 |
|  3 |  10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
+----+--------+-----------------+------------+------------+
3 rows in set (0.00 sec)

"-- 查看此时初始状态下的表结构 --"
show create table ljtest\G
*************************** 1. row ***************************
       Table: ljtest
Create Table: CREATE TABLE `ljtest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_no` int(11) NOT NULL,
  `title` varchar(50) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

注意：初始状态下 AUTO_INCREMENT=4

一、insert ignore into

1、作用

insert ignore 会根据主键或者唯一键判断，忽略数据库中已经存在的数据
若数据库没有该条数据，就插入为新的数据，跟普通的 insert into 一样
若数据库有该条数据，就忽略这条插入语句，不执行插入操作。

2、主键冲突情况

1. 同时向表中插入两条包含主键的数据：id = 2（表中已有），id = 4（表中没有）

> insert ignore into ljtest(id, emp_no) values (2, 20002), (4, 40004);       
Query OK, 1 row affected, 3 warnings (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 3

注意：

insert ignore into 与普通 insert into 的使用方法几乎完全一样
- 插入时要么指定插入的具体列，要么不指定列名插入全部列值，要么对字段加有默认值
- 否则会报错：
  - ERROR 1136 (21S01): Column count doesn't match value count at row 1
  - 或 ERROR 1364 (HY000): Field 'title' doesn't have a default value

注意插入后返回的信息：
- 1 row affected：插入成功一条数据
- 3 warnings：有三条警告信息
- Duplicates: 1：有一条重复数据

2. 查看 warning 警告信息

> show warnings;
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1364 | Field 'title' doesn't have a default value     |
| Warning | 1364 | Field 'from_date' doesn't have a default value |
| Warning | 1062 | Duplicate entry '2' for key 'PRIMARY'          |
+---------+------+------------------------------------------------+
3 rows in set (0.00 sec)

其中的关键信息就是告诉我们主键为 2 的数据重复了。

3. 查看插入后的表数据

> select * from ljtest;
+----+--------+-----------------+------------+------------+
| id | emp_no | title           | from_date  | to_date    |
+----+--------+-----------------+------------+------------+
|  1 |  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
|  2 |  10002 | Staff           | 1996-08-03 | 9999-01-01 |
|  3 |  10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
|  4 |  40004 |                 | 0000-00-00 | NULL       |
+----+--------+-----------------+------------+------------+
4 rows in set (0.00 sec)

其中，id = 2 的记录还是之前的数据，id = 4 的数据是新插入的。

即，insert ignore into 会忽略已存在数据（按主键或唯一键判断），只插入新数据。

4. 查看插入后的表结构

> show create table ljtest\G
*************************** 1. row ***************************
       Table: ljtest
Create Table: CREATE TABLE `ljtest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_no` int(11) NOT NULL,
  `title` varchar(50) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

注意：插入后 AUTO_INCREMENT=5 （从初始 4 增加为 5），这是正常的。

3、唯一键冲突情况

先重建表数据，对 emp_no 字段增加 unique 约束，并初始化同样的三条数据。

1. 向表中插入一条包含唯一键冲突的数据：emp_no = 10003（表中已有）

insert ignore ljtest(emp_no, title, from_date) values (10003, 'ignore test 3', '0000-00-00');

Query OK, 0 rows affected, 1 warning (0.00 sec)

2. 查看插入后的表数据

跟原数据一样，未发生任何变化。

3. 查看插入后的表结构

> show create table ljtest\G
*************************** 1. row ***************************
       Table: ljtest
Create Table: CREATE TABLE `ljtest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_no` int(11) NOT NULL,
  `title` varchar(50) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `emp_no` (`emp_no`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

注意：虽然未插入成功，但 AUTO_INCREMENT=5 （从初始 4 增加为 5），再次新增数据时，id 将会从 5 开始。

二、insert into ... on duplicate key update

1、作用

在 insert into 语句末尾指定 on duplicate key update，会根据主键或者唯一键判断：
- 若数据库有该条数据，则直接更新原数据，相当于 update
- 若数据库没有该条数据，则插入为新的数据，跟普通的 insert into 一样

2、主键冲突情况

1. 同时向表中插入两条包含主键的数据：id = 2（表中已有），id = 4（表中没有）

> insert into ljtest(id, emp_no, title, from_date) values (2, 20002, 'insert test 2', '0000-00-00'), (4, 40004, 'insert test 4', '0000-00-00') on duplicate key update emp_no=values(emp_no), title=values(title);           

Query OK, 3 rows affected (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 0

其中：

与普通 insert into 的使用语法差别不大，只是语句结尾有所区别
emp_no=values(emp_no), title=values(title)：
- 表示当存在主键或唯一键冲突时，使用插入语句中对应的 emp_no 和 title 值替换原数据
- 同时，还可以通过运算表达式来指定字段值，如：emp_no=emp_no+1, title=title+"test"

因为这里未指定 update from_date 字段，所以当 id 重复时，不会更新 from_date 字段。
3 row affected：
- 插入一条数据，受影响的行为 1；
- 表中原有记录被更新，受影响的行计为 2。

2. 查看插入后的表数据

> select * from ljtest;
+----+--------+-----------------+------------+------------+
| id | emp_no | title           | from_date  | to_date    |
+----+--------+-----------------+------------+------------+
|  1 |  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
|  2 |  20002 | insert test 2  | 1996-08-03 | 9999-01-01 |
|  3 |  10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
|  4 |  40004 | insert test 4  | 0000-00-00 | NULL       |
+----+--------+-----------------+------------+------------+
4 rows in set (0.00 sec)

其中，id = 2 的记录中 emp_no 和 title 字段被替换成新的数据，其它字段还是原来的值。
id = 4 的数据是新插入的。

3. 查看插入后的表结构

> show create table ljtest\G     
*************************** 1. row ***************************
       Table: ljtest
Create Table: CREATE TABLE `ljtest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_no` int(11) NOT NULL,
  `title` varchar(50) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

注意：插入后 AUTO_INCREMENT=5 （从初始 4 增加为 5），这是正常的。

3、唯一键冲突情况

先重建表数据，对 emp_no 字段增加 unique 约束，并初始化同样的三条数据。

1. 向表中插入一条包含唯一键冲突的数据：emp_no = 10003（表中已有）

insert into ljtest(emp_no, title, from_date) values (10003, 'insert test 3', '0000-00-00') on duplicate key update title=values(title);

Query OK, 2 rows affected (0.00 sec)

2. 查看插入后的表数据

+----+--------+-----------------+------------+------------+
| id | emp_no | title           | from_date  | to_date    |
+----+--------+-----------------+------------+------------+
|  1 |  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
|  2 |  10002 | Staff           | 1996-08-03 | 9999-01-01 |
|  3 |  10003 | insert test 3   | 1995-12-03 | 9999-01-01 |
+----+--------+-----------------+------------+------------+
3 rows in set (0.00 sec)

其中，emp_no = 10003 记录的 title 被更新了，但主键 id 不变，即更新了原记录的指定列数据。

3. 查看插入后的表结构

> show create table ljtest\G
*************************** 1. row ***************************
       Table: ljtest
Create Table: CREATE TABLE `ljtest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_no` int(11) NOT NULL,
  `title` varchar(50) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

注意：插入后 AUTO_INCREMENT=5 （从初始 4 增加为 5），再次新增数据时，id 将会从 5 开始。

三、replace into

1、作用

replace into 会根据主键或者唯一键判断：
- 若表中已存在该数据，则先删除此行数据，然后插入新的数据，相当于 delete + insert
  - 可能会丢失数据、主从服务器的 AUTO_INCREMENT 不一致。

- 若表中不存在该数据，则直接插入新数据，跟普通的 insert into 一样

2、主键冲突情况

1. 同时向表中插入两条包含主键的数据：id = 2（表中已有），id = 4（表中没有）

> replace into ljtest(id, emp_no, title, from_date) values (2, 20002, 'replace test 2', '0000-00-00'), (4, 40004, 'replace test 4', '0000-00-00'); 

Query OK, 3 rows affected (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 0

注意：

replace into 与普通 insert into 的使用语法几乎一样
注意插入后返回的信息：
- 3 row affected：删除一条数据，插入两条数据
- Duplicates: 1：有一条重复数据

2. 查看插入后的表数据

> select * from ljtest;
+----+--------+-----------------+------------+------------+
| id | emp_no | title           | from_date  | to_date    |
+----+--------+-----------------+------------+------------+
|  1 |  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
|  2 |  20002 | replace test 2  | 0000-00-00 | NULL       |
|  3 |  10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
|  4 |  40004 | replace test 4  | 0000-00-00 | NULL       |
+----+--------+-----------------+------------+------------+
4 rows in set (0.00 sec)

其中，id = 2 的记录被替换成新的数据、且 to_date 字段原数据丢失，id = 4 的数据是新插入的。

4. 查看插入后的表结构

> show create table ljtest\G     
*************************** 1. row ***************************
       Table: ljtest
Create Table: CREATE TABLE `ljtest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_no` int(11) NOT NULL,
  `title` varchar(50) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

注意：插入后 AUTO_INCREMENT=6 （从初始 4 增加为 5），这是正常的。

3、唯一键冲突情况

先重建表数据，对 emp_no 字段增加 unique 约束，并初始化同样的三条数据。

1. 向表中插入一条包含唯一键冲突的数据：emp_no = 10003（表中已有）

replace into ljtest(emp_no, title, from_date) values (10003, 'replace test 3', '0000-00-00');

Query OK, 2 rows affected (0.00 sec)

2. 查看插入后的表数据

+----+--------+-----------------+------------+------------+
| id | emp_no | title           | from_date  | to_date    |
+----+--------+-----------------+------------+------------+
|  1 |  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
|  2 |  10002 | Staff           | 1996-08-03 | 9999-01-01 |
|  4 |  10003 | replace test 3  | 0000-00-00 | NULL       |
+----+--------+-----------------+------------+------------+
3 rows in set (0.00 sec)

其中，emp_no = 10003 记录的 title 被更新了，同时主键 id 也变成 4 ，即执行了 delete + insert 操作。

3. 查看插入后的表结构

> show create table ljtest\G
*************************** 1. row ***************************
       Table: ljtest
Create Table: CREATE TABLE `ljtest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_no` int(11) NOT NULL,
  `title` varchar(50) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

注意：插入后 AUTO_INCREMENT=5 （从初始 4 增加为 5），再次新增数据时，id 将会从 5 开始。

技术github学习地址：https://github.com/codeGoogler/JavaCodeHub

程序员编程书籍：https://github.com/codeGoogler/

总结：

在主键冲突情况下，三种方法都可以使用
在唯一键冲突情况下，且有自增主键时：
- 三种方法都会出现 AUTO_INCREMENT 不连续问题，且这种不连续不会同步更新到 slave 的 AUTO_INCREMENT
  - 当 master 被 kill，且 slave 升级为 master 时，就会出现主键冲突问题。
replace into 方法可能会导致部分数据丢失。

所以，实际使用时，若是唯一键冲突的情况，一定要谨慎，避免踩坑！

本文参考自csdn，作者：媛测
链接：https://blog.csdn.net/lijing742180/article/details/90243470

补充

MySQL的Replace into 与Insert into on duplicate key update真正的不同之处

replace into和on duplcate key update都是只有在primary key或者unique key冲突的时候才会执行。

没有key的时候，replace与insert .. on deplicate udpate相同。

有key的时候，都保留主键值，并且auto_increment自动+1

如果数据存在，replace into则会将原有数据删除，再进行插入操作，这样就会有一种情况，如果某些字段有默认值，但是replace into语句的字段不完整，则会设置成默认值。而on duplicate key update则是执行update后面的语句。

从底层执行效率上来讲，replace要比insert .. on deplicate update效率要高，但是在写replace的时候，字段要写全，防止老的字段数据被删除。

个人习惯倾向于用on deplicate update。