如何使用MySQL语句删除title字段含重复单词的hospital表记录

Alright, let's figure out how to delete records from the hospital table where the title field has duplicate words (any word, not just specific ones). Here's a step-by-step approach tailored to MySQL since you mentioned utf8_general_ci collation.

Step 1: Identify records with duplicate words first #

Before deleting anything, it's always smart to confirm which records will be removed. We can use a recursive CTE (available in MySQL 8.0+) to split the title into individual words, then check for duplicates per record.

WITH word_split AS (
    SELECT 
        id,
        -- Trim whitespace and extract each word
        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(title, ' ', n), ' ', -1)) AS word
    FROM hospital
    -- Join with a number list to cover all possible word positions in titles
    JOIN (
        SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
        UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
        -- Add more numbers here if your titles have more than 10 words
    ) numbers
    -- Only include rows where the number of spaces is at least n-1 (so we don't get empty words)
    ON CHAR_LENGTH(title) - CHAR_LENGTH(REPLACE(title, ' ', '')) >= n - 1
)
-- Get distinct IDs where any word appears more than once
SELECT DISTINCT id
FROM word_split
WHERE word != '' -- Skip empty strings from multiple spaces
GROUP BY id, word
HAVING COUNT(*) > 1;

Handling punctuation (optional but recommended) #

If your titles have punctuation like commas or periods (e.g., "cancer, survival"), these will be treated as part of the word, so "cancer," and "cancer" won't be seen as duplicates. To fix this, clean the title first with a regex replace:

WITH word_split AS (
    SELECT 
        id,
        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(
            REGEXP_REPLACE(title, '[^a-zA-Z0-9 ]', ''), -- Remove non-alphanumeric/space chars
            ' ', n), ' ', -1)) AS word
    FROM hospital
    JOIN (
        SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
        UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
    ) numbers
    ON CHAR_LENGTH(title) - CHAR_LENGTH(REPLACE(title, ' ', '')) >= n - 1
)
SELECT DISTINCT id
FROM word_split
WHERE word != ''
GROUP BY id, word
HAVING COUNT(*) > 1;

Step 2: Delete the identified records #

Once you've verified the IDs are correct, wrap the above query in a DELETE statement:

DELETE FROM hospital
WHERE id IN (
    WITH word_split AS (
        SELECT 
            id,
            TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(
                REGEXP_REPLACE(title, '[^a-zA-Z0-9 ]', ''),
                ' ', n), ' ', -1)) AS word
        FROM hospital
        JOIN (
            SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
            UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
        ) numbers
        ON CHAR_LENGTH(title) - CHAR_LENGTH(REPLACE(title, ' ', '')) >= n - 1
    )
    SELECT DISTINCT id
    FROM word_split
    WHERE word != ''
    GROUP BY id, word
    HAVING COUNT(*) > 1
);

For MySQL versions older than 8.0 (no CTE support) #

If you're stuck with an older MySQL version, create a custom function to split strings, then use that to find duplicates:

First, create the split function:

DELIMITER //
CREATE FUNCTION split_string(str VARCHAR(255), delim VARCHAR(12), pos INT)
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
       LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1),
       delim, '');
END //
DELIMITER ;

Then find duplicate IDs:

SELECT DISTINCT h.id
FROM hospital h
JOIN (
    SELECT id, split_string(REGEXP_REPLACE(title, '[^a-zA-Z0-9 ]', ''), ' ', n) AS word
    FROM hospital
    JOIN (
        SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
        UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
    ) numbers
    ON CHAR_LENGTH(title) - CHAR_LENGTH(REPLACE(title, ' ', '')) >= n - 1
) ws ON h.id = ws.id
WHERE ws.word != ''
GROUP BY h.id, ws.word
HAVING COUNT(*) > 1;

And delete using:

DELETE FROM hospital
WHERE id IN (
    SELECT DISTINCT h.id
    FROM hospital h
    JOIN (
        SELECT id, split_string(REGEXP_REPLACE(title, '[^a-zA-Z0-9 ]', ''), ' ', n) AS word
        FROM hospital
        JOIN (
            SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
            UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
        ) numbers
        ON CHAR_LENGTH(title) - CHAR_LENGTH(REPLACE(title, ' ', '')) >= n - 1
    ) ws ON h.id = ws.id
    WHERE ws.word != ''
    GROUP BY h.id, ws.word
    HAVING COUNT(*) > 1
);

Important Notes #

• Backup first: Always back up your table or run the delete in a transaction (START TRANSACTION; ... COMMIT;) so you can roll back if something goes wrong.
• Word count limit: Extend the number list in the JOIN clause if your titles have more than 10 words (e.g., add UNION ALL SELECT 11, etc.).
• Case sensitivity: Since your collation is utf8_general_ci, "Cancer" and "cancer" will be treated as duplicates. If you need case-sensitive checks, use utf8_bin collation instead.

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