创建临时表删除
create table tmp_t as
select
t1.userid,t1.product_key,t1.id as del_id
from
user_table t1
LEFT JOIN
(
select
t2.userid,product_key,max(id) as max_id
from user_table t2
group by t2.userid,product_key
having(count(*))>1
)t3
ON t1.userid = t3.userid AND t1.product_key = t3.product_key
WHERE t1.id<t3.max_id
AND t3.userid is not null
order by t1.id asc
alter table tmp_t add index del_id (del_id);
delete from user_table where id in (select del_id from tmp_t);
创建唯一索引自动删除
alter table user_table add unique(userid,product_key);