MySQL查询获取列名?

I'd like to get all of a mysql table's col names into an array in php?

我想把所有的mysql表的col名都放到php数组中?

Is there a query for this?

对此有疑问吗?

18 个解决方案

#1

390

The best way is to use the INFORMATION_SCHEMA metadata virtual database. Specifically the INFORMATION_SCHEMA.COLUMNS table...

最好的方法是使用INFORMATION_SCHEMA元数据虚拟数据库。特别是INFORMATION_SCHEMA。列的表…

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' 
    AND `TABLE_NAME`='yourtablename';

It's VERY powerful, and can give you TONS of information without need to parse text (Such as column type, whether the column is nullable, max column size, character set, etc)...

它非常强大，可以提供大量的信息，而不需要解析文本(比如列类型、列是空的、最大列大小、字符集等等)……

Oh, and it's standard SQL (Whereas SHOW ... is a MySQL specific extension)...

哦，这是标准SQL(而SHOW……)是一个MySQL特定的扩展)…

For more information about the difference between SHOW... and using the INFORMATION_SCHEMA tables, check out the MySQL Documentation on INFORMATION_SCHEMA in general...

有关显示之间的差异的更多信息…使用INFORMATION_SCHEMA表，请查看关于INFORMATION_SCHEMA的MySQL文档。

#2

146

You can use following query for MYSQL:

你可以对MYSQL使用以下查询:

SHOW columns FROM your-table;

Below is the example code which shows How to implement above syntax in php to list the names of columns:

下面是示例代码，它展示了如何在php中实现上述语法，以列出列的名称:

$sql = "SHOW COLUMNS FROM your-table";
$result = mysqli_query($conn,$sql);
while($row = mysqli_fetch_array($result)){
    echo $row['Field']."<br>";
}

For Details about output of SHOW COLUMNS FROM TABLE visit: MySQL Refrence.

有关表访问显示列输出的详细信息，请参阅MySQL。

#3

Seems there are 2 ways:

似乎有两种方式:

DESCRIBE `tablename`

或

SHOW COLUMNS FROM `tablename`

More on DESCRIBE here: http://dev.mysql.com/doc/refman/5.0/en/describe.html

更多关于此处的描述:http://dev.mysql.com/doc/refman/5.0/en/describe.html。

#4

I have done this in the past.

我以前也这么做过。

SELECT column_name
FROM information_schema.columns
WHERE table_name='insert table name here';

#5

Edit: Today I learned the better way of doing this. Please see ircmaxell's answer.

今天我学到了更好的方法。请参阅ircmaxell的回答。

Parse the output of SHOW COLUMNS FROM table;

从表中解析显示列的输出;

Here's more about it here: http://dev.mysql.com/doc/refman/5.0/en/show-columns.html

这里有更多关于它的信息:http://dev.mysql.com/doc/refman/5.0/en/show-columns.html。

#6

An old PHP function "mysql_list_fields()" is deprecated. So, today the best way to get names of fields is a query "SHOW COLUMNS FROM table_name [LIKE 'name']". So, here is a little example:

一个旧的PHP函数“mysql_list_fields()”被弃用。因此，当前获取字段名的最佳方法是查询“从table_name(如名称)中显示列”。这里有一个小例子:

$fields = array();
$res=mysql_query("SHOW COLUMNS FROM mytable");
while ($x = mysql_fetch_assoc($res)){
  $fields[] = $x['Field'];
}
foreach ($fields as $f) { echo "<br>Field name: ".$f; }

#7

Use mysql_fetch_field() to view all column data. See manual.

使用mysql_fetch_field()查看所有列数据。参见手册。

$query = 'select * from myfield';
$result = mysql_query($query);
$i = 0;
while ($i < mysql_num_fields($result))
{
   $fld = mysql_fetch_field($result, $i);
   $myarray[]=$fld->name;
   $i = $i + 1;
}

"Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future."

“警告这个扩展被弃用为PHP 5.5.0，并且将在将来被删除。”

#8

function get_col_names(){  
    $sql = "SHOW COLUMNS FROM tableName";  
    $result = mysql_query($sql);     
    while($record = mysql_fetch_array($result)){  
     $fields[] = $record['0'];  
    }
    foreach ($fields as $value){  
      echo 'column name is : '.$value.'-';  
}  
 }  

return get_col_names();

#9

when you want to check your all table structure with some filed then use this code. In this query i select column_name,column_type and table_name for more details . I use order by column_type so i can see it easily.

当您想要检查所有表结构时，请使用此代码。在这个查询中，我选择column_name、column_type和table_name以获取更多细节。我使用column_type的order，所以我可以很容易地看到它。

