mysql - 如何在 mySQL 数据库中查找具有两列或多列的所有表

Question

我想在 mySQL 数据库中找到所有包含两列“zoneid”和“affiliateid”的表。

如果我使用下面的查询，它将给我包含任一列的表。我想找出具有两列的表

SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('affiliateid','zoneid')
        AND TABLE_SCHEMA='openx';

我试过像这样写查询

SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('affiliateid') AND COLUMN_NAME IN ('zoneid')
        AND TABLE_SCHEMA='openx';

但是没用

Answer 1

  SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME ='affiliateid' union SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'zoneid'

或

SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME ='affiliateid' union all SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'zoneid'

或类似于

SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%affiliateid%' union all SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%zoneid%'