Field types for columns in a MySQL query

Question

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

Answer 1

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.

Answer 2

SELECT column_name, data_type,character_maximum_length 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourTable' and TABLE_SCHEMA='yourDBname';

Answer 3

Use INFORMATION_SCHEMA.COLUMNS table to retrive the field type.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tableName';