MySQL选择具有多个特定列的所有表 [英] MySql select all tables with multiple specific columns
问题描述
我想获取所有具有3个特定列的表名.
I want to get all table names which has 3 specific columns.
我想从信息架构中获取所有表名,其中包含columnA
AND columnB
AND
What I want is from information schema I want to get all table names which contains columnA
AND columnB
AND columnC
.
当前我正在使用类似的查询
Currently I am using a query like
SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME='columnA'
AND TABLE_SCHEMA='mysampledatabase';
如何扩展以上查询,并选择包含列名称为columnA
和columnB
的表?
How can I extend the above query and select tables which contains column names columnA
and 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';
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'
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'
我试图用WHERE COLUMN_NAME='columnA' AND COLUMN_NAME='columnB' AND COLUMN_NAME='columnC'
替换WHERE子句,该子句显然不会返回任何结果,因为COLUMN_NAME
一次只能是1个值!
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!
推荐答案
以下查询可能对您有帮助:
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';
但是,您需要为每个要查找的列添加|删除额外的JOIN
.
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应该等于您要查找的列数.如果是这样,那么您的表将包含所有必填列
TablesCount should be equal number of columns you looking for. If it's so, then your table have all required columns
这篇关于MySQL选择具有多个特定列的所有表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!