mysql - 多表联合的限制

Question

我的 mysql 查询对每个选择都有限制，但我也希望对完整查询限制为 1。我也尝试围绕整个查询构建一个选择，但它仍然会遍历所有表，即使第一个表中有结果，因此持续时间是相同的。我希望查询在得到结果后立即停止。有什么解决办法吗？

(SELECT count(*) FROM table1 i JOIN table1item it where it.columnId = 2 LIMIT 1)union
(SELECT count(*) FROM table2 i JOIN table2item it where it.columnId = 2 LIMIT 1)union
(SELECT count(*) FROM table3 i JOIN table3item it where it.columnId = 2 LIMIT 1)union
(SELECT count(*) FROM table4 i JOIN table4item it where it.columnId = 2 LIMIT 1)union
(SELECT count(*) FROM table5 i JOIN table5item it where it.columnId = 2 LIMIT 1)union
(SELECT count(*) FROM table6 i JOIN table6item it where it.columnId = 2 LIMIT 1)union
(SELECT count(*) FROM table7 i JOIN table7item it where it.columnId = 2 LIMIT 1)

Answer 1

添加 UNION ALL 以确保您从所有表中获得结果(如果它们相等)。

(SELECT count(*) FROM table1 i JOIN table1item it where it.columnId = 2) UNION ALL
(SELECT count(*) FROM table2 i JOIN table2item it where it.columnId = 2) UNION ALL
(SELECT count(*) FROM table3 i JOIN table3item it where it.columnId = 2) UNION ALL
(SELECT count(*) FROM table4 i JOIN table4item it where it.columnId = 2) UNION ALL
(SELECT count(*) FROM table5 i JOIN table5item it where it.columnId = 2) UNION ALL
(SELECT count(*) FROM table6 i JOIN table6item it where it.columnId = 2) UNION ALL
(SELECT count(*) FROM table7 i JOIN table7item it where it.columnId = 2)
LIMIT 1