关于 mysql 两表查询，三列字段相等的一个问题

目前情况是这样的，我有两张表，分别为 t1 和 t2，t1 中有字段（ id,a,b,c ）,t2 中有字段(name,a,b,c)，想让这两表中的 a,b,c 字段相等时，查询出 id 和 name，我的查询语句如下select id,name from t1,t2 where(t1.a=t2.a and t1.b=t2.b and t1.c=t2.c)，但是一直查不出来，也尝试过先把 a 字段相等的查到一张表上，但是数据量实在太大了，表直接满了，报错了，各位大师傅有啥办法吗

相等

name

查询

大师傅

16 条回复 • 2020-10-04 20:16:57 +08:00

xyjincan

2020-10-02 23:09:46 +08:00 via Android

a，b，c where 顺序按表内重复数据量从小到大来，建立索引，这两个表还是合并吧

clf

2020-10-02 23:34:02 +08:00

试试连接？
select t1.id,t2.name from t1 inner join t2 on t1.a = t2.a and t1.b = t2.b and t1.c = t2.c;

不过其实最好还是弄个 t3 表，数据结构是 id,name,a,b,c 把两个表数据合并了最方便。

0clickjacking0

2020-10-02 23:38:38 +08:00

@lychs1998 我的思路是这样的，先把 a 相等的做一张临时表，然后在这张临时表中去查询 b 相等的，然后去查询 c 相等的，但是这样好像有问题，查不出来

0clickjacking0

2020-10-02 23:38:48 +08:00

@xyjincan 我的思路是这样的，先把 a 相等的做一张临时表，然后在这张临时表中去查询 b 相等的，然后去查询 c 相等的，但是这样好像有问题，查不出来

bowser1701

2020-10-02 23:42:24 +08:00 via iPhone

@0clickjacking0 用连接不行吗？感觉你这种思路也不会比连接高效。

KomiSans

2020-10-03 08:40:05 +08:00

select b01.id as id , b02.name as name from b1 b01 INNER JOIN b2 b02 ON b01.a = b02.a and b01.b = b02.b and b01.c = b02.c; ？？？

lpts007

2020-10-03 09:24:36 +08:00 via Android

想求楼主个事
能把表数据量，数据分布描述一下吗
abc 重的多了肯定查不出来，你自己打算怎么办

0clickjacking0

2020-10-03 09:49:38 +08:00

@lpts007 表 1 大概 15 万条，表 2 大约 4900 条，就目前万想要的结果是 t1.a=t2.a and t1.b=t2.b and t1.c=t2.c 同时满足，但是查出来的貌似是笛卡尔积，有些数据有问题的，我去试试自然连接行不行，如果不行的话，考虑用 py 了，因为数据量也没有达到上百万的级别，用 py 也是无奈之举

wangritian

2020-10-03 10:08:32 +08:00

t2 作主表（总行数少的），left join t1，t1 创建联合索引 a,b,c
也可以增加字段计算 a,b,c 的哈希，对哈希字段创建索引，查询时不用管 a,b,c 了

0clickjacking0

2020-10-03 10:26:53 +08:00

@wangritian 那我是不是可以对 t1 和 t2 的 a,b,c 字段都计算哈希，然后直接去比较他们的哈希就行了，这样就变成了比较一个字段

lpts007

2020-10-03 12:40:53 +08:00

@0clickjacking0
“但是查出来的貌似是笛卡尔积” 那就对了，说明存在大量 abc 相等的数据
假设
表 t1
|id | a| b| c|
|1| a1|b1|c1|
|2| a1|b1|c1|
|3| a3|b3|c3|

表 t2
|name | a| b| c|
|n1| a1|b1|c1|
|n2| a1|b1|c1|
|n3| a1|b1|c1|
|n4| a3|b3|c3|

结果会有 2 x 3 +1 x 1 = 7 条。那你的预期是不是查出 7 条呢？
abc 重复记录越多，数据条数越多。
你对俩表数据分布没有大体概念吗.

我半瓶子水。

lpts007

2020-10-03 12:47:20 +08:00

至于 hash 成一个字段再原样查——如果结果跟之前不一样的话，要么 hash 碰撞，要么 mysql 的 bug 。

zhangysh1995

2020-10-03 14:00:38 +08:00

九楼靠谱 @wangritian
楼主可能要给出表结构会更好一些。另外，如果 a,b,c 是组合外键好说，不是的话，为啥 name 没有也放在 t1 里面？感觉有些许奇怪。从查询来说，需要给 t2 加组合索引 (a,b,c) ，inner join 和 left join 区别不大，都是 index scan

vZexc0m

2020-10-04 16:41:46 +08:00 via Android

试试先求和，然后对和做索引。然后缩小范围再查

PopRain

2020-10-04 20:12:54 +08:00

select distinct id,name from t1,t2 where(t1.a=t2.a and t1.b=t2.b and t1.c=t2.c)

你这个数据量一点都不大，查不出来说明满足条件的记录太多太多了。。。。试试消重

wangritian

2020-10-04 20:16:57 +08:00

@0clickjacking0 是的，这是哈希的作用之一，但不排除有哈希碰撞概率，在超大数据量时，哈希相等，还需要对应的 n 个字段也分别相等