1

I am trying to email mysql query results through mail command in unix as below.

mysql -uroot -p*** -e "select query" --table|mail -s "test" abc@example.com

but format is little bit creepy kind of below.

+--------------+-------------+
| PARTNER_NAME | ORDER_COUNT |
+--------------+-------------+
| Legacy | 0 |
| NextJump | 0 |
| Provide | 0 |
+--------------+-------------+

Can you help me with formatting the output if you have any idea

1

The formatting is down to the client in which you are reading the result. If the client is set to fixed width font then the display formatting will be as you expect.

  • I am using microsoft outlook ..so any idea to format the same in outlook? – kasi Nov 16 '16 at 7:11
  • Manually change to a fixed-width font. – Rick James Nov 16 '16 at 17:56
0

If you want to directly pipe it to mail you can

A) use the -B flag. The help is a bit confusing by saying:

-B, --batch Don't use history file. Disable interactive behavior.

but what it produces is this:

[root@server ~]# mysql -B -e 'select * from cities limit 2' test
id     name state_id
5562        20
5563   asd  20

B) format to html:

[root@server ~]# mysql -e 'select * from cities limit 2' test| sed -e 's/\|//g' | awk '{a++; if(a<2){print "<tr><th>" $1 "</th><th>" $2 "</th></tr>";} else { print "<tr><td>" $1 "</td><td>" $2 "</td></tr>"; }}'
<tr><th>id</th><th>name</th></tr>
<tr><td>5562</td><td>20</td></tr>
<tr><td>5563</td><td>asd</td></tr>

You still need to wrap this into <table> ... </table> and make sure your mail is sent as proper html otherwise it will be only be creepier.

As it was described by Baron Schwartz: https://www.xaprb.com/blog/2006/04/13/using-awk-to-convert-mysql-output-to-tables/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.