Simple way to print structured SQL SELECT in bash

Question

I'm trying to echo the output of a SELECT request in bash, in a structured form with column names. The issue is that i cannot do it properly with more than 2 fields or if the value is larger that the column name.

Example :

My table looks like this : value1 value2

If i do in bash : echo "select value1, value2 from table" | mysql -uUSER -pPASS

The result looks like this in bash :

value1  value2
a       d  
b       e
c       f

Now if i have 3 fields or a large value the result looks like this :

value1 value2 value3
aaaaaaaaa ddddddddddd ggg
bbbb      eeeeeeeee   hhhh
ccccccc   fffffffff   iiii

Is there a simple way to have a structured result ? I mean with column names correctly spaced? I know it is possible to do it with a sort to get the largest value and add the number of spaces needed but it seems to be too much for a simple problem like this.

Do you have an idea? Thanks !

Answer 1

Use the mysql -e option to execute your query, and -t to print table output to stdout:

mysql -uUSER -t -e "select value1, value2, value3 from table" -pPASS

Your output will look something like this:

+-----------+-------------+--------+
| value1    | value2      | value3 |
+-----------+-------------+--------+
| aaaaaaaaa | ddddddddddd | ggg    |
| bbbb      | eeeeeeeee   | hhhh   |
| ccccccc   | fffffffff   | iiii   |
+-----------+-------------+--------+

From the mysql manpage:

--execute=statement, -e statement

       Execute the statement and quit. The default output format is like that produced with --batch. See Section 4.2.3.1, “Using Options on
       the Command Line”, for some examples. With this option, mysql does not use the history file.

and

-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.

Answer 2

echo "select value1, value2 from table" | mysql -uUSER -pPASS | column -t