4

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?

  • Now am in the need of something like expanded mode, for Oracle sqlplus... – 178024 Jul 24 '15 at 6:24
7

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
  • Nice. Obviating the obvious and obviously obviable horizontal aligning difference, it will make OK for me in most cases. – 178024 Aug 7 '14 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. – 178024 Aug 8 '14 at 7:51

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.