使用MySql选择具有多个特定列的所有表

Question

我希望获取所有包含三个特定列的表名。

我需要从信息模式中获取所有表名，这些表包含columnA 和 columnB 和 columnC。

目前我正在使用类似以下查询：

SELECT DISTINCT TABLE_NAME 
  FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME='columnA'
        AND TABLE_SCHEMA='mysampledatabase';

如何扩展以上查询并选择包含列名columnA和columnB的表？

---

我看到了这个答案和其他类似的答案。他们回答了以下内容:

SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('columnA','ColumnB') AND TABLE_SCHEMA='YourDatabase';

然而，他们选择了任何具有这3个列中任意一个的表。我认为这是因为WHERE COLUMN_NAME IN ('columnA','ColumnB','ColumnC')，我相信它等同于COLUMN_NAME = 'columnA' OR COLUMN_NAME = 'columnB' OR COLUMN_NAME = 'columnC'。

---

我试图用 WHERE COLUMN_NAME='columnA' AND COLUMN_NAME='columnB' AND COLUMN_NAME='columnC' 替换 WHERE 子句，但显然这不会返回任何结果，因为 COLUMN_NAME 一次只能是一个值！

- Nis

Answer 1

这是一个更快的版本:

SELECT TABLE_NAME 
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE COLUMN_NAME IN ('columnA','columnB','columnC')
group by TABLE_NAME
having count(distinct COLUMN_NAME) = 3 

在上述查询中，使用count函数将在IN短语中指定的列数进行计算。

或者尝试以下方法（但这将为许多列创建灾难，请避免使用它）。

SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN 
( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'columnA' )
and TABLE_NAME in 
( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'columnB')
and TABLE_NAME in 
( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'columnC')

- Dhwani

---

感谢您的回复。您的解决方案2会非常慢，因为它要遍历通常非常庞大的INFORMATION_SCHEMA.COLUMNS表3次。如果我想比较6列怎么办？还是要给您的贡献点个赞。 - Nis

Answer 2

以下查询可能会对您有所帮助：

SELECT`TABLE_SCHEMA`, `table_name`,c1.`column_name`,c2.`column_name`,c3.`column_name` FROM `COLUMNS` C1
INNER JOIN `COLUMNS` C2 USING(`TABLE_SCHEMA`, `TABLE_NAME`)
INNER JOIN `COLUMNS` C3 USING(`TABLE_SCHEMA`, `TABLE_NAME`)
WHERE c1.`column_name` = 'col1'
AND c2.`column_name` = 'col2'
AND c3.`column_name` = 'col3';

但是你需要为每个寻求的列添加|删除额外的JOIN。

现在我认为这有点复杂和难以理解，以下是另一种选项：

SELECT table_schema, table_name, count(*) AS `TablesCount` FROM `COLUMNS`
WHERE `column_name` IN ('col1','col1','col1')
GROUP BY table_schema, table_name
HAVING `TablesCount` = 3

TablesCount应该等于你要查找的列的数量。如果是这样的话，那么你的表格就有所有必需的列。

- Uriil

---

谢谢。我尝试了第一个查询，它可以工作，但确实很重。然而，在第二个查询中，我应该把columnA、columnG、columnX等列名放在哪里？ - Nis

@Nis 对不起，我在测试时忘记从查询中删除我的列。已更新*（第二行） - Uriil

谢谢。对于其他有兴趣了解性能结果的人，解决方案2的速度最少是解决方案1的两倍（执行时间减半）。例如，如果解决方案1需要36秒（是的，我有一个巨大的数据库），那么解决方案2只需要最多18秒。 - Nis