The method of deleting duplicate Records in Database by mysql

This article is about how mysql deletes duplicate records in the database. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

Mysql steps to delete duplicate records in the database: 1, statistics duplicate data; 2, use "SELECT DISTINCT" statement to filter duplicate data; 3, add INDEX (index) and PRIMAY KEY (primary key) to delete duplicate records in the data table.

For the normal MySQL data table, there may be duplicate data, some cases allow the existence of duplicate data, some cases are not allowed, at this time we need to find and delete these duplicate data, here are the specific processing methods!

Method 1: prevent duplicate data in the table

When no data is added to the table, you can set the specified field to PRIMARY KEY (primary key) or UNIQUE (* *) index in the MySQL data table to ensure the * * nature of the data.

For example, in the student information table, the student number no does not allow repetition, you need to set the student number no as the primary key, and the default value cannot be NULL.

CREATE TABLE student (no CHAR (12) NOT NULL, name CHAR (20), sex CHAR (10), PRIMARY KEY (no)

Method 2: filter and delete duplicate values

For the original data in the data table, the removal of duplicate data needs to go through the steps of duplicate data search, filtering and deletion.

1. Statistical duplicate data

Mysql > SELECT COUNT (*) as repetitions,no-> FROM student-> GROUP BY no-> HAVING repetitions > 1

The above query returns the number of duplicate records in the student table.

two。 Filter duplicate data

If you need to read non-duplicated data, you can use the DISTINCT keyword in the SELECT statement to filter the duplicated data.

Mysql > SELECT DISTINCT no-> FROM student

You can also use GROUP BY to read non-duplicated data in a data table

Mysql > SELECT no-> FROM student-> GROUP BY (no)

3. Delete duplicate data

To delete duplicate data in the data table, you can use the following SQL statement:

Mysql > CREATE TABLE tmp SELECT no, name, sex FROM student GROUP BY (no, sex); mysql > DROP TABLE student; mysql > ALTER TABLE tmp RENAME TO student

You can also add INDEX (index) and PRIMAY KEY (primary key) to the data table to delete duplicate records in the table as follows:

Mysql > ALTER IGNORE TABLE student-> ADD PRIMARY KEY (no); thank you for reading! On mysql to delete duplicate records in the database method to share here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.