Mysql command line for vertical output

Question

I want to get mysql query result with vertical output. My problem when using --vertical (or G) is the highlighted lines.

$mysql -N -e 'select filed1, field2 from db.tlb\G' *************************** 1. row *************************** value1_field1 value1_field2 *************************** 2. row *************************** value2_field1 value2_field2 ... 

Is there an option that I did not find to get rid of the lines *** [...] x. row [...] ***?

Right now I'm using egrep -v '^ \ *. * \ * $ ' , but I'm sure a better solution exists.

Answer 1

I'm not sure if this is a good solution, but if you explicitly use egrep annoying, you can define a shell function to start mysql with the right pager. Assuming you are using bash (or compatible):

 # define a shell function to launch mysql with the required _pager_ sh$ mysql() { `which mysql` $* --pager="egrep -v '^\*.*\*$'" ; } [ ... ] # launch mysql "as usual" (in reality, this is the shell function that is invoked) sh$ mysql -u user -p -h mysql-host mydb Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 67 Server version: 5.1.49-3 (Debian) -- Use mysql normally -- but the "egrep pager" will remove the "star lines" mysql> select * from T\G col1: w col2: x col3: 0.4 1 row in set (0.00 sec) 

As I said before, this is not an ideal solution, as egrep blindly removes any "star line" from the output - not just one of the ego ( \G ) commands.

Answer 2

try this (but I can’t imagine why you need such a conclusion)

 mysql -N -B -e 'select * from db' mysql | tr "\t" "\n" 

Answer 3

First option

would change the MySQL pager as follows:

test.sh

 #!/usr/bin/env bash # Some Basic Configuration db="homestead" dbuser="homestead" dbhost="127.0.0.1" # Executes the MySQL command using the basic configuration # This also sets the MySql Pager to less -Sin to enable # it also removes all lines starting with "*" mysql -h $dbhost -u $dbuser -p --pager="grep -Ev '(^\*.*$)|(^$)' | less -Sin" $db 

Using

First change the Config variables:

 $ nano ./test.sh 

Secondly run the script

 $ bash ./test.sh 

Second option

Change the pager after you are already in the MySQL CLI

 $ mysql -u root -p -h 127.0.0.1 somedatabase enter password: mysql> pager grep -Ev '(^\*.*$)|(^$)' | less -Sin