Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is it possible to get the field type from a MySQL query, in the same way you can get it from a table with the SHOW COLUMNS command?

edit: i should clarify that a bit. I am aware that I can get the field types PER TABLE with the methods suggested below. However I would need the field types from query results that combine fields from multiple tables via Joins and Unions. I was hoping there was something similar to the EXPLAIN command that could do the work for me. I really don't want to parse the SQL query myself, find involved tables and fields and then look up information_schema for the fields I found there...

share|improve this question
up vote 4 down vote accepted

There isn't a comparable way to hand a query to MySQL and ask it to return a resultset containing the names and attributes of the columns that your query will return when it is executed.

However, the library you're using to access MySQL probably has such a mechanism that your code could use... because on the wire, MySQL does return this information to clients with every executed query.

By way of example, the DBD::mysql library in Perl returns arrays of column names and data types.

An executed statement handle returns an array of the column names in @{$sth->{NAME}} and array of the column data types in @{$sth->{mysql_type_name}}. It gets these from functions provided by the MySQL C API, which (as far as I know) is the same underlying code used by a lot of different languages for their MySQL libraries... so I would expect similar structures to be exposed in other environments.

share|improve this answer

Use INFORMATION_SCHEMA.COLUMNS table to retrive the field type.

share|improve this answer
See the comment by @Brilliand on his answer for why this doesn't answer the question. – Max Vernon Jun 2 '15 at 21:19
SELECT column_name, data_type,character_maximum_length 
WHERE TABLE_NAME = 'yourTable' and TABLE_SCHEMA='yourDBname';
share|improve this answer
See the comment by @Brilliand on his answer for why this doesn't answer the question. – Max Vernon Jun 2 '15 at 21:20

Within MySQL, you can get this information by creating a temporary table:

FROM ...
DESCRIBE `temp`;
share|improve this answer
Why not just run DESCRIBE on the original table ??? – RolandoMySQLDBA Apr 1 '14 at 18:51
The column we want to know the type of is a calculated column, not something being directly pulled from a table. (See the OP - this question is specifically about getting the resulting types of an SQL query, without having to go back to the original tables.) – Brilliand Apr 1 '14 at 20:46

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.