php MySQL查询以获取列名？

MySQL query to get column names?

提问by Haroldo

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?

有这方面的查询吗？

回答by ircmaxell

The best way is to use the INFORMATION_SCHEMAmetadata virtual database. Specifically the INFORMATION_SCHEMA.COLUMNStable...

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

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_SCHEMAtables, check out the MySQL Documentation on INFORMATION_SCHEMAin general...

有关表之间的区别SHOW...和使用INFORMATION_SCHEMA表的更多信息，请查看 MySQL文档INFORMATION_SCHEMA一般...

回答by bcosca

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 TABLEvisit: MySQL Refrence.

有关SHOW COLUMNS FROM TABLE访问输出的详细信息：MySQL 参考。

回答by Surreal Dreams

Seems there are 2 ways:

好像有2种方式：

DESCRIBE `tablename`

or

或者

SHOW COLUMNS FROM `tablename`

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

更多DESCRIBE信息：http: //dev.mysql.com/doc/refman/5.0/en/describe.html

回答by Jeff

I have done this in the past.

我过去曾这样做过。

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

回答by The Pixel Developer

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

编辑：今天我学会了更好的方法。请参阅 ircmaxell 的回答。

---

---

Parse the output of SHOW COLUMNS FROM table;

解析输出 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

回答by Gavin Simpson

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 起已弃用，将来会被删除。”

回答by user2558588

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()”。因此，今天获取字段名称的最佳方法是查询“SHOW COLUMNS FROM table_name [LIKE '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; }

回答by Abdul Gaffar Shah

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

回答by useranon

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

回答by Shafiqul Islam

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.

你想检查更多然后thik链接也可以帮助你。

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

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