MySQL 解决批量插入数据去重问题

情景

对大量的数据进行写入数据库操作时，会有这样的问题，如果有重复的数据这些数据有如下特征：
- 完全一模一样
- 有部分不一样

解决思路：
- 把数据表存在的数据先删除，在重新添加一份
- 一样的数据不跟新，部分不一样的数据跟新，完全不一样的插入

MySQL 中可以使用 REPLACE 和 INSERT ... ON DUPLICATE KEY UPDATE 语法实现如上的思路。

INSERT … ON DUPLICATE KEY UPDATE

简介

ON DUPLICATE KEY UPDATE 只需要在 INSERT 语句后面声明 ON DUPLICATE KEY UPDATE 子句，插入数据时
MySQL 就会根据唯一索引和主键进行判断，如果有唯一索引和主键有重复，则会跟新数据，否则会插入数据。

用法

在 UPDATE 后直指定要跟新的属性和值（多个值以逗号隔开）。例如有一张 user 表（建表 SQL 及表结构在文章底部），其中有一条用户数据：
CREATE TABLE `user` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `username`
varchar(255) DEFAULT NULL, `password` varchar(255) DEFAULT NULL, `email` varchar
(255) DEFAULT NULL, `created_at` datetime DEFAULT NULL, `updated_at` datetime
DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; +
----+----------+----------------------------------+----------------+---------------------+---------------------+
| id | username | password | email | created_at | updated_at | +
----+----------+----------------------------------+----------------+---------------------+---------------------+
| 1 | benett | 180fa8ab9bbe78117351740bb6c0ed33 | [email protected] | 2018-07-13
00:00:00 | 2018-07-13 00:00:00 | +
----+----------+----------------------------------+----------------+---------------------+---------------------+
执行如下语句（这里是根据主键是否重复进行判断的）：
insert into user (id, username, password, email, created_at, updated_at) values
(1, 'benett123', md5('123456'), '[email protected]', current_date(), current_date
())ON DUPLICATE KEY UPDATE username='xiaoming';
可以看到数据表中只有一条记录，已经将名称改为了 xiaoming 结果如下：
+----+----------+----------------------------------+----------------
+---------------------+---------------------+ | id | username | password |
email | created_at | updated_at |
+----+----------+----------------------------------+----------------+---------------------+---------------------+
| 1 | xiaoming | e10adc3949ba59abbe56e057f20f883e | [email protected] | 2018-07-13
00:00:00 | 2018-07-13 00:00:00 |
+----+----------+----------------------------------+----------------+---------------------+---------------------+
在 ON DUPLICATE KEY UPDATE 子句中进行复制操作时，可以使用 VALUES() 获取对应列的值。例如：执行如下语句
insert into user (id, username, password, email, created_at, updated_at) values
(1, 'benett123', md5('123456'), '[email protected]', current_date(), current_date
())ON DUPLICATE KEY UPDATE username=values(username), password=values
(password), created_at=values(created_at), updated_at=values(updated_at);
结果如下:
+----+-----------+----------------------------------+----------------
+---------------------+---------------------+ | id | username | password |
email | created_at | updated_at |
+----+-----------+----------------------------------+----------------+---------------------+---------------------+
| 1 | benett123 | e10adc3949ba59abbe56e057f20f883e | [email protected] |
2018-07-13 00:00:00 | 2018-07-13 00:00:00 |
+----+-----------+----------------------------------+----------------+---------------------+---------------------+
注意： VALUES() 函数只有在 INSERT 语句或者 ON DUPLICATE KEY UPDATE 子句中使用有效，在其他地方使用将会返回 NULL
。
在实际操作时，插入数据将不会把主键作为插入的数据（一般都是自增的，插入时生成的），则可以建立唯一索引进行判断标准。

REPLACE

简介

REPLACE 与INSERT 的工作方式完全相同，只是如果表中的旧行与主键或唯一索引的新行具有相同的值，则在插入新行之前删除旧行。

MYSQL 实现 REPLACE 算法如下：
1. 尝试把新数据行插入表中
2. 当插入数据行时因为主键重复或者唯一索引重复插入失败时
a. 删除表中重复的旧数据行
b. 再次重试将新数据插入表中

