CODEWITHSUNDEEP
mysql
JYelton
JYelton

Reputation: 36512

How can I get just the column names for a given mysql query?

I am creating a utility which lets users enter a SQL query for the purposes of importing data to my database.

The first step is to show a list of resulting fields so the user can route them to the destination fields.

When users import from MSSQL, I can use SET FMTONLY ON to fetch the list of output columns that the query would produce if ran (assuming the query is valid in the first place).

I haven't been able to find a way to do this for MySQL. EXPLAIN doesn't list the resulting fields.

Given the following query:

SELECT CONCATENATE(first_name, " ", last_name) AS name, age, foo
FROM customers
ORDER BY name ASC;

I ultimately need to get a list of output fields only, like this:

{ "name", "age", "foo" }

How can I do this in MySQL?

Upvotes: 0

Views: 1479

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

SET FMTONLY ON still requires you to get the column names and types manually, it just generates an empty result set.

For MySQL, add a WHERE FALSE somewhere

SELECT CONCATENATE(first_name, " ", last_name) AS name, age, foo
FROM customers
WHERE FALSE
ORDER BY name ASC;

You get this lovely execution plan

"id";"select_type";"table";"type";"possible_keys";"key";"key_len";"ref";"rows";"Extra"
"1";"SIMPLE";NULL;NULL;NULL;NULL;NULL;NULL;NULL;"Impossible WHERE"

Then parse the columns as you would set fmtonly on with MSSQL

For complex queries (nested, group by, limit-ed), wrap it in a subquery

select * from (
   <your wonderful brilliant complex query>
) x where false

MSSQL would have complained if the inner query contains ORDER BY without TOP, MySQL is ok with it.

Upvotes: 3

vpit3833
vpit3833

Reputation: 7951

I think you need to look at the resultsetmetada. I carries the number of columns, column name, and a few more about the result set.

Upvotes: 1

NightWatchman
NightWatchman

Reputation: 1248

I think you're looking for DESC {table_name}

Upvotes: 0

Related Questions