值得收藏: 当向数据库导入大量数据时,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 简单且灵活