149

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?

einpoklum
  • 118,144
  • 57
  • 340
  • 684

3 Answers3

317

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 ...
suspectus
  • 16,548
  • 8
  • 49
  • 57
  • 5
    -sN worked well for me to assign the output to a variable in a script: `TABLES=$(mysql -sN -u $DB_USER -p$DB_PASS`... – Michael J Apr 28 '16 at 20:23
  • 8
    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. – codeforester Mar 13 '18 at 17:24
  • just a brief comment, simplify using **select * from testdb.names;** without explicit 'use'. – fcm Mar 25 '19 at 12:02
  • 4
    The long option for -s is --silent, for -N --skip-column-names. -B or --batch also works well instead of -s. – Ale Jan 20 '21 at 09:00
21

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;
Tom Warfield
  • 721
  • 6
  • 8
  • `Error: Type mismatch: expected type string, but got` error with empty alias – QkiZ Jun 04 '20 at 13:16
  • 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;` – Tom Warfield Jun 07 '20 at 14:22
  • this approach leaves an initial blank line that may need to be removed. – edwardsmarkf Jun 12 '22 at 01:11
0

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.

Martin Bramwell
  • 2,003
  • 2
  • 19
  • 35