How to get the datatype of MySQL table columns?

You can get the MySQL table columns data type with the help of “information_schema.columns”.

The syntax is as follows −

SELECT DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where
table_schema = ’yourDatabaseName’ and table_name = ’yourTableName’.

To understand the above syntax, let us first create a table −

mysql> create table DataTypeDemo
−> (
   −> Id int,
   −> Address varchar(200),
   −> Money decimal(10,4)
−> );
Query OK, 0 rows affected (0.60 sec)

Apply the above syntax to get the MySQL columns data type. The query is as follows −

mysql> select data_type from information_schema.columns where table_schema = 'business' and able_name = 'DataTypeDemo';

The following is the output −

+-----------+
| DATA_TYPE |
+-----------+
| int       |
| varchar   |
| decimal   |
+-----------+
3 rows in set (0.00 sec)

If you want, include the column name as well in the output before the datatype. The query is as follows −

mysql> select column_name,data_type from information_schema.columns where table_schema = 'business' and table_name = 'DataTypeDemo';

The following output displays the column name corresponding to the data type −

+-------------+-----------+
| COLUMN_NAME | DATA_TYPE |
+-------------+-----------+
| Id          | int       |
| Address     | varchar   |
| Money       | decimal   |
+-------------+-----------+
3 rows in set (0.00 sec)

Anvi Jain

Updated on 29-Jun-2020 07:17:36

• Related Questions & Answers
• Get the number of columns in a MySQL table?
• How to can I get the names of my MySQL table columns?
• How to get the datatype of a column of a table using JDBC?
• Python Pandas – Get the datatype and DataFrame columns information
• How to get the number of columns of a table using JDBC?
• How to find the number of columns in a MySQL table?
• How can we get a list of columns in an existing MySQL table?
• How to sum rows of VARCHAR datatype or TIME datatype in MySQL?
• How to get the creation date of a MySQL table?
• How to get the greatest of two columns values in MySQL?
• How to select the table with the greatest number of columns in MySQL?
• How to display MySQL Table Name with columns?
• How to list temporary table columns in MySQL?
• How to add columns to an existing MySQL table?
• Do we have any other statement in MySQL instead of SHOW COLUMNS to get the list of columns in an existing table?