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 ?

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> create table </span><span class="typ">DataTypeDemo</span><span class="pln">
</span><span class="pun">?></span><span class="pln"> </span><span class="pun">(</span><span class="pln">
   </span><span class="pun">?></span><span class="pln"> </span><span class="typ">Id</span><span class="pln"> </span><span class="kwd">int</span><span class="pun">,</span><span class="pln">
   </span><span class="pun">?></span><span class="pln"> </span><span class="typ">Address</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">200</span><span class="pun">),</span><span class="pln">
   </span><span class="pun">?></span><span class="pln"> </span><span class="typ">Money</span><span class="pln"> </span><span class="kwd">decimal</span><span class="pun">(</span><span class="lit">10</span><span class="pun">,</span><span class="lit">4</span><span class="pun">)</span><span class="pln">
</span><span class="pun">?></span><span class="pln"> </span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">0</span><span class="pln"> rows affected </span><span class="pun">(</span><span class="lit">0.60</span><span class="pln"> sec</span><span class="pun">)</span>

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

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> data_type </span><span class="kwd">from</span><span class="pln"> information_schema</span><span class="pun">.</span><span class="pln">columns </span><span class="kwd">where</span><span class="pln"> table_schema </span><span class="pun">=</span><span class="pln"> </span><span class="str">'business'</span><span class="pln"> </span><span class="kwd">and</span><span class="pln"> able_name </span><span class="pun">=</span><span class="pln"> </span><span class="str">'DataTypeDemo'</span><span class="pun">;</span>

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 ?

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> column_name</span><span class="pun">,</span><span class="pln">data_type </span><span class="kwd">from</span><span class="pln"> information_schema</span><span class="pun">.</span><span class="pln">columns </span><span class="kwd">where</span><span class="pln"> table_schema </span><span class="pun">=</span><span class="pln"> </span><span class="str">'business'</span><span class="pln"> </span><span class="kwd">and</span><span class="pln"> table_name </span><span class="pun">=</span><span class="pln"> </span><span class="str">'DataTypeDemo'</span><span class="pun">;</span>

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)