MySQL数据库中多供应商重复酒店数据的去重方案咨询

Hey there! Great call thinking about duplicate hotel data before you even start coding—this will save you a ton of headache later. Let's break down how to handle this in MySQL step by step.

First, you need to set clear rules for identifying duplicate entries, since suppliers might submit slightly different data for the same property. Here are the most reliable approaches:

• Hard Match (Best Case): If all suppliers provide a universal hotel ID (like an industry-standard ID or a shared OTA platform ID), use this as your primary unique key—no guesswork needed.
• Geography + Standardized Name: If no universal ID exists, pair rounded latitude/longitude (to account for minor GPS errors) with a cleaned-up hotel name. For example, round coordinates to 4 decimal places (this translates to roughly 10-meter precision).
• Standardized Address + Name: If you don’t have coordinates, use a normalized full address (split into province/city/street/door number with consistent formatting) plus a cleaned name.

Before running any deduplication queries, clean your data to eliminate format-based false duplicates:

• Normalize Text: Convert all names/addresses to lowercase, trim extra spaces, and remove special characters (e.g., turn Hotel Grand Royale! into hotel grand royale).
• Standardize Addresses: Split addresses into discrete fields (city, district, street) and unify regional names (e.g., "Beijing" vs. "北京市" should become the same value).
• Fix Name Variants: Standardize common abbreviations or brand names (e.g., "Hampton Inn" vs. "Hampton Inn & Suites" might need manual rules, depending on your use case).

Case 1: You Have a Universal Unique ID #

Use window functions to keep the most recent or most trusted record for each hotel:

-- Keep the latest entry for each unique hotel ID
SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY external_hotel_id ORDER BY created_at DESC) AS record_rank
    FROM hotels
) ranked_hotels
WHERE record_rank = 1;

Case 2: Using Coordinates + Standardized Name #

Round coordinates to reduce GPS noise, then group by the cleaned name and rounded coordinates:

WITH normalized_hotels AS (
    SELECT 
        LOWER(TRIM(REPLACE(hotel_name, '!', ''))) AS cleaned_name,
        ROUND(latitude, 4) AS rounded_lat,
        ROUND(longitude, 4) AS rounded_lon,
        address,
        phone,
        created_at,
        id
    FROM hotels
)
SELECT 
    cleaned_name,
    rounded_lat,
    rounded_lon,
    -- Grab the latest address/phone for each unique hotel
    MAX(CASE WHEN record_rank = 1 THEN address END) AS primary_address,
    MAX(CASE WHEN record_rank = 1 THEN phone END) AS primary_phone
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY cleaned_name, rounded_lat, rounded_lon ORDER BY created_at DESC) AS record_rank
    FROM normalized_hotels
) ranked_hotels
WHERE record_rank = 1
GROUP BY cleaned_name, rounded_lat, rounded_lon;

Case 3: Handling Fuzzy Matches (Similar Names/Addresses) #

If suppliers spell names differently (e.g., "Marriott" vs. "Mariott"), use the Levenshtein distance (a string similarity metric) to find near-matches. First, create the function in MySQL (it’s not built-in):

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);
    SET 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;
    SET j = 1;
    SET cv0 = CONCAT(cv0, UNHEX(HEX(j)));
    
    REPEAT
        SET j = j + 1;
        SET cv0 = CONCAT(cv0, UNHEX(HEX(j)));
    UNTIL j > s2_len END REPEAT;
    
    SET i = 1;
    REPEAT
        SET s1_char = SUBSTRING(s1, i, 1);
        SET cv1 = UNHEX(HEX(i));
        SET j = 1;
        REPEAT
            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;
        UNTIL j > s2_len END REPEAT;
        SET cv0 = cv1;
        SET i = i + 1;
    UNTIL i > s1_len END REPEAT;
    
    RETURN CONV(HEX(SUBSTRING(cv0, s2_len+1, 1)), 16, 10);
END //
DELIMITER ;

Then use it to find similar hotels within a small geographic radius:

-- Find pairs of hotels with similar names (edit distance ≤3) and within 50 meters of each other
SELECT h1.id, h1.hotel_name, h2.id, h2.hotel_name
FROM hotels h1
JOIN hotels h2 ON h1.id < h2.id
WHERE LEVENSHTEIN(LOWER(h1.hotel_name), LOWER(h2.hotel_name)) <= 3
AND ST_DISTANCE_SPHERE(POINT(h1.longitude, h1.latitude), POINT(h2.longitude, h2.latitude)) <= 50;

You can use these results to manually merge entries or build an automated merging rule set.

• Prevent Duplicates Upfront: When new supplier data comes in, run a quick check against existing records using your deduplication rules before inserting.
• Schedule Regular Cleanups: Set up a weekly cron job or stored procedure to catch new duplicates that slip through.
• Keep Raw Data: Even after merging duplicates, retain the original supplier records—you might need to reference them later for disputes or data validation.

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