How to delete duplicate data with the same id

existwebsite operation and maintenanceIn, sometimes multiple pieces of data with duplicate ids appear in the database. I want to delete duplicate ids, but I can't find a good solution. This article records and describes based on the actual situation.Please back up the data when operating the database to avoid irreversible operations and causing unexpected losses.

You see multiple deletion methods on the Internet, but the operation will report an error and send it to people who need to solve the problem.

Solution:

First check whether there are duplicate data in the table

SELECT id,COUNT(*) FROM table name
GROUP BY id

HAVING COUNT(*) > 1;

Check how much duplicate data there is in total and the statement to delete duplicate data:

DELETE FROM 表名 WHERE
id IN (
SELECT id FROM (
SELECT id,COUNT(*) FROM table name
GROUP BY id
HAVING COUNT(*) > 1
) AS a

) Limit number;

limit means the number of items you want to delete. Since the execution speed of delete statements is relatively slow, it is recommended to set a number if there is too much data, such as deleting 1000 or 10000 items first. Otherwise, if the data is too large, mysql execution will crash if it is too slow. If it is rushed to close and then execute, the table will be locked. Unable to operate database.

Table interpretation operation:

1. Check the process, mainly to find the ID of the process whose table is locked

SHOW PROCESSLIST;

2. The process ID of the kill lock table

KILL 10866;//The ID of the immediate process after the number