MySQL对比同结构两表差异：未知列时GROUP BY排除指定列

需求描述 #

对比两个字段/类型完全相同的表，找出至少一个字段不同或仅存在于其中一个表的记录。两表均包含唯一id列，可用于匹配对应记录，但匹配的记录可能其他列存在差异。

示例表 #

table0:

| id | col1 | col2 | ... |
| -- | ---- | ---- | --- |
| 22 | 1111 | 'gt' | ... |
| 23 | 5624 | 'ha' | ... |
| 24 | 7775 | 'oh' | ... |
| 26 | 2113 | 'yh' | ... |
| 28 | 9988 | 'wq' | ... |

table1:

| id | col1 | col2 | ... |
| -- | ---- | ---- | --- |
| 22 | 1111 | 'gt' | ... |
| 23 | 5624 | 'ha' | ... |
| 25 | 3333 | 'er' | ... |
| 26 | 2113 | 'ya' | ... |
| 28 | 9988 | 'wq' | ... |

期望结果 #

| id | reason |
| -- | ------ |
| 24 | only in table0 |
| 25 | only in table1 |
| 26 | not identical values |

尝试方案 #

SELECT *
    FROM
    (
        SELECT *, 0 AS src /* added src to identify table 0 */
        FROM table0
        UNION ALL
        SELECT *, 1 AS src /* added src to identify table 1 */
        FROM table1
    )  temp
    GROUP BY col1, col2, col3, ...
    HAVING COUNT(*) = 1

存在问题 #

• 编写代码时无法获知完整列集合，表结构会随CSV文件更新而变化，是否支持类似GROUP BY *的分组方式？
• 若支持上述分组方式，如何排除新增的src标识列？

解决方案 #

针对问题1：动态适配列的分组逻辑 #

绝大多数SQL数据库不支持直接用GROUP BY *，但可以通过两种方式动态适配表结构：

1. 查询系统元数据获取列名：以MySQL为例，可通过系统表查询目标表的所有列名，再拼接成分组语句：

SELECT GROUP_CONCAT(column_name SEPARATOR ', ')
FROM information_schema.columns
WHERE table_name = 'table0' AND table_schema = '你的数据库名';

该查询会返回table0的所有列名拼接字符串（如id, col1, col2），直接代入原SQL的GROUP BY子句即可。
2. 脚本层面动态生成SQL：如果是用Python等脚本处理CSV导入，可直接读取CSV表头，自动生成分组列列表，再拼接完整SQL语句，完全适配表结构更新。

针对问题2：排除src列的正确姿势 #

核心逻辑是仅对原表的列进行分组，不包含新增的src列：

• 若用元数据查询列名，结果本身就不包含src，直接使用即可；
• 若手动编写，确保GROUP BY后只写原表列名，不要加入src。

另外，原尝试方案存在逻辑缺陷：无法区分同一id下的列差异（如示例中的id=26）。更高效准确的写法是通过JOIN匹配id后直接对比列，无需分组：

推荐SQL写法（适配动态表结构）

SELECT 
    COALESCE(t0.id, t1.id) AS id,
    CASE
        WHEN t0.id IS NULL THEN 'only in table1'
        WHEN t1.id IS NULL THEN 'only in table0'
        ELSE 'not identical values'
    END AS reason
FROM table0 t0
FULL OUTER JOIN table1 t1 ON t0.id = t1.id
WHERE 
    t0.id IS NULL 
    OR t1.id IS NULL 
    OR NOT (t0 <=> t1); -- 对比所有列是否完全相等（MySQL语法，其他数据库可调整）

t0 <=> t1会自动对比两表所有列的一致性（包括NULL值），完美适配动态表结构，无需手动指定列名。

不支持FULL OUTER JOIN的数据库兼容写法

SELECT 
    t0.id AS id,
    'only in table0' AS reason
FROM table0 t0
LEFT JOIN table1 t1 ON t0.id = t1.id
WHERE t1.id IS NULL

UNION

SELECT 
    t1.id AS id,
    'only in table1' AS reason
FROM table1 t1
LEFT JOIN table0 t0 ON t1.id = t0.id
WHERE t0.id IS NULL

UNION

SELECT 
    t0.id AS id,
    'not identical values' AS reason
FROM table0 t0
JOIN table1 t1 ON t0.id = t1.id
WHERE NOT (t0 <=> t1);

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