Heim >Datenbank >MySQL-Tutorial >MySql-如何查询删除数据表重复记录

MySql-如何查询删除数据表重复记录

WBOY
WBOYOriginal
2016-06-07 14:49:471373Durchsuche

查询 SELECT name, email, COUNT (*)FROM usersGROUP BY name, emailHAVING COUNT(*) 1 重点来了,查询容易,那应该如何删除重复记录呢? 演示数据 表结构: mysql desc demo;+-------+------------------+------+-----+---------+----------------+ | Field

查询

<code class=" hljs oxygene"><span class="hljs-keyword">SELECT</span>
    name, email, COUNT<span class="hljs-comment">(*)
FROM
    users
GROUP BY
    name, email
HAVING 
    COUNT(*)</span> > <span class="hljs-number">1</span></code>

重点来了,查询容易,那应该如何删除重复记录呢?

演示数据
表结构:

<code class=" hljs asciidoc"><span class="hljs-header">mysql> desc demo;
+-------+------------------+------+-----+---------+----------------+</span>
<span class="hljs-header">| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+</span>
| id    | int(11) unsigned | NO   | PRI | NULL    | auto<span class="hljs-emphasis">_increment |
| site  | varchar(100)     | NO   | MUL |         |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)</span></code>

数据:

<code class=" hljs asciidoc"><span class="hljs-header">mysql> select * from demo order by id;
+----+------------------------+</span>
<span class="hljs-header">| id | site                   |
+----+------------------------+</span>
|  1 | http://www.CodeBit.cn  |
|  2 | http://YITU.org        |
|  3 | http://www.ShuoWen.org |
|  4 | http://www.CodeBit.cn  |
<span class="hljs-header">|  5 | http://www.ShuoWen.org |
+----+------------------------+</span>
5 rows in set (0.00 sec)</code>

没有创建表或创建索引权限的时候,可以用下面的方法:

如果你要删除较旧的重复记录,可以使用下面的语句:

<code class=" hljs haml">mysql> delete from a 
    -<span class="ruby">> using demo as a, demo as b
</span>    -<span class="ruby">> where (a.id > b.id)
</span>    -<span class="ruby">> <span class="hljs-keyword">and</span> (a.site = b.site);
</span>Query OK, 2 rows affected (0.12 sec)</code>
<code class=" hljs asciidoc"><span class="hljs-header">mysql> select * from demo order by id;
+----+------------------------+</span>
<span class="hljs-header">| id | site                   |
+----+------------------------+</span>
|  1 | http://www.CodeBit.cn  |
|  2 | http://YITU.org        |
<span class="hljs-header">|  3 | http://www.ShuoWen.org |
+----+------------------------+</span>
3 rows in set (0.00 sec)</code>

如果你要删除较新的重复记录,可以使用下面的语句:

<code class=" hljs haml">mysql> delete from a 
    -<span class="ruby">> using demo as a, demo as b
</span>    -<span class="ruby">> where (a.id < b.id)
</span>    -<span class="ruby">> <span class="hljs-keyword">and</span> (a.site = b.site);
</span>Query OK, 2 rows affected (0.12 sec)</code>
<code class=" hljs asciidoc"><span class="hljs-header">mysql> select * from demo order by id;
+----+------------------------+</span>
<span class="hljs-header">| id | site                   |
+----+------------------------+</span>
|  2 | http://YITU.org        |
|  4 | http://www.CodeBit.cn  |
<span class="hljs-header">|  5 | http://www.ShuoWen.org |
+----+------------------------+</span>
3 rows in set (0.00 sec)</code>

你可以用下面的语句先确认将被删除的重复记录

<code class=" hljs asciidoc">mysql> SELECT a.* 
<span class="hljs-code">    -> FROM demo a, demo b</span>
<span class="hljs-code">    -> WHERE a.id > b.id</span>
<span class="hljs-header">    -> AND (a.site = b.site);
+----+------------------------+</span>
<span class="hljs-header">| id | site                   |
+----+------------------------+</span>
|  1 | http://www.CodeBit.cn  |
<span class="hljs-header">|  3 | http://www.ShuoWen.org |
+----+------------------------+</span>
2 rows in set (0.00 sec)</code>

如果有创建索引的权限,可以用下面的方法:

在表上创建唯一键索引:

