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

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

CREATE TEMPORARY TABLE `temp`
SELECT ...
FROM ...
LIMIT 0;
DESCRIBE `temp`;

We cannot just use DESCRIBE on the original table because the column we want to know the type of is a calculated column, not something being directly pulled from a table. The question is specifically about getting the resulting types of a SQL query, without having to go back to the original tables.

share|improve this answer
SELECT column_name, data_type,character_maximum_length 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourTable' and TABLE_SCHEMA='yourDBname';
share|improve this answer

The question, as I read it is about getting details on the columns in a resultset, as opposed to the columns of database tables.

If you are using PHP, there are built-in functions for accessing databases with MYSQLI and PDO which can give you detailed information (data type and so forth) about the columns in a query result (as opposed to the columns of the original tables).

For details about these methods see the PHP documentation:

share|improve this answer

Use INFORMATION_SCHEMA.COLUMNS table to retrive the field type.

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

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.