In order to clean up an old large site from spam, I'd like to delete all duplicate comments from mysql. By duplicate I mean, the comments which are posted by the same user across all nodes of a certain type where content of the comments are exactly the same.
For toying things on the backend in SQL. The best place to start would be to look to see how the API does something in Drupal. For this you want to look at comment_delete_multiple which you can call to delete mass comments. You will need to know the comment IDs of the comments that you want to delete.
This is where this gets complicated. You have to find the CIDs you need before you can start deleting. Your criteria for finding the comments is going to depend on what you want to do. If you just want to delete any and all comments that are dupes then you could run a query like this to find dupes and generate the CIDs you want to delete.
Once you ahve the CIDs, it is very easy.You write a delete statement against the comment table. Using the Drupal API where it would iterate through the list of CIDs
$cids = array( , , , .....) db_delete('comment')->condition('cid', $cids, 'IN')->execute();
With the amount of comments you are trying to delete doing this strictly in SQL is going to be hard because you will need to write a statement with a gigantic list in a LIKE clause.
In my humble opinion: when I have had to do massive data changes like this in Drupal I write a PHP CLI script to do it so I can handle the data. I've migrated a website that had 150k users, 2000 blogs, and 110k comments, I did it all with PHP CLI scripts.