MySQL没有主键情况下少量重复记录删除

MySQL没有主键情况下少量重复记录删除

琪小新
2021/08/05

procedure

drop procedure if exists p_delete_repeat_records;
delimiter //
create procedure p_delete_repeat_records(p_table_name varchar(120), p_group_field varchar(120),
                                         p_date_field varchar(120), p_date bigint)
    comment 'MySQL删除指定日期内重复数据'
    /*
        p_table_name 表名
        p_group_field 判断重复的字段
        p_date_field 表中得日期筛选字段
        p_date 按指定日期内查询

        ps：如果要修改的表存在text类型字段，就不要创建临时表

        author：琪小新
        date：2021/08/03
    */
begin
    declare v_table_name varchar(120);


    set v_table_name = concat(p_table_name, '_temp_del_rpt_records_', extract(hour from now()));


    set @v_sql_create =
            concat('drop temporary table if exists ', v_table_name);
    prepare sql_exec from @v_sql_create;
    execute sql_exec;
    deallocate prepare sql_exec;

    # 将重复数据入到一张临时表，并生成cnt
    set @v_sql_create =
            concat('create temporary table ', v_table_name, ' engine memory as',
                   ' select *, row_number() over ( partition by ',
                   p_group_field,
                   ' order by ', p_group_field, ') as cnt from ', p_table_name, ' where ', p_date_field, ' >= ',
                   p_date, ' and ', p_group_field, ' in (select ', p_group_field, ' from ', p_table_name, ' where ',
                   p_date_field, ' >= ', p_date, ' group by ', p_group_field, ' having count(*) > 1)');
    prepare sql_exec from @v_sql_create;
    execute sql_exec;
    deallocate prepare sql_exec;


    # 重复数统计
    set @v_sql_create =
            concat('select count(*) into @v_count', ' from ', v_table_name, ' where cnt > 1');
    prepare sql_exec from @v_sql_create;
    execute sql_exec;
    deallocate prepare sql_exec;

    if @v_count = 0 then
        select '没有发现重复记录' as MsgOut;
    else

        # 删除原表中重复的所有记录
        set @v_sql_create =
                concat('delete from ', p_table_name, ' where ', p_date_field, ' >= ', p_date, ' and ', p_group_field,
                       ' in (select DISTINCT ', p_group_field, ' from ', v_table_name, ')');

        prepare sql_exec from @v_sql_create;
        execute sql_exec;
        deallocate prepare sql_exec;

        # 删除临时表中cnt>1的数据
        set @v_sql_create =
                concat('delete from ', v_table_name, ' where cnt > 1');
        prepare sql_exec from @v_sql_create;
        execute sql_exec;
        deallocate prepare sql_exec;

        # 删除临时表较原表多出的列
        set @v_sql_create =
                concat('alter table ', v_table_name, ' drop cnt');
        prepare sql_exec from @v_sql_create;
        execute sql_exec;
        deallocate prepare sql_exec;

        # 从临时表向原表插入数据
        set @v_sql_create =
                concat('insert into ', p_table_name, ' select * from ', v_table_name);
        prepare sql_exec from @v_sql_create;
        execute sql_exec;
        deallocate prepare sql_exec;
        # 结果输出
        select p_table_name TABLE_NAME, @v_count RETEPATABLE_RECORDS_DELETE;

    end if;


    # 删除临时表
    set @v_sql_create =
            concat('drop temporary table  if exists ', v_table_name);
    prepare sql_exec from @v_sql_create;
    execute sql_exec;
    deallocate prepare sql_exec;

end
//
delimiter ;

call procedure

call p_delete_repeat_records('tt', 'tt_id', 'tt_date', 20210802);