MySQL GROUP BY除一列外的所有列

Question

我需要比较具有完全相同字段/类型的两个表，并识别至少一个字段中不同的或仅存在于两个表中的一个表的所有记录。

两个表都有一列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' | ... |
What I need is the following result/output:
| 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

但是这个解决方案有两个问题：

1. 在编写此代码时，我不知道完整的列集。表规范确实会随着时间的推移而变化(这两个表是通过读取csv文件创建的，尽管要比较的两个表总是相同的(。有没有办法用"*"这样的词来分组？

2. 如果确实可以按照我上面描述的方式进行分组，那么我需要排除我添加的src值，以标识生成记录的表。

Answer 1

您的上一次编辑使它成为一个不同的问题，因此可能值得提出一个新问题并删除这个问题，这样一些经验丰富的贡献者可能会重新访问它。

另一种方法是对所有列进行散列并比较值。如果有值，您仍然需要从information_schema.columns构建查询，但至少不需要单独匹配每一列。这里有一个的例子

DROP TABLE IF EXISTS T,T1;

CREATE TABLE T
( id INT, col1 INT, col2 VARCHAR(2));
INSERT INTO T VALUES
( 22 , 1111 , 'gt'  ),
( 23 , 5624 , 'ha'  ),
( 24 , 7775 , 'oh'  ),
( 26 , 2113 , 'yh'  ),
( 28 , 9988 , 'wq'  );

CREATE TABLE T1
( id INT, col1 INT, col2 VARCHAR(2));
INSERT INTO T1 VALUES
( 22 , 1111 , 'gt'  ),
( 23 , 5624 , 'ha'  ),
( 25 , 3333 , 'er'  ),
( 26 , 2113 , 'ya'  ),
( 28 , 9988 , 'wq'  );

SELECT T.ID,T.COL1,T.COL2,MD5(CONCAT(T.COL1,T.COL2)) MD5T,
T1.COL1,T1.COL2,MD5(CONCAT(T1.COL1,T1.COL2)) MD5T1
FROM T
LEFT JOIN T1 ON T1.ID = T.ID
HAVING MD5T <> MD5T1 OR MD5T1 IS NULL
UNION 
SELECT T.ID,T.COL1,T.COL2,MD5(CONCAT(T.COL1,T.COL2)) MD5T,
T1.COL1,T1.COL2,MD5(CONCAT(T1.COL1,T1.COL2)) MD5T1
FROM T1
LEFT JOIN T ON T1.ID = T.ID
WHERE T.ID IS NULL
;
+------+------+------+----------------------------------+------+------+----------------------------------+
| ID   | COL1 | COL2 | MD5T                             | COL1 | COL2 | MD5T1                            |
+------+------+------+----------------------------------+------+------+----------------------------------+
|   26 | 2113 | yh   | 0a67aa31e57d3b81e9a3adedcbf52926 | 2113 | ya   | bf963ac4282e4f16339a5f79d5042dcb |
|   24 | 7775 | oh   | fde0959e35d89f4ad2b0f8c0b89e4b20 | NULL | NULL | NULL                             |
| NULL | NULL | NULL | NULL                             | 3333 | er   | eb773ea6e5dd316a4e461ce4b52b1706 |
+------+------+------+----------------------------------+------+------+----------------------------------+
3 rows in set (0.015 sec)

注意：我不知道这在很多列中是如何执行的，但我想还有对哈希值进行分块的空间。

Answer 2

如果您知道源，并且可以使用窗口函数求和值1，那么根据定义，如果结果为1，那么丢失的源是显而易见的。例如

DROP TABLE IF EXISTS T,T1,t2;
create table t(id int);
create table t1(id int);
insert into t values (1),(2),(3);
insert into t1 values (1),(4),(3);
create table t2 as
select *,'t' as src from t
union all
select *,'t1' from t1;
with cte as
(
select *,sum(1) over (partition by id) sumrows 
from t2
)
select *,
case when src = 't' then 't1'
else 't'
end as 'missing' 
from cte
where sumrows = 1;
+------+-----+---------+---------+
| id   | src | sumrows | missing |
+------+-----+---------+---------+
|    2 | t   |       1 | t1      |
|    4 | t1  |       1 | t       |
+------+-----+---------+---------+
2 rows in set (0.013 sec)

在您的情况下，由于您不知道需要使用动态sql的所有列。

注意：我不知道在大量列和大量数据的情况下会如何执行，出于说明的目的，我没有试图优化这个过程。

"至少在一个字段中不同"也不常见，但如果上述过程的结果将结果集缩小到合理的大小，则可能会出现这种情况。