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.

I would highly suggest you choose this answer: https://dba.stackexchange.com/a/62262/2639 (which is the only way to do it within the official client) or my own https://dba.stackexchange.com/a/203927/2639 (which addresses the C API). The chosen answer, addressing only the Perl bindings from the user's perspective seems off topic here. — Evan Carroll, Apr 14 '18 at 07:10

Brilliand · Accepted Answer · 2018-04-14T15:18:59.307

14

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.

edited Apr 14 '18 at 15:18

answered Apr 01 '14 at 17:22

Brilliand

256
3
6

This is exactly what I am looking for, thanks. – Dean Chiu Mar 17 '19 at 06:55

score 3 · Answer 2 · answered Dec 11 '12 at 23:47

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.

Perl is typing into the MySQL C API through XS – Evan Carroll Apr 14 '18 at 03:43 — Evan Carroll, Apr 14 '18 at 03:43

Evan Carroll · Answer 3 · 2018-04-14T07:12:07.473

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

score -1 · Answer 4 · edited May 02 '16 at 22:37

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:

MySQL metadata function to get projected column type in query?

4 Answers4