7

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.

1
11

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.

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

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.

1
0

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

-1

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:

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.