注意：只有当表具有主键（PRIMARY KEY）或唯一索引（UNIQUE）索引时，REPLACE 才有意义。否则，它变得等同于
INSERT，因为没有索引可用于确定新行是否与另一行重复。

用法（用法与 INSERT 相同，在这主要说一下需要注意的地方）

注意一：当设置组合主键和组合唯一索引时，主键或者唯一索引列值必须与要替换的行的现有行的值匹配;否则，插入一行。

建表语句
CREATE TABLE `test1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `data`
varchar(255) DEFAULT NULL, `created_at` datetime DEFAULT NULL ON UPDATE
CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
可以看出 test1 中有一个主键，执行如下 SQL 语句：
mysql> replace into test1 values(1, 'Old Data', '2018-07-13 19:47:00'); Query
OK, 1 row affected (0.00 sec) mysql> replace into test1 values(1,'New Data',
'2018-07-13 19:47:05'); Query OK, 2 rows affected (0.00 sec) mysql> select *
from test1; +----+----------+---------------------+ | id | data | created_at |
+----+----------+---------------------+ | 1 | New Data | 2018-07-13 19:47:05 |
+----+----------+---------------------+
建表语句:
CREATE TABLE `test2` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `data`
varchar(255) DEFAULT NULL, `created_at` datetime NOT NULL ON UPDATE
CURRENT_TIMESTAMP, PRIMARY KEY (`id`,`created_at`) USING BTREE ) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4;
可以看出 test2 中是组合主键，执行如下 SQL 语句：
mysql> replace into test2 values(1, 'Old Data', '2018-07-13 19:47:00'); Query
OK, 1 row affected (0.01 sec) mysql> replace into test2 values(1,'New Data',
'2018-07-13 19:47:05'); Query OK, 1 row affected (0.01 sec) mysql> select *
from test2; +----+----------+---------------------+ | id | data | created_at |
+----+----------+---------------------+ | 1 | Old Data | 2018-07-13 19:47:00 |
| 1 | New Data | 2018-07-13 19:47:05 | +----+----------+---------------------+
2 rows in set (0.00 sec)
注意二：
如果两张表有外键关联，在主表中使用 replace 命令替换旧数据时，关联表会被清空
CREATE TABLE `test3` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `data`
varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2
DEFAULT CHARSET=utf8mb4; CREATE TABLE `test4` ( `foreign_id` int(10) unsigned
NOT NULL, `created_at` datetime DEFAULT NULL, KEY `fkey` (`foreign_id`),
CONSTRAINT `fkey` FOREIGN KEY (`foreign_id`) REFERENCES `test3` (`id`) ON DELETE
CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
分别在两张表中添加数据
mysql> insert into test3 values(1, 'old data'); Query OK, 1 row affected (0.00
sec) mysql> insert into test4 values(1,'2018-07-14'); Query OK, 1 row affected
(0.01 sec) mysql> insert into test4 values(1,'2018-07-13'); Query OK, 1 row
affected (0.01 sec) mysql> insert into test4 values(1,'2018-07-12'); Query OK,
1 row affected (0.00 sec)mysql> select * from test3; +----+----------+ | id |
data | +----+----------+ | 1 | old data | +----+----------+ 1 row in set (0.00
sec)mysql> select * from test4; +------------+---------------------+ |
foreign_id | created_at | +------------+---------------------+ | 1 | 2018-07-14
00:00:00 | | 1 | 2018-07-13 00:00:00 || 1 | 2018-07-12 00:00:00 |
+------------+---------------------+ 1 row in set (0.01 sec)
插入了测试数据后，我们使用 replace 命令替换之前的重复数据：
mysql> replace into test3 values(1, 'new data'); Query OK, 2 rows affected
(0.02 sec)mysql> select * from test3; +----+----------+ | id | data |
+----+----------+ | 1 | new data | +----+----------+ 1 row in set (0.00 sec)
mysql> select * from test4; Empty set (0.00 sec)
在使用 replace 命令的时候要小心

• « 上一篇：对抗攻击（Adversarial attacks）的常用术语
• » 下一篇：维基解密创始人阿桑奇或将面临最高 175 年监禁