eg:
delete t1 from table1 t1, table2 t2
where
t1.fkcustomerid = t2.fkcustomerid and t1.fkpersonid = t2.fkpersonid and t1.fcooperationtype = t2.fcooperationtype and
t1.fid not in (
select min_fid from table3 t3
)
这样可以读到索引 ,可以用explain去分析,会比in要少查询很多行,mysql里面In里面接子查询 读不到索引
删除表里面的重复数据demo:
-- 1.先把去重的数据备份好 用于后面删除的关联
create table t_bc_belongperson_170907notrepeat as
select min(fid) as minid from t_bc_belongperson bp
where bp.fcooperationtype in ('PRIVATE','MANAGER_GIVE','PUBTOPRI')
group by fkcustomerid ;
-- 2.创建索引
alter table t_bc_belongperson_170907notrepeat add index idx_07_miniid(minid);
-- 3.删除之前先备份好客户归属人的数据
create table t_bc_belongperson170907bak as select * from t_bc_belongperson;
-- 4.执行删除重复数据
delete from t_bc_belongperson where fid not in (
select minid from t_bc_belongperson_170907notrepeat
);
create table t_bc_belongperson_170907notrepeat as
select min(fid) as minid from t_bc_belongperson bp
where bp.fcooperationtype in ('PRIVATE','MANAGER_GIVE','PUBTOPRI')
group by fkcustomerid ;
-- 2.创建索引
alter table t_bc_belongperson_170907notrepeat add index idx_07_miniid(minid);
-- 3.删除之前先备份好客户归属人的数据
create table t_bc_belongperson170907bak as select * from t_bc_belongperson;
-- 4.执行删除重复数据
delete from t_bc_belongperson where fid not in (
select minid from t_bc_belongperson_170907notrepeat
);