Mysql数据重复问题处理

当一某个字段重复判断重复时 ：查询重复数据 重复取id最小值

select table,min(id) 
from table 
where  “字段”  = 'xx' 
group by “重复字段”   having count(1) > 1 ;

查询影响条数

select *  from table
where " 字段"= 'xx'
and  id not in 
    (select min(id) 
    from table 
    where  "字段" =  'xx' 
    group by  "重复字段" having count(1) > 1 )
and "重复字段" in
      (select "重复字段"
    from table 
    where  "字段" = 'xx' 
    group by "重复字段" having count(1) > 1 );

删除
delete from table
where "字段" = 'xx'
 and id not in (select a.* from
    (select min(id) 
     from table
     where  "字段" = 'xx' 
     group by 重复字段 having count(1) > 1 ) a )
 and "重复字段" in (select b.* from
       (select "重复字段"
    from table
     where  "字段" = 'xx' 
     group by "重复字段" having count(1) > 1 ) b);

所有字段重复时：使用 distinct

insert into table1 select distinct * from  table ;
ALTER TABLE table RENAME TO table_20220908;
ALTER TABLE table1RENAME TO table ;