如何在MySQL中查找所有包含特定列名的表？

Question

我有2-3个不同的列名，希望在整个数据库中查找并列出拥有这些列的所有表。是否有简单的脚本可用？

- Joy

---

请参考此链接：http://winashwin.wordpress.com/2012/08/28/mysql-search/ - user1573308

Answer 1

要获取数据库YourDatabase中所有具有columnA或ColumnB列的表：

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

- Ken

---

@Echo - 你可以通过使用 --skip-extended-insert 参数来玩转 mysqldump，然后用 grep 在文件中搜索... 这样做可能有些不正当，但会带来奇妙的满足感 :) - Ken

@Echo，对于版本低于5的情况，请使用mysqldump将结构转储到文件中，详见我的回答。 - Radu Maris

您可以使用DATABASE()而不是字符串来在当前选定的数据库中进行搜索。 - Sherlock

@Ken：你有办法在查询中添加一个过滤器吗？实际上，选择列A具有特定值的表。 - Fred FLECHE

@FredFLECHE 我认为在那个阶段，我会只是在脚本中循环遍历它们，并保持简单。 - Ken

Answer 2

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%wild%';

- GSerg

---

如果有多个数据库，请同时添加一个WHERE TABLE_SCHEMA=""的语句。 - John Millikin

谢谢John，如果我需要获取具有ColumnA和ColumnB的表名，该如何查询？ - Jobi Joy

@JohnMillikin：或者相反，如果您不知道数据库，只知道字段名称，请将其省略并将TABLE_SCHEMA添加到返回集的字段中，以查看包含该列名称的所有数据库+表。 - Abel

这句话不应该是像'wild'这样的模糊匹配，而应该是像'%wild%'或者= 'wild'这样的精确匹配吗？ - Uncle Iroh

我刚试了一下，但发现所有结果都是针对不会在查询“show tables;”中显示的对象。有人能解释一下这可能是为什么吗？ - wdkrnls

Answer 3

更简单的做法是使用一行SQL代码：

SELECT * FROM information_schema.columns WHERE column_name = 'column_name';

- Xman Classical

---

在这里使用 * 有什么缺点吗？ - Guney Ozsan

@GuneyOzsan 唯一的缺点是获取比表名更多的数据，而这正是 OP 所要求的。 - timetofly

Answer 4

SELECT DISTINCT TABLE_NAME, COLUMN_NAME  
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE column_name LIKE 'employee%'  
AND TABLE_SCHEMA='YourDatabase'

- baycaysoi

Answer 5

在较旧的MySQL版本或某些没有information_schema的MySQL NDB Cluster版本中，您可以转储表结构并手动搜索列。

mysqldump -h$host -u$user -p$pass --compact --no-data --all-databases > some_file.sql

现在使用您喜欢的文本编辑器在some_file.sql中搜索列名，或者使用一些巧妙的AWK脚本。

---

这是一个简单的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,

你可以直接将转储内容通过管道传输到sed中进行处理，但那很简单。

- Radu Maris

---

版本指的是什么？MySQL？"ndb"是什么？InnoDB？还是其他什么？请尽可能通过编辑（更改）您的答案来回答，而不是在评论中回答（不要包含“编辑：”，“更新：”或类似内容 - 答案应该看起来像今天写的）。例如，通过链接到"ndb"是什么。 - Peter Mortensen

@PeterMortensen，感谢您的用心，但由于这是一个10年前的答案，我无法记得确切的版本或其他细节。我会重新表述并使用MySQL NDB Cluster而不是简单地使用ndb，希望现在清楚了。 - Radu Maris

Answer 6

对于那些正在寻找相反的情况，即寻找不包含特定列名的表格的人，请使用以下查询...

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

当我们开始逐步实施使用InnoDB的特殊ai_col列并需要找出尚未升级的200个表格时，这真的非常方便。

- oucil

Answer 7

使用这一行查询。将desired_column_name替换为您的列名。

SELECT TABLE_NAME FROM information_schema.columns WHERE column_name = 'desired_column_name';

- António Delgado

Answer 8

如果您想要“仅获取所有表”，则使用以下查询：

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like '%'
and TABLE_SCHEMA = 'tresbu_lk'

如果你想“获取包含列的所有表”，那么使用这个查询：

SELECT DISTINCT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE '%'
AND TABLE_SCHEMA='tresbu_lk'

- Shivendra Prakash Shukla

Answer 9

select distinct table_name 
from information_schema.columns 
where column_name in ('ColumnA') 
    and table_schema='YourDatabase';
    and table_name in 
    (
        select distinct table_name 
        from information_schema.columns 
        where column_name in ('ColumnB')
              and table_schema='YourDatabase';
    );

这样做 ^^ 将获取具有 ColumnA 和 ColumnB 而不是仅具有 ColumnA 或 ColumnB 的表格，就像被接受的答案所示。

- love2code

---

这真的有效吗？第一个分号怎么样？无论如何，需要解释一下。例如，它的要点是什么？你在哪个版本的MySQL上测试过它？等等。请通过编辑（更改）您的答案来回复，而不是在评论中回复（不带“Edit：”，“Update：”或类似内容 - 答案应该看起来像今天写的）。 - Peter Mortensen

Answer 10

SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%city_id%' AND TABLE_SCHEMA='database'

- nageen nayak