How to get the mysql table columns data type?

Question

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).

To get data types of all columns:

describe table_name

or just a single column:

describe table_name column_name

Please use the below mysql query.

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
FROM information_schema.columns 
WHERE table_schema = '<DATABASE NAME>' 
AND table_name = '<TABLE NAME>' 
AND COLUMN_NAME = '<COLOMN NAME>' 

Refer this link

mysql> SHOW COLUMNS FROM mytable FROM mydb;
mysql> SHOW COLUMNS FROM mydb.mytable;

Hope this may help you

First select the Database using use testDB; then execute

desc `testDB`.`images`;
-- or
SHOW FIELDS FROM images;

Output: