CODEWITHSUNDEEP
mysqllinuxbashshell
MacUsers
MacUsers

Reputation: 2229

Put the MySQL command output to a variable and also print on the screen?

I'm really ashamed to ask this question but is there really a way to redirect a MySQL output (e.g. ls -l) to STDOUT and also assign to a variable at the same time? I tried this:

san@pcjq:~$ exec 5>&1
san@pcjq:~$ SQL=$(mysql -uroot -p mysql -e "SELECT Host,User,Password FROM user;" | tee >(cat - >&5))
Enter password: 
Host    User    Password
127.0.0.1   root    *CD0B39440D9701958FDA10ED6B02E3507DC28EB
%   slvUser *8E6F445A9F0E7AA18DA1D830CC61B5AAC9C606
localhost   root    *CD0B39440D9701958FDA10ED6B02E3507DC28EB
localhost   san *8E6F4A10ED6B00E7AA18DA10EDD830CC61B5AAC9C606
localhost   debian-sys-maint    *1BF46B679FC8911CD06712EC782E446F01201CB7

which is not really the exact representation of the output that we normally see on the screen. On the other hand:

SQL=$(mysql -uroot -p mysql -e "SELECT Host,User FROM user;" >&2)

does print the correct output on the screen but noting gets in the variable (for obvious reason). Does anyone know any a workaround? Cheers!!

Upvotes: 1

Views: 2645

Answers (2)

MacUsers
MacUsers

Reputation: 2229

Answering to my own question: 

san@pcjq:~$ SQL=$(mysql -uroot -p mysql -t -e "SELECT Host,User FROM user;" \
| tee /dev/tty) && { echo "This is the ECHO:"; echo "$SQL"; }
Enter password: 
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| %         | slvUser          |
| 127.0.0.1 | root             |
| localhost | debian-sys-maint |
| localhost | root             |
| localhost | santanu          |
+-----------+------------------+
This is the ECHO:
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| %         | slvUser          |
| 127.0.0.1 | root             |
| localhost | debian-sys-maint |
| localhost | root             |
| localhost | santanu          |
+-----------+------------------+

-t is the magic option. From MySQL man page:

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

Cheers!!

Upvotes: 3

Harlin
Harlin

Reputation: 1139

You could do something like

# mysql -u [user] -p[password] mydb -e "select * from [table]" > out.txt
# cat out.txt

or

# echo $(mysql -u [user] -p[password] mydb -e "select * from [table]")

Maybe the password prompt is messing you up here.

*keep in mind you have to do this if 'secret' is your password:

# mysql -u [user] -psecret ....

Hope this helps, Harlin

Upvotes: 0

Related Questions