How to delete duplicate records in mysql database?

Mysql steps to delete duplicate records in the database: first count duplicate data; then use the "SELECT DISTINCT" statement to filter duplicate data; finally add INDEX and PRIMAY KEY to the data table to delete duplicate records in the table .

For regular MySQL data tables, there may be duplicate data. In some cases, duplicate data is allowed, and in some cases it is not allowed. At this time we You need to find and delete these duplicate data. The following is the specific processing method!

Related learning recommendations: mysql tutorial (video)

Method 1: Prevent duplicate data from appearing in the table

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

For example, the student number no is not allowed to be repeated in the student information table. The student number no needs to be set 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, if you want to remove duplicate data, you need to go through a duplicate data search , filtering and deletion steps.

1. Count duplicate data

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

The above query statement will return the number of duplicate records in the student table.

2. Filter duplicate data

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

mysql> SELECT DISTINCT no 
-> FROM student;

You can also use GROUP BY to read non-duplicate data in the data table

mysql> SELECT no 
-> FROM student 
-> GROUP BY (no);

3. Delete duplicate data

Delete the data in the data table For duplicate data, 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. The method is as follows:

mysql> ALTER IGNORE TABLE student 
-> ADD PRIMARY KEY (no);

This article comes from the php中文网mysql graphic tutorial channel, welcome to learn!

The above is the detailed content of How to delete duplicate records in mysql database?. For more information, please follow other related articles on the PHP Chinese website!