在MySQL中获取表列名?

调查:

mysql_query('DESCRIBE '.$table);

MySQL函数 描述表 应该把你带到你想去的地方(把你的表名输入“table”)。您必须对输出进行一些解析，但这非常简单。我记得，如果执行该查询，PHP查询结果访问函数将以列名作为键，该函数通常会提供一个键-值对。但我已经有一段时间没有使用PHP了，所以不要强迫我这么做。:) < / p >

下面的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']

参考:INFORMATION_SCHEMA列

如果你喜欢的话，还有这个:

mysql_query('SHOW COLUMNS FROM tableName');

mysql_list_fields函数可能会让你感兴趣;但是，正如手册所述:

该函数不支持。它是 最好使用mysql_query()来 而不是使用SQL SHOW COLUMNS FROM table [LIKE 'name']语句

你可以使用描述:

DESCRIBE my_table;

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

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

或者你可以使用显示列:

SHOW COLUMNS FROM my_table;

或者在一行中获取带逗号的列名:

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

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

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

我做了一个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);
}
}

输出将是一个数组:

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

对不起死灵，但我喜欢我的功能;)

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

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

mysql>USE information_schema;

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

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

这个怎么样:

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;

这对我很有效。

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

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

DESC TableName

或者您可以使用以下查询。

SHOW COLUMNS FROM TableName

在mysql中，通过关键字或查询来获取列的详细信息和表的结构

1. __abc0

2. __abc0

3. __abc0

4. __abc0

5. __abc0

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

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

$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现在等于:

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

<?php
$db = 'Database'; //Database name
$host = 'Database_host'; //Hostname or Server ip
$user = 'USER'; //Database user
$pass = 'Password'; //Database user password
$con = mysql_connect($host, $user, $pass);
if ($con) {
$link = mysql_select_db($db) or die("no database") . mysql_error();
$count = 0;
if ($link) {
$sql = "
SELECT column_name
FROM   information_schema.columns
WHERE  table_schema = '$db'
AND table_name = 'table_name'"; // Change the table_name your own table name
$result = mysql_query($sql, $con);
if (mysql_query($sql, $con)) {
echo $sql . "<br> <br>";
while ($row = mysql_fetch_row($result)) {
echo "COLUMN " . ++$count . ": {$row[0]}<br>";
$table_name = $row[0];
}
echo "<br>Total No. of COLUMNS: " . $count;
} else {
echo "Error in query.";
}
} else {
echo "Database not found.";
}
} else {
echo "Connection Failed.";
}
?>

享受吧!

Mysqli fetch_field()为我工作:

if ($result = $mysqli -> query($sql)) {
// Get field information for all fields
while ($fieldinfo = $result -> fetch_field()) {
printf("Name: %s\n", $fieldinfo -> name);
printf("Table: %s\n", $fieldinfo -> table);
printf("Max. Len: %d\n", $fieldinfo -> max_length);
}
$result -> free_result();
}

来源:https://www.w3schools.com/pHP/func_mysqli_fetch_field.asp

如果使用assoc加载结果，简单的方法是这样做:

$sql = "SELECT p.* FROM (SELECT 1) as dummy LEFT JOIN `product_table` p on null";
$q = $this->db->query($sql);
$column_names = array_keys($q->row);

Array(
'product_id' => null,
'sku' => null,
'price' => null,
...
)

之后，您可以使用PHP函数array_keys($result)轻松获取表的列名。