mysql新增时去重复操作

一、mysql 添加索引语句

1.PRIMARY  KEY(主键索引)

mysql>ALTER  TABLE  `table_name`  ADD  PRIMARY  KEY (  `column` )

2.UNIQUE(唯一索引)

mysql>ALTER  TABLE  `table_name`  ADD  UNIQUE (`column` )

3.INDEX(普通索引)

mysql>ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column`  )

4.FULLTEXT(全文索引)

mysql>ALTER  TABLE  `table_name`  ADD  FULLTEXT ( `column` )

5.多列索引

mysql>ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column1`,  `column2`,  `column3`  )

mysql>ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column1`,  `column2`,  `column3`  ), ADD  INDEX index_name (  `column1`)

6.说明
table_name  表名
index_name   自定义索引名称
column   要添加索引的字段

二、 mysql数据库插入去重

根据一个字段去重

比如插入时根据a字段去重，如果要插入的值已在数据库a字段中存在，则不会再重新插入

首先添加a字段的唯一索引：

ALTER TABLE  table_name ADD UNIQUE index_name ( `a`);

然后再插入相同值时便会抛出异常 **Duplicate entry 'a' for key 'index_name '** 
这时再在代码里捕捉异常即可，说明已经存在a字段有重复值了

根据多个字段去重

比如根据a和b两个字段判断， 当a和b字段都不同，即允许其中一个可重复，如插入(a,b)(a,c)(a,d)这样，
但两个都相同则无法插入，只需要再添加一个b字段

ALTER TABLE  table_name ADD UNIQUE index_name ( `a`,`b`);

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

情景
对大量的数据进行写入数据库操作时，会有这样的问题，如果有重复的数据这些数据有如下特征：

• 完全一模一样
• 有部分不一样

解决思路：

• 完全一模一样：把数据表存在的数据先删除，在重新添加一份
• 有部分不一样：一样的数据不跟新，部分不一样的数据跟新，完全不一样的插入

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

ON DUPLICATE KEY UPDATE 简介

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

在 UPDATE 后直指定要跟新的属性和值（多个值以逗号隔开）。例如有一张 user 表，其中有一条用户数据：

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)