mysql去重删除数据只保留一条（保留id最小的那一条）

这里记录一下用到的语句和语句模板：

先把我用到语句分别列出来：

-- 查询出重复的数据
SELECT
  COUNT(*) as repeats,
  address,
  signer_name,
  signer_mobile
FROM user_operation_useraddress
GROUP BY
  address,
  signer_name,
  signer_mobile
HAVING
  repeats > 1;

-- 查询出重复的数据中最小的id
SELECT
  MIN(id) as min_id,
  COUNT(*) as repeats,
  address,
  signer_name,
  signer_mobile
FROM user_operation_useraddress
GROUP BY
  address,
  signer_name,
  signer_mobile
HAVING
  repeats > 1;

-- 查出重复的数据中非最小的id(需要删除的)
SELECT
  id
FROM user_operation_useraddress
WHERE
  (address, signer_name, signer_mobile) IN (
    SELECT
      address,
      signer_name,
      signer_mobile
    FROM user_operation_useraddress
    GROUP BY
      address,
      signer_name,
      signer_mobile
    HAVING
      COUNT(*) > 1
  )
  AND id NOT IN (
    SELECT
      MIN(id)
    FROM user_operation_useraddress
    GROUP BY
      address,
      signer_name,
      signer_mobile
    HAVING
      COUNT(*) > 1
  );

-- 建立一张临时表t
SELECT
  id
FROM (
    SELECT
      id
    FROM user_operation_useraddress
    WHERE
      (address, signer_name, signer_mobile) IN (
        SELECT
          address,
          signer_name,
          signer_mobile
        FROM user_operation_useraddress
        GROUP BY
          address,
          signer_name,
          signer_mobile
        HAVING
          COUNT(*) > 1
      )
      AND id IN (
        SELECT
          MIN(id)
        FROM user_operation_useraddress
        GROUP BY
          address,
          signer_name,
          signer_mobile
        HAVING
          COUNT(*) > 1
      )
  ) as t;

-- 删除重复的数据(只保留一条，保留最小id的)
DELETE FROM user_operation_useraddress
WHERE
  id IN (
    SELECT
      id
    FROM (
        SELECT
          id
        FROM user_operation_useraddress
        WHERE
          (address, signer_name, signer_mobile) IN (
            SELECT
              address,
              signer_name,
              signer_mobile
            FROM user_operation_useraddress
            GROUP BY
              address,
              signer_name,
              signer_mobile
            HAVING
              COUNT(*) > 1
          )
          AND id IN (
            SELECT
              MIN(id)
            FROM user_operation_useraddress
            GROUP BY
              address,
              signer_name,
              signer_mobile
            HAVING
              COUNT(*) > 1
          )
      ) as t
  );

在Mysql中是不能删除查询出来的记录，而是要通过一张临时表来解决，以上是我用到的语句。
然后下面给出删除重复数据的模板，可以套用：

-- 删除重复的数据(只保留一条，保留最小id的)
DELETE FROM table_name
WHERE
  id IN (
    SELECT
      id
    FROM (
        SELECT
          id
        FROM table_name
        WHERE
          (field1, field2, field3) IN (
            SELECT
              field1,
              field2,
              field3
            FROM table_name
            GROUP BY
              field1,
              field2,
              field3
            HAVING
              COUNT(*) > 1
          )
          AND id IN (
            SELECT
              MIN(id)
            FROM table_name
            GROUP BY
              field1,
              field2,
              field3
            HAVING
              COUNT(*) > 1
          )
      ) as t
  );

参考文章：简书