SELECT `COLUMN_NAME`,COLUMN_TYPE,TABLE_NAME 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' order by DATA_TYPE;

If you want to check only double type filed then you can do it easily

如果你想只检查双类型的文件，那么你可以很容易地完成它。

SELECT `COLUMN_NAME`,COLUMN_TYPE,TABLE_NAME,DATA_TYPE 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' AND DATA_TYPE like '%bigint%'  order by DATA_TYPE;

if you want to check which field allow null type etc then you can use this

如果你想检查哪个字段允许空类型等等，那么你可以使用这个。

SELECT `COLUMN_NAME`,COLUMN_TYPE,TABLE_NAME,IS_NULLABLE,DATA_TYPE 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' and DATA_TYPE like '%bigint%' and IS_NULLABLE ='NO' order by COLUMN_TYPE;

you want to check more then thik link also help you.

你想要查看更多的链接也可以帮助你。

https://dev.mysql.com/doc/refman/5.7/en/columns-table.html

#10

SHOW COLUMNS in mysql 5.1 (not 5.5) uses a temporary disk table.

在mysql 5.1(不是5.5)中显示列使用临时磁盘表。

http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html
http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html
http://dev.mysql.com/doc/refman/5.1/en/show-columns.html
http://dev.mysql.com/doc/refman/5.1/en/show-columns.html

So it can be considered slow for some cases. At least, it can bump up your created_tmp_disk_tables value. Imagine one temporary disk table per connection or per each page request.

所以在某些情况下，它可以被认为是缓慢的。至少，它可以提高created_tmp_disk_tables的值。假设每个连接或每个页面请求都有一个临时磁盘表。

SHOW COLUMNS is not really so slow, possibly because it uses file system cache. Phpmyadmin says ~0.5ms consistently. This is nothing compared to 500ms-1000ms of serving a wordpress page. But still, there are times it matters. There is a disk system involvement, you never know what happens when server is busy, cache is full, hdd is stalled etc.

显示列并不那么慢，可能是因为它使用了文件系统缓存。Phpmyadmin ~ 0.5女士坚持说。这与提供一个wordpress页面的500ms-1000ms相比简直微不足道。但是，有时这也很重要。有一个磁盘系统的参与，你永远不知道当服务器繁忙时发生了什么，缓存是满的，硬盘驱动器是停滞的等等。

Retrieving column names through SELECT * FROM ... LIMIT 1 was around ~0.1ms, and it can use query cache as well.

通过SELECT *从…检索列名。限制1在~0.1ms左右，它也可以使用查询缓存。

So here is my little optimized code to get column names from a table, without using show columns if possible:

这里是我的一些优化的代码，可以从表中获取列名，如果可能的话，不使用显示列:

function db_columns_ar($table)
{
//returns Array('col1name'=>'col1name','col2name'=>'col2name',...)
if(!$table) return Array();
if(!is_string($table)) return Array();

global $db_columns_ar_cache;
if(!empty($db_columns_ar_cache[$table]))
    return $db_columns_ar_cache[$table];


//IMPORTANT show columns creates a temp disk table
$cols=Array();
$row=db_row_ar($q1="SELECT * FROM `$table` LIMIT 1");
if($row)
    {
    foreach($row as $name=>$val)
        $cols[$name]=$name;
    }
else
    {
    $coldata=db_rows($q2="SHOW COLUMNS FROM `$table`");
    if($coldata)
        foreach($coldata as $row)
            $cols[$row->Field]=$row->Field;
    }
$db_columns_ar_cache[$table]=$cols;
//debugexit($q1,$q2,$row,$coldata,$cols);
return $cols;
}

Notes:

注:

As long as your tables first row does not contain megabyte range of data, it should work fine.
只要您的表第一行不包含兆字节的数据，它就可以正常工作。
The function names db_rows and db_row_ar should be replaced with your specific database setup.
应该用特定的数据库设置替换函数名db_rows和db_row_ar。

#11

Not sure if this is what you were looking for, but this worked for me:

不确定这是不是你想要的，但这对我来说很有效:

$query = query("DESC YourTable");  
$col_names = array_column($query, 'Field');

That returns a simple array of the column names / variable names in your table or array as strings, which is what I needed to dynamically build MySQL queries. My frustration was that I simply don't know how to index arrays in PHP very well, so I wasn't sure what to do with the results from DESC or SHOW. Hope my answer is helpful to beginners like myself!

这将返回表或数组中的列名/变量名的简单数组作为字符串，这是我动态构建MySQL查询所需要的。我的沮丧之处在于，我根本不知道如何在PHP中索引数组，所以我不知道如何处理来自DESC或SHOW的结果。希望我的回答对像我这样的初学者有帮助!

