How does mysql delete duplicate records in the database?

The steps for mysql 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, the existence of duplicate data is allowed, and in some cases, it is not allowed. At this time, we It is necessary to find and delete these duplicate data, the following is the specific processing method!

Related learning recommendation: mysql tutorial (video)

Method 1: Prevent Duplicate data appears in the table

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

For example, in the student information table, the student number no is not allowed to be repeated, 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 data tables To remove duplicate data from the original data, you need to go through steps such as duplicate data search, filtering, and deletion.

1. Statistics of repeated data

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

The above query statement will return the number of repeated 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-repeated data in the data table

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

3. Delete duplicate data

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, the method is as follows:

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

This article comes from 1024programmer.com mysql graphic tutorial channel, welcome to learn!

The above is how mysql deletes duplicate records in the database? For more details, please pay attention to other related articles on 1024programmer.com!