Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Am frustated by things like:

0:33:1407402356:root@ahost:~# echo 'use wordpress_3_6_1; select * from wp_posts;'
 | mysql -u mysqluser -pmysqlpasswdord | wc -l -L
42   40585

SQL query results being mangled ugly.

PostgreSQL s psql provides the helper feature expanded mode. See it in action:

postgres@ahost:~$ echo '\c openerp7-0 \\ select * from pg_shadow' | psql
You are now connected to database "openerp7-0" as user "postgres".
 usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |          
   passwd                | valuntil | useconfig 
----------+----------+-------------+----------+-----------+---------+----------
-------------------------+----------+-----------
 openerp |    16384 | t           | t        | t         | t       | 
ahash |          | 
 postgres |       10 | t           | t        | t         | t       | 
anotherhash |          | 
(2 rows)

postgres@ahost:~$ echo '\c openerp7-0 \\ \x \\ select * from pg_shadow' |
psql                
You are now connected to database "openerp7-0" as user "postgres".
Expanded display is on.
-[ RECORD 1 ]------------------------------------
usename     | openerp
usesysid    | 16384
usecreatedb | t
usesuper    | t
usecatupd   | t
userepl     | t
passwd      | ahash
valuntil    | 
useconfig   | 
-[ RECORD 2 ]------------------------------------
usename     | postgres
usesysid    | 10
usecreatedb | t
usesuper    | t
usecatupd   | t
userepl     | t
passwd      | anotherhash
valuntil    | 
useconfig   |

That expanded mode is cute for tables with many columns:

postgres@ahost:~$ echo '\c openerp7-0 \\ \x \\ select * from res_partner' |
psql | wc -l -L
223   44423
postgres@ahost:~$ echo '\c openerp7-0 \\ select * from res_partner' | psql |
wc -l -L
  9   94030

Of course that is only when people do not mind mangling up the row count with wc -l.

How can people accomplish for mysql a feature similar to the expanded mode featured in psql?

share|improve this question

1 Answer 1

up vote 6 down vote accepted

If I understand correctly what you want to achieve, you can try it with the \G separator (instead of semicolon), like this:

echo 'SELECT * FROM mytable\G' | mysql -u myuser -p mypassword mydb

Example output:

*************************** 1. row ***************************
    id: 1
locale: de
  name: Afghanistan
*************************** 2. row ***************************
    id: 2
locale: de
  name: Ägypten
share|improve this answer
    
Nice. Obviating the obvious and obviously obviable horizontal aligning difference, it will make OK for me in most cases. –  galegosimpatico Aug 7 at 10:13
1  
So in man mysql they call it the vertical format. But it is related to an ego command, that does the same as go but displaying results using the vertical format. Maybe that e in ego stands for expanded. Guess the terminology comes from earlier closed source RDBMS most notably Oracle. –  galegosimpatico Aug 8 at 7:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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