To check result: print_r($col_names);

检查结果:print_r($ col_names);

#12

Try this one out I personally use it:

试试这个，我个人用它:

SHOW COLUMNS FROM $table where field REGEXP 'stock_id|drug_name'

#13

IN WORDPRESS:

在WORDPRESS:

global $wpdb;   $table_name=$wpdb->prefix.'posts';
foreach ( $wpdb->get_col( "DESC " . $table_name, 0 ) as $column_name ) {
  var_dump( $column_name );
}

#14

if you use php, use this gist.

如果使用php，请使用此要点。

it can get select fields full info with no result，and all custom fields such as:

它可以选择字段全部信息,没有结果,和所有定制字段,如:

SELECT a.name aname, b.name bname, b.* 
FROM table1 a LEFT JOIN table2 b
ON a.id = b.pid;

if above sql return no data,will also get the field names aname, bname, b's other field name

如果上面的sql没有返回数据，也会得到字段名aname, bname, b的其他字段名。

just two line:

仅仅两行:

$query_info = mysqli_query($link, $data_source);
$fetch_fields_result = $query_info->fetch_fields();

#15

This query fetches a list of all columns in a database without having to specify a table name. It returns a list of only column names:

该查询获取数据库中所有列的列表，而不需要指定表名。它返回一个列名称列表:

SELECT COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_schema = 'db_name'

However, when I ran this query in phpmyadmin, it displayed a series of errors. Nonetheless, it worked. So use it with caution.

但是，当我在phpmyadmin中运行这个查询时，它会显示一系列错误。尽管如此,它工作。所以要谨慎使用。

#16

The simplest solution out of all Answers:

最简单的解决办法:

DESC `table name`

或

DESCRIBE `table name`

或

SHOW COLUMNS FROM `table name`

#17

This question is old, but I got here looking for a way to find a given query its field names in a dynamic way (not necessarily only the fields of a table). And since people keep pointing this as the answer for that given task in other related questions, I'm sharing the way I found it can be done, using Gavin Simpson's tips:

这个问题已经过时了，但我在这里寻找一种方法，以动态的方式查找给定查询的字段名称(不一定只有表的字段)。既然人们总是把这个问题作为解决其他相关问题的答案，我就会用加文·辛普森的建议来分享我的发现。

//Function to generate a HTML table from a SQL query
function myTable($obConn,$sql)
{
    $rsResult = mysqli_query($obConn, $sql) or die(mysqli_error($obConn));
    if(mysqli_num_rows($rsResult)>0)
    {
        //We start with header. >>>Here we retrieve the field names<<<
        echo "<table width=\"100%\" border=\"0\" cellspacing=\"2\" cellpadding=\"0\"><tr align=\"center\" bgcolor=\"#CCCCCC\">";
        $i = 0;
        while ($i < mysqli_num_fields($rsResult)){
           $field = mysqli_fetch_field_direct($rsResult, $i);
           $fieldName=$field->name;
           echo "<td><strong>$fieldName</strong></td>";
           $i = $i + 1;
        }
        echo "</tr>"; 
        //>>>Field names retrieved<<<

        //We dump info
        $bolWhite=true;
        while ($row = mysqli_fetch_assoc($rsResult)) {
            echo $bolWhite ? "<tr bgcolor=\"#CCCCCC\">" : "<tr bgcolor=\"#FFF\">";
            $bolWhite=!$bolWhite;
            foreach($row as $data) {
                echo "<td>$data</td>";
            }
            echo "</tr>";
        }
        echo "</table>";
    }
}

This can be easily modded to insert the field names in an array.

这可以很容易地将字段名插入到数组中。

Using a simple: $sql="SELECT * FROM myTable LIMIT 1" can give you the fields of any table, without needing to use SHOW COLUMNS or any extra php module, if needed (removing the data dump part).

使用简单的:$sql="SELECT * FROM myTable LIMIT 1"可以给您任何表的字段，如果需要，无需使用SHOW列或任何额外的php模块(删除数据转储部分)。

Hopefully this helps someone else.

希望这能帮助别人。

#18

i no expert, but this works for me..

我不是专家，但这对我很有效。

$sql = "desc MyTable";
$result = @mysql_query($sql);
while($row = @mysql_fetch_array($result)){
    echo $row[0]."<br>"; // returns the first column of array. in this case Field

      // the below code will return a full array-> Field,Type,Null,Key,Default,Extra    
      // for ($c=0;$c<sizeof($row);$c++){echo @$row[$c]."<br>";}    

}

#1

390