How to get the mysql table columns data type?

I want to get the column data type of a mysql table.

Thought I could use MYSQLFIELD structure but it was enumerated field types.

Then I tried with mysql_real_query()

The error which i am getting is query was empty

How do I get the column data type?


You can use the information_schema columns table:

SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE table_name = 'tbl_name' AND COLUMN_NAME = 'col_name';

The query below returns a list of information about each field, including the MySQL field type. Here is an example:

SHOW FIELDS FROM tablename
/* returns "Field", "Type", "Null", "Key", "Default", "Extras" */

See this manual page.


Most answers are duplicates, it might be useful to group them. Basically two simple options have been proposed.

First option

The first option has 4 different aliases, some of which are quite short :

EXPLAIN db_name.table_name;
DESCRIBE db_name.table_name;
SHOW FIELDS FROM db_name.table_name;
SHOW COLUMNS FROM db_name.table_name;

(NB : as an alternative to db_name.table_name, one can use a second FROM : db_name FROM table_name).

This gives something like :

+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| product_id       | int(11)      | NO   | PRI | NULL    |       |
| name             | varchar(255) | NO   | MUL | NULL    |       |
| description      | text         | NO   |     | NULL    |       |
| meta_title       | varchar(255) | NO   |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+

Second option

The second option is a bit longer :

SELECT
  COLUMN_NAME, DATA_TYPE 
FROM
  INFORMATION_SCHEMA.COLUMNS 
WHERE
  TABLE_SCHEMA = 'db_name'
AND
  TABLE_NAME = 'table_name';

It is also less talkative :

+------------------+-----------+
| column_name      | DATA_TYPE |
+------------------+-----------+
| product_id       | int       |
| name             | varchar   |
| description      | text      |
| meta_title       | varchar   |
+------------------+-----------+

It has the advantage of allowing selection per column, though, using AND COLUMN_NAME = 'column_name' (or like).