How can I suppress column header output for a single SQL statement?

Question

I'm executing some SQL statements in batch (using the mysql command-line binary). I want one of my several SELECT statements to not print the column headers, just the selected records. Is this possible?

Gratitude

Answer 1

Invoke mysql with the -N (the alias for -N is --skip-column-names) option:

mysql -N ...
use testdb;
select * from names;

+------+-------+
|    1 | pete  |
|    2 | john  |
|    3 | mike  |
+------+-------+
3 rows in set (0.00 sec)

Credit to ErichBSchulz for pointing out the -N alias.

To remove the grid (the vertical and horizontal lines) around the results use -s (--silent). Columns are separated with a TAB character.

mysql -s ...
use testdb;
select * from names;

id  name
1   pete
2   john
3   mike

To output the data with no headers and no grid just use both -s and -N.

mysql -sN ...

Iridescent

-sN worked well for me to assign the output to a variable in a script: TABLES=$(mysql -sN -u $DB_USER -p$DB_PASS... – Julide

This applies to the entire session, not just to a single SQL statement. Oracle SQLPlus has set feedback on and set feedback off which can be used anywhere in a session. Does MySQL have an equivalent? Looks like that's what OP was looking for. – Desdemona

just a brief comment, simplify using select * from testdb.names; without explicit 'use'. – Pellucid

The long option for -s is --silent, for -N --skip-column-names. -B or --batch also works well instead of -s. – Boatwright

Answer 2

You can fake it like this:

-- with column headings 
select column1, column2 from some_table;

-- without column headings
select column1 as '', column2 as '' from some_table;

Bucket

Error: Type mismatch: expected type string, but got error with empty alias – Primatology

Looks like that error is coming from MySQL Workbench, not from MySQL. Anyway you can also use a single blank space instead of an empty string, and that seems to work in MySQL Workbench: select column1 as ' ', column2 as ' ' from some_table; – Bucket

this approach leaves an initial blank line that may need to be removed. – Nightrider

Why not do both of these at once? SELECT column1 AS '', column2 AS '' FROM (SELECT column1, column2 FROM some_table) AS tbl; – Gratitude

Answer 3

A good reason to "... want ... SELECT statements to not print the column headers..." is for documenting output.

Thanks to @tom_warfield I do this:

select "Relevant details from Stock Entry." as ""\G
select 
      SE.name
    , SED.item_code
    , SED.s_warehouse
    , SED.t_warehouse
    , REPLACE(SED.serial_no,'\n',', ') as serial_no
from
    `tabStock Entry` SE left join `tabStock Entry Detail` SED 
    :
    :

My output then looks like this:

*************************** 1. row ***************************
: Relevant details from Stock Entry.
+--------------------+-------------------------------+--------------------------+----------------------------+---------------------------------------------------------------------------------------------------------------------+
| name               | item_code                     | s_warehouse              | t_warehouse                | serial_no                                                                                                           |

Note that "\G" instead of ";" outputs one attribute per line, rather than one row per line.

Magnus