Location>code7788 >text

How to remove duplicate data in mysql

Popularity:232 ℃/2024-08-02 16:10:32
#Grouping and de-duplication, grouping duplicate columns, and then using min(id).
# Take the smallest and keep it, delete the rest.
-- move 1: Creating Temporary Tables,Save the smallest of each groupID
CREATE TEMPORARY TABLE tmp_keep_ids AS
SELECT MIN(id) AS id
FROM Duplicate table names
GROUP BY repeating column;
-- move 2: Deleting records from the original table that are not in the temporary table
DELETE FROM original form
WHERE id NOT IN (SELECT id FROM interim table);
-- move 3: 删除interim table
DROP TEMPORARY TABLE tmp_keep_ids;

 ps: Why create a temporary table?

Because mysql does not allow this table to be used as a conditional query when deleting this table, using the original table as a conditional is going to report errors

 What is the rationale for doing this?

In mysql min () function is to take the minimum value of this column, the idea is that, first find duplicate columns for grouping, and then use the min () function to retain the minimum value in each duplicate group. It can be concluded that if not here id are duplicate column id, can be deleted.