使用MySQL查询删除相似新闻标题旧条目技术咨询

Hey there! Since your dataset isn't large, we can put together a straightforward solution to clean up those duplicate (highly similar) old headings and keep only the latest ones. Here's how to approach it step by step:

1. First: Add a Levenshtein Distance Function (for similarity checks) #

MySQL doesn't come with a built-in function to calculate text similarity, but we can create a custom Levenshtein distance function. This function measures the number of edits needed to turn one string into another, which we'll use to compute how similar two titles are.

Run this query once to create the function:

DELIMITER //
CREATE FUNCTION LEVENSHTEIN(s1 VARCHAR(255), s2 VARCHAR(255)) 
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE s1_len, s2_len, i, j, c, c_temp INT;
    DECLARE s1_char CHAR;
    DECLARE cv0, cv1 VARBINARY(256);
    
    SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2);
    IF s1_len = 0 THEN RETURN s2_len; END IF;
    IF s2_len = 0 THEN RETURN s1_len; END IF;
    
    SET cv0 = 0x00;
    FOR i FROM 1 TO s2_len DO
        SET cv0 = CONCAT(cv0, UNHEX(HEX(i)));
    END FOR;
    
    FOR i FROM 1 TO s1_len DO
        SET s1_char = SUBSTRING(s1, i, 1);
        SET cv1 = UNHEX(HEX(i));
        SET j = 1;
        WHILE j <= s2_len DO
            SET c = IF(s1_char = SUBSTRING(s2, j, 1), 0, 1);
            SET c_temp = CONV(HEX(SUBSTRING(cv0, j, 1)), 16, 10) + c;
            SET cv1 = CONCAT(cv1, UNHEX(HEX(LEAST(
                CONV(HEX(SUBSTRING(cv0, j+1, 1)), 16, 10) + 1,
                c_temp,
                CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + 1
            ))));
            SET j = j + 1;
        END WHILE;
        SET cv0 = cv1;
    END FOR;
    
    RETURN CONV(HEX(SUBSTRING(cv0, s2_len+1, 1)), 16, 10);
END //
DELIMITER ;

2. Identify Duplicate (Highly Similar) Entries First #

Before deleting anything, let's validate which entries are actually duplicates. We'll calculate similarity percentage using the Levenshtein distance:
(1 - (LEVENSHTEIN(title1, title2) / GREATEST(LENGTH(title1), LENGTH(title2)))) * 100

Replace news_table with your actual table name, title with your title column, and created_at with your timestamp column for tracking entry age:

SELECT 
    t1.id AS old_entry_id,
    t1.title AS old_title,
    t1.created_at AS old_created,
    t2.id AS new_entry_id,
    t2.title AS new_title,
    t2.created_at AS new_created,
    ROUND((1 - (LEVENSHTEIN(t1.title, t2.title) / GREATEST(LENGTH(t1.title), LENGTH(t2.title)))) * 100, 2) AS similarity_percent
FROM 
    news_table t1
JOIN 
    news_table t2 ON t1.id <> t2.id
WHERE 
    ROUND((1 - (LEVENSHTEIN(t1.title, t2.title) / GREATEST(LENGTH(t1.title), LENGTH(t2.title)))) * 100, 2) BETWEEN 90 AND 95
    AND t1.created_at < t2.created_at
ORDER BY 
    similarity_percent DESC;

Run this to make sure you're targeting the right old entries before proceeding to delete.

3. Delete Old Entries Safely #

Once you confirm the results from the above query, you can modify it to delete the older duplicate entries. Always back up your data first!

Here's the delete query:

DELETE t1
FROM 
    news_table t1
JOIN 
    news_table t2 ON t1.id <> t2.id
WHERE 
    ROUND((1 - (LEVENSHTEIN(t1.title, t2.title) / GREATEST(LENGTH(t1.title), LENGTH(t2.title)))) * 100, 2) BETWEEN 90 AND 95
    AND t1.created_at < t2.created_at;

This will remove all older entries that have a 90-95% similar title to a newer entry.

4. Set Up a Cron Job for Regular Cleanup #

To automate this process, create a simple shell script (e.g., clean_duplicates.sh):

#!/bin/bash
mysql -u your_username -p'your_password' your_database_name << EOF
DELETE t1
FROM 
    news_table t1
JOIN 
    news_table t2 ON t1.id <> t2.id
WHERE 
    ROUND((1 - (LEVENSHTEIN(t1.title, t2.title) / GREATEST(LENGTH(t1.title), LENGTH(t2.title)))) * 100, 2) BETWEEN 90 AND 95
    AND t1.created_at < t2.created_at;
EOF

Make the script executable with: chmod +x clean_duplicates.sh

Then add a Cron job to run it on a schedule. Run crontab -e and add this line (adjust the timing to your needs—this example runs daily at 2 AM):

0 2 * * * /full/path/to/clean_duplicates.sh

Quick Notes: #

• If your titles are tied to specific sources (e.g., same news URL or source ID), add t1.source_id = t2.source_id to the JOIN condition to avoid matching unrelated titles from different sources.
• Since your data volume is small, this query will run quickly without performance hits.

内容的提问来源于stack exchange，提问作者golev