在mysql中获取表列名?

Is there a way to grab the columns name of a table in mysql? using php

有没有办法在mysql中获取表的列名?使用php

17 个解决方案

#1

403

You can use DESCRIBE:

您可以使用描述:

DESCRIBE my_table;

Or in newer versions you can use INFORMATION_SCHEMA:

或者在新版本中，你可以使用INFORMATION_SCHEMA:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';

Or you can use SHOW COLUMNS:

也可以使用SHOW COLUMNS:

SHOW COLUMNS FROM my_table;

#2

The following SQL statements are nearly equivalent:

下面的SQL语句几乎是等价的:

SELECT COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_name = 'tbl_name'
  [AND table_schema = 'db_name']
  [AND column_name LIKE 'wild']

SHOW COLUMNS
FROM tbl_name
[FROM db_name]
[LIKE 'wild']

Reference: INFORMATION_SCHEMA COLUMNS

参考:INFORMATION_SCHEMA列

#3

I made a PDO function which returns all the column names in an simple array.

我做了一个PDO函数，它返回一个简单数组中的所有列名。

public function getColumnNames($table){
    $sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = :table";
    try {
        $core = Core::getInstance();
        $stmt = $core->dbh->prepare($sql);
        $stmt->bindValue(':table', $table, PDO::PARAM_STR);
        $stmt->execute();
        $output = array();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
            $output[] = $row['COLUMN_NAME'];                
        }
        return $output; 
    }

    catch(PDOException $pe) {
        trigger_error('Could not connect to MySQL database. ' . $pe->getMessage() , E_USER_ERROR);
    }
}

The output will be an array:

输出将是一个数组:

Array (
[0] => id
[1] => name
[2] => email
[3] => shoe_size
[4] => likes
... )

Sorry for the necro but I like my function ;)

为necro对不起，但我喜欢我的功能;

P.S. I have not included the class Core but you can use your own class.. D.S.

附注:我没有包含类的核心，但是你可以使用你自己的类。科学博士

#4

This solution is from command line mysql

这个解决方案来自命令行mysql

mysql>USE information_schema;

In below query just change <--DATABASE_NAME--> to your database and <--TABLENAME--> to your table name where you just want Field values of DESCRIBE statement

在下面的查询中，只需将<—DATABASE_NAME—>更改为您的数据库，并将<—TABLENAME—>更改为您的表名，您只需要描述语句的字段值

mysql> SELECT COLUMN_NAME FROM COLUMNS WHERE TABLE_SCHEMA = '<--DATABASE_NAME-->' AND   TABLE_NAME='<--TABLENAME-->';

#5

How about this:

这个怎么样:

SELECT @cCommand := GROUP_CONCAT( COLUMN_NAME ORDER BY column_name SEPARATOR ',\n')
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';

SET @cCommand = CONCAT( 'SELECT ', @cCommand, ' from my_database.my_table;');
PREPARE xCommand from @cCommand;
EXECUTE xCommand;

#6

Look into:

调查:

mysql_query('DESCRIBE '.$table);

#7

The MySQL function describe table should get you where you want to go (put your table name in for "table"). You'll have to parse the output some, but it's pretty easy. As I recall, if you execute that query, the PHP query result accessing functions that would normally give you a key-value pair will have the column names as the keys. But it's been a while since I used PHP so don't hold me to that. :)

MySQL函数description table应该可以得到您想要的位置(将您的表名放在“table”中)。您将不得不解析输出，但这非常简单。我记得，如果您执行这个查询，PHP查询结果访问通常会给您一个键值对的函数将以列名作为键。但是我使用PHP已经有一段时间了，所以不要逼我这么做。:)

#8

There's also this if you prefer:

如果你喜欢，也可以这样:

mysql_query('SHOW COLUMNS FROM tableName');

#9

You may also want to check out mysql_fetch_array(), as in:

您可能还想检查mysql_fetch_array()，如:

$rs = mysql_query($sql);
while ($row = mysql_fetch_array($rs)) {
//$row[0] = 'First Field';
//$row['first_field'] = 'First Field';
}

#10

It's also interesting to note that you can use
EXPLAIN table_name which is synonymous with DESCRIBE table_name and SHOW COLUMNS FROM table_name although EXPLAIN is more commonly used to obtain information about the query execution plan.

同样有趣的是，您可以使用EXPLAIN table_name，它是描述table_name的同义词，并且可以从table_name显示列，尽管解释更常用来获取关于查询执行计划的信息。

#11

The mysql_list_fields function might interest you ; but, as the manual states :

您可能对mysql_list_fields函数感兴趣;但是，正如手册所说:

This function is deprecated. It is preferable to use mysql_query() to issue a SQL SHOW COLUMNS FROM table [LIKE 'name'] statement instead.

这个函数是弃用。最好使用mysql_query()从表[比如'name']语句中发出SQL SHOW列。

#12

I needed column names as a flat array, while the other answers returned associative arrays, so I used:

我需要将列名作为一个平面数组，而其他答案返回关联数组，所以我使用:

$con = mysqli_connect('localhost',$db_user,$db_pw,$db_name);
$table = 'people';

/**
* Get the column names for a mysql table
**/

function get_column_names($con, $table) {
  $sql = 'DESCRIBE '.$table;
  $result = mysqli_query($con, $sql);

  $rows = array();
  while($row = mysqli_fetch_assoc($result)) {
    $rows[] = $row['Field'];
  }

  return $rows;
}

$col_names = function get_column_names($con, $table);

$col_names now equals:

现在美元col_names等于:

(
    [0] => name
    [1] => parent
    [2] => number
    [3] => chart_id
    [4] => type
    [5] => id
)

#13

this worked for me..

这个工作对我来说. .

$sql = "desc MyTableName";
$result = @mysql_query($sql);
while($row = @mysql_fetch_array($result)){
    echo $row[0]."<br>";
}

#14

you can get the entire table structure using following simple command.

您可以使用以下简单命令获得整个表结构。

DESC TableName

or you can use following query.

或者可以使用以下查询。

SHOW COLUMNS FROM TableName

#15

in mysql to get columns details and table structure by following keywords or queries

在mysql中，通过以下关键字或查询获取列细节和表结构

1.DESC table_name

1。DESC table_name

2.DESCRIBE table_name

2。描述table_name

3.SHOW COLUMNS FROM table_name

3所示。从table_name显示列

4.SHOW create table table_name;

4所示。显示创建表table_name;

5.EXPLAIN table_name

5。解释table_name

#16

$col = $db->query("SHOW COLUMNS FROM category");

while ($fildss = $col->fetch_array())
{             
    $filds[] = '"{'.$fildss['Field'].'}"';
    $values[] = '$rows->'.$fildss['Field'].'';
}

if($type == 'value')
{
    return $values = implode(',', $values);
}
else {
     return $filds = implode(',', $filds);
}

#17

I have write a simple php script to fetch table columns through PHP: Show_table_columns.php

我编写了一个简单的php脚本，通过php: Show_table_columns.php获取表列

Enjoy!

享受吧!

#1

403