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

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/zp_00000/article/details/81042589

情景

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

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

MySQL 中可以使用 REPLACEINSERT ... 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 | 123456@163.com | 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'), '123456@163.com', current_date(), current_date()) ON DUPLICATE KEY UPDATE username='xiaoming';

可以看到数据表中只有一条记录,已经将名称改为了 xiaoming 结果如下:

+----+----------+----------------------------------+----------------+---------------------+---------------------+
| id | username | password                         | email          | created_at          | updated_at          |
+----+----------+----------------------------------+----------------+---------------------+---------------------+
|  1 | xiaoming | e10adc3949ba59abbe56e057f20f883e | 123456@163.com | 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'), '123456@163.com', 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 | 123456@163.com | 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 命令的时候要小心

展开阅读全文

没有更多推荐了,返回首页