How to remove duplicate data under Mysql

First, create a temporary table, and then remove the data from the author table and put it into the temporary table.

create temporary table table name.

select distinct id,name,passwordfrom author

Then delete all the records in the author table.

delete from author

Finally, insert the records in the temporary table into the author table.

insert into author (id,name,password)

select id,name,passwordfrom temporary tables.

Let's create a temporary table to handle it.

For example, select distinct id, name, password into tmp

delete from author

insert into author(id name password)

select id, name, password from #tmp

It's a good idea to back up before doing so.

There are two senses of duplicate records

Exact duplicate records, i.e., records where all fields are duplicated.

Some key fields are duplicated, such as the name field, while other fields are not necessarily duplicated or can be ignored.

1. For the first kind of repetition, it is easier to solve and use

select distinct * from tablename

This results in a result set with no duplicate records. Then, the temporary table is used to maintain the data.

select distinct * into #tmp from tablename

drop table tablename

select * into tablename from #tmp

drop table #tmp

tmp is the system parameter and tablename is the name of the table to be operated.

2. The second type of duplicate problem usually requires the retention of the first record in the duplicate record, and the operation method is as follows:

Suppose that there are duplicate fields named and address, and the result set that is unique to these two fields is required.

select identity(int,1,1) as autoid, *into #tmp from tablename

select min(autoid) as autoid into #tmp2 from #tmp group by name,autoid

select * from #tmp where autoid in(select autoid from #tmp2)

The last select gets the result set with the name and address being duplicated (but there is an extra autoid field, which can be omitted in the select clause when actually writing).

waste bak is the name of the table.

id is an auto-incrementing primary key.

Waste Date and Reflection ID are two fields with duplicate data.

In addition to this, there are some other fields in the waste bak table. But you can ignore them here.

Now you want to delete the rows in the table where there is duplicate data in the Waste Date and Reflection ID fields, and keep only one row. The data with the smallest value of id.

Pay attention to what data you want, and then wrap a layer of deletion statements around the outermost layer, and the deletion condition is to exclude (not in) the data you want. This deletes the data you don't want.

Duplicate data queried:

Deduplication: