【mysql】 表的数据去重且保留id最小的行

• 查询重复数据中id最小的行：

  -- 去重，保留Id最小的
  select * 
  FROM ntid_source_mdns_data
  WHERE value IN (SELECT value FROM ntid_source_mdns_data GROUP BY value HAVING count(value) > 1)
  AND id NOT IN (SELECT min(id) FROM ntid_source_mdns_data GROUP BY value HAVING count(value) > 1);
  

• 删除重复数据，保留id最小的行

  -- 去重，删除重复的保留最小ID的行
  delete from ntid_source_mdns_data 
  WHERE value IN (select value from (SELECT value FROM ntid_source_mdns_data GROUP BY value HAVING count(value) > 1) a)
  AND id NOT IN (select * from (SELECT min(id) FROM ntid_source_mdns_data GROUP BY value HAVING count(value) > 1) b);
  

• 注意：
  相比与查询语句，删除语句需要多封装一层，否则会出现如下报错