MySQL去除表中重复数据后保留最小ID数据【测试可用】

@开局警示：建议一步一步测试或重新建表测试，避免操作不当误删  

DELETE 
FROM
  users
WHERE
  id NOT IN ( SELECT temp.min_id FROM ( 
  SELECT MIN(id) min_id FROM t_cement_concrete_info GROUP BY produce_time
  ) AS temp);

请编写一个SQL语句,清除表中垃圾数据
1.首先查看重复数据

SELECT
    id,
    user_name,
    user_code,
FROM
    users 
GROUP BY
    user_name,
    user_code

注意:MySQL5.7以上默认开启了only_full_group_by模式,查询了非分组字段会报错

windows打开my.ini直接删掉only_full_group_by这一项即可

mac在my.cnf文件中下列代码到mysqld配置中

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

重启mysql服务就会生效

2.选择最小的id

SELECT 
    MIN(id) 
FROM 
    users 
GROUP BY 
    user_code, user_name

3.删除id不在最小id里的数据

DELETE 
FROM
    users 
WHERE
    id NOT IN ( SELECT MIN( id ) FROM users GROUP BY user_code, user_name )

直接写上面语句会报错,原因是:MySQL中不能同时查询和修改语句
所以需要加入中间表查询
正确的操作在文章开头

以下SQL经测试无误【文章开头的也可以用】

DELETE
FROM
  table
WHERE
  id NOT IN (
    SELECT
      dt.minno
    FROM
      (
        SELECT
          MIN(id) AS minno
        FROM
          table
        GROUP BY
          id
  ) dt
)

@以下分享比较优秀的一篇

@mysql删除重复记录并且只保留一条