如何在 MySQL 中查找包含特定列名的所有表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/193780/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find all the tables in MySQL with specific column names in them?
提问by Jobi Joy
I have 2-3 different column names that I want to look up in the entire DB and list out all tables which have those columns. Any easy script?
我有 2-3 个不同的列名，我想在整个数据库中查找并列出所有包含这些列的表。有什么简单的脚本吗？
回答by Ken
To get all tables with columns columnAor ColumnBin the database YourDatabase:
要获取带有列columnA或ColumnB数据库中的所有表YourDatabase：
SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('columnA','ColumnB')
AND TABLE_SCHEMA='YourDatabase';
回答by GSerg
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%wild%';
回答by Xman Classical
More simply done in one line of SQL:
在一行 SQL 中更简单地完成：
SELECT * FROM information_schema.columns WHERE column_name = 'column_name';
回答by baycaysoi
SELECT DISTINCT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE 'employee%'
AND TABLE_SCHEMA='YourDatabase'
回答by Radu Maris
In version that do not have information_schema(older versions, or some ndb's) you can dump the table structure and search the column manually.
在没有的information_schema版本（旧版本或某些 ndb）中，您可以转储表结构并手动搜索列。
mysqldump -h$host -u$user -p$pass --compact --no-data --all-databases > some_file.sql
Now search the column name in some_file.sqlusing your preferred text editor, or use some nifty awk scripts.
现在some_file.sql使用您喜欢的文本编辑器搜索列名，或者使用一些漂亮的 awk 脚本。
And a simple sed script to find the column, just replace COLUMN_NAMEwith your's:
还有一个简单的 sed 脚本来查找列，只需将COLUMN_NAME替换为您的：
sed -n '/^USE/{h};/^CREATE/{H;x;s/\nCREATE.*\n/\n/;x};/COLUMN_NAME/{x;p};' <some_file.sql
USE `DATABASE_NAME`;
CREATE TABLE `TABLE_NAME` (
`COLUMN_NAME` varchar(10) NOT NULL,
You can pipe the dump directly in sed but that's trivial.
您可以直接在 sed 中通过管道传输转储，但这很简单。
回答by oucil
For those searching for the inverse of this, i.e. looking for tables that do not contain a certain column name, here is the query...
对于那些搜索与此相反的，即寻找不包含特定列名的表，这里是查询...
SELECT DISTINCT TABLE_NAME FROM information_schema.columns WHERE
TABLE_SCHEMA = 'your_db_name' AND TABLE_NAME NOT IN (SELECT DISTINCT
TABLE_NAME FROM information_schema.columns WHERE column_name =
'column_name' AND TABLE_SCHEMA = 'your_db_name');
This came in really handy when we began to slowly implement use of InnoDB's special ai_colcolumn and needed to figure out which of our 200 tables had yet to be upgraded.
当我们开始慢慢实现 InnoDB 特殊ai_col列的使用并需要弄清楚我们的 200 个表中的哪些尚未升级时，这非常方便。
回答by Shivendra Prakash Shukla
If you want "To get all tables only", Then use this query:
如果您想要“仅获取所有表”，请使用以下查询：
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like '%'
and TABLE_SCHEMA = 'tresbu_lk'
If you want "To get all tables with Columns", Then use this query:
如果您想要“获取带有列的所有表”，请使用以下查询：
SELECT DISTINCT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE '%'
AND TABLE_SCHEMA='tresbu_lk'
回答by António Delgado
Use this one line query, replace desired_column_name by your column name.
使用这一行查询，用您的列名替换 required_column_name。
SELECT TABLE_NAME FROM information_schema.columns WHERE column_name = 'desired_column_name';
回答by nageen nayak
SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%city_id%' AND TABLE_SCHEMA='database'
回答by user2587656
The problem with information_schema is that it can be terribly slow. It is faster to use the SHOW commands.
information_schema 的问题在于它可能非常慢。使用 SHOW 命令更快。
After you select the database you first send the query SHOW TABLES. And then you do SHOW COLUMNS for each of the tables.
选择数据库后，您首先发送查询 SHOW TABLES。然后你为每个表做 SHOW COLUMNS。
In PHP that would look something like
在 PHP 中，它看起来像
$res = mysqli_query("SHOW TABLES");
while($row = mysqli_fetch_array($res))
{ $rs2 = mysqli_query("SHOW COLUMNS FROM ".$row[0]);
while($rw2 = mysqli_fetch_array($rs2))
{ if($rw2[0] == $target)
....
}
}
