MySQL - SHOW COLUMNS from Multiple Tables

问题:

I am trying to get the column names from 2 tables.

I tried a query like: (SHOW COLUMNS FROM users) UNION (SHOW COLUMNS FROM posts) but that does not work & returns a syntax error. I tried the same query using DESCRIBE but that did not work either. How can I get all the column names from multiple tables in a single query? Is it possible?

回答1:

From the docs for version 5.0 (http://dev.mysql.com/doc/refman/5.0/en/show-columns.html)

"SHOW COLUMNS displays information about the columns in a given table" 

So you can't really use it on multiple tables. However if you have information_schema database then you could use it like follows:

select column_name 
from `information_schema`.`columns` 
where `table_schema` = 'mydb' and `table_name` in ('users', 'posts');

Here you'd have to replace the mydb with your database name, or just use DATABASE().

回答2:

Yes use the information_Schema views.

SELECT * FROM information_schema.columns
WHERE Table_Name=? OR Table_name=?;

Use them as they are a standards way of querying database metadata.

回答3:

If you also would like to get the name of the table column is from select table_name too

SELECT column_name, table_name 
FROM `information_schema`.`columns` 
WHERE `table_schema` = DATABASE() AND `table_name` in ('table1', 'table2');