mysql删除掉重复项

类别 mysql

用SQL语句,删除掉重复项只保留一条

在几十万条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢

## 过滤重复数据
如果你需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。

mysql> SELECT DISTINCT last_name, first_name FROM t_cutormer;

查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

mysql> SELECT last_name, first_name FROM t_cutormer GROUP BY (last_name, first_name);


## 删除重复数据 
删除前最好备份下数据。
如果你想删除数据表中的重复数据，你可以使用以下的SQL语句：

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM t_cutormer  GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE t_cutormer;
mysql> ALTER TABLE tmp RENAME TO t_cutormer;

另外一种方法：

delete from t_cutormer where
last_name, first_name, sex in (select last_name, first_name, sex from ( select last_name, first_name, sex from t_cutormer group by last_name, first_name, sex having count(last_name, first_name, sex)>1) a)
and id not in ( select min(id) from (select min(id) as id from t_cutormer group by last_name, first_name, sex having count(last_name, first_name, sex)>1 ) b)

## 统计重复数据

以下我们将统计表中 first_name 和 last_name的重复记录数：

mysql> SELECT COUNT(*) as repetitions, last_name, first_name FROM t_cutormer GROUP BY last_name, first_name
HAVING repetitions > 1;
mysql删除掉重复项

随机文章
