mysql query data duplicates

MySQL is a popular relational database management system that can be used to store, manage and query databases. In MySQL, sometimes we need to query duplicate data. This article will introduce you how to use MySQL to query data duplication.

Step one: Create a database in MySQL and add data

Create a database in MySQL and add a table with duplicate data. Here is a sample SQL query:

CREATE DATABASE testdb;

USE testdb;

CREATE TABLE users (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  email VARCHAR(50) NOT NULL
);

INSERT INTO users (name, email) VALUES
  ('Tom', 'tom@email.com'),
  ('Steve', 'steve@email.com'),
  ('John', 'john@email.com'),
  ('Tom', 'tom@email.com'),
  ('Jim', 'jim@email.com');

This query will create a new database named "testdb" and create a table named "users" in it. The table contains three columns: "id" (primary key), "name", and "email". We inserted five rows, two of which contained the same name and email address.

Step 2: Query duplicate data

Now we need to find all duplicate rows. To do this, we will use a nested query:

SELECT name, email, COUNT(*) c
FROM users
GROUP BY name, email
HAVING c > 1;

This query will return all rows where the name and email address appear more than once in the table. We use the GROUP BY statement to group by name and email address, and the HAVING statement to filter out groups with more than 1 rows in the group.

After running this query, we will get the following results:

+------+----------------+---+
| name | email          | c |
+------+----------------+---+
| Tom  | tom@email.com  | 2 |
+------+----------------+---+

As we can see, this query found the duplicate data we added in the table. The results show duplicate names and email addresses and the number of times they appear in the table.

Step Three: Delete Duplicate Data

If we want to delete all duplicate rows, we can use the following query to delete them:

DELETE FROM users WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT MIN(id) id
    FROM users
    GROUP BY name, email
  ) t
);

This query will delete all duplicate rows that are not Duplicate row with minimum ID. We used a subquery to select the smallest ID in each repeating group. This subquery first uses a GROUP BY statement to group by name and email address, then selects the smallest ID in each group. The main query uses a NOT IN statement and a subquery to delete all rows that do not contain the smallest ID.

After running this query, we can run the above query again to verify whether the duplicate data was successfully removed.

Summary

Querying for duplicate data in MySQL is easy. We can use nested queries and GROUP BY statements to find duplicate rows and delete them using DELETE statements. By using these technologies, we can manage and maintain the database easily.

The above is the detailed content of mysql query data duplicates. For more information, please follow other related articles on the PHP Chinese website!