Take the 2-minute tour ×
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.

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
add comment

4 Answers

up vote 3 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
add comment

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

CREATE TEMPORARY TABLE `temp`
SELECT ...
FROM ...
LIMIT 0;
DESCRIBE `temp`;
share|improve this answer
    
Why not just run DESCRIBE on the original table ??? –  RolandoMySQLDBA Apr 1 at 18:51
1  
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 at 20:46
add comment
SELECT column_name, data_type,character_maximum_length 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourTable' and TABLE_SCHEMA='yourDBname';
share|improve this answer
add comment

Use INFORMATION_SCHEMA.COLUMNS table to retrive the field type.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tableName';
share|improve this answer
add comment

Your Answer

 
discard

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.