mysql删除重复索引_mysql删除多个重复数据，多个字段添加唯一性索引

需求：删除station_id、ab_data_time、item_code_id、data_cycle、ab_value 字段重复的记录

#查询重复的数据

select

b.id,b.station_id,b.ab_data_time,b.item_code_id,b.data_cycle,b.ab_lable,b.ab_value

from

d_abnormal_data_yyyymms b

where

(b.station_id,b.ab_data_time,b.item_code_id,b.data_cycle,b.ab_value) in (

select *

from

(

select

station_id,ab_data_time,item_code_id,data_cycle,ab_value

from

d_abnormal_data_yyyymms

group by

station_id,ab_data_time,item_code_id,data_cycle,ab_value

having

count(*) >1

) b

)

and ab_data_time <= '2019-01-22 12:00:00';

#删除多个字段重复数据(去掉ab_lable字段)

delete

from

d_abnormal_data_yyyymms

where

(station_id,ab_data_time,item_code_id,data_cycle,ab_value) in (

select *

from

(

select

station_id,ab_data_time,item_code_id,data_cycle,ab_value

from

d_abnormal_data_yyyymms

group by

station_id,ab_data_time,item_code_id,data_cycle,ab_value

having

count(*) >1

) b

)

and

ab_data_time <= '2019-01-22 12:00:00'

and

id not in

(select * from

(select

min(id)

from

d_abnormal_data_yyyymms

where

ab_data_time <= '2019-01-22 12:00:00'

group by

station_id,ab_data_time,item_code_id,data_cycle,ab_value

having count(*)>1)c

);

#查看数据是否还保留一条

select

b.id,b.station_id,b.ab_data_time,b.item_code_id,b.data_cycle,b.ab_lable,b.ab_value

from

d_abnormal_data_yyyymms b

where

ab_data_time = '2018-12-17 11:50:00';

#添加唯一索引

create unique index station_id_time_code_cycle_lable_value on d_abnormal_data_yyyymms(station_id,ab_data_time,item_code_id,data_cycle,ab_lable,ab_value);