值得收藏:当向数据库导入大量数据时,mysql主键唯一键重复插入,如何丝滑操作并不导入重复数据呢

最近要导入大量数据到数据库，数据库中有数据列要求唯一的，也就是唯一键，但是我拿到的数据有部分重复，一运行就会出错，如果把重复数据找出来，删除后在导入，太麻烦了，所以想要丝滑导入,并忽略重复数据,有了下面的解决方案:

本次案例使用phpmyadmin进行演示

1.准备

比如我有如下的数据表(order):orderNo为唯一列

目前表中已存在如下数据:

建表代码如下:当然你也可以使用PHPmyadmin或者Navicat等管理工具,使用可视化的方式创建数据库和表

--建表
CREATE DATABASE hotel;

--
-- 表的结构 `order`
--

CREATE TABLE IF NOT EXISTS `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `orderNo` varchar(80) NOT NULL DEFAULT '0',
  `proId` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `orderNo` (`orderNo`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=6 ;

--
-- 转存表中的数据 `order`
--

INSERT INTO `order` (`id`, `orderNo`, `proId`) VALUES
(1, '12345678', 1),
(2, '12345676', 1),
(3, '12345876', 2),
(4, '12345877', 2),
(5, '12345879', 3);

我现在拿到的数据如下:

INSERT INTO `order` ( `orderNo`, `proId`) VALUES
('12345678', 1),
('12345696', 1),
('12345886', 2),
('12345877', 2),
('32345817', 2),
('12345827', 2),
('12345887', 2),
('12345849', 3);

上面的数据中有多条重复,如果这时候导入,会出现下面的报错:

执行到第一条的时候就已经有重复的数据.所以在执行导入第一条数据时就会报错,剩下的数据不会再继续执行,但是如果导入的数据中前面没有重复的,只是中间某一条和数据库orderNo列已存在的数据重复,前面重复的数据会导入,直到执行到出现重复那条数据,然后出现如上图一样的报错,并停止执行数据导入.

那么遇到以上问题,想要丝滑导入不重复的数据,且不会因为个别数据重复,影响数据导入中断,应该怎么操作?

2.解决方法:

1.初级方法:

a.insert后面添加ignore

使用ignore当插入的值遇到​​主键​​(PRIMARY KEY)或者唯一键(UNIQUE KEY)重复时自动忽略重复的记录行，而且不会影响后面的记录行的插入。

比如我把执行脚本改为如下代码:

INSERT ignore INTO `order` ( `orderNo`, `proId`) VALUES
('12345678', 1),
('12345696', 1),
('12345886', 2),
('12345877', 2),
('32345817', 2),
('12345827', 2),
('12345887', 2),
('12345849', 3);

这时候执行这个SQL语句就能丝滑导入数据:从下图执行的结果可以看出,不重复的数据已经完全导入,而且会忽略重复数据,不受重复数据的影响

2.replace

a.使用replace代替insert

使用replace插入的记录遇到主键或者唯一键重复时会先删除表中已存在的重复记录行再执行插入

代码如下:

replace INTO `order` ( `orderNo`, `proId`) VALUES
('12345678', 1),
('12345696', 1),
('12345886', 2),
('12345877', 2),
('32345817', 2),
('12345827', 2),
('12345887', 2),
('12345849', 3);

执行结果如下:

从执行结果的自增ID就可以看出replace会先删除已有数据再插入

3.使用更新的方式插入已有数据​

ON DUPLICATE KEY UPDATE为Mysql特有语法，语句的作用，当​​insert​​已经存在的记录时，执行Update

Mysql告诉我们，我们的主键冲突了，我们是不是可以改变一下思路，当插入已存在主键的记录时，将插入操作变为修改：

INSERT INTO `order` (id,orderNo,proId) VALUES(2,'88888',4) ON DUPLICATE KEY UPDATE id = 88,orderNo='888888';

执行结果如下:

本来id为2的数据变为88且orderNo更新为888888

VALUES修改

那么问题又来了，有人会说我ON DUPLICATE KEY UPDATE 后面跟的是固定的值，如果我想要分别给不同的记录插入不同的值怎么办呢？

可以将后面的修改条件改为VALUES(orderNo)，动态的传入要修改的值，执行以下：

INSERT INTO `order` (id,orderNo,proId) VALUES(3,'808080808',4),(88,'9191919',4) ON DUPLICATE KEY UPDATE orderNo=VALUES(orderNo);

执行结果:

我们成功的为不同id的orderNo修改成了不同的值

3.总结

修改的方法有很多种，包括SET或用REPLACE，ignore,连事务都省了，这些在日常的使用当中已经能够满足导入需求,ON DUPLICATE KEY UPDATE能够让我们便捷的完成重复插入的开发需求，但它是Mysql的特有语法，使用时应多注意主键和插入值是否是我们想要插入或修改的key、Value。前面几种较,ON DUPLICATE KEY UPDATE简单且灵活