How to remove duplicate data in MySQL?

This article mainly introduces the relevant information of MySQL Detailed explanation of duplicate data instances. Friends in need can refer to

MySQL Detailed explanation of duplicate data instances

There are two senses of duplicate records. One is a completely duplicate record, that is, all fields are repeated. The other is a record with some fields repeated. For the first type of duplication, it is relatively easy to solve. Just use the distinct keyword in the query statement to remove duplicates. Almost all database systems support the distinct operation. The main reason for this duplication is poor table design, which can be avoided by adding a primary key or a unique index column to the table.

select distinct * from t;

For the second type of duplicate problem, it is usually required to query any record among the duplicate records. Assume that table t has three fields: id, name, and address. id is the primary key, and the duplicate fields are name and address. It is required to obtain a unique result set of these two fields.

-- Oracle, MySQL, use correlated subquery

select * from t t1

 where t1.id =

 (select min(t2.id)

  from t t2

  where t1.name = t2.name and t1.address = t2.address);

-- Hive only supports FROM clause #Use subquery, the subquery must have a name, and the column must be unique

select t1.*

 from t t1,

   (select name, address, min(id) id from t group by name, address) t2

 where t1.id = t2.id;

-- You can also use hive's row_number() analysis

function

select t.id, t.name, t.address

 from (select id, name, address,

row_number() over (distribute by name, address sort by id) as rn 

     from t) t 

 where t.rn=1;

The above is the detailed content of How to remove duplicate data in MySQL?. For more information, please follow other related articles on the PHP Chinese website!