最近工作很忙,很久没写博客,嘻嘻!今天写一遍关于MySQL重复数据处理的博客!前不久有个开发问我,能不能把重复的数据去除,留下唯一的数据。然后我问他为什么有这么重复的数据呢,他说写了程序去爬数据,爬到很多重复的。我擦,你就不能添加主键或者唯一键保证行数据的唯一性吗?表或结果集有时含有重复记录,有时它是允许的,但有时它被要求停止使用重复记录。有时,需要识别重复记录并从表中删除它们,下面我们举几个小例子说明下。

 

需求:

1、向一张表里插入数据,如果行数据存在就不插入,当行数据不存在就插入

2、删除一个表里出现相同的行记录

 

一、向一个表插入数据,表没有主键和唯一键的情况下,可以插入重复数据,而且不会报错:

  1. mysql> show create table user\G
  2. *************************** 1. row ***************************
  3. Table: user
  4. Create Table: CREATE TABLE `user` (
  5. `first_name` char(20) DEFAULT NULL,
  6. `code` char(20) DEFAULT NULL,
  7. `sex` char(10) DEFAULT NULL
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  9. 1 row in set (0.00 sec)
  10. mysql>

往表里插入数据,可以插入大量的重复数据而且不会报错:

  1. mysql> insert into user values (\'aa\',\'GZ\',\'M\');
  2. Query OK, 1 row affected (0.05 sec)
  3. mysql> insert into user values (\'aa\',\'GZ\',\'M\');
  4. Query OK, 1 row affected (0.04 sec)
  5. mysql> select * from user;
  6. +------------+------+------+
  7. | first_name | code | sex |
  8. +------------+------+------+
  9. | aa | GZ | M |
  10. | aa | GZ | M |
  11. +------------+------+------+
  12. 2 rows in set (0.00 sec)
  13. mysql>

为了防止表中被创建的多个记录具有相同的值,添加一个主键(PRIMARY KEY)到它的定义。 当要做这一点,也必须声明索引列是NOT NULL,因为PRIMARY KEY不允许NULL值:

  1. mysql> show create table user\G
  2. *************************** 1. row ***************************
  3. Table: user
  4. Create Table: CREATE TABLE `user` (
  5. `first_name` char(20) NOT NULL DEFAULT \'\',
  6. `code` char(20) NOT NULL DEFAULT \'\',
  7. `sex` char(10) DEFAULT NULL,
  8. PRIMARY KEY (`first_name`,`code`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  10. 1 row in set (0.00 sec)
  11. mysql>

如果插入一条与现有记录重复到表,在列或定义索引列,表中一个唯一索引的存在通常会导致错误的发生:

  1. mysql> insert into user values (\'aa\',\'GZ\',\'M\');
  2. Query OK, 1 row affected (0.04 sec)
  3. mysql> insert into user values (\'aa\',\'GZ\',\'M\');
  4. ERROR 1062 (23000): Duplicate entry \'aa-GZ\' for key \'PRIMARY\'
  5. mysql>

可以看到了吧,报错了,如果是执行一个sql脚本,报错就可能退出脚本了,后面的SQL语句就不会执行了,当然你可以加上 -f 参数。但报错的提示总是让人感觉到不安,哈哈,我们可以用INSERT IGNORE和REPLACE INTO去插入数据,朋友友可以通过HELP INSERT 和 HELP REPLACE去了解下用法,这里不作过多的说明

  1. mysql> select * from user;
  2. +------------+------+------+
  3. | first_name | code | sex |
  4. +------------+------+------+
  5. | aa | GZ | M |
  6. +------------+------+------+
  7. 1 row in set (0.00 sec)
  8. mysql> insert ignore user values (\'aa\',\'GZ\',\'M\');
  9. Query OK, 0 rows affected (0.00 sec)
  10. mysql>

可以看到用INSERT IGNORE的语法插入就没报错了,如果记录与现有现有不重复时,MySQL将其正常插入。如果记录是一个重复的,则 IGNORE 关键字告诉MySQL丢弃它而不会产生错误。

我们再来看看用REPLACE INTO的效果:

  1. mysql> replace into user values (\'aa\',\'GZ\',\'M\');
  2. Query OK, 1 row affected (0.05 sec)
  3. mysql> select * from user;
  4. +------------+------+------+
  5. | first_name | code | sex |
  6. +------------+------+------+
  7. | aa | GZ | M |
  8. +------------+------+------+
  9. 1 row in set (0.00 sec)

可以看到没报错,使用REPLACE,如果记录是新的,它插入就像使用INSERT。如果它是重复的,新的记录将取代旧的记录。

INSERT IGNORE和REPLACE应根据实现的重复处理行为来选择。INSERT忽略保持第一套重复记录,并丢弃剩下的。REPLACE保持最后一组重复的和擦除任何较早的记录。

 

二、从查询结果消除重记录

方法一:

  1. mysql> show create table user2\G
  2. *************************** 1. row ***************************
  3. Table: user2
  4. Create Table: CREATE TABLE `user2` (
  5. `first_name` char(20) DEFAULT NULL,
  6. `code` char(20) DEFAULT NULL,
  7. `sex` char(10) DEFAULT NULL
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  9. 1 row in set (0.00 sec)
  10. mysql>

往表里插入相当数据做测试:

  1. mysql> insert into user2 values (\'bb\',\'BJ\',\'N\');
  2. Query OK, 1 row affected (0.03 sec)
  3. mysql> insert into user2 values (\'bb\',\'BJ\',\'N\');
  4. Query OK, 1 row affected (0.02 sec)
  5. mysql> insert into user2 values (\'cc\',\'SH\',\'M\');
  6. Query OK, 1 row affected (0.03 sec)
  7. mysql> insert into user2 values (\'cc\',\'SH\',\'M\');
  8. Query OK, 1 row affected (0.03 sec)

我们可以从查询的结果中消除相同的记录:

  1. mysql> SELECT DISTINCT first_name,code FROM user2 ORDER BY first_name;
  2. +------------+------+
  3. | first_name | code |
  4. +------------+------+
  5. | bb | BJ |
  6. | cc | SH |
  7. +------------+------+
  8. 2 rows in set (0.00 sec)
  9. mysql>

替代DISTINCT方法是添加GROUP BY子句列名称到选择的列。这有删除重复并选择在指定的列值的唯一组合的效果:

  1. mysql> CREATE TABLE tmp SELECT first_name,code,sex FROM user2 GROUP BY (first_name,code);
  2. ERROR 1786 (HY000): CREATE TABLE ... SELECT is forbidden when ENFORCE_GTID_CONSISTENCY = 1.
  3. mysql> show variables like \'ENFORCE_GTID_CONSISTENCY\';
  4. +--------------------------+-------+
  5. | Variable_name | Value |
  6. +--------------------------+-------+
  7. | enforce_gtid_consistency | ON |
  8. +--------------------------+-------+
  9. 1 row in set (0.00 sec)

如果开了GTID复制的,这样的执行就会报错,所以要关了GTID复制的几个参数才可以,这变量只能修改配置文件才能生效,不支持在线修改,修改后重启再试试:

  1. mysql> SELECT first_name,code,sex FROM user2 ;
  2. +------------+------+------+
  3. | first_name | code | sex |
  4. +------------+------+------+
  5. | bb | BJ | N |
  6. | bb | BJ | N |
  7. | cc | SH | M |
  8. | cc | SH | M |
  9. +------------+------+------+
  10. 4 rows in set (0.00 sec)
  11. mysql> CREATE TABLE tmp SELECT first_name,code,sex FROM user2 GROUP BY first_name,code;
  12. Query OK, 2 rows affected (0.44 sec)
  13. Records: 2 Duplicates: 0 Warnings: 0
  14. mysql>

可以看到可以创建表并且插入数据了。把原来的表DROP掉,再把临时表修改名字为之前的表名:

  1. mysql> drop table user2;
  2. Query OK, 0 rows affected (0.12 sec)
  3. mysql> rename table tmp to user2;
  4. Query OK, 0 rows affected (0.13 sec)
  5. mysql> select * from user2;
  6. +------------+------+------+
  7. | first_name | code | sex |
  8. +------------+------+------+
  9. | bb | BJ | N |
  10. | cc | SH | M |
  11. +------------+------+------+
  12. 2 rows in set (0.00 sec)
  13. mysql>

 

方法二:

从表中删除重复记录的一个简单的方法就添加索引(INDEX) 或 主键(PRIMAY KEY)到该表。即使该表已经提供,可以使用此技术来删除重复的记录

  1. mysql> SELECT * FROM user2;
  2. +------------+------+------+
  3. | first_name | code | sex |
  4. +------------+------+------+
  5. | bb | BJ | N |
  6. | cc | SH | M |
  7. | bb | BJ | N |
  8. | cc | SH | M |
  9. +------------+------+------+
  10. 4 rows in set (0.00 sec)
  11. mysql> ALTER IGNORE TABLE user2 ADD PRIMARY KEY (first_name,code);
  12. Query OK, 4 rows affected (1.22 sec)
  13. Records: 4 Duplicates: 2 Warnings: 0
  14. mysql> SELECT * FROM user2;
  15. +------------+------+------+
  16. | first_name | code | sex |
  17. +------------+------+------+
  18. | bb | BJ | N |
  19. | cc | SH | M |
  20. +------------+------+------+
  21. 2 rows in set (0.00 sec)
  22. mysql>

 

 

总结:

   一、可以用INSERT IGNORE和REPLACE实现重复行数据处理,另一种方法是强制唯一性是增加唯一(UNIQUE)索引,而不是一个主键(PRIMARY KEY)来实现数据的唯一。

   二、通过CREATE TABLE tmp SELECT xx的方式来来创建表,要关闭GTID复制才能执行,如果线上的主从复制是基于GTID的,只能用添加索引(INDEX)或主键(PRIMAY KEY)到该表去消除相同的行记录了。

   三、这些需求在现实生活中肯定会有的,所以希望大家多测试,分享更多的想法

 

 

 

作者:陆炫志

出处:xuanzhi的博客 http://www.cnblogs.com/xuanzhi201111

您的支持是对博主最大的鼓励,感谢您的认真阅读。本文版权归作者所有,欢迎转载,但请保留该声明。

 

版权声明:本文为xuanzhi201111原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://www.cnblogs.com/xuanzhi201111/p/4841535.html