Print MySQL column names for empty result set

Question

I am running some SQL queries from command line as follows:

cat my_query.sql | mysql --defaults-file=my_conf.cnf

I need to print column names whether the query returns any data or not. Currently when there is no data to return, i.e when query returns an empty result set this command does not print anything.

For example my query is:

-- id=-1 doesn't exist
SELECT col1, col2 FROM table WHERE id=-1

I need this query to return

col1    col2

Instead it returns nothing. Is it possible to do that using purely mysql and standard unix commands?

Thanks.

You could take the intended query and UNION ALL a "dummy" SELECT emulating an empty row after it. UNION results use the data types and column names of the first query of the UNION. — Uueerdo
– Uueerdo, Commented May 12, 2015 at 22:29
I guess you suggest to add a dummy SELECT like SELECT '',''. It would work but then it will add a new line and a few tab characters at the end of the results. It's not the cleanest solution but it might work. I was looking for a mysql command option to do that but there may not be one. Thanks. — burhan
– burhan, Commented May 12, 2015 at 22:42
Yes, it is not the ideal solution, but if you always have that, you know to always ignore the last line, or you could give your dummy SELECT some blatant "flag" values like '[cmdlinedummy]' for strings. — Uueerdo
– Uueerdo, Commented May 12, 2015 at 22:45

Uueerdo · Accepted Answer · 2015-05-12 22:42:23Z

3

Adding a UNION ALL to a SELECT with "dummy"/blank data might work:

SELECT col1, col2 FROM table WHERE id=-1
UNION ALL
SELECT '', '' -- OR 0, 0 whatever is appropriate
;

I don't run queries from the command line, so this is assuming it normally would give you the column names if you had at least one row in the results.

answered May 12, 2015 at 22:42

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

burhan Over a year ago

I used your idea and added this to remove the last line. "| sed '$ d' "

Collectives™ on Stack Overflow

Print MySQL column names for empty result set

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related