Parsing MySQL output in Bash

Question

How can I redirect output of mysql query with table border as separator? I try with this code :

mysql -u root -h localhost -D cronjob -e "select * from cron_list" > query.txt

What I expect is output like :

| id |  datetime  | recurrency |          command          |
|  1 | 2014-10-10 |    daily   | bash /media/data/daily.sh |
|  2 | 2014-10-09 |  minutely  |           conky           |

But when I use that code, it give me output like

 id   datetime   recurrency           command          
  1  2014-10-10     daily    bash /media/data/daily.sh 
  2  2014-10-09   minutely             conky

So, how can I redirect the output from terminal into file with separator like in terminal? Sorry for bad english

Try using -t parameter.

hjpotter92
– hjpotter92

2014-10-10 08:37:19 +00:00
Commented Oct 10, 2014 at 8:37 — hjpotter92
– hjpotter92, Commented Oct 10, 2014 at 8:37

fedorqui · Accepted Answer · 2014-10-13 09:27:17Z

12

From man mysql:

--table, -t

Display output in table format. This is the default for interactive use, but can be used to produce table output in batch mode.

Note -t is the default behaviour, but not whenever you pipe to a file. That's why in that case you have to make it explicit.

Other interesting options, for other kind of outputs:

--silent, -s

Silent mode. Produce less output. This option can be given multiple times to produce less and less output.

This option results in nontabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the --raw option.

--skip-column-names, -N

Do not write column names in results.

--html, -H

Produce HTML output.

edited Oct 13, 2014 at 9:27

answered Oct 10, 2014 at 8:45

fedorqui

293k112 gold badges592 silver badges640 bronze badges

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

4 Comments

Lazuardi N Putra Over a year ago

I don't know, when I didn't include -t parameter. It give me output like --silent. By the way, thanks for explanation :)

fedorqui Over a year ago

I have been looking for information of default settings but I cannot find anywhere that sets these defaults. Just guessing: maybe you have an alias?

Michael - sqlbot Over a year ago

"...produce table output in batch mode" appears to mean "whenever STDOUT is not a tty," as is the case with > redirection to a file.

fedorqui Over a year ago

@Michael-sqlbot excellent! This seems to answer the question.

Collectives™ on Stack Overflow

Parsing MySQL output in Bash

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related