SQL查询重复行：列数众多时无需逐一列写的优化方法？

当表中存在大量列（上千列）时，手动罗列所有字段显然不现实，以下是两种实用的优化方案：

1. 利用系统元数据自动生成查询SQL #

所有主流数据库都提供存储表结构的系统视图，可以通过查询这些视图自动拼接出包含所有列的重复行查询语句，无需手动输入列名。

示例（MySQL） #

SELECT CONCAT(
    'SELECT ', GROUP_CONCAT(column_name SEPARATOR ', '), ', COUNT(*) ',
    'FROM emp ',
    'GROUP BY ', GROUP_CONCAT(column_name SEPARATOR ', '), ' ',
    'HAVING COUNT(*) > 1'
) AS duplicate_check_sql
FROM information_schema.columns
WHERE table_schema = '你的数据库名' AND table_name = 'emp';

执行这条语句后，会得到一个完整的查询SQL字符串，直接复制该字符串执行即可得到所有列组合下的重复行。

其他数据库适配 #

• SQL Server：替换系统视图为sys.columns，并调整拼接逻辑：

  SELECT CONCAT(
      'SELECT ', STRING_AGG(name, ', '), ', COUNT(*) ',
      'FROM emp ',
      'GROUP BY ', STRING_AGG(name, ', '), ' ',
      'HAVING COUNT(*) > 1'
  ) AS duplicate_check_sql
  FROM sys.columns
  WHERE object_id = OBJECT_ID('emp');
  

• PostgreSQL：使用string_agg函数拼接列名：

  SELECT CONCAT(
      'SELECT ', string_agg(column_name, ', '), ', COUNT(*) ',
      'FROM emp ',
      'GROUP BY ', string_agg(column_name, ', '), ' ',
      'HAVING COUNT(*) > 1'
  ) AS duplicate_check_sql
  FROM information_schema.columns
  WHERE table_schema = 'public' AND table_name = 'emp';
  

2. 通过哈希/校验和函数简化重复判断 #

将整行所有字段组合成一个唯一哈希值（或校验和），通过分组哈希值来快速定位重复行，避免罗列所有列。

示例（MySQL） #

使用MD5和CONCAT_WS处理所有列（自动处理NULL值）：

-- 先自动生成哈希拼接的SQL
SELECT CONCAT(
    'SELECT MD5(CONCAT_WS(''|'', ', GROUP_CONCAT(IFNULL(column_name, '''''') SEPARATOR ', '), ')), COUNT(*) ',
    'FROM emp ',
    'GROUP BY MD5(CONCAT_WS(''|'', ', GROUP_CONCAT(IFNULL(column_name, '''''') SEPARATOR ', '), ')) ',
    'HAVING COUNT(*) > 1'
) AS duplicate_check_sql
FROM information_schema.columns
WHERE table_schema = '你的数据库名' AND table_name = 'emp';

执行生成的SQL后，哈希值相同的行即为重复行。若需要查看具体重复的行，可以通过哈希值关联原表查询：

-- 假设生成的哈希列名为row_hash
WITH duplicate_hashes AS (
    SELECT MD5(CONCAT_WS('|', col1, col2, ...)) AS row_hash
    FROM emp
    GROUP BY row_hash
    HAVING COUNT(*) > 1
)
SELECT e.*
FROM emp e
JOIN duplicate_hashes dh ON MD5(CONCAT_WS('|', e.col1, e.col2, ...)) = dh.row_hash;

其他数据库适配 #

• SQL Server：使用HASHBYTES函数：

  SELECT HASHBYTES('SHA2_256', CONCAT_WS('|', col1, col2, ...)), COUNT(*)
  FROM emp
  GROUP BY HASHBYTES('SHA2_256', CONCAT_WS('|', col1, col2, ...))
  HAVING COUNT(*) > 1;
  

• PostgreSQL：使用md5函数：

  SELECT md5(concat_ws('|', col1, col2, ...)), COUNT(*)
  FROM emp
  GROUP BY md5(concat_ws('|', col1, col2, ...))
  HAVING COUNT(*) > 1;
  

注意事项 #

• 哈希碰撞概率极低，但如果需要绝对精确，找到重复哈希后需对比原始行数据；
• 务必处理NULL值，避免因NULL导致拼接结果异常（CONCAT_WS会自动忽略NULL，或用IFNULL转换为特定字符串）。

内容的提问来源于stack exchange，提问作者Anim