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

I want to get all table names which has 3 specific columns.

我想要得到所有有3个特定列的表名。

What I want is from information schema I want to get all table names which contains columnA AND columnB AND columnC.

我想从信息模式中获得包含columnA、columnB和columnC的所有表名。

Currently I am using a query like

目前我正在使用一个类似的查询

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

How can I extend the above query and select tables which contains column names columnA and columnB ?

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

I came across this answer and other similar answers. They have answered following:

我遇到了这个答案和其他类似的答案。他们回答说:

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

从INFORMATION_SCHEMA选择不同的TABLE_NAME。列，其中COLUMN_NAME ('columnA'，'ColumnB')和TABLE_SCHEMA='YourDatabase';

However what they select any table which has either of the 3 columns. I believe that is because of WHERE COLUMN_NAME IN ('columnA','ColumnB','ColumnC') which , i believe, is equivalent to COLUMN_NAME = 'columnA' OR COLUMN_NAME = 'columnB' OR COLUMN_NAME = 'columnC'

然而，他们选择的任何表格都包含这三列中的任何一列。我认为这是因为COLUMN_NAME ('columnA'，'ColumnB'，'ColumnC')，我认为它等价于COLUMN_NAME = 'columnA'或COLUMN_NAME = 'ColumnB'或COLUMN_NAME = 'ColumnC'

I have tried to replace the WHERE clause with WHERE COLUMN_NAME='columnA' AND COLUMN_NAME='columnB' AND COLUMN_NAME='columnC' which obviously doesn't return any results because COLUMN_NAME can only be 1 value at a time!

我尝试将WHERE子句替换为COLUMN_NAME='columnA'和COLUMN_NAME='columnB'和COLUMN_NAME='columnC'，这显然不会返回任何结果，因为COLUMN_NAME一次只能是一个值!

2 个解决方案

#1

Here is query which might help you:

以下是可能对您有所帮助的查询:

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';

But you will need to add|remove extra JOIN for each column you looking for.

但是您需要为每一列添加|删除额外的连接。

Now i think it's kind of heavy and hard to understand, here is another option:

现在我觉得这有点沉重，很难理解，这里还有一个选择:

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 should be equal number of columns you looking for. If it's so, then your table have all required columns

表计数应该等于您要查找的列数。如果是这样，那么您的表拥有所有必需的列

#2

This is a faster version:

这是一个更快的版本:

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

In above query having count will have no. of columns specified in IN phrase.

在上面的查询中有计数将没有。在短语中指定的列。

or try following(but it will create disaster for so many columns. So avoid it)

或者尝试跟随(但它会为这么多的列造成灾难)。所以避免)

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')

#1