限制數(shù)據(jù)重復(fù)的方式：表上增加主鍵（Primary Key）或增加唯一性索引（Unique） 主鍵對重復(fù)資料進行限制，這樣資料在導(dǎo)入時就無法重復(fù)插入 create table primary_t( last_name varchar(20), first_name varchar(20), sex varchar(8), primary key(last_name,first_name) )engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模擬插入兩筆有主鍵約束的資料 insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t; +-----------+------------+-------+ | last_name | first_name | sex | +-----------+------------+-------+ | Coco | Li | Woman | | Frank | Cai | Man | | Frank | Li | Man | +-----------+------------+-------+ 3 rows in set (0.00 sec)

?

唯一性索引對復(fù)制資料進行限制 create table unique_t( last_name varchar(20), first_name varchar(20), sex varchar(8), unique index unique_name(last_name,first_name) )engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模擬插入兩筆有唯一性約束的資料 insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman'); mysql> select * from unique_t; +-----------+------------+-------+ | last_name | first_name | sex | +-----------+------------+-------+ | Frank | Li | Man | | Frank | Cai | Man | | Coco | Li | Woman | +-----------+------------+-------+ 3 rows in set (0.00 sec)

?

對資料的統(tǒng)計 create table count_t( last_name varchar(20), first_name varchar(20), sex varchar(8) )engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'), ('Frank','B','Man'), ('Frank','C','Woman'), ('Frank','D','Man'), ('Frank','E','Man'), ('Frank','F','Woman'), ('Frank','G','Man'), ('Frank','H','Man'), ('Frank','I','Woman'), ('Coco','A','Woman'), ('Coco','B','Man'), ('Coco','C','Man'), ('Coco','D','Man'), ('Coco','E','Man'), ('Coco','F','Man'), ('Coco','G','Woman'), ('Coco','H','Woman'), ('Alex','B','Man'), ('ALex','C','Man'), ('ALex','D','Man'), ('Alex','E','Man'), ('ALex','F','Man'), ('ALex','G','Woman'), ('ALex','H','Woman') ;commit;按各個列進行分組統(tǒng)計 select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name; +-----------+----------+ | last_name | count(*) | +-----------+----------+ | Alex | 7 | | Coco | 8 | | Frank | 9 | +-----------+----------+ 3 rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name; +------------+----------+ | first_name | count(*) | +------------+----------+ | A | 2 | | B | 3 | | C | 3 | | D | 3 | | E | 3 | | F | 3 | | G | 3 | | H | 3 | | I | 1 | +------------+----------+ 9 rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex; +-------+----------+ | sex | count(*) | +-------+----------+ | Man | 16 | | Woman | 8 | +-------+----------+ 2 rows in set (0.00 sec)對分組的統(tǒng)計在進行限定，having 大于等于8次的記錄 select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8; +-----------+----------+ | last_name | count(*) | +-----------+----------+ | Coco | 8 | | Frank | 9 | +-----------+----------+ 2 rows in set (0.00 sec)

?

對資料進行去重操作，主要使用distinct函數(shù) select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

?

對已有資料的表進行去重操作 insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主鍵約束，加入ignore，忽悠重復(fù)的資料alter ignore table count_t add primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t-> add primary key(last_name,first_name); Query OK, 26 rows affected (0.01 sec) Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi'; +-----------+------------+------+ | last_name | first_name | sex | +-----------+------------+------+ | Xi | Xi | Man | +-----------+------------+------+ 1 row in set (0.00 sec)
使用group by方式也可去除重復(fù)資料 create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex; ?

?

轉(zhuǎn)載于:https://www.cnblogs.com/also-brook/p/9050609.html

總結(jié)

以上是生活随笔為你收集整理的MySQL-处理重复数据[20180517]的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。