mysql - 加入更多父表

Question

我有 8 个主表，每个表有大约 60 万条记录。

假设表名是

type_11_14 
type_12_15
type_13_16
type_4_5
type_6
type_7
type_8
type_10

每个表都有code 和name 列。 代码 是一个主键。我无法将所有这些表格合并成一张表格。

在上面的表中，列表 type_11_14 有类型 11 和 14 的记录并且 type_7 有类型 7 的记录

我还有其他表 all_types_count 。它具有上述类型的计数信息。对于给定的类型和代码列表(我们给出的最大代码是 50)，我需要获取它的名称、类型、代码和计数

我用 UNION 写了下面的查询。查询运行正常。但是如果我运行 Explain 计划，我会得到

错误无法启用约束。一行或多行包含违反非空、唯一或外键约束的值。

再次在下面的查询中，我查询了 all_types_count 表 8 次。取而代之的是，我想首先从 all_types_count 获取所有记录，然后与主表连接以获得所需的结果..

不确定对此的最佳 SQL 查询解决方案是什么。有人可以为我提供最好的方法吗？

SELECT type,code,total_count,type_11_14.name 
FROM all_types_count,type_11_14 
WHERE all_types_count.type in (11,14) 
AND all_types_count.code=type_11_14.code 
AND all_types_count.code in ( 3456,6789)

UNION ALL

SELECT type,code,total_count,type_12_15.name 
FROM all_types_count,type_12_15 
WHERE all_types_count.type in (12,15) 
AND all_types_count.code=type_12_15.code 
AND all_types_count.code in ( 2345,9087,234)

UNION ALL

SELECT type,code,total_count,type_13_16.name 
FROM all_types_count,type_13_16 
WHERE all_types_count.type in (13,16) 
AND all_types_count.code=type_13_16.code 
AND all_types_count.code in ( 98,24)

UNION ALL

SELECT type,code,total_count,type_4_5.name 
FROM all_types_count,type_4_5 
WHERE all_types_count.type in (4,5) 
AND all_types_count.code=type_4_5.code  
AND all_types_count.code in ( 765,9087,3456)

UNION ALL

SELECT type,code,total_count,type_6.name 
FROM all_types_count,type_6 
WHERE all_types_count.type=6 
AND all_types_count.code=type_6.code  
AND all_types_count.code in ( 5563,323,434,3442)

UNION ALL

SELECT type,code,total_count,type_7.name 
FROM all_types_count,type_7 
WHERE all_types_count.type=7 
AND all_types_count.code=type_7.code  
AND all_types_count.code in ( 7887,313,23,32,21)

UNION ALL

SELECT type,code,total_count,type_8.name 
FROM all_types_count,type_8 
WHERE all_types_count.type=8 
AND all_types_count.code=type_8.code  
AND all_types_count.code in ( 9988,1221)

UNION ALL

SELECT type,code,total_count,type_10.name 
FROM all_types_count,type_10 
WHERE all_types_count.type=10 
AND all_types_count.code=type_10.code  
AND all_types_count.code in ( 7787,23213)

谢谢

基兰

Answer 1

如果这样写，你的查询应该更有效率:

SELECT type,code,total_count,type_11_14.name 
FROM all_types_count 
left outer join type_11_14 
     on all_types_count.type in (11,14) 
     and all_types_count.code=type_11_14.code 
     and all_types_count.code in ( 3456,6789) 
left outer join type_12_15 
     on all_types_count.type in (12,15) 
     and all_types_count.code=type_12_15.code 
     and all_types_count.code in ( 2345,9087,234) 
left outer join  . . .

如果您不熟悉 join/left outer join/right outer join/cross join 语法，那么您应该学习它。

在您的原始查询中，优化器很可能会为每个子查询读取一次 all_types_count 表。使用连接，它应该只读取一次，并在需要时连接到其他表。