MySQL metadata function to get projected column type in 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? Such as from a derived table,

SELECT x -- presumedMetaFn(x) -- returns "int"
FROM (
  SELECT 1 AS x
  UNION SELECT 2
) AS t;

Given the above query, is there a function or something that I can use to get the type of x? PostgreSQL makes this available with the System Information Functions pg_typeof

SELECT x, pg_typeof(x)
FROM ( VALUES (1),(2) ) AS t(x);
 x | pg_typeof 
---+-----------
 1 | integer
 2 | integer
(2 rows)

I am not looking for metadata on a table, but from the result of query. With psql on PostgreSQL 11+, this is also possible by running \gdesc after the query.

Answer 1

Within MySQL, you can get this information by creating a temporary table, then using DESCRIBE on that 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. Likewise, we cannot use DESCRIBE directly on the query, because DESCRIBE can only be used on tables. Creating a temporary table solves both of those problems.

Answer 2

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 3

I believe this data is available through the C API and documented in C API Prepared Statement Type Codes but that the server has no such ability to provide it to the end user,

The buffer_type member of MYSQL_BIND structures indicates the data type of the C language variable bound to a statement parameter or result set column. For input, buffer_type indicates the type of the variable containing the value to be sent to the server. For output, it indicates the type of the variable into which a value received from the server should be stored.

For a table of the SQL types, and the corresponding buffer_type value see this chart.

The only work around is to CREATE TEMPORARY TABLE AS SELECT (CTAS) ... DESCRIBE

Answer 4

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:

• PDOStatement::getColumnMeta
• mysqli_result::fetch_field_direct