How to process MySQL record duplication

MySQL record duplication makes it inconvenient for us to use the database. How to delete MySQL record duplication becomes a common operation. The following describes how to process duplicate MySQL records for your reference.

There are two MySQL Repeated Records. One is a completely repeated record, that is, records with all fields being repeated, and the other is records with duplicate key fields, such as duplicate Name fields, other fields are not necessarily repeated or can be ignored.

1. Repeat the first MySQL type, which is easier to solve.

Select distinct * from tableName

You can get the result set without repeated records.

If the table needs to delete duplicate records and retain one record), you can delete the record as follows:

Select distinct * into # Tmp from tableName

Drop table tableName

Select * into tableName from # Tmp

Drop table # Tmp

The reason for this repetition is that the table design is not weekly. You can add a unique index column.

2. Repeat problems usually require that the first record in the repeat record be retained. The procedure is as follows:

Assume that the duplicate fields are Name and Address. You must obtain the unique result set of the two fields.

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 Name is obtained in the last select statement. The Address does not have duplicate result sets, but an autoID field is added. This column can be omitted in the select clause during actual writing)

How to query time periods in MySql

Use MySQL temporary tables to accelerate queries

How to properly use MySQL Indexes

Five common MySQL Log types

Automatic Restoration of MySQL Log Files