从所有MySQL表获取所有列

Is there a fast way of getting all column names from all tables in MySQL, without having to list all the tables?

是否有一种快速的方法，从MySQL中获取所有表的所有列名，而不需要列出所有表?

10 个解决方案

#1

222

select * from information_schema.columns
where table_schema = 'your_db'
order by table_name,ordinal_position

#2

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'

#3

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出色的答案:将table_schema = 'your_db'替换为table_schema = DATABASE()。

#4

it is better that you use the following query to get all column names easily

最好使用以下查询轻松获取所有列名

Show columns from tablename

显示列的表

#5

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_len limit, which can cause the data to get truncated.

注意:当使用具有大量列和/或具有长字段名的表时，请注意group_concat_max_len限制，这会导致数据被截断。

#6

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

#7

Similar to the answer posted by @suganya this doesn't directly answer the question but is a quicker alternative for a single table:

与@suganya贴出的答案相似，这并不能直接回答问题，但对于单个表来说，这是一个更快的选择:

DESCRIBE column_name;

#8

I wrote this silly thing a long time ago and still actually use it now and then:

我很久以前就写了这个愚蠢的东西，现在仍然经常用它:

https://gist.github.com/kphretiq/e2f924416a326895233d

Basically, it does a "SHOW TABLES", then a "DESCRIBE " on each table, then spits it out as markdown.

基本上，它会做一个“显示表”，然后在每个表上“描述”，然后把它作为标记。

Just edit below the "if name" and go. You'll need to have pymysql installed.

只需在“if名称”下面编辑，然后继续。您需要安装pymysql。

#9

Piggybacking on Nicola's answer with some readable php

利用Nicola的答案和一些可读的php

$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>";

#10

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

#1

222

select * from information_schema.columns
where table_schema = 'your_db'
order by table_name,ordinal_position

#2