mysql删除重复的记录 declare

DELETE t1 FROM table_name t1
INNER JOIN table_name t2 
WHERE t1.id > t2.id AND t1.column = t2.column;

DELIMITER //

CREATE PROCEDURE RemoveDuplicates()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur_id INT;
    DECLARE cur_value VARCHAR(255);
    DECLARE cur_row INT;

    -- 创建一个临时表来存储非重复记录的ID
    CREATE TEMPORARY TABLE temp_table (id INT PRIMARY KEY);

    -- 声明一个游标来遍历重复记录
    DECLARE cur CURSOR FOR 
        SELECT MIN(id) FROM table_name GROUP BY column HAVING COUNT(*) > 1;

    -- 声明一个继续处理器
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO cur_id;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 获取当前ID对应的值
        SELECT column INTO cur_value FROM table_name WHERE id = cur_id;

        -- 删除除了当前ID以外的所有重复记录
        DELETE FROM table_name WHERE column = cur_value AND id != cur_id;

        -- 将当前ID插入临时表
        INSERT INTO temp_table (id) VALUES (cur_id);
    END LOOP;

    CLOSE cur;

    -- 更新原表，只保留临时表中的ID
    DELETE FROM table_name WHERE id NOT IN (SELECT id FROM temp_table);

    -- 删除临时表
    DROP TEMPORARY TABLE temp_table;
END //

DELIMITER ;

-- 调用存储过程
CALL RemoveDuplicates();