143

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
  • 102,731
  • 48
  • 279
  • 553

2 Answers2

300

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
  • 15,729
  • 8
  • 46
  • 54
  • 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
  • 6
    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
17

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
  • 601
  • 5
  • 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