一开始数据库中未建立唯一性索引,但是在业务逻辑上是有唯一性约束的。在运行一段时间后,才发现这个问题,记录一下
对应的表T_ROUTE_ORBIT,唯一限制字段: longitude, latitude, task_id
删除重复记录,保留第一条:
delete from T_ROUTE_ORBIT a
where (a.longitude, a.latitude, a.task_id) in
(select longitude, latitude, task_id
from T_ROUTE_ORBIT
group by longitude, latitude, task_id
having count(*) > 1)
and rowid not in (select min(rowid)
from T_ROUTE_ORBIT
group by longitude, latitude, task_id
having count(*) > 1) order by time
建立唯一性索引:
create unique index route_orbit on T_ROUTE_ORBIT(longitude,latitude,task_id)