How to select only fieldname when using show columns query in mysql

Question

I use this query to select fields in a given table. Is it possible to select only the fieldname and not the whole structure of the table?

SHOW COLUMNS FROM student

Answer 1

You're trying to determine the table structure? You can query MySQL's information_schema database directly for the fieldnames:

select COLUMN_NAME from information_schema.COLUMNS where TABLE_NAME='student';

Answer 2

The solution mentioned here earlier is not the correct one. Example:

CREATE DATABASE db1;
CREATE DATABASE db2;
CREATE TABLE db1.t ( id_1 INT);
CREATE TABLE db2.t ( id_2 INT);
SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME  ='t';

This will display:

+-------------+
| COLUMN_NAME |
+-------------+
| id_1        |
| id_2        |
+-------------+

suggesting that the table t has two column which is obviously not true. This query lists all the columns of the tables called t in all of your databases.

Instead, you should specify which database contains the table t you want to select the column names from:

SELECT COLUMN_NAME 
    FROM information_schema.COLUMNS 
    WHERE 
        TABLE_NAME = 't' AND 
        TABLE_SCHEMA = 'db1';