blocks|key|932726|text|select+*+from+information_schema.columns
where+table_schema+=+'your_db'
order+by+table_name,ordinal_position|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|932727|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select * from information_schema.columns
where table_schema = 'your_db'
order by table_name,ordinal_position
</code></pre>

blocks|key|716690|text|要列出MySQL中表中的所有字段，请执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|716691|select+*+
++from+information_schema.columns+
+where+table_schema+=+'your_DB_name'+
+++and+table_name+=+'Your_tablename'|code-block|syntax|javascript|716692|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

To list all the fields from a table in MySQL:

<pre><code>select * 
 from information_schema.columns 
 where table_schema = 'your_DB_name' 
 and table_name = 'Your_tablename'
</code></pre>

blocks|key|716717|text|<?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>';
?>|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|716718|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>&lt;?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 '&lt;pre&gt;';
 print_r($columns);
 echo '&lt;/pre&gt;';
?&gt;
</code></pre>

blocks|key|716773|text|SELECT+*+FROM+information_schema.columns
WHERE+table_schema+=+DATABASE()
ORDER+BY+table_name,+ordinal_position|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|716774|由于我没有足够的代表来发表评论，这里有一个(在我看来)对nick+rulez的优秀答案的小改进:用WHERE+table_schema+=+DATABASE()替换WHERE+table_schema+=+'your_db'。|unstyled|offset|length|style|CODE|716775|entityMap^0|0|1D|V|2A|U|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|N|8|@$G|O|H|P|I|J]|$G|Q|H|R|I|J]]|9|@]|A|$]]|$1|K|3|-4|5|F|7|S|8|@]|9|@]|A|$]]]|L|$]]

<pre><code>SELECT * FROM information_schema.columns
WHERE table_schema = DATABASE()
ORDER BY table_name, ordinal_position
</code></pre>

Since I don't have enough rep to comment, here's a minor improvement (in my view) over nick rulez's excellent answer: replacing <code>WHERE table_schema = 'your_db'</code> with <code>WHERE table_schema = DATABASE()</code>.

blocks|key|932860|text|用一些可读的php搭载Nicola的答案|type|unstyled|depth|inlineStyleRanges|entityRanges|data|932861|$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>";|code-block|syntax|javascript|932862|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Piggybacking on Nicola's answer with some readable php

<pre><code>$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 "&lt;pre&gt;",print_r($d),"&lt;/pre&gt;";
</code></pre>

blocks|key|716894|text|与answer+posted+by+@suganya类似，它没有直接回答这个问题，但对于单个表来说，它是一个更快的替代方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|716895|DESCRIBE+column_name;|code-block|syntax|javascript|716896|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/13506582/773263^0|1|P|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Similar to the <a href="https://stackoverflow.com/a/13506582/773263">answer posted by @suganya</a> this doesn't directly answer the question but is a quicker alternative for a single table:

<pre><code>DESCRIBE column_name;
</code></pre>

blocks|key|932992|text|您可以使用此查询查询特定数据库中每个表的所有列|type|unstyled|depth|inlineStyleRanges|entityRanges|data|932993|select
++++TABLE_SCHEMA,
++++TABLE_NAME,
++++COLUMN_NAME
from
++++information_schema.columns
where
++++table_schema+=+'database_name'
order+by+
++++table_name,
++++ordinal_position;|code-block|syntax|javascript|932994|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can query all columns per table in specific database using this query
<pre class="lang-sql prettyprint-override"><code>select
 TABLE_SCHEMA,
 TABLE_NAME,
 COLUMN_NAME
from
 information_schema.columns
where
 table_schema = 'database_name'
order by 
 table_name,
 ordinal_position;
</code></pre>

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

How to get all columns' names for all the tables in MySQL?

数据库

MySQL

有没有一种快速的方法可以从MySQL中的所有表中获取所有列名，而不必列出所有表？

问如何获取MySQL中所有表的所有列的名称？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何获取MySQL中所有表的所有列的名称？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何获取MySQL中所有表的所有列的名称？
EN