How to find duplicates in MySQL

Question

Suppose I have many columns. If two columns match and match, then they are duplicates.

ID | title | link | size | author

Suppose the link and size are the same for two or more lines, then these lines are duplicates. How to get these duplicates in a list and process them?

Answer 1

Will return all records with duplicates:

SELECT theTable.*
FROM theTable
INNER JOIN (
  SELECT link, size
  FROM theTable 
  GROUP BY link, size
  HAVING count(ID) > 1
) dups ON theTable.link = dups.link AND theTable.size = dups.size

I like the b / c subquery, I can do things like select everything except the first or last. (it’s very easy to turn into a delete request).

Example: select all duplicate entries. EXCLUDE the one with the maximum ID:

SELECT theTable.*
FROM theTable
INNER JOIN (
  SELECT link, size, max(ID) as maxID
  FROM theTable 
  GROUP BY link, size
  HAVING count(ID) > 1
) dups ON theTable.link = dups.link 
          AND theTable.size = dups.size 
          AND theTable.ID <> dups.maxID

Answer 2

, id, link size NULL, id - . . , , .

select a.id, b.id 
from tbl a, tbl b  
where a.id < b.id   
  and a.link = b.link  
  and a.size = b.size   

Answer 3

MySQL , :

create unique index theTable_index on theTable (link,size);

Answer 4

SQL, - ( link size) . Python, , Python; "SELECT * FROM thetable ORDER BY", , and process with itertools.groupby using, as key, the operator.itemgetter` ; 1+ .

, , , , !