<code class=" hljs avrasm">mysql> alter ignore table demo <span class="hljs-keyword">add</span> unique index ukey (site)<span class="hljs-comment">;</span>
Query OK, <span class="hljs-number">5</span> rows affected (<span class="hljs-number">0.46</span> <span class="hljs-keyword">sec</span>)
<span class="hljs-label">Records:</span> <span class="hljs-number">5</span>  Duplicates: <span class="hljs-number">2</span>  Warnings: <span class="hljs-number">0</span></code>
<code class=" hljs asciidoc"><span class="hljs-header">mysql> select * from demo order by id;
+----+------------------------+</span>
<span class="hljs-header">| id | site                   |
+----+------------------------+</span>
|  1 | http://www.CodeBit.cn  |
|  2 | http://YITU.org        |
<span class="hljs-header">|  3 | http://www.ShuoWen.org |
+----+------------------------+</span>
3 rows in set (0.00 sec)</code>

重复记录被删除后,如果需要,可以删除索引:

<code class=" hljs avrasm">mysql> alter table demo drop index ukey<span class="hljs-comment">;</span>
Query OK, <span class="hljs-number">3</span> rows affected (<span class="hljs-number">0.37</span> <span class="hljs-keyword">sec</span>)
<span class="hljs-label">Records:</span> <span class="hljs-number">3</span>  Duplicates: <span class="hljs-number">0</span>  Warnings: <span class="hljs-number">0</span></code>

如果有创建表的权限,可以用下面的方法:

创建一个新表,然后将原表中不重复的数据插入新表:

<code class=" hljs oxygene">mysql> <span class="hljs-keyword">create</span> table demo_new <span class="hljs-keyword">as</span> <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> demo <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> site;
Query OK, <span class="hljs-number">3</span> rows affected (<span class="hljs-number">0.19</span> sec)
Records: <span class="hljs-number">3</span>  Duplicates: <span class="hljs-number">0</span>  Warnings: <span class="hljs-number">0</span></code>
<code class=" hljs asciidoc"><span class="hljs-header">mysql> show tables;
+----------------+</span>
<span class="hljs-header">| Tables_in_test |
+----------------+</span>
| demo           |
<span class="hljs-header">| demo_new       |
+----------------+</span>
2 rows in set (0.00 sec)</code>
<code class=" hljs asciidoc"><span class="hljs-header">mysql> select * from demo order by id;
+----+------------------------+</span>
<span class="hljs-header">| id | site                   |
+----+------------------------+</span>
|  1 | http://www.CodeBit.cn  |
|  2 | http://YITU.org        |
|  3 | http://www.ShuoWen.org |
|  4 | http://www.CodeBit.cn  |
<span class="hljs-header">|  5 | http://www.ShuoWen.org |
+----+------------------------+</span>
5 rows in set (0.00 sec)</code>
<code class=" hljs asciidoc"><span class="hljs-header">mysql> select * from demo_new order by id;
+----+------------------------+</span>
<span class="hljs-header">| id | site                   |
+----+------------------------+</span>
|  1 | http://www.CodeBit.cn  |
|  2 | http://YITU.org        |
<span class="hljs-header">|  3 | http://www.ShuoWen.org |
+----+------------------------+</span>
3 rows in set (0.00 sec)</code>

然后将原表备份,将新表重命名为当前表:

<code class=" hljs asciidoc">mysql> rename table demo to demo<span class="hljs-emphasis">_old, demo_</span>new to demo;
Query OK, 0 rows affected (0.04 sec)

<span class="hljs-header">mysql> show tables;
+----------------+</span>
<span class="hljs-header">| Tables_in_test |
+----------------+</span>
| demo           |
<span class="hljs-header">| demo_old       |
+----------------+</span>
2 rows in set (0.00 sec)</code>
<code class=" hljs asciidoc"><span class="hljs-header">mysql> select * from demo order by id;
+----+------------------------+</span>
<span class="hljs-header">| id | site                   |
+----+------------------------+</span>
|  1 | http://www.CodeBit.cn  |
|  2 | http://YITU.org        |
<span class="hljs-header">|  3 | http://www.ShuoWen.org |
+----+------------------------+</span>
3 rows in set (0.00 sec)</code>

注意:使用这种方式创建的表会丢失原表的索引信息!

<code class=" hljs asciidoc"><span class="hljs-header">mysql> desc demo;
+-------+------------------+------+-----+---------+-------+</span>
<span class="hljs-header">| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+</span>
| id    | int(11) unsigned | NO   |     | 0       |       |
<span class="hljs-header">| site  | varchar(100)     | NO   |     |         |       |
+-------+------------------+------+-----+---------+-------+</span>
2 rows in set (0.00 sec)</code>

如果要保持和原表信息一致,你可以使用 show create table demo; 来查看原表的创建语句,然后使用原表的创建语句创建新表,接着使用 insert … select 语句插入数据,再重命名表即可。

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn