MySQL-从多个表中显示列 [英] MySQL - SHOW COLUMNS from Multiple Tables

问题描述

我正在尝试从2个表中获取列名.

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

我尝试了类似(SHOW COLUMNS FROM users) UNION (SHOW COLUMNS FROM posts)的查询，但这不起作用&返回语法错误.我使用DESCRIBE尝试了相同的查询，但也无法正常工作. 如何在一个查询中从多个表中获取所有列名?有可能吗?

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?

推荐答案

从文档中获取版本5.0(

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" 

因此您不能真正在多个表上使用它.但是，如果您有information_schema数据库，则可以按如下方式使用它:

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

在这里，您必须将mydb替换为您的数据库名称，或者只使用DATABASE().

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

这篇关于MySQL-从多个表中显示列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！