MySQL 如何获取MySQL中所有表的所有列名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5648420/
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 get all columns' names for all the tables in MySQL?
提问by dieter
Is there a fast way of getting all column names from all tables in MySQL, without having to list all the tables?
有没有一种快速的方法可以从 中的所有表中获取所有列名MySQL，而不必列出所有表？
回答by Nicola Cossu
select * from information_schema.columns
where table_schema = 'your_db'
order by table_name,ordinal_position
回答by suganya
To list all the fields from a table in MySQL:
要列出 MySQL 中表中的所有字段：
select *
from information_schema.columns
where table_schema = 'your_DB_name'
and table_name = 'Your_tablename'
回答by appel
SELECT * FROM information_schema.columns
WHERE table_schema = DATABASE()
ORDER BY table_name, ordinal_position
Since I don't have enough rep to comment, here's a minor improvement (in my view) over nick rulez's excellent answer: replacing WHERE table_schema = 'your_db'with WHERE table_schema = DATABASE().
由于我没有足够的代表来发表评论，因此（在我看来）这是对 nick rulez 的出色答案的一个小改进：替换WHERE table_schema = 'your_db'为WHERE table_schema = DATABASE().
回答by rajesh satpute
it is better that you use the following query to get all column names easily
最好使用以下查询轻松获取所有列名
Show columns from tablename
Show columns from tablename
回答by trapper_hag
On the offchance that it's useful to anyone else, this will give you a comma-delimited list of the columns in each table:
如果它对其他人有用，这将为您提供每个表中列的逗号分隔列表：
SELECT table_name,GROUP_CONCAT(column_name ORDER BY ordinal_position)
FROM information_schema.columns
WHERE table_schema = DATABASE()
GROUP BY table_name
ORDER BY table_name
Note : When using tables with a high number of columns and/or with long field names, be aware of the group_concat_max_lenlimit, which can cause the data to get truncated.
注意：使用具有大量列和/或长字段名称的表时，请注意group_concat_max_len限制，这可能会导致数据被截断。
回答by Ali Nawaz
<?php
$table = 'orders';
$query = "SHOW COLUMNS FROM $table";
if($output = mysql_query($query)):
$columns = array();
while($result = mysql_fetch_assoc($output)):
$columns[] = $result['Field'];
endwhile;
endif;
echo '<pre>';
print_r($columns);
echo '</pre>';
?>
回答by Philip Kirkbride
Similar to the answer posted by @suganyathis doesn't directly answer the question but is a quicker alternative for a single table:
与@suganya 发布的答案类似，这并没有直接回答问题，但对于单个表来说是一个更快的替代方案：
DESCRIBE column_name;
回答by J.BizMai
The question was :
问题是：
Is there a fast way of getting all COLUMN NAMES from all tables in MySQL, without having to list all the tables?
有没有一种快速的方法可以从 MySQL 的所有表中获取所有列名，而不必列出所有表？
SQL to get all information for each column
SQL获取每列的所有信息
select * from information_schema.columns
where table_schema = 'your_db'
order by table_name,ordinal_position
SQL to get all COLUMN NAMES
获取所有列名的 SQL
select COLUMN_NAME from information_schema.columns
where table_schema = 'your_db'
order by table_name,ordinal_position
回答by lysdexia
I wrote this silly thing a long time ago and still actually use it now and then:
我很久以前写过这个愚蠢的东西，现在仍然时不时地使用它：
https://gist.github.com/kphretiq/e2f924416a326895233d
https://gist.github.com/kphretiq/e2f924416a326895233d
Basically, it does a "SHOW TABLES", then a "DESCRIBE " on each table, then spits it out as markdown.
基本上，它在每张桌子上做一个“SHOW TABLES”，然后是“DESCRIBE”，然后将其作为降价吐出。
Just edit below the "if name" and go. You'll need to have pymysql installed.
只需在“if name”下方编辑即可。您需要安装 pymysql。
回答by Scot Nery
Piggybacking on Nicola's answer with some readable php
用一些可读的 php 支持 Nicola 的回答
$a = mysqli_query($conn,"select * from information_schema.columns
where table_schema = 'your_db'
order by table_name,ordinal_position");
$b = mysqli_fetch_all($a,MYSQLI_ASSOC);
$d = array();
foreach($b as $c){
if(!is_array($d[$c['TABLE_NAME']])){
$d[$c['TABLE_NAME']] = array();
}
$d[$c['TABLE_NAME']][] = $c['COLUMN_NAME'];
}
echo "<pre>",print_r($d),"</pre>";
