php 在 MySQL 中获取表列名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1526688/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get table column names in MySQL?
提问by An employee
Is there a way to grab the columns name of a table in mysql? using php
有没有办法在mysql中获取表的列名？使用 php
回答by Greg
You can use DESCRIBE:
您可以使用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;
回答by OMG Ponies
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
回答by Philip
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 ;)
为死灵感到抱歉，但我喜欢我的功能；)
P.S. I have not included the class Core but you can use your own class.. D.S.
PS我没有包括核心课程，但你可以使用你自己的课程..DS
回答by leela
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--> 更改为您只需要 DESCRIBE 语句的字段值的表名
mysql> SELECT COLUMN_NAME FROM COLUMNS WHERE TABLE_SCHEMA = '<--DATABASE_NAME-->' AND TABLE_NAME='<--TABLENAME-->';
回答by AnthonyS
It's also interesting to note that you can useEXPLAIN table_namewhich is synonymous with DESCRIBE table_nameand SHOW COLUMNS FROM table_namealthough EXPLAINis more commonly used to obtain information about the query execution plan.
值得注意的EXPLAIN table_name是DESCRIBE table_name，SHOW COLUMNS FROM table_name虽然EXPLAIN更常用于获取有关查询执行计划的信息，但您可以使用which 与and同义。
回答by Wolf Metzner
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;
回答by James Goodwin
There's also this if you prefer:
如果您愿意，还有这个：
mysql_query('SHOW COLUMNS FROM tableName');
回答by Andy Hume
Look into:
调查：
mysql_query('DESCRIBE '.$table);
回答by dannysauer
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 函数 describe table 应该可以让你到达你想去的地方（把你的表名放在“表”中）。您必须对输出进行一些解析，但这很容易。我记得，如果您执行该查询，通常会为您提供键值对的 PHP 查询结果访问函数将以列名称作为键。但是自从我使用 PHP 已经有一段时间了，所以不要拘泥于我。:)
回答by thewebguy
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